20条记录一页,扫描第2页就需要访问40条记录。
SQL> select * from ( select * from ( select /*+ index_desc(a idx_page_3) */ a.*,rownum rn from page
a where object_id >1000 and owner='SYS' order by object_id desc ) where rownum<=40 ) where
rn>=21 2 3
4 ;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------
SQL_ID bwcbf54a6h4th, child number 1
-------------------------------------
select * from ( select * from ( select /*+ index_desc(a idx_page_3) */
a.*,rownum rn from page a where object_id >1000 and owner='SYS'
order by object_id desc ) where rownum<=40 ) where rn>=21
Plan hash value: 3526010999
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.01 | 10 |
|* 1 | VIEW | | 1 | 40 | 20 |00:00:00.01 | 10 |
|* 2 | COUNT STOPKEY | | 1 | | 40 |00:00:00.01 | 10 |
| 3 | VIEW | | 1 | 30556 | 40 |00:00:00.01 | 10 |
| 4 | COUNT | | 1 | | 40 |00:00:00.01 | 10 |
| 5 | TABLE ACCESS BY INDEX ROWID | PAGE | 1 | 30556 | 40 |00:00:00.01 | 10 |
|* 6 | INDEX RANGE SCAN DESCENDING| IDX_PAGE_3 | 1 | 30556 | 40 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=21)
2 - filter(ROWNUM<=40)
6 - access("OBJECT_ID" IS NOT NULL AND "OBJECT_ID">1000 AND "OWNER"='SYS')
filter("OWNER"='SYS')
28 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------
SQL_ID 6gay6kkxxsqgw, child number 0
-------------------------------------
select * from ( select * from ( select /*+ index_desc(a idx_page_3) */
a.*,rownum rn from page a where object_id >1000 and owner='SYS'
order by object_id desc ) where rownum<=60 ) where rn>=41
Plan hash value: 3526010999
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.01 | 11 |
|* 1 | VIEW | | 1 | 60 | 20 |00:00:00.01 | 11 |
|* 2 | COUNT STOPKEY | | 1 | | 60 |00:00:00.01 | 11 |
| 3 | VIEW | | 1 | 30556 | 60 |00:00:00.01 | 11 |
| 4 | COUNT | | 1 | | 60 |00:00:00.01 | 11 |
| 5 | TABLE ACCESS BY INDEX ROWID | PAGE | 1 | 30556 | 60 |00:00:00.01 | 11 |
|* 6 | INDEX RANGE SCAN DESCENDING| IDX_PAGE_3 | 1 | 30556 | 60 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=41)
2 - filter(ROWNUM<=60)
6 - access("OBJECT_ID" IS NOT NULL AND "OBJECT_ID">1000 AND "OWNER"='SYS')
filter("OWNER"='SYS')
28 rows selected.