Order by taking less time than max() in oracle

oracle

I am using oracle db and I am running 2 queries to find the maximum id from an indexed column.

1st query – 

select max(id) 
from tableName

Explain Plan:

Plan hash value: 3791258597

<table style="width:100%">
<tr>
<td>ID</td>
<td>Operation</td>
<td>Name</td>
<td>Rows</td>
<td>Bytes</td>
<td>Cost(%CPU)</td>
<td>Time</td>
</tr>
<tr>
<td>0</td>
<td> SELECT STATEMENT</td>
<td></td>
<td>1</td>
<td>6</td>
<td>1(0)</td>
<td>00:00:01</td>
</tr>
<tr>
<td>1</td>
<td> SORT AGGREGATE </td>
<td></td>
<td>1</td>
<td>6</td>
<td></td>
<td></td>
</tr>
<tr>
<td>2</td>
<td>  INDEX FULL SCAN (MIN/MAX)</td>
<td>Index Name</td>
<td>1</td>
<td>6</td>
<td>1(0)</td>
<td>00:00:01</td>
</tr>
</table>

It takes 2.839 seconds to execute on a table having nearly 80000000 rows.

2nd query – 

select id 
from (
   select id 
   from tableName 
   order by id desc) 
where rownum < 2

Explain Plan:

Plan hash value: 1514191988

<table style="width:100%">
<tr>
<td>ID</td>
<td>Operation</td>
<td>Name</td>
<td>Rows</td>
<td>Bytes</td>
<td>Cost(%CPU)</td>
<td>Time</td>
</tr>
<tr>
<td>0</td>
<td> SELECT STATEMENT</td>
<td></td>
<td>1</td>
<td>13</td>
<td>27876 (1)</td>
<td>00:05:35 </td>
</tr>
<tr>
<td>* 1</td>
<td> COUNT STOPKEY </td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>2</td>
<td>VIEW</td>
<td></td>
<td>84M</td>
<td>1046M</td>
<td>27876 (1)</td>
<td>00:05:35 </td>
</tr>

<tr>
<td>3</td>
<td> INDEX FULL SCAN</td>
<td>INDEX Name</td>
<td>84M</td>
<td>482M</td>
<td>27876 (1)</td>
<td>00:05:35 </td>
</tr>
</table>

It takes 2.6 seconds to execute on the same table.

When I see the explain plan of the 1st query, I see that it is using SORT(AGGREGATE). Now I don't understand is that since the column is indexed, it should directly return the max value instead of using SORT(AGGREGATE) function.

Why max() is taking more time than order by?

Best Answer

Both execution plans are fine

select    max(id) 
from tableName a;

---------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |          |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| TAB_IDX1 |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Here the index is accessed to get the MAX value (should require only few index block access). The SORT AGGREGATE is performed, but process only one value.

The second query ...

select id 
from (
   select id 
   from tableName 
   order by id desc) 
where rownum < 2;

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    13 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |          |       |       |            |          |
|   2 |   VIEW                       |          |     1 |    13 |     3   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN DESCENDING| TAB_IDX1 |  3000K|    17M|     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<2)

... access the index descendent and limits the number of rows to one. (again few index block access only).

Both plans are IMO comparable and my question is not why is one is slower, but why both take more than few milliseconds.

Please check the size and BLEVEL of the index.

I suppose the ID is NUMBER. My test give for both queries on an index with 96M response < 100ms.

Related Question