Identifying Duplicates: Traditional vs. Analytical.

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.
Query Flow

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?

3 Comments

  1. Alex Nuijten May 7, 2006
  2. Karl r. May 5, 2006
  3. Mr. Ed May 5, 2006