SQL分页优化六分区表分页

原创

食品安全辛吉飞 2023-08-05 15:47:11 博主文章分类：Oracle ©著作权

文章标签 分页 sql优化 order by 分区表 文章分类 Oracle 数据库 技术人为什么要写博客？

©著作权归作者所有：来自51CTO博客作者食品安全辛吉飞的原创作品，请联系作者获取转载授权，否则将追究法律责任

测试验证

如果分页语句中排序的表是分区表，这时我们要看分页语句是否有跨区扫描：
如果有跨区扫描，创建索引一般为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 p8 values less than (80000),
partition pmax values less than (maxvalue)
  );

SQL> insert into p_test select * from dba_objects;

72594 rows created.

SQL> commit;

Commit complete.

测试1：如下分页SQL：

select * 
 from (select * 
         from (select a.*, rownum rn 
                 from (select * from p_test order by object_id) a) 
        where rownum <= 10) 
where rn >= 1;

该分页语句没有过滤条件，因此会扫描所有分区。因为排序列正好是范围分区列，范围分区每个分区的数据也是递增的，这时我们创建索引可以创建为local索引。但是，如果将范围分区改成LIST分区或者HASH分区，这时，我们就必须创建global索引，因为LIST分区和HASH分区是无序的。

SQL> create index idx_ptest_id on p_test(object_id,0) local;

Index created.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  3p7c6h5tjgd8w, child number 0
-------------------------------------
select *   from (select *           from (select a.*, rownum rn
          from (select /*+index(p_test idx_ptest_id)*/
        *                           from p_test
 order by object_id) a)          where rownum <= 10)  where rn >= 1

Plan hash value: 1636704844

------------------------------------------------------------------------------------------------------------------
| 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 |  72353 |     10 |00:00:00.01 |       5 |
|   4 |     COUNT                               |              |      1 |        |     10 |00:00:00.01 |       5 |
|   5 |      VIEW                               |              |      1 |  72353 |     10 |00:00:00.01 |       5 |
|   6 |       PARTITION RANGE ALL               |              |      1 |  72353 |     10 |00:00:00.01 |       5 |
|   7 |        TABLE ACCESS BY LOCAL INDEX ROWID| P_TEST       |      1 |  72353 |     10 |00:00:00.01 |       5 |
|   8 |         INDEX FULL SCAN                 | IDX_PTEST_ID |      1 |  72353 |     10 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)

测试2：如下分页SQL（根据object_name排序）：

select * 
 from (select * 
         from (select a.*, rownum rn 
                 from (select * from p_test order by object_name) a) 
        where rownum <= 10) 
where rn >= 1;

这时，就需要创建global索引，如果使用local索引就会产生sort order by：

SQL> create index idx_ptest_name on p_test(object_name,0) local;

Index created.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
------------------------------
SQL_ID  gz2awka1nh2sf, child number 0
-------------------------------------
select *   from (select *           from (select a.*, rownum rn
          from (select /*+index(p_test idx_ptest_name)*/
          *                           from p_test
   order by object_name) a)          where rownum <= 10)  where rn >= 1

Plan hash value: 2548872510

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                |      1 |        |     10 |00:00:00.07 |   35399 |
|*  1 |  VIEW                                    |                |      1 |     10 |     10 |00:00:00.07 |   35399 |
|*  2 |   COUNT STOPKEY                          |                |      1 |        |     10 |00:00:00.07 |   35399 |
|   3 |    VIEW                                  |                |      1 |  72353 |     10 |00:00:00.07 |   35399 |
|   4 |     COUNT                                |                |      1 |        |     10 |00:00:00.07 |   35399 |
|   5 |      VIEW                                |                |      1 |  72353 |     10 |00:00:00.07 |   35399 |
|   6 |       SORT ORDER BY                      |                |      1 |  72353 |     10 |00:00:00.07 |   35399 |
|   7 |        PARTITION RANGE ALL               |                |      1 |  72353 |  72594 |00:00:00.04 |   35399 |
|   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| P_TEST         |      9 |  72353 |  72594 |00:00:00.03 |   35399 |
|   9 |          INDEX FULL SCAN                 | IDX_PTEST_NAME |      9 |  72353 |  72594 |00:00:00.01 |     392 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)



SQL> drop index idx_ptest_name;

Index dropped.

SQL> create index idx_ptest_name on p_test(object_name,0);

Index created.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
------------------------------
SQL_ID  gz2awka1nh2sf, child number 0
-------------------------------------
select *   from (select *           from (select a.*, rownum rn
          from (select /*+index(p_test idx_ptest_name)*/
          *                           from p_test
   order by object_name) a)          where rownum <= 10)  where rn >= 1

Plan hash value: 4135902528

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                |      1 |        |     10 |00:00:00.01 |      10 |
|*  1 |  VIEW                                   |                |      1 |     10 |     10 |00:00:00.01 |      10 |
|*  2 |   COUNT STOPKEY                         |                |      1 |        |     10 |00:00:00.01 |      10 |
|   3 |    VIEW                                 |                |      1 |  72353 |     10 |00:00:00.01 |      10 |
|   4 |     COUNT                               |                |      1 |        |     10 |00:00:00.01 |      10 |
|   5 |      VIEW                               |                |      1 |  72353 |     10 |00:00:00.01 |      10 |
|   6 |       TABLE ACCESS BY GLOBAL INDEX ROWID| P_TEST         |      1 |  72353 |     10 |00:00:00.01 |      10 |
|   7 |        INDEX FULL SCAN                  | IDX_PTEST_NAME |      1 |  72353 |     10 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)

错误的分页框架：

select * 
 from (select t.*, rownum rn from (需要分页的SQL) t) 
where rn >= 1 
  and rn <= 10;

正确的分页框架：

select * 
 from (select * 
         from (select a.*, rownum rn from (需要分页的SQL) a) 
        where rownum <= 10) 
where rn >= 1;

分页语句的优化思路：

如果分页语句中有排序（order by），要利用索引已经排序特性，将order by的列按照排序的先后顺序包含在索引中，同时要注意排序是升序还是降序。
如果分页语句中有过滤条件，我们要注意过滤条件是否有等值过滤条件，如果有等值过滤条件，要将等值过滤条件优先组合起来，然后将排序列放在等值过滤条件后面，最后将非等值过滤列放排序列后面。
如果分页语句中没有等值过滤条件，我们应该先将排序列放在索引前面，将非等值过滤列放在后面，最后利用rownum的count stopkey特性来优化分页SQL。
如果分页中没有排序，可以直接利用rownum的count stopkey特性来优化分页SQL。
当where条件中有like时，非等值过滤，排序时，应该将排序列放在索引最前面，like和非等值过滤列放后面，二者之间顺序可以互换。

等值过滤>排序列>非等值过滤