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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
