Oracle分页可以分为两部分，作用是用于IO的优化和排序的优化，下面对其简单介绍
一、简单的单表分页查询
SQL>create table sta01 as select object_id,object_name from dba_objects;复制
分页A：
SELECT b.object_id,b.object_name FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM sta01 a where rownum<=20 )b WHERE RN>=10;复制
分页B：
SELECT b.object_id,b.object_name FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM sta01 a )b WHERE RN BETWEEN 10 AND 20;复制
分页A实现了IO上的优化：
SQL>SELECT b.object_id,b.object_name FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM sta01 a where rownum<=20 )b WHERE RN>=10; Plan hash value: 2040248633 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 1840 | 2 (0)| 00:00:01 | |* 1 | VIEW | | 20 | 1840 | 2 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | TABLE ACCESS FULL| STA01 | 20 | 600 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=10) 2 - filter(ROWNUM<=20) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 821 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed复制
分页B的成本并没有实现IO上的优化：
SQL> SELECT b.object_id,b.object_name FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM sta01 a )b WHERE RN BETWEEN 10 AND 20; Plan hash value: 3221659781 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 87044 | 7820K| 115 (0)| 00:00:02 | |* 1 | VIEW | | 87044 | 7820K| 115 (0)| 00:00:02 | | 2 | COUNT | | | | | | | 3 | TABLE ACCESS FULL| STA01 | 87044 | 2550K| 115 (0)| 00:00:02 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"<=20 AND "RN">=10) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 421 consistent gets 0 physical reads 0 redo size 821 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed复制
两种分页写法的区别：
分页写法A表示的是表全表扫描执行计划只取前20行数据就结束全表扫描工作，所以这里在全表扫描结束后有个count stopkey操作
分页写法B表示的的表全表扫描取出所有数据后添加RN列构造view，再去view上面进行谓词过滤操作；再放大到优化器查询转换来看，此时没有做简单谓词推入filter_push_down，因为RN between 10 and 20推进到view或者连接谓词推进会影响原SQL含义，因为ROWNUM被转化为RN了，所以rownum的stopkey没有办法生效。
二、分页查询越往后面的页查询成本会更高
查询语句1：
SQL> SELECT b.object_id,b.object_name FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM sta01 a where rownum<=100 )b WHERE RN>=90; OBJECT_ID OBJECT_NAME RN --------- ---------------------------------------- ---------- 91 PROFNAME$ 90 92 DEPENDENCY$ 91 93 ACCESS$ 92 94 LINK$ 93 95 TRUSTED_LIST$ 94 96 PROPS$ 95 97 COM$ 96 98 RESOURCE_COST$ 97 99 I_VIEW1 98 100 I_TYPED_VIEW1 99 101 I_SYN1 100 11 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2040248633 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 9200 | 2 (0)| 00:00:01 | |* 1 | VIEW | | 100 | 9200 | 2 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | TABLE ACCESS FULL| STA01 | 100 | 3000 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=90) 2 - filter(ROWNUM<=100) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 911 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed复制
查询语句2：
SQL> SELECT b.object_id,b.object_name FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM sta01 a where rownum<=1000 )b WHERE RN>=990; OBJECT_ID OBJECT_NAME RN --------- ---------------------------------------- ---------- 1036 V_$LATCH_CHILDREN 990 1037 V$LATCH_CHILDREN 991 1038 V_$LATCH_PARENT 992 1039 V$LATCH_PARENT 993 1040 V_$LATCHNAME 994 1041 V$LATCHNAME 995 1042 V_$LATCHHOLDER 996 1043 V$LATCHHOLDER 997 1044 V_$LATCH_MISSES 998 1045 V$LATCH_MISSES 999 1046 V_$SESSION_LONGOPS 1000 11 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2040248633 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 92000 | 5 (0)| 00:00:01 | |* 1 | VIEW | | 1000 | 92000 | 5 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | TABLE ACCESS FULL| STA01 | 1000 | 30000 | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=990) 2 - filter(ROWNUM<=1000) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16 consistent gets 0 physical reads 0 redo size 995 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed复制
查询语句3：
SQL> SELECT b.object_id,b.object_name FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM sta01 a where rownum<=10000 )b WHERE RN>=9990; OBJECT_ID OBJECT_NAME RN --------- ---------------------------------------- ---------- 10491 /78e6d350_BinaryExceptionHandl 9990 24378 /78e6d350_BinaryExceptionHandl 9991 10492 /af5a8ef3_JarVerifierStream1 9992 24379 /af5a8ef3_JarVerifierStream1 9993 10493 /4d0ec03b_JarVerifierStream2 9994 24380 /4d0ec03b_JarVerifierStream2 9995 10494 /744ed420_JarVerifierStreamCer 9996 24381 /744ed420_JarVerifierStreamCer 9997 10495 /b203c305_DateFormatZoneData1 9998 24382 /b203c305_DateFormatZoneData1 9999 10496 /dedbf4e7_CompactShortArrayIte 10000 11 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2040248633 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 898K| 32 (0)| 00:00:01 | |* 1 | VIEW | | 10000 | 898K| 32 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | TABLE ACCESS FULL| STA01 | 10000 | 292K| 32 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=9990) 2 - filter(ROWNUM<=10000) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 130 consistent gets 0 physical reads 0 redo size 1048 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed复制
查询语句4：
SQL>SELECT b.object_id,b.object_name FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM sta01 a where rownum<=100000 )b WHERE RN>=99990; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2040248633 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50080 | 4499K| 154 (2)| 00:00:02 | |* 1 | VIEW | | 50080 | 4499K| 154 (2)| 00:00:02 | |* 2 | COUNT STOPKEY | | | | | | | 3 | TABLE ACCESS FULL| STA01 | 50080 | 1467K| 154 (2)| 00:00:02 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=99990) 2 - filter(ROWNUM<=100000) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 693 consistent gets 0 physical reads 0 redo size 455 bytes sent via SQL*Net to client 481 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed复制
上面四个查询随着rownum的变化，带来的成本也越来越高，所以随着不停的需要后面的页，查询的成本将会越来越高，这也是为什么分页往往是对前面的页带来的性能收益较高，越往后面带来的收益将会减少。
三、包含排序的分页查询
SQL> create table sta02 as select object_id,object_name from dba_objects; SQL> create index index_object_id on sta02(object_id); SQL> alter table sta02 modify object_id not null;复制
3.1 排序字段有索引可以实现IO的分页优化
SQL> SELECT b.object_id,b.object_name FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM (SELECT * FROM sta02 order by object_id)a where rownum<=20 )b WHERE RN>=10; Plan hash value: 2344400197 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 1840 | 3 (0)| 00:00:01 | |* 1 | VIEW | | 20 | 1840 | 3 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 81024 | 6250K| 3 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| STA02 | 81024 | 6250K| 3 (0)| 00:00:01 | | 5 | INDEX FULL SCAN | INDEX_OBJECT_ID | 20 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=10) 2 - filter(ROWNUM<=20) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 786 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed复制
但是需要注意分页的写法，避免ROWNUM无法推进到
SQL> SELECT * FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM (SELECT * FROM sta02 order by object_id)a )b WHERE RN BETWEEN 10 AND 20; 11 rows selected. Plan hash value: 2222971110 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 81024 | 7279K| 655 (1)| 00:00:08 | |* 1 | VIEW | | 81024 | 7279K| 655 (1)| 00:00:08 | | 2 | COUNT | | | | | | | 3 | VIEW | | 81024 | 6250K| 655 (1)| 00:00:08 | | 4 | TABLE ACCESS BY INDEX ROWID| STA02 | 81024 | 6250K| 655 (1)| 00:00:08 | | 5 | INDEX FULL SCAN | INDEX_OBJECT_ID | 81024 | | 196 (1)| 00:00:03 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"<=20 AND "RN">=10) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 655 consistent gets 0 physical reads 0 redo size 786 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed复制
从上述分页写法可以得出，count stopkey性能明显优于count的性能，count stopkey是在通过全索引扫描回表数据阶段只需要找到满足条件的前20条数据就停止扫描，这个会节约部分IO资源消耗。
3.2 排序字段无索引也可以实现排序的分页
上面是排序列含有索引的，如果排序列没有索引，我们来观察两种分页查询的带来的成本的变化：
SQL> SELECT * FROM 2 ( 3 SELECT a.object_id,a.object_name,ROWNUM RN 4 FROM (SELECT * FROM sta02 order by object_name)a where rownum<=20 5 ) 6 WHERE RN>=10; 11 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4075842950 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 20 | 1840 | | 1606 (1)| 00:00:20 | |* 1 | VIEW | | 20 | 1840 | | 1606 (1)| 00:00:20 | |* 2 | COUNT STOPKEY | | | | | | | | 3 | VIEW | | 81024 | 6250K| | 1606 (1)| 00:00:20 | |* 4 | SORT ORDER BY STOPKEY| | 81024 | 6250K| 7056K| 1606 (1)| 00:00:20 | | 5 | TABLE ACCESS FULL | STA02 | 81024 | 6250K| | 115 (0)| 00:00:02 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=10) 2 - filter(ROWNUM<=20) 4 - filter(ROWNUM<=20) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 421 consistent gets 0 physical reads 0 redo size 1029 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 11 rows processed SQL> SELECT * FROM 2 ( 3 SELECT a.object_id,a.object_name,ROWNUM RN 4 FROM (SELECT * FROM sta02 order by object_name)a 5 ) 6 WHERE RN BETWEEN 10 AND 20; 11 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3644733759 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 81024 | 7279K| | 1606 (1)| 00:00:20 | |* 1 | VIEW | | 81024 | 7279K| | 1606 (1)| 00:00:20 | | 2 | COUNT | | | | | | | | 3 | VIEW | | 81024 | 6250K| | 1606 (1)| 00:00:20 | | 4 | SORT ORDER BY | | 81024 | 6250K| 7056K| 1606 (1)| 00:00:20 | | 5 | TABLE ACCESS FULL| STA02 | 81024 | 6250K| | 115 (0)| 00:00:02 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"<=20 AND "RN">=10) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 421 consistent gets 0 physical reads 0 redo size 1029 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 11 rows processed复制
这里执行计划有变化了，分别为count stopkey和count，但是资源消耗逻辑读是没有变化的。依然推荐使用第一种分页的写法，第一种分页对于排序区的需求量是比较小的，后面有具体的例子可供参考。
四、排序字段索引也可以实现排序上的分页，优化PGA关于排序区的资源需求
测试验证：反复插入数据到sta02表中
SQL> select count(*) from sta02; COUNT(*) ---------- 2785440 SQL Text ------------------------------ SELECT /*+monitor a*/ * FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM (SELECT * FROM sta02 order by object_name)a where rownum<=20 ) WHERE RN>=10; Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (43:53) SQL ID : 807apkhjaysms SQL Execution ID : 16777216 Execution Started : 10/31/2018 01:14:25 First Refresh Time : 10/31/2018 01:14:25 Last Refresh Time : 10/31/2018 01:14:25 Duration : .284605s Module/Action : sqlplus@11g-node01 (TNS V1-V3)/- Service : SYS$USERS Program : sqlplus@11g-node01 (TNS V1-V3) Fetch Calls : 2 Global Stats =========================================================================== | Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | =========================================================================== | 0.28 | 0.24 | 0.02 | 0.02 | 2 | 13305 | 2 | 144KB | =========================================================================== SQL Plan Monitoring Details (Plan Hash Value=4075842950) ========================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) | ========================================================================================================================================================= | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 11 | | | | | | | 1 | VIEW | | 20 | 54666 | 1 | +0 | 1 | 11 | | | | | | | 2 | COUNT STOPKEY | | | | 1 | +0 | 1 | 20 | | | | | | | 3 | VIEW | | 3M | 54666 | 1 | +0 | 1 | 20 | | | | | | | 4 | SORT ORDER BY STOPKEY | | 3M | 54666 | 1 | +0 | 1 | 20 | | | 14336 | | | | 5 | TABLE ACCESS FULL | STA02 | 3M | 3617 | 1 | +0 | 1 | 3M | 2 | 144KB | | | | ========================================================================================================================================================= Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 13305 consistent gets 0 physical reads 0 redo size 902 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 11 rows processed SQL Text ------------------------------ SELECT /*+monitor b*/ * FROM ( SELECT a.object_id,a.object_name,ROWNUM RN FROM (SELECT * FROM sta02 order by object_name)a ) WHERE RN BETWEEN 10 AND 20 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (43:53) SQL ID : d6zt2zgw373qp SQL Execution ID : 16777217 Execution Started : 10/31/2018 01:16:39 First Refresh Time : 10/31/2018 01:16:39 Last Refresh Time : 10/31/2018 01:16:45 Duration : 6s Module/Action : sqlplus@11g-node01 (TNS V1-V3)/- Service : SYS$USERS Program : sqlplus@11g-node01 (TNS V1-V3) Fetch Calls : 2 Global Stats =========================================================================================== | Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | Write | Write | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | =========================================================================================== | 6.30 | 1.74 | 3.07 | 1.49 | 2 | 13309 | 1906 | 100MB | 436 | 100MB | =========================================================================================== SQL Plan Monitoring Details (Plan Hash Value=3644733759) ======================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) | ======================================================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 5 | +2 | 1 | 11 | | | | | | | | | | 1 | VIEW | | 3M | 54666 | 5 | +2 | 1 | 11 | | | | | | | | | | 2 | COUNT | | | | 5 | +2 | 1 | 3M | | | | | | | | | | 3 | VIEW | | 3M | 54666 | 5 | +2 | 1 | 3M | | | | | | | | | | 4 | SORT ORDER BY | | 3M | 54666 | 6 | +1 | 1 | 3M | 1905 | 100MB | 436 | 100MB | 94M | 106M | 85.71 | Cpu (3) | | | | | | | | | | | | | | | | | | direct path read temp (3) | | 5 | TABLE ACCESS FULL | STA02 | 3M | 3617 | 3 | +0 | 1 | 3M | 1 | 8192 | | | | | 14.29 | Cpu (1) | ======================================================================================================================================================================================== Statistics ---------------------------------------------------------- 101 recursive calls 4 db block gets 13305 consistent gets 12833 physical reads 0 redo size 879 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 11 rows processed复制
虽然逻辑读几乎任何变化，但是我们看见物理读，SQL响应时间、CPU time和IO time有明显的区别。对于第一种分页写法count stopkey，同样是全表扫描所有数据，然后在pga排序时会只排序top n的数据，也就是top 20的数据，对于第二种分页写法count，优化器是首先全表扫描取出表中的所有数据，然后在pga中排序，如果pga空间不足，则可能需要用磁盘去排序，此时就会出现direct path read temp，注意这里的排序是要对所有数据进行排序，不论是否属于top n的数据，最后排序完后通过RN列去筛选数据
五、关于分区表的分页查询
[oracle@ora11g-node01 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 2 14:30:28 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved.复制
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL>create table tab03 partition by range(object_id) (partition p1 values less than(10000), partition p2 values less than(20000), partition p3 values less than(30000), partition p4 values less than(40000), partition p5 values less than(maxvalue)) as select * from dba_objects; SQL>create index ind_type_dataid on tab03(object_type,data_object_id) local;复制
5.1 扫描单个分区，又无inlist的执行计划——分页查询可以实现IO优化：
SQL Text ------------------------------ select /*+monitor b*/* from (select * from tab03 where object_type='INDEX' and object_id<10000 order by data_object_id desc) where rownum<2 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (56:211) SQL ID : 3yrrt8j8jkmuz SQL Execution ID : 16777217 Execution Started : 11/01/2018 01:53:07 First Refresh Time : 11/01/2018 01:53:07 Last Refresh Time : 11/01/2018 01:53:07 Duration : .00007s Module/Action : sqlplus@11g-node01 (TNS V1-V3)/- Service : SYS$USERS Program : sqlplus@11g-node01 (TNS V1-V3) Fetch Calls : 1复制
Global Stats ======================================= | Elapsed | Other | Fetch | Buffer | | Time(s) | Waits(s) | Calls | Gets | ======================================= | 0.00 | 0.00 | 1 | 3 | ======================================= SQL Plan Monitoring Details (Plan Hash Value=66316966) ======================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ======================================================================================================================================================= | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | 1 | COUNT STOPKEY | | | | 1 | +0 | 1 | 1 | | | | 2 | VIEW | | 1736 | 3 | 1 | +0 | 1 | 1 | | | | 3 | PARTITION RANGE SINGLE | | 1736 | 3 | 1 | +0 | 1 | 1 | | | | 4 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB03 | 1736 | 3 | 1 | +0 | 1 | 1 | | | | 5 | INDEX RANGE SCAN DESCENDING | IND_TYPE_DATAID | 1 | 2 | 1 | +0 | 1 | 1 | | | ======================================================================================================================================================= Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<2) 5 - access("OBJECT_TYPE"='INDEX') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1625 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed复制
5.2 扫描多个分区、排序字段又不是分区键——分页查询无法可以实现IO优化：
SQL Text ------------------------------ select /*+monitor a*/* from (select * from tab03 where object_type='INDEX' order by data_object_id desc) where rownum<2 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (56:207) SQL ID : dbc0zbzyj1d7k SQL Execution ID : 16777217 Execution Started : 11/01/2018 01:42:14 First Refresh Time : 11/01/2018 01:42:14 Last Refresh Time : 11/01/2018 01:42:14 Duration : .004901s Module/Action : sqlplus@11g-node01 (TNS V1-V3)/- Service : SYS$USERS Program : sqlplus@11g-node01 (TNS V1-V3) Fetch Calls : 1复制
Global Stats ================================================= | Elapsed | Cpu | Other | Fetch | Buffer | | Time(s) | Time(s) | Waits(s) | Calls | Gets | ================================================= | 0.00 | 0.00 | 0.00 | 1 | 328 | ================================================= SQL Plan Monitoring Details (Plan Hash Value=3620270499) ================================================================================================================================================================ | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) | ================================================================================================================================================================ | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | | 1 | COUNT STOPKEY | | | | 1 | +0 | 1 | 1 | | | | | 2 | VIEW | | 6189 | 539 | 1 | +0 | 1 | 1 | | | | | 3 | SORT ORDER BY STOPKEY | | 6189 | 539 | 1 | +0 | 1 | 1 | 75776 | | | | 4 | PARTITION RANGE ALL | | 6189 | 257 | 1 | +0 | 1 | 5047 | | | | | 5 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB03 | 6189 | 257 | 1 | +0 | 5 | 5047 | | | | | 6 | INDEX RANGE SCAN | IND_TYPE_DATAID | 6189 | 22 | 1 | +0 | 5 | 5047 | | | | ================================================================================================================================================================ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<2) 3 - filter(ROWNUM<2) 6 - access("OBJECT_TYPE"='INDEX') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 328 consistent gets 0 physical reads 0 redo size 1617 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(*) from tab03 where object_type='INDEX' ; COUNT(*) ---------- 5047复制
如果扫描多个分区，排序字段又不是分区键，则无法保证取出来的数据是有序的，优化器需要将所有数据通过索引范围扫描回表后，在排序阶段count stopkey，无法在IO上面进行分页。
5.3 扫描多个分区、但是排序字段是分区关键字——分页查询可以实现IO优化：
SQL>create index ind_type_objid on tab03(object_type,object_id) local; SQL Text ------------------------------ select /*+monitor c*/* from (select * from tab03 where object_type='INDEX' order by object_id desc) where rownum<2 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (56:211) SQL ID : dbjy6amh806rr SQL Execution ID : 16777219 Execution Started : 11/01/2018 02:04:37 First Refresh Time : 11/01/2018 02:04:37 Last Refresh Time : 11/01/2018 02:04:37 Duration : .000039s Module/Action : sqlplus@11g-node01 (TNS V1-V3)/- Service : SYS$USERS Program : sqlplus@11g-node01 (TNS V1-V3) Fetch Calls : 1 Global Stats ======================================= | Elapsed | Other | Fetch | Buffer | | Time(s) | Waits(s) | Calls | Gets | ======================================= | 0.00 | 0.00 | 1 | 3 | ======================================= SQL Plan Monitoring Details (Plan Hash Value=3568082482) ====================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ====================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | 1 | COUNT STOPKEY | | | | 1 | +0 | 1 | 1 | | | | 2 | PARTITION RANGE ALL | | 6189 | 7 | 1 | +0 | 1 | 1 | | | | 3 | VIEW | | 6189 | 7 | 1 | +0 | 1 | 1 | | | | 4 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB03 | 6189 | 7 | 1 | +0 | 1 | 1 | | | | 5 | INDEX RANGE SCAN DESCENDING | IND_TYPE_OBJID | 1 | 6 | 1 | +0 | 1 | 1 | | | ====================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<2) 5 - access("OBJECT_TYPE"='INDEX') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1616 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed复制
这里即使没有添加分区键扫描了全分区的索引，此时IO上的分页依然生效了,因为object_id是range分区的分区关键字，所以这里会直接定位到最小分区P1的partition p1 local index去扫描满足where条件的key，当找到满足的key后就会回表停止查询。
六、包含inlist执行计划和排序的分页查询——存在inlist执行计划无法保证取出数据是有序的，无法实现IO的分页
SQL Text ------------------------------ SELECT /*+monitor d*/ b.obj,b.type FROM ( SELECT a.obj,a.type,ROWNUM RN FROM (SELECT * FROM tab02 where type='INDEX' order by obj)a where rownum<=20 )b WHERE RN>=10 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (56:213) SQL ID : 2pf4f4vkzyr3q SQL Execution ID : 16777217 Execution Started : 11/01/2018 02:13:10 First Refresh Time : 11/01/2018 02:13:10 Last Refresh Time : 11/01/2018 02:13:10 Duration : .000086s Module/Action : sqlplus@11g-node01 (TNS V1-V3)/- Service : SYS$USERS Program : sqlplus@11g-node01 (TNS V1-V3) Fetch Calls : 2 Global Stats ======================================= | Elapsed | Other | Fetch | Buffer | | Time(s) | Waits(s) | Calls | Gets | ======================================= | 0.00 | 0.00 | 2 | 3 | ======================================= SQL Plan Monitoring Details (Plan Hash Value=2921650777) =========================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | =========================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 11 | | | | 1 | VIEW | | 20 | 2 | 1 | +0 | 1 | 11 | | | | 2 | COUNT STOPKEY | | | | 1 | +0 | 1 | 20 | | | | 3 | VIEW | | 20 | 2 | 1 | +0 | 1 | 20 | | | | 4 | INDEX RANGE SCAN | IND_TAB02_TYPE_OBJID | 20 | 2 | 1 | +0 | 1 | 20 | | | =========================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=10) 2 - filter(ROWNUM<=20) 4 - access("TYPE"='INDEX') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 725 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed复制
SQL Text ------------------------------ SELECT /*+monitor e*/ b.obj,b.type FROM ( SELECT a.obj,a.type,ROWNUM RN FROM (SELECT * FROM tab02 where type in ('INDEX','TABLE') order by obj)a where rownum<=20 )b WHERE RN>=10 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (56:213) SQL ID : 57b6hr569r7gr SQL Execution ID : 16777217 Execution Started : 11/01/2018 02:14:53 First Refresh Time : 11/01/2018 02:14:53 Last Refresh Time : 11/01/2018 02:14:53 Duration : .001999s Module/Action : sqlplus@11g-node01 (TNS V1-V3)/- Service : SYS$USERS Program : sqlplus@11g-node01 (TNS V1-V3) Fetch Calls : 2 Global Stats ====================================== | Elapsed | Cpu | Fetch | Buffer | | Time(s) | Time(s) | Calls | Gets | ====================================== | 0.00 | 0.00 | 2 | 28 | ====================================== SQL Plan Monitoring Details (Plan Hash Value=1185425335) ======================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) | ======================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 11 | | | | | 1 | VIEW | | 20 | 16 | 1 | +0 | 1 | 11 | | | | | 2 | COUNT STOPKEY | | | | 1 | +0 | 1 | 20 | | | | | 3 | VIEW | | 3868 | 16 | 1 | +0 | 1 | 20 | | | | | 4 | SORT ORDER BY STOPKEY | | 3868 | 16 | 1 | +0 | 1 | 20 | 2048 | | | | 5 | INLIST ITERATOR | | | | 1 | +0 | 1 | 8042 | | | | | 6 | INDEX RANGE SCAN | IND_TAB02_TYPE_OBJID | 3868 | 15 | 1 | +0 | 2 | 8042 | | | | ======================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=10) 2 - filter(ROWNUM<=20) 4 - filter(ROWNUM<=20) 6 - access("TYPE"='INDEX' OR "TYPE"='TABLE') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 28 consistent gets 0 physical reads 0 redo size 737 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 11 rows processed复制
最后修改时间：2020-05-06 14:40:08
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
Oracle DataGuard高可用性解决方案详解
孙莹
578次阅读
2025-03-26 23:27:33
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
537次阅读
2025-04-15 17:24:06
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
440次阅读
2025-04-18 14:18:38
XTTS跨版本迁移升级方案（11g to 19c RAC for Linux）
zwtian
432次阅读
2025-04-08 09:12:48
墨天轮个人数说知识点合集
JiekeXu
431次阅读
2025-04-01 15:56:03
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
430次阅读
2025-04-22 00:20:37
Oracle SQL 执行计划分析与优化指南
Digital Observer
430次阅读
2025-04-01 11:08:44
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
408次阅读
2025-04-22 00:13:51
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
395次阅读
2025-04-20 10:07:02
Oracle 19c RAC更换IP实战，运维必看！
szrsu
367次阅读
2025-04-08 23:57:08
