1

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?

  • Perhaps the query plan would enlighten us – Ed Heal Feb 14 '16 at 8:43
  • 2
    Please show plans for both queries. Please don't attach a screen capture of a plan, but run a command EXPLAIN PLAN FOR select ......, then SELECT * FROM table( DBMS_XPLAN.Display ) and append it's result to the question as plain text. – krokodilko Feb 14 '16 at 9:04
  • The added "plan" is not the output of dbms_xplan. How did you retrieve that? – a_horse_with_no_name Feb 14 '16 at 9:19
  • I have added the explain plans. – P3A Feb 14 '16 at 9:43
  • ...why are you doing this? What are you planning on doing with the answer? Is id an auto-generated column? If so, the maximum value is pretty useless, generally. – Clockwork-Muse Feb 14 '16 at 14:15
1

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.

| improve this answer | |
  • This definitely looks like a testing issue. The difference between 2.6 and 2.8 seconds can easily be explained by caching, server load, network, etc. – Jon Heller Feb 14 '16 at 21:45
0

Because it's INDEX FULL SCAN (MIN/MAX) I would have supposed it to be a O(log(n)) operation, both for MAX(id) and ORDER BY. Without an index, a Full Table Scan would be done with O(n) - but that's not the case here.

Maybe recalculating the table statistics might help:

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'tableName');

btw: is our ID a number or varchar2 or date?

Probably also have a look at this: https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:9843506698920

| improve this answer | |
  • 1
    getting MAX from index (which is sorted) is O(1) – Marmite Bomber Feb 14 '16 at 20:41
  • Since it is an O(1) operation it should be faster than order by, but here just opposite is happening. Can you clarify why is it so ? – P3A Feb 16 '16 at 5:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.