Oracle优化——单表分页优化
单表分页优化思路：--创建测试表：
SQL> create table t_test as select * from dba_objects;
Table created.
如，下面的sql （没有过滤条件，只有排序），要将查询结果分页显示，每页显示10条，如：
select * from t_test order by object_id;
例子：
1、分页查询sql语句，如下（通常会采用下面的方法，但是这是错误的分页框架）
语法：select * from (select t.*,rownum rn from (需要分页的sql)t ) where rn >=1 and rnselect * from (select t.*,rownum rn from (select * from t_test order by object_id) t) where rn >=1 and rncreate index>
SQL> select * from (select t.*,rownum rn from (select /*+ index)t_test>
SQL>>SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_IDgvq2rvmutr85w, child number 0
-------------------------------------
select * from (select t.*,rownum rn from (select /*+ index)t_test
idx_test) */ * from t_test order by object_id) t) where rn >=1 and rn
select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test) */ * from t_test order by object_id) a ) where rownum =1;
SQL> alter session set statistics_level=all;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_IDcgjp65zfj1yqa, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test) */ * from t_test order by object_id) a ) where
rownum =1
Plan hash value: 1201925926
-------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 |
|*1 |VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 |
|*2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 |
| 3 | VIEW | | 1 |80700 | 10 |00:00:00.01 | 5 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 |
| 5 | VIEW | | 1 |80700 | 10 |00:00:00.01 | 5 |
| 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 |80700 | 10 |00:00:00.01 | 5 |
| 7 | INDEX FULL SCAN | IDX_test | 1 |80700 | 10 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test) */ * from t_test where owner='SCOTT' order by object_id) a ) where rownum =1;
SQL> alter session set statistics_level=all;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------
SQL_ID0w9tbrwkn9tn6, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test) */ * from t_test where owner='SCOTT' order by
object_id) a ) where rownum =1
Plan hash value: 1201925926
-------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.04 | 1245 |
|*1 |VIEW | | 1 | 10 | 10 |00:00:00.04 | 1245 |
|*2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.04 | 1245 |
| 3 | VIEW | | 1 | 12 | 10 |00:00:00.04 | 1245 |
| 4 | COUNT | | 1 | | 10 |00:00:00.04 | 1245 |
| 5 | VIEW | | 1 | 12 | 10 |00:00:00.04 | 1245 |
|*6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 12 | 10 |00:00:00.04 | 1245 |
| 7 | INDEX FULL SCAN | IDX_test | 1 |80700 |71901 |00:00:00.01 | 181 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test) */ * from t_test where owner='SYS' order by object_id) a ) where rownum =1;
SQL> alter session set statistics_level=all;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------
SQL_IDdfwkpppbtc8h7, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test) */ * from t_test where owner='SYS' order by
object_id) a ) where rownum =1
Plan hash value: 1201925926
-------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 |
|*1 |VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 |
|*2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 |
| 3 | VIEW | | 1 |28483 | 10 |00:00:00.01 | 5 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 |
| 5 | VIEW | | 1 |28483 | 10 |00:00:00.01 | 5 |
|*6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 |28483 | 10 |00:00:00.01 | 5 |
| 7 | INDEX FULL SCAN | IDX_test | 1 |80700 | 10 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM create index idx_test_all on t_test(owner,object_id);---创建一个组合索引，包含owner字段
SQL>select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test_all) */ * from t_test where owner='SCOTT' order by object_id) a ) where rownum =1;
SQL>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------、
SQL_ID9mm61b7j943sf, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test_all) */ * from t_test where owner='SCOTT' order
by object_id) a ) where rownum =1
Plan hash value: 3696904346
-----------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 6 |
|*1 |VIEW | | 1 | 10 | 10 |00:00:00.01 | 6 |
|*2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 6 |
| 3 | VIEW | | 1 | 12 | 10 |00:00:00.01 | 6 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 6 |
| 5 | VIEW | | 1 | 12 | 10 |00:00:00.01 | 6 |
| 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 12 | 10 |00:00:00.01 | 6 |
|*7 | INDEX RANGE SCAN | IDX_test_ALL | 1 | 12 | 10 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM create index idx_test_2 on t_test(object_id,object_name desc,0);---创建一个组合索引，包含owner字段
SQL>select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test_2) */ * from t_testorder by object_id,object_name desc) a ) where rownum =1;
SQL>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------
SQL_IDg8zgbvt1u1qjq, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test_2) */ * from t_testorder by
object_id,object_name desc) a ) where rownum =1
Plan hash value: 2251915778
------------------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads|
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 | 1 |
|*1 |VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 | 1 |
|*2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 | 1 |
| 3 | VIEW | | 1 |80700 | 10 |00:00:00.01 | 5 | 1 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 | 1 |
| 5 | VIEW | | 1 |80700 | 10 |00:00:00.01 | 5 | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 |80700 | 10 |00:00:00.01 | 5 | 1 |
| 7 | INDEX FULL SCAN | IDX_test_2 | 1 |80700 | 10 |00:00:00.01 | 3 | 1 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM create index idx_test_3 on t_test(object_id,object_name,0);
SQL>select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test_3) */ * from t_testorder by object_id,object_name desc) a ) where rownum =1;
SQL>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------
SQL_IDbapu5jn5vn22r, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test_3) */ * from t_testorder by
object_id,object_name desc) a ) where rownum =1
Plan hash value: 3547841113
-----------------------------------------
| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads|OMem |1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.10 | 1490 | 426 | | | |
|*1 |VIEW | | 1 | 10 | 10 |00:00:00.10 | 1490 | 426 | | | |
|*2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.10 | 1490 | 426 | | | |
| 3 | VIEW | | 1 |80700 | 10 |00:00:00.10 | 1490 | 426 | | | |
| 4 | COUNT | | 1 | | 10 |00:00:00.10 | 1490 | 426 | | | |
| 5 | VIEW | | 1 |80700 | 10 |00:00:00.10 | 1490 | 426 | | | |
| 6 | SORT ORDER BY | | 1 |80700 | 10 |00:00:00.10 | 1490 | 426 | 10M|1272K| 9811K (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| t_test | 1 |80700 |71903 |00:00:00.06 | 1490 | 426 | | | |
| 8 | INDEX FULL SCAN | IDX_test_3 | 1 |80700 |71903 |00:00:00.03 | 427 | 426 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM 1000 order by object_name;
--：创建索引（因为owner是等值过滤，object_Id是非等值过滤，创建索引的时候要优先将等值过滤列和排序列组合在一起，然后再将非等值过滤列放到后面）如下：
create index idx_test_4 on t_test(owner,object_name,object_id);
select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test_4) */ * from t_test where owner='SYS' and object_id > 1000 order by object_name) a ) where rownum =1;
SQL>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------:
SQL_ID4z6tjgrdjm5a1, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test_4) */ * from t_test where owner='SYS' and
object_id > 1000 order by object_name) a ) where rownum =1
Plan hash value: 1432357471
------------------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads|
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 14 | 2 |
|*1 |VIEW | | 1 | 10 | 10 |00:00:00.01 | 14 | 2 |
|*2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 14 | 2 |
| 3 | VIEW | | 1 |25683 | 10 |00:00:00.01 | 14 | 2 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 14 | 2 |
| 5 | VIEW | | 1 |25683 | 10 |00:00:00.01 | 14 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 |25683 | 10 |00:00:00.01 | 14 | 2 |
|*7 | INDEX RANGE SCAN | IDX_test_4 | 1 | 256 | 10 |00:00:00.01 | 4 | 2 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM1000)
filter("OBJECT_ID">1000)
Note
-----
- dynamic sampling used for this statement (level=2)
34 rows selected.
（可以看出，执行计划中没有出现 sort order by，逻辑读也只有14个，说明执行计划是正确的。）
注意：
①：如果分页语句中有排序（order by），要利用索引已经排序的特性，将order by的列按照排序的先后顺序包含在索引中，同时要注意排序是升序还是降序。如果分页语句中有过滤条件，我们要注意过滤条件是否有等值过滤条件，如果有等值过滤条件，要将等值过滤条件优先组合在一起，然后将排序列放在等值过滤条件后面，最后将非等值过滤列放排序列后面。如果分页语句中没有等值过滤条件，我们应该先将排序列放在索引前面，将非等值过滤列放后面，最后利用rownum的count stopkey特性来优化分页sql。如果分页中没有排序，可以直接利用rownum的count stopkey特性来优化分页sql。
②：要想一眼看出执行计划是否正确，就要先看分页语句有没有order by，再看执行计划有没有sort order by，如果执行计划中有sort order by，说明执行计划一般是错误的。
③：如果分页语句中排序的表是分区表，这时我们要看分页语句中是否有跨区分区扫描，如果有跨分区扫描，创建的索引一般都是global索引，如果不创建global索引，就无法保证分页的顺序与所有的顺序是一致的。如果就只扫描一个分区，这时可以创建local索引：
例子：创建分区表，并导入数据
CREATE TABLE p_test
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
) 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 (50000),
partition p6 values less than (60000),
partition p7 values less than (70000),
partition px values less than (maxvalue));
SQL> insert into p_test select * from dba_objects;
71917 rows created.
---例子1：分页语句：
select * from (selct * from (select a.*,rownum rn from (select * from p_test order by object_id) a ) where rownum =1;
（这个分页语句没有过滤条件，因此会扫描表中的所有分区。因为排序列恰好是范围分区列，范围分区每个分区的数据也是递增的，这时我们创建索引可以创建为local索引。但是如果将范围分区改为list分区或者hash分区，这时我们就必须创建global索引，因为list分区和hash分区是无序的。）
SQL> create index>select * from (select * from (select a.*,rownum rn from (select /*+ index(p_test idx_test_id ) */ * from p_test order by object_id) a ) where rownum =1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL_IDbxw1059jmgxvx, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(p_test idx_test_id ) */ * from p_test order by object_id) a )
where rownum =1
Plan hash value: 1291390031
--------------------------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads|
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 | 1 |
|*1 |VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 | 1 |
|*2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 | 1 |
| 3 | VIEW | | 1 |63696 | 10 |00:00:00.01 | 5 | 1 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 | 1 |
| 5 | VIEW | | 1 |63696 | 10 |00:00:00.01 | 5 | 1 |
| 6 | PARTITION RANGE ALL | | 1 |63696 | 10 |00:00:00.01 | 5 | 1 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| P_TEST | 1 |63696 | 10 |00:00:00.01 | 5 | 1 |
| 8 | INDEX FULL SCAN | IDX_TEST_ID | 1 |63696 | 10 |00:00:00.01 | 3 | 1 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM drop index idx_test_id ;
SQL> create index idx_test_id on p_test(object_name,0);
select * from (select * from (select a.*,rownum rn from (select /*+ index(p_test idx_test_id ) */ * from p_test order by object_name) a ) where rownum =1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL_ID25pm0f6b6m49x, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(p_test idx_test_id ) */ * from p_test order by object_name) a )
where rownum =1
Plan hash value: 246970912
--------------------------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads|
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 10 | 2 |
|*1 |VIEW | | 1 | 10 | 10 |00:00:00.01 | 10 | 2 |
|*2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 10 | 2 |
| 3 | VIEW | | 1 |63696 | 10 |00:00:00.01 | 10 | 2 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 10 | 2 |
| 5 | VIEW | | 1 |63696 | 10 |00:00:00.01 | 10 | 2 |
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| P_TEST | 1 |63696 | 10 |00:00:00.01 | 10 | 2 |
| 7 | INDEX FULL SCAN | IDX_TEST_ID | 1 |63696 | 10 |00:00:00.01 | 4 | 2 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM
页:
[1]