Oracle 分页的SQL语句优化

标签：语句 COUNT 00 mm SQL Oracle 100 null select

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。

标签：语句,COUNT,00,mm,SQL,Oracle,100,null,select
From： https://www.cnblogs.com/missyou-shiyh/p/18132207

Oracle 分页的SQL语句优化

相关文章

赞助商

阅读排行