While preparing my paper for ODTUG, “One Analytic Function can do more than a 1000 lines of code” I stated that you should be careful not to get carried away using Analytic Functions when there is no need to do so. The example I give is the following:
SQL> select distinct 2 ename 3 from (select ename 4 , Row_Number() over (partition by ename 5 order by null 6 ) rn 7 from big_emp 8 ) 9 where rn > 1; ENAME ---------- ADAMS ALLEN BLAKE
A more traditional way to fulfill the same requirement is a query like;
SQL> select ename 2 from big_emp 3 group by ename 4 having Count(*) > 1; ENAME ---------- ADAMS ALLEN BLAKE
My proof reader made the following comment: Which one performs better? Good question, I was merely focussed on the number of lines you had to type in when comparing Analytic Functions to the traditional way of writing this query. In order to provide an answer I did some tests.
The BIG_EMP table has a record count of a little over 200.000. It is simply a copy of Scott’s EMP table with the same data inserted into it over and over.
SQL> select count(*)
2 from big_emp
3 /
COUNT(*)
----------
229376
When the timing is set on in SQL*Plus, I got these results
SQL> select distinct 2 ename 3 from (select ename 4 , Row_Number() over (partition by ename 5 order by null 6 ) rn 7 from big_emp 8 ) 9 where rn > 1; Elapsed: 00:00:01.70 SQL> SQL> select ename 2 from big_emp 3 group by ename 4 having Count(*) > 1; Elapsed: 00:00:00.53 SQL>
On my laptop the traditional way is faster. Now the question arises why is the traditional way faster?
Why? And prove it!
Analytic Functions are applied after the Joins, Where conditions, Group By and Having clauses are done. Since we are using a Group By in the traditional query, we are done before any Analytic counterpart can start. There are simply less steps to take.
Looking at the TKProf output from both queries, we see this:
********************************************************************************
select distinct
ename
from (select ename
, Row_Number() over (partition by ename
order by null
) rn
from big_emp
)
where rn > 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.03 0 0 0 0
Fetch 2 1.01 2.54 1809 1383 10 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.01 2.58 1809 1383 10 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
------- ---------------------------------------------------
14 SORT UNIQUE NOSORT (cr=1383 pr=1809 pw=431 time=1845188 us)
229362 VIEW (cr=1383 pr=1809 pw=431 time=3383113 us)
229376 WINDOW SORT (cr=1383 pr=1809 pw=431 time=3154001 us)
229376 TABLE ACCESS FULL BIG_EMP (cr=1383 pr=1378 pw=0 time=707223 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 96 0.04 0.60
direct path write temp 82 0.00 0.00
direct path read temp 108 0.02 0.16
SQL*Net message from client 2 0.00 0.00
********************************************************************************
select ename
from big_emp
group by ename
having Count(*) > 1
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.20 0.49 1369 1383 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.20 0.50 1369 1383 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
------- ---------------------------------------------------
14 FILTER (cr=1383 pr=1369 pw=0 time=499152 us)
14 SORT GROUP BY (cr=1383 pr=1369 pw=0 time=499224 us)
229376 TABLE ACCESS FULL BIG_EMP (cr=1383 pr=1369 pw=0 time=705140 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 96 0.01 0.35
SQL*Net message from client 2 0.00 0.00
********************************************************************************
What I thought was very interesting to see is the way the Analytic Query is carried out. In the Row Source operation-section you can the steps that are necessary to perform the query. The number of rows passed from one step to the next stays pretty high, except for the final step the SORT UNIQUE NOSORT.
You can also see evidence of the way Analytic Functions are applied to the intermediate result set. The italic lines in the Wait Event section shows you how the intermediate result set is read from disk to the PGA. (The direct path write temp and direct path read temp wait events.) While it is doing this, you have to wait. This explains the time difference between the two queries.
Conclusion
Analytic Functions are really cool, but sometimes the traditional way performs better. Always check your query when you are using Analytic Functions and a Distinct to get the final result. There just might be an easier, and better performing, way like using a Group By.
And of course, it’s always good to have someone proofread and ask questions. Thank you, Lucas.
As usual the demo script can be downloaded here: Demo Script The support scripts (TK and Trace) can be found here.
Follow Up to the comment by Mr. Ed:
The TKProf shown in this blog is a straight copy-paste action. But I agree, it looks strange.
According to Tom Kyte, the values in the TKProf report are aggregated, unless you specify otherwise. When I run the test, but specify not to aggregate (aggregate=NO), the TKProf reports shows
select distinct
ename
from (select ename
, Row_Number() over (partition by ename
order by null
) rn
from big_emp
)
where rn > 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.06 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.75 2.13 1029 1383 10 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.75 2.20 1029 1384 10 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
------- ---------------------------------------------------
14 SORT UNIQUE NOSORT (cr=1383 pr=1029 pw=432 time=1623965 us)
229362 VIEW (cr=1383 pr=1029 pw=432 time=2701586 us)
229376 WINDOW SORT (cr=1383 pr=1029 pw=432 time=2701829 us)
229376 TABLE ACCESS FULL BIG_EMP (cr=1383 pr=597 pw=0 time=688209 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 3 0.01 0.01
db file scattered read 46 0.06 0.38
direct path write temp 85 0.01 0.02
direct path read temp 105 0.07 0.23
SQL*Net message from client 2 0.00 0.00
********************************************************************************
When you add the time-values (688209 + 2701829), you will find that the result (3390038) is pretty close to the original TKProf report.
Now I’m curious, what does your TKProf show?
First of all, thank you for your comments.
To Mr. Ed:
My response to your comment is included in the blog. Because it is not possible to format the TKProf in this comment section (markup is not allowed) I added it to the blog.
To Karl R.:
The database I use on my laptop, where I did these tests is a 10.1.0.2.0 (10g Release 1). It’s the Personal Edition that I have.
Very nice article.
You see in RowSource that a window sort does not reduce the number rows as a group by does and that’s what i think the direct write read is probably the storing of the result of the window sort operation in temp.
Greetings
Karl
PS.: which database version you used?
Something is fishy about your tkprof. Look at this line:
229376 WINDOW SORT (cr=1383 pr=1809 pw=431 time=3154001 us)
The “time” is over 3 seconds. That’s longer than the elapsed time for the entire query!