Oracle的分区修剪介绍：Partition Pruning

最新推荐文章于 2025-01-24 01:29:35 发布

cuccntzd50572

最新推荐文章于 2025-01-24 01:29:35 发布

阅读量1.3k

点赞数

文章标签：数据库数据结构与算法

Oracle的Partition Pruning是提高数据仓库性能的重要特性，通过分析SQL的WHERE和FROM子句，减少不必要的分区读取。文章介绍了静态和动态修剪，以及何时生效，展示了如何在查询中使用范围、等于、列表等谓词实现分区修剪，以减少数据读取，提升查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle的分区修剪介绍：Partition Pruning

该文译自官方guide

Partition Pruning

在数据仓库中分区修剪是一种非常有效的性能特性。分析修剪分析SQL中的WHERE 和FROM字句，从而在查询中消除不不必要分区。分区修剪技术能大大的减少从磁盘上读取的数据量，从而缩短运行时间，改善查询性能，减少资源浪费。即使你的索引分区和表分区不同，分区修剪也可以在索引上生效（global partition index），从而消除不必要的索引分区。

分区修剪的特性依赖SQL语句，Oracle 有两种分区修剪：动态修剪和静态修剪。静态修剪发生在编译时期，在执行计划指定的时候，已经知道那些分区会被使用。而动态修剪发生在运行时，也就是说在运行的时候，才会知道那些分区会被用到。例如，WHERE字句里面包含一个函数或者子查询用于返回分区键的值。

Information That Can Be Used for Partition Pruning

Oracle分区修剪在你使用range，like，=，inlist等谓词在range或者list分区的时候生效，以及使用=和inlist谓词在hash 分区时。

对于复合分区对象，Oracle能在每个level都实现分区修剪。例如下面的SQL，表sales_range_hash按字段s_saledate做范围分区，按s_productid字段做hash子分区：

CREATE TABLE sales_range_hash(

s_productid NUMBER,

s_saledate DATE,

s_custid NUMBER,

s_totalprice NUMBER)

PARTITION BY RANGE (s_saledate)

SUBPARTITION BY HASH (s_productid) SUBPARTITIONS 8

(PARTITION sal99q1 VALUES LESS THAN

(TO_DATE('01-APR-1999', 'DD-MON-YYYY')),

PARTITION sal99q2 VALUES LESS THAN

(TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),

PARTITION sal99q3 VALUES LESS THAN

(TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),

PARTITION sal99q4 VALUES LESS THAN

(TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));

SELECT * FROM sales_range_hash

WHERE s_saledate BETWEEN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY'))

AND (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) AND s_productid = 1200;

Oracle的分区修剪过程如下：

Oracle访问partitions sal99q2 和 sal99q3
Oracle访问子partition 通过s_productid=1200

How to Identify Whether Partition Pruning has been Used

在EXPAIN PLAN中可以看出分区修剪是否生效。查看PLAN TABLE的字段PSTART (PARTITION_START) and PSTOP (PARTITION_STOP)。

Static Partition Pruning

大多情况下，Oracle在编译的时候判断分区的访问方式。当你使用静态的谓词的时候即发生静态分区，除了下面这些情况：

分区修剪的条件来至一个子查询的结果
优化器利用星型转换重写了查询，而分区修剪发生在转换以后
最有效的执行计划是一个NESTED LOOP

这三种情况其实就是动态修剪。

请看下面的例子:

SQL> explain plan for select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------

Plan hash value: 3971874201

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 673 | 19517 | 27 (8)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE| | 673 | 19517 | 27 (8)| 00:00:01 | 17 | 17 |

|* 2 | TABLE ACCESS FULL | SALES | 673 | 19517 | 27 (8)| 00:00:01 | 17 | 17 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("TIME_ID"=TO_DATE('2001-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

执行计划显示Oracle访问的分区号为17（PSTART 和 PSTOP）。有一点例外的是，执行计划在显示对一个间隔分区的全表扫描时候，PSTART为1，PSTOP为1048575，而不是实际的分区数量。

Dynamic Partition Pruning

动态分区发生在如果静态分区修剪无法生效的时：

Dynamic Pruning with Bind Variables

使用绑定变量会发生分区修剪. 例如:

SQL> explain plan for select * from sales s where time_id in ( :a, :b, :c, :d);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------

Plan hash value: 513834092

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | |2517|72993| 292 (0)|00:00:04| | |

| 1 | INLIST ITERATOR | | | | | | | |

| 2 | PARTITION RANGE ITERATOR | |2517|72993| 292 (0)|

最低0.47元/天解锁文章