Re: build a query
Date: Thu, 18 Aug 2011 09:41:25 -0700 (PDT)
Message-ID: <6afde31e-02a3-49c0-9a67-8748180b29db_at_j9g2000prj.googlegroups.com>
On Aug 18, 5:46 am, Andreas Mosmann <mosm..._at_expires-31-08-2011.news- group.org> wrote:
> ddf schrieb am 18.08.2011 in
> <d6e122f6-d480-4ecc-a6d1-0cff4d33e..._at_bl1g2000vbb.googlegroups.com>:
>
> > SQL> select ref, prev_loc, location, prev_loc_obj_ct, object_count
> > 2 from
> > 3 (select ref,
> > 4 nvl(lag(ref) over (order by ref, location), ref)
> > prev_ref,
> > 5 nvl(lag(location) over (order by ref, location), ref)
> > prev_loc,
> > 6 location,
> > 7 object_count,
> > 8 nvl(lag(object_count) over (order by ref, location),
> > object_count) prev_loc_obj_ct
> > 9 from location)
> > 10 where nvl(prev_loc_obj_ct, object_count) <> object_count
> > 11 and prev_ref = ref
> > 12 /
>
> ..
>
> > David Fitzjarrell
>
> I love this kind of sql. It seems to me to be oversized in this case,
> but in general it is great. It makes many things easier and faster.
>
> Do you know what kind of license you need to use these analytic
> functions?
> Is the enterprise license enough or is there more to be licensed?
> From which Oracle version these functions exist?
>
> Thanks in advance
> Andreas
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de
These kinds of functions are called analytics, and are included in all editions as part of the sql language. There is a more advanced analytics option called OLAP, extra cost only available in EE.
Some functions go way back, others have been added over time. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3170642805938 shows an 8i question, 9 years ago - many good examples throughout asktom. FAQ says 8.1.6 for lag and lead, etc. http://www.orafaq.com/wiki/Oracle_8i#Oracle_8i_.288.1.6.29_-_November_1999
I don't use analytics enough to keep them straight, I've found these links good to remind me when I need them:
http://www.orafaq.com/node/1874
http://psoug.org/reference/analytic_functions.html
Most people say analytics rock, since it often reduces what would be many scans to one, I mostly agree, but I think there may be some cases where the performance can still be better with procedural processing. But as Tom and others often say, if you can do it in the sql engine, that's likely to be best. I surely agree with that, much better to deal with exceptional cases as needed. I don't know that anyone has figured out how to determine the exceptional cases beforehand.
jg
-- _at_home.com is bogus. Shades o' Canter and Siegal: http://www.signonsandiego.com/news/2011/aug/18/can-you-get-green-card-online/Received on Thu Aug 18 2011 - 11:41:25 CDT