【Oracle】-【ROWNUM與索引】-索引對ROWNUM檢索的影響

看到ASK TOM的一篇文章，挺有感觸的。

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:32812348052

主要問的是ROWNUM的問題。後面的一個討論提問談到：

select ename, sal
from emp
where rownum<=10
order by sal desc;
和
select ename, sal
from ( select ename, sal
from emp
order by sal desc)
where rownum<=10;

是否相同？

第一個SQL是先找到ROWNUM<10的記錄，然後排序。

第二個SQL是先ORDER BY排序，再找ROWNUM<10的記錄。

因此兩種查詢得到的答案不同，當然有時也會碰巧相同。

另外，如果表有索引，那麼對於第二個SQL，可以從後面的記錄開始讀，避免排序。對於這個問題我做了實驗：

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 statement

Statistics
----------------------------------------------------------
          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 processed

SQL> 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 statement

Statistics
----------------------------------------------------------
          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

第二個SQL僅比第一個SQL少2個consistent gets，不像討論中說的會明顯的變化。這個討論是2001年的，不知道是不是版本的問題？我用的是10g。

還請高手指點！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7192724/viewspace-767684/，如需轉載，請註明出處，否則將追究法律責任。