[Oracle] 探讨分区之一 -分区剪枝

[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 |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |    13 |   525   (8)| 00:00:07 |       |       |
|   1 |  SORT AGGREGATE            |       |     1 |    13 |            |          |       |       |
|*  2 |   FILTER                   |       |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|       |   230K|  2932K|   525   (8)| 00:00:07 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | SALES |   230K|  2932K|   525   (8)| 00:00:07 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------

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

   2 - filter(TO_DATE('01-JAN-00')<=TO_DATE('31-DEC-00'))
   4 - filter("TIME_ID">='01-JAN-00' AND "TIME_ID"<='31-DEC-00') 

 

上例中，分区键time_id为Date类型，但是where字句是Char类型，因此需要做个隐式类型转换，从而导致静态分区剪枝变成动态分区剪枝，影响性能。

 

----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |    13 |   525   (8)| 00:00:07 |       |       |
|   1 |  SORT AGGREGATE            |       |     1 |    13 |            |          |       |       |
|*  2 |   FILTER                   |       |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|       |   230K|  2932K|   525   (8)| 00:00:07 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | SALES |   230K|  2932K|   525   (8)| 00:00:07 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------

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

   2 - filter(TO_DATE('01-JAN-00')<=TO_DATE('31-DEC-00'))
   4 - filter("TIME_ID">='01-JAN-00' AND "TIME_ID"<='31-DEC-00') 

 

 

2. 函数

如果在分区键上有函数（显示或隐式），会导致分区剪枝失效，例如：

EXPLAIN PLAN FOR
SELECT SUM(quantity_sold)
FROM sales
WHERE time_id = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy');

 

因为time_id为Date类型，因此Oracle要把它升级为Timestamp，所以Oracle真正执行的语句如下：

TO_TIMESTAMP(time_id) = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy')

 

分区键上的函数导致分区剪枝失效：

--------------------------------------------------------------------------------------------
|Id | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT     |       |     1 |    11 |     6  (17)| 00:00:01 |       |       |
| 1 |  SORT AGGREGATE      |       |     1 |    11 |            |          |       |       |
| 2 |   PARTITION RANGE ALL|       |    10 |   110 |     6  (17)| 00:00:01 |     1 |    19 |
|*3 |    TABLE ACCESS FULL | SALE