Re: basic question about analytic functions

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 21 Feb 2003 14:35:47 -0800
Message-ID: <130ba93a.0302211435.4b45734b@posting.google.com>

I would try not to quote Tom out of the context. There are 4 examples in that cpater in his book where he compares stardard SQL with ones that use AFs, none of them are simple aggregate as it is in your case. I would be very interested in seeing your tkprof output of the two queries that you ran. Can you post it?

I did a simple test and found out that, no surprise here, if you use AFs needlessly, you simply kill the performance.

select department_id, sum(salary)
from
t1 group by department_id order by department_id

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2      0.00       1.37        901       1383          0  
       12

------- ------ -------- ---------- ---------- ---------- ----------

total        4      0.00       1.37        901       1383          0  
       12

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61

Rows Row Source Operation

-------  ---------------------------------------------------
     12  SORT GROUP BY

876544 TABLE ACCESS FULL T1

select distinct DEPARTMENT_ID, sum(salary) over (partition by DEPARTMENT_ID)
from
t1

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2      6.79      12.69       6051       1383         32  
       12

------- ------ -------- ---------- ---------- ---------- ----------

total        4      6.79      12.69       6051       1383         32  
       12

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 61

Rows Row Source Operation

-------  ---------------------------------------------------
     12  SORT UNIQUE

876544 WINDOW SORT
876544 TABLE ACCESS FULL T1 As can be seen, on a table with close to 1 million rows, you can get a simple aggregate quickly with close to 0 CPU time. If you use AF to get it the CPU time jumps to 6.79. You try to use AFs to avoid convoluted standard SQLs. The opposite is also true - you don't want to convert a simple standard SQL into a convoluted SQL with AFs. That's the point I am trying to make.

Jusung Yang

udo456_at_yahoo.com (Udo Diekmann) wrote in message news:<5757f268.0302201242.39bdbd7_at_posting.google.com>...
> 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 Fri Feb 21 2003 - 16:35:47 CST