SORT ORDER BY STOPKEY

select * from 
(
select * from 
(
select  a.*,rownum rn
  from page a 
 where object_id >1000 and owner='SYS'
 order by object_id desc
) where rownum<=20
) where rn>=20;



现在加个HINT

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9w6p4hu1q1z4n, child number 0
-------------------------------------
select * from ( select * from ( select /*+ index(a) */ a.*,rownum rn   from page a  where object_id >1000 and owner='SYS'
order by object_id desc ) where rownum<=20 ) where rn>=0

Plan hash value: 3486388599

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  VIEW                            |            |      1 |     20 |     20 |00:00:01.28 |     739 |       |       |          |
|*  2 |   COUNT STOPKEY                  |            |      1 |        |     20 |00:00:01.28 |     739 |       |       |          |
|   3 |    VIEW                          |            |      1 |  22595 |     20 |00:00:01.28 |     739 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY        |            |      1 |  22595 |     20 |00:00:01.28 |     739 |  1234K|   574K| 1096K (0)|
|   5 |      COUNT                       |            |      1 |        |  22130 |00:00:01.22 |     739 |       |       |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| PAGE       |      1 |  22595 |  22130 |00:00:01.22 |     739 |       |       |          |
|*  7 |        INDEX RANGE SCAN          | IDX_PAGE_3 |      1 |  22595 |  22130 |00:00:01.20 |     150 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter("RN">=0)
   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   7 - access("OBJECT_ID">1000 AND "OWNER"='SYS' AND "OBJECT_ID" IS NOT NULL)
       filter("OWNER"='SYS')



|*  4 |     SORT ORDER BY STOPKEY        |            |      1 |  22595 |     20 |



SORT ORDER BY STOPKEY 是指:

排序过后取数据 

走索引后返回 100w条 

是不是要对100w排序

转载于:https://www.cnblogs.com/zhaoyangjian724/p/3797920.html