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
------------------
分页SQL取下一页
最新推荐文章于 2019-04-08 17:54:52 发布