Oracle 分页的SQL语句优化
ORACLE的分页SQL，基本上在绝大部分的业务系统上都有这种SQL。处理这种SQL,基本上要用到两点：
(1). 利用rownum的COUNT STOPKEY特性.
(2). 利用索引的排序特性，消除sort order by .
今天，同事发给我两个SQL。
执行计划大概如下：
第1个SQL的执行计划，没有出现COUNT STOPKEY，结合对应的SQL文本来看，分页框架就不对。 框架不对，就不可能出现COUNT STOPKEY，性能也就绝对的差。。
第2个SQL的执行计划，出现了SORT ORDER BY，进行了全表扫描，然后再排序，性能同样也会很差。
为了说明这个问题，做了简单测试。
1. 测试一张mm测试表。
| create table mm as select * from dba_objects; insert into mm select * from dba_objects; commit; insert into mm select * from dba_objects; commit; insert into mm select * from dba_objects; commit; insert into mm select * from dba_objects; commit; insert into mm select * from mm; commit; insert into mm select * from mm; commit; insert into mm select * from mm; commit; update mm set object_type=''; commit; update mm set owner=''; commit; |
2. 使用错误的分页框架，查看对应的执行计划。
| set linesize 200 set pagesize 2000 alter session set statistics_level = all; select count(0) from (select rownum rn, t.object_id from mm t where t.object_type is null and t.owner is null) where rn > 1 and rn <= 100; select * from table(dbms_xplan.display_cursor(null,null,'allstats alias outline LAST')); ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.62 | 59480 | 59469 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.62 | 59480 | 59469 | |* 2 | VIEW | | 1 | 4758K| 99 |00:00:01.62 | 59480 | 59469 | | 3 | COUNT | | 1 | | 4559K|00:00:00.94 | 59480 | 59469 | |* 4 | TABLE ACCESS FULL| MM | 1 | 4758K| 4559K|00:00:00.55 | 59480 | 59469 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("RN"<=100 AND "RN">1)) 4 - filter(("T"."OBJECT_TYPE" IS NULL AND "T"."OWNER" IS NULL)) |
使用了错误的分页框架后，无法利用COUNT STOPKEY特性，buffer读高达59480。
3. 使用正确的分页框架。
| select count(0) from (select * from (select t.*, rownum rn from (select t.object_id from mm t where t.object_type is null and t.owner is null) t) where rownum <= 100) where rn > 1; select * from table(dbms_xplan.display_cursor(null,null,'allstats alias outline LAST')); Plan hash value: 1654568715 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 5 | 13 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 5 | 13 | |* 2 | VIEW | | 1 | 100 | 99 |00:00:00.03 | 5 | 13 | |* 3 | COUNT STOPKEY | | 1 | | 100 |00:00:00.03 | 5 | 13 | | 4 | VIEW | | 1 | 4758K| 100 |00:00:00.02 | 5 | 13 | | 5 | COUNT | | 1 | | 100 |00:00:00.02 | 5 | 13 | |* 6 | TABLE ACCESS FULL| MM | 1 | 4758K| 100 |00:00:00.02 | 5 | 13 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("RN">1) 3 - filter(ROWNUM<=100) 6 - filter(("T"."OBJECT_TYPE" IS NULL AND "T"."OWNER" IS NULL)) |
使用了正确的分页框架后，利用了COUNT STOPKEY特性，buffer读才13。
4. 第一种分页SQL，分页语句没有使用order by排序，但实际上我们常见的分页语句类似于第2种SQL，也即分页语句中带有order by。 这种情况下，仅仅利用COUNT STOPKEY特性，如果不消除order by，性能仍然不会太好。
| select * from (select * from (select t.*, rownum rn from (select t.object_id from mm t where t.object_type is null and t.owner is null order by data_object_id) t) where rownum <= 100) where rn > 1; select * from table(dbms_xplan.display_cursor(null,null,'allstats alias outline LAST')); ------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| ------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 99 |00:00:01.80 | 59483 | 59497 | 7256 | | | | | |* 1 | VIEW | | 1 | 100 | 99 |00:00:01.80 | 59483 | 59497 | 7256 | | | | | |* 2 | COUNT STOPKEY | | 1 | | 100 |00:00:01.80 | 59483 | 59497 | 7256 | | | | | | 3 | VIEW | | 1 | 4758K| 100 |00:00:01.80 | 59483 | 59497 | 7256 | | | | | | 4 | COUNT | | 1 | | 100 |00:00:01.80 | 59483 | 59497 | 7256 | | | | | | 5 | VIEW | | 1 | 4758K| 100 |00:00:01.80 | 59483 | 59497 | 7256 | | | | | | 6 | SORT ORDER BY | | 1 | 4758K| 100 |00:00:01.80 | 59483 | 59497 | 7256 | 63M| 2776K| 100M (1)| 58368 | |* 7 | TABLE ACCESS FULL| MM | 1 | 4758K| 4559K|00:00:00.76 | 59480 | 59469 | 0 | | | | | ------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">1) 2 - filter(ROWNUM<=100) 7 - filter(("T"."OBJECT_TYPE" IS NULL AND "T"."OWNER" IS NULL)) |
5. 这时，我们需要利用索引已经排序的特性来消除sort order by . 针对这个SQL，我们可以创建如下索引：
| create index idx_mm_01 on mm(data_object_id, object_type, owner, 0); |
6. 创建完索引后，我们看新的执行计划。
| select * from (select * from (select t.*, rownum rn from (select t.object_id from mm t where t.object_type is null and t.owner is null order by data_object_id) t) where rownum <= 100) where rn > 1; select * from table(dbms_xplan.display_cursor(null,null,'allstats alias outline LAST')); -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 99 |00:00:00.01 | 43 | |* 1 | VIEW | | 1 | 100 | 99 |00:00:00.01 | 43 | |* 2 | COUNT STOPKEY | | 1 | | 100 |00:00:00.01 | 43 | | 3 | VIEW | | 1 | 4758K| 100 |00:00:00.01 | 43 | | 4 | COUNT | | 1 | | 100 |00:00:00.01 | 43 | | 5 | VIEW | | 1 | 4758K| 100 |00:00:00.01 | 43 | | 6 | TABLE ACCESS BY INDEX ROWID| MM | 1 | 4758K| 100 |00:00:00.01 | 43 | |* 7 | INDEX FULL SCAN | IDX_MM_01 | 1 | 11897 | 100 |00:00:00.01 | 10 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">1) 2 - filter(ROWNUM<=100) 7 - access("T"."OBJECT_TYPE" IS NULL AND "T"."OWNER" IS NULL) filter(("T"."OBJECT_TYPE" IS NULL AND "T"."OWNER" IS NULL)) |
利用了COUNT STOPKEY特性，同时利用了索引的已经排序的特性，buffer读才43。
