[
Oracle] 探讨分区之一 -分区剪枝
分区剪枝
分区剪枝是指对于分区表或分区索引来说，优化器可以自动从FROM和WHERE字句里根据分区键提取出需要扫描的分区，从而避免全表扫描，减少扫描的数据块，提高性能。分区剪枝分为静态和动态，静态分区剪枝发生在编译阶段，动态分区剪枝发生在执行阶段，下面我们分别来看看这两种分区剪枝执行计划的异同点。
静态分区剪枝
静态分区剪枝在解析阶段就知道需要扫描多少个分区，因此执行计划里的PSTART和PSTOP明确显示扫描的起止分区数，例如：
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
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 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'))
上例中的PSTART,PSTOP都等于17表示只需要扫描第17个分区。
动态分区剪枝
如果在解析阶段无法知道需要扫描多少分区，只有在运行时才能确定，这时Oracle将自动采用动态分区剪枝技术，例如：
SQL> explain plan for select sum(amount_sold) from sales where time_id in
(select time_id from times where fiscal_year = 2000);
Explained.
SQL>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3827742054
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 523 (5)| 00:00:07 | | |
| 1 | SORT AGGREGATE | | 1 | 25 | | | | |
|* 2 | HASH JOIN | | 191K| 4676K| 523 (5)| 00:00:07 | | |
|* 3 | TABLE ACCESS FULL | TIMES | 304 | 3648 | 18 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE SUBQUERY| | 918K| 11M| 498 (4)| 00:00:06 |KEY(SQ)|KEY(SQ)|
| 5 | TABLE ACCESS FULL | SALES | 918K| 11M| 498 (4)| 00:00:06 |KEY(SQ)|KEY(SQ)|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TIME_ID"="TIME_ID")
3 - filter("FISCAL_YEAR"=2000)
在上例中，只有等子查询返回结果之后，才知道需要扫描多少分区，因此在执行计划里，PSTART和PSTOP无法显示确切的分区数，而是用关键字KEY代替，表示动态分区剪枝。
分区剪枝注意事项
1. 数据类型
数据类型的转换可能会导致静态分区剪枝变成动态分区剪枝，影响性能，例如：
explain plan for SELECT SUM(amount_sold) total_revenue
FROM sales,
WHERE time_id between '01-JAN-00' and '31-DEC-00';
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------