9 Replies Latest reply: May 5, 2014 2:58 AM by 1057347

Which one is faster

Hi All,

I have two query to find 2nd highest salary.

Please explain Which one is faster one and why ?

Analytic function:

select *  from

(

select ename,sal,dense_rank() over (order by sal desc) ranking    from   emp

)  where ranking = 2

Aggregate Function:

SELECT max(sal) FROM (SELECT sal FROM emp WHERE sal NOT IN (SELECT max(sal) FROM emp)).

Thanks,

• 1. Re: Which one is faster

Since they give different answers and different data, what does it matter which one is faster?

• 2. Re: Which one is faster

Hi John,

I mean to ask if we have to find 2nd highest salary from table then we can find it by either Aggregate or Analytical function..

which one is better and faster ?

I have corrected the Query .

Analytic function:

select sal  from

(

select sal,dense_rank() over (order by sal desc) ranking    from   emp

)  where ranking = 2

Aggregate Function:

SELECT max(sal) FROM (SELECT sal FROM emp WHERE sal NOT IN (SELECT max(sal) FROM emp)).

Thanks,

• 3. Re: Which one is faster

"it depends"

The analytic function can retrieve all the data on the row easily, so you can see all of the employee information for those who make the second-highest salary. I'd probably be using that one anyway, as it's more concise to code.

• 4. Re: Which one is faster

You can check it for yourself. Look for the Cost in the execution plan and the consistent gets in the statistics.

SQL> select *
2    from (
3           select e.*
4                , dense_rank() over (order by sal desc) ranking
5             from emp e
6         )
7   where ranking = 2;

EMPNO ENAME  JOB              MGR HIREDATE         SAL        COM     DEPTNO    RANKING
---------- ------ --------- ---------- --------- ---------- ---------- ---------- ----------
7369 SMITH  CLERK           7902 02-APR-13      12975          0         20          2
7566 JONES  MANAGER         7839 02-APR-13      12975          0         20          2

Execution Plan
----------------------------------------------------------
Plan hash value: 3040716376

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    11 |  1078 |     3  (34)| 00:00:01 |
|*  1 |  VIEW                    |      |    11 |  1078 |     3  (34)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |    11 |   429 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMP  |    11 |   429 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RANKING"=2)
2 - filter(DENSE_RANK() OVER ( ORDER BY INTERNAL_FUNCTION("SAL") DESC
)<=2)

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
532  bytes sent via SQL*Net to client
247  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
2  rows processed

SQL> select *
2    from emp
3   where sal in
4        (
5           select max(sal)
6             from (
7                      select sal
8                        from emp
9                       where sal not in (select max(sal) from emp)
10                  )
11        );

EMPNO ENAME  JOB              MGR HIREDATE         SAL        COM     DEPTNO
---------- ------ --------- ---------- --------- ---------- ---------- ----------
7369 SMITH  CLERK           7902 02-APR-13      12975          0         20
7566 JONES  MANAGER         7839 02-APR-13      12975          0         20

Execution Plan
----------------------------------------------------------
Plan hash value: 1184514365

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    39 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL    | EMP  |     1 |    39 |     2   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE      |      |     1 |     5 |            |          |
|*  3 |    TABLE ACCESS FULL  | EMP  |    10 |    50 |     2   (0)| 00:00:01 |
|   4 |     SORT AGGREGATE    |      |     1 |     5 |            |          |
|   5 |      TABLE ACCESS FULL| EMP  |    11 |    55 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("SAL"= (SELECT MAX("SAL") FROM "EMP" "EMP" WHERE "SAL"<>
(SELECT MAX("SAL") FROM "EMP" "EMP")))
3 - filter("SAL"<> (SELECT MAX("SAL") FROM "EMP" "EMP"))

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
10  consistent gets
0  physical reads
0  redo size
498  bytes sent via SQL*Net to client
247  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2  rows processed

• 5. Re: Which one is faster

> Look for the Cost in the execution plan and the consistent gets in the statistics.

cost has nothing whatsoever to do with it. consistent gets - sure, but you cannot compare costs for 2 different queries.

• 6. Re: Which one is faster

John Stegeman wrote:

> Look for the Cost in the execution plan and the consistent gets in the statistics.

cost has nothing whatsoever to do with it. consistent gets - sure, but you cannot compare costs for 2 different queries.

Hello John,

When we compare execution plans, do you think the only criteria we have to look is consistent gets? If not, which parts we have to take into account when comparing execution plans?

Regards

Charlie

• 7. Re: Which one is faster

Talking about index access in general case.

If both analytic function and max approaches can go through the same path for the highest value, it is possible both can go through the same path for the 2nd highest value.  If not, bet on max approach.

By the way, in your analytic query, rownum=1 is needed somewhere or it may return multiple rows.

• 8. Re: Which one is faster
When we compare execution plans, do you think the only criteria we have to look is consistent gets? If not, which parts we have to take into account when comparing execution plans?

You're getting WAY ahead of yourself.

The first thing, and most important IMHO, is to create the plans on

a meaningful and representative set of data. Any performance info based on the default EMP table of 14 rows will be utterly meaningless.

Which parts of the plan? ALL OF THEM!

This is from the first plan posted:

3 |    TABLE ACCESS FULL     | EMP

0  db block gets

3  consistent gets

0  physical reads

1  sorts (memory)

0  sorts (disk)

No physical reads? Is that realistic? Sounds like the data was already in the buffer cache.

One 'memory' sort but ZERO disk sorts? Must not be dealing with very much data

And ONE full table scan - that seems reasonable.

This is from the second plan posted.

|*  1 |  TABLE ACCESS FULL    | EMP  |     1 |    39 |     2   (0)| 00:00:01 |

|   2 |   SORT AGGREGATE      |      |     1 |     5 |            |          |

|*  3 |    TABLE ACCESS FULL  | EMP  |    10 |    50 |     2   (0)| 00:00:01 |

|   4 |     SORT AGGREGATE    |      |     1 |     5 |            |          |

|   5 |      TABLE ACCESS FULL| EMP

0  db block gets

10  consistent gets

0  physical reads

0  redo size

498  bytes sent via SQL*Net to client

247  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

No physical reads? Data is in the buffer cache. Also, the second query may have been executed AFTER the first query loaded the data into the cache.

TWO sorts mentioned in the plan yet the stats show ZERO memory sorts and ZERO disk sorts. Well clearly the data MUST have been sorted so where is that represented?

Three mentions of TABLE ACCESS FULL in the plan instead of the one. Well the subquery needs a MAX(SAL) for the main query to use so that may require a scan of some sort: might be able to use an index scan if an index existed on that column.

Virtually ALL of the info for the two plans posted is useless for any meaningful answer to your initial questions because the data used in the tests was not appropriate for what you were trying to find out.

• 9. Re: Which one is faster

Thanks All .