利用索引倒序index_desc， 优化取表中最后20行记录的例子。

先建立测试数据。

create table t1 as 
select level seq, level+10000 n  from dual connect by level <= 500000;


一般这么取最后后20个记录。 


select *
from (
select t1.* from t1
order by seq desc 
)
where rownum<= 20 ;


执行计划
----------------------------------------------------------
Plan hash value: 270731910


----------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    20 |   520 |       |  3058   (2)| 00:00:37 |
|*  1 |  COUNT STOPKEY          |      |       |       |       |            |          |
|   2 |   VIEW                  |      |   372K|  9460K|       |  3058   (2)| 00:00:37 |
|*  3 |    SORT ORDER BY STOPKEY|      |   372K|  9460K|    12M|  3058   (2)| 00:00:37 |
|   4 |     TABLE ACCESS FULL   | T1   |   372K|  9460K|       |   287   (2)| 00:00:04 |
----------------------------------------------------------------------------------------


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


   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1039  consistent gets
          0  physical reads
          0  redo size
       1096  bytes sent via SQL*Net to client
        535  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed


建立索引 


create index idx_t1_01 on t1(seq);


上面的 SQL 改写成如下， 利用索引倒序，会快很多。 




select /*+ index_desc(t1 idx_t1_01)*/ t1.seq from t1
where t1.seq > 1
and rownum<=20
order by seq desc ;




执行计划
----------------------------------------------------------
Plan hash value: 654235128


------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |    20 |   260 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |           |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_T1_01 |   372K|  4730K|     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


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


   1 - filter(ROWNUM<=20)
   2 - access("T1"."SEQ">1)
       filter("T1"."SEQ">1)


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        935  bytes sent via SQL*Net to client
        535  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed


执行计划里可以看到， 性能明显提升。 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13696961/viewspace-2123489/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13696961/viewspace-2123489/