[Oracle]-[ROWNUM and Index]-Impact of indexes on ROWNUM Retrieval

[Oracle]-[ROWNUM and Index]-Impact of indexes on ROWNUM Retrieval

Select ename, salfrom empwhere rownum <= 10 order by sal desc; and select ename, salfrom (select ename, salfrom emporder by sal desc) where rownum <= 10;

 

Are they the same? The first SQL statement first finds the records with ROWNUM <10 and then sorts them. The second SQL statement is to sort BY and find records with ROWNUM <10. Therefore, the two types of queries have different answers, and sometimes they happen to be the same. In addition, if the table has an index, you can read the second SQL statement from the subsequent records to avoid sorting. I did an experiment on this problem:

create table t as select * from dba_objects;create table t2 as select * from dba_objects;create index t2_i on t2(object_id);SQL> select * from (select owner, object_name, object_id from t order by object_id desc) where rownum<10;9 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3299198703----------------------------------------------------------------------------------------| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT        |      |     9 |   864 |       |  1189   (1)| 00:00:15 ||*  1 |  COUNT STOPKEY          |      |       |       |       |            |       ||   2 |   VIEW                  |      | 47308 |  4435K|       |  1189   (1)| 00:00:15 ||*  3 |    SORT ORDER BY STOPKEY|      | 47308 |  4435K|     9M|  1189   (1)| 00:00:15 ||   4 |     TABLE ACCESS FULL   | T    | 47308 |  4435K|       |   150   (1)| 00:00:02 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(ROWNUM<10)   3 - filter(ROWNUM<10)Note-----   - dynamic sampling used for this statementStatistics----------------------------------------------------------          7  recursive calls          0  db block gets        793  consistent gets          0  physical reads          0  redo size        878  bytes sent via SQL*Net to client        492  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)          9  rows processedSQL> select * from ( select owner, object_name, object_id from t2 order by object_id desc) where rownum < 10;9 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 98068844----------------------------------------------------------------------------------------| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT        |      |     9 |   864 |       |  1164   (1)| 00:00:14 ||*  1 |  COUNT STOPKEY          |      |       |       |       |            |       ||   2 |   VIEW                  |      | 46110 |  4322K|       |  1164   (1)| 00:00:14 ||*  3 |    SORT ORDER BY STOPKEY|      | 46110 |  4322K|  9848K|  1164   (1)| 00:00:14 ||   4 |     TABLE ACCESS FULL   | T2   | 46110 |  4322K|       |   150   (1)| 00:00:02 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(ROWNUM<10)   3 - filter(ROWNUM<10)Note-----   - dynamic sampling used for this statementStatistics----------------------------------------------------------          7  recursive calls          0  db block gets        791  consistent gets          0  physical reads          0  redo size        878  bytes sent via SQL*Net to client        492  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)          9  rows processed