往期回顾：

在分区表的查询优化中，是否在WHERE条件中包含分区键字段，会对执行计划产生根本性影响。本文通过实际案例演示分区条件的存在与否如何改变SQL的执行路径，帮助读者深入理解分区表查询优化的核心要点。

实验环境准备

为了清晰展示分区条件对执行计划的影响，我们创建一个基于地区编码的列表分区表，模拟福建省各地市的数据分布：

drop table list_part_tab purge;
--注意，此分区为列表分区
create table list_part_tab (id number,deal_date date,area_code number,nbr number,building varchar2(4000))
           partition by list (area_code)
           (
           partition p_591 values  (591),
           partition p_592 values  (592),
           partition p_593 values  (593),
           partition p_594 values  (594),
           partition p_595 values  (595),
           partition p_596 values  (596),
           partition p_597 values  (597),
           partition p_598 values  (598),
           partition p_599 values  (599),
           partition p_other values  (DEFAULT)
           )
--以下是插入一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into list_part_tab (id,deal_date,area_code,nbr,building)
      select rownum,
             to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
             ceil(dbms_random.value(590,599)),
             ceil(dbms_random.value(18900000001,18999999999)),
             rpad('*',400,'*')
        from dual
      connect by rownum <= 100000;
commit;
update list_part_tab set building='福州市政府' where area_code=591 and rownum=1;
commit;

查询对比分析

语句一：未包含分区条件

select * from list_part_tab where building='福州市政府';
执行计划
--------------------------------------------------------------------------------------------
Plan hash value: 1711407169
--------------------------------------------------------------------------------------------
| Id | Operation          | Name        |Rows | Bytes | Cost (%CPU)| Time     |Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0| SELECT STATEMENT   |              |   86|  172K|  1829   (1)| 00:00:22 |      |       |
|   1|  PARTITION LIST ALL|              |   86|  172K|  1829   (1)| 00:00:22 |    1 |    10 |
|*  2|   TABLE ACCESS FULL| LIST_PART_TAB|   86|  172K|  1829   (1)| 00:00:22 |    1 |    10 |
--------------------------------------------------------------------------------------------
统计信息
--------------------------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
       6576  consistent gets
          0  physical reads
          0  redo size
        694  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

语句二：包含分区条件

select * from list_part_tab where building='福州市政府' and area_code=591;
执行计划
--------------------------------------------------------------------------------------------
Plan hash value: 42322831
--------------------------------------------------------------------------------------------
| Id | Operation             | Name         |Rows | Bytes |Cost (%CPU)| Time    |Pstart| Pstop|
--------------------------------------------------------------------------------------------
|   0| SELECT STATEMENT      |             |    1|  2050 |  205   (1)| 00:00:03|      |      |
|   1|  PARTITION LIST SINGLE|             |    1|  2050 |  205   (1)| 00:00:03|  KEY |   KEY|
|*  2|   TABLE ACCESS FULL   | LIST_PART_TAB|    1|  2050 |  205   (1)| 00:00:03|    1 |     1|
--------------------------------------------------------------------------------------------
统计信息
--------------------------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
        695  consistent gets
          0  physical reads
          0  redo size
        694  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

执行计划差异分析

通过对比两个查询的执行计划，我们发现了关键差异：

分区访问范围：

- 语句一：PARTITION LIST ALL，Pstart=1，Pstop=10，需要扫描所有10个分区

- 语句二：PARTITION LIST SINGLE，Pstart=KEY，Pstop=KEY，仅访问指定分区

性能指标对比：

- 逻辑读取：6576 vs 695，性能提升约9.5倍

- 执行时间：22秒 vs 3秒，显著缩短

业务逻辑与技术实现的平衡

值得注意的是，从业务角度来看，这两个查询在逻辑上是等价的。因为"福州市政府"必然位于福州市（area_code=591），所以添加分区条件并不会改变查询结果的正确性。

然而，从技术实现角度，Oracle优化器无法自动推断出这种业务逻辑关系，因此需要开发人员在编写SQL时主动添加分区条件，以获得最佳性能。

总结与最佳实践

分区条件的有无直接决定了查询的执行效率。在分区表查询中，即使业务逻辑上某些条件是冗余的，但从性能优化角度考虑，明确指定分区键条件仍然是必要的。这种看似"多余"的条件实际上是分区表查询优化的关键所在。

最佳实践建议：

- 在查询分区表时，尽可能在WHERE子句中包含分区键条件

- 即使业务逻辑上条件冗余，技术实现上仍需明确指定

- 通过执行计划中的Pstart和Pstop值来验证分区裁剪是否生效

- 关注consistent gets等统计信息来量化性能提升效果

通过合理运用分区条件，我们可以在不改变查询结果的前提下，实现数量级的性能提升，这正是分区表设计的核心价值所在。

未完待续..

左右SQL执行计划妙招 ⑪ ——执行计划利用设计特性改变【分区设计影响】

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

左右SQL执行计划妙招 ⑩ ——执行计划SQL写法差异改变【分区条件有无】

实验环境准备

查询对比分析

执行计划差异分析

业务逻辑与技术实现的平衡

总结与最佳实践

最新文章