[Oracle] 探讨分区之一 -分区剪枝(二)
--------------------------------------------------------------
| 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 | SALES | 10 | 110 | 6 (17)| 00:00:01 | 1 | 19 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(INTERNAL_FUNCTION("TIME_ID")=TO_TIMESTAMP('1-jan-2000',:B1))