 | |
#11
 
| |||
| |||
|
Re: build a query -
08-18-2011
, 10:32 AM
|
In article <1313671608... (AT) user (DOT) newsoffice.de>, mosmann@expires-31-08- 2011.news-group.org says... 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? of analytics All editions if I am not very much mistaken. -- jeremy K1200S |
additional licensing.
David Fitzjarrell
#12
| |||
| |||
|
|
ddf schrieb am 18.08.2011 in d6e122f6-d480-4ecc-a6d1-0cff4d33e... (AT) bl1g2000vbb (DOT) 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 |
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/...: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..._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
--
@home.com is bogus.
Shades o' Canter and Siegal: http://www.signonsandiego.com/news/2...n-card-online/
#13
| |||
| |||
|
|
David Fitzjarrell hi David, thanks a lot for this demo, i'll try it and i come back to you if i can't understand something Regards Paolo |
#14
| |||
| |||
|
|
On Aug 18, 5:53*am, Jeremy <jeremy0... (AT) gmail (DOT) com> wrote: In article <1313671608... (AT) user (DOT) newsoffice.de>, mosmann@expires-31-08- 2011.news-group.org says... 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? of analytics All editions if I am not very much mistaken. -- jeremy K1200S AFAIK it's available in all editions, including XE, and needs no additional licensing. |
Quote:
|
David Fitzjarrell |
Andreas
--
wenn email, dann AndreasMosmann <bei> web <punkt> de
#15
| |||
| |||
|
|
On Aug 18, 5:46*am, Andreas Mosmann <mosm... (AT) expires-31-08-2011 (DOT) news- group.org> wrote: ddf schrieb am 18.08.2011 in d6e122f6-d480-4ecc-a6d1-0cff4d33e... (AT) bl1g2000vbb (DOT) 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/...: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..._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 -- @home.com is bogus. Shades o' Canter and Siegal: http://www.signonsandiego.com/news/2...n-card-online/ |
there are some functions I use very often (count/lead/lag) and so I am
familiar with them, but sometimes I need more and it is a little
difficult to find good information. Seems that there are only few people
using it. (Not that few, as spatial ...)
Andreas
--
wenn email, dann AndreasMosmann <bei> web <punkt> de
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.