Re: basic question about analytic functions

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0302192205.5e192c08_at_posting.google.com>...
> Well, I may ask you where you heard that from. Analytical functions
> provide "windowing" capability that is either difficult or impossible
> to achieve with regular SQL. I would suggest that you read up on the
> ORACLE DOC and see what it is all about. With analytical functions you
> can calculate aggregates, such as SUM, in many different ways. You can
> even massage it so that it gives you the same output as your regular
> aggregate SQLs. But it really serves no purpose.
>
> - Jusung Yang

Where I got it from was Tom Kyte's Expert One on One. He has a whole chapter devoted to analytic functions, and his main point from very his first example is:

"You get the same exact answer from both queries, but what a difference these [analytic] functions can make. The run time is man times longer [with the standard query] and the number of logical I/Os is increased by many orders of magnitude. The analytical functions processed the resultset using significantly fewer resources and an astoundingly reduced amount of time [>90% execution improvement in his example]."

So this piqued my curiosity and thus the question about performance.

Thanks for the help and translation, the same analytic query provided improved performance in my own case (> 1.2M rows) by 25%.

I wonder if anyone has any knowledge of caching and analytic queries. It does seem that the amount of time to re-execute the analytic query is quite fixed (no noticable subsequent improvement), whereas repeated standard queries seem to show evidence of caching (substantial improvement).

Cheers,

Udo. Received on Thu Feb 20 2003 - 14:42:57 CST