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)|00:00:04|KEY(I) |KEY(I)|

| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |2517|72993| 292 (0)|00:00:04|KEY(I) |KEY(I)|

| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |

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

Predicate Information (identified by operation id):

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

5 - access("TIME_ID"=:A OR "TIME_ID"=:B OR "TIME_ID"=:C OR "TIME_ID"=:D)

對於並行執行計劃來說，只有分割槽START和STOP欄位包含分割槽修剪資訊。Operation欄位包含的是並行操作的資訊，如下例子：

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

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 4058105390

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

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

| 0| SELECT STATEMENT | |2517|72993| 75(36)|00:00:01| | | | | |

| 1| PX COORDINATOR | | | | | | | | | | |

| 2| PX SEND QC(RANDOM)|:TQ10000|2517|72993| 75(36)|00:00:01| | |Q1,00| P->S|QC(RAND|

| 3| PX BLOCK ITERATOR| |2517|72993| 75(36)|00:00:01|KEY(I)|KEY(I)|Q1,00| PCWC| |

|* 4| TABLE ACCESS FULL| SALES |2517|72993| 75(36)|00:00:01|KEY(I)|KEY(I)|Q1,00| PCWP| |

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

Predicate Information (identified by operation id):

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

4 - filter("TIME_ID"=:A OR "TIME_ID"=:B OR "TIME_ID"=:C OR "TIME_ID"=:D)

Dynamic Pruning with Subqueries

子查詢使用動態修剪的例子:

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 hash value: 3827742054

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

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

| 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)

Dynamic Pruning with Star Transformation

星型轉換和分割槽修剪的例子:

SQL> explain plan for select p.prod_name, t.time_id, sum(s.amount_sold)

from sales s, times t, products p

where s.time_id = t.time_id and s.prod_id = p.prod_id and t.fiscal_year = 2000

and t.fiscal_week_number = 3 and p.prod_category = 'Hardware'

group by t.time_id, p.prod_name;

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 4020965003

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

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

| 0 | SELECT STATEMENT | | 1 | 79 | | |

| 1 | HASH GROUP BY | | 1 | 79 | | |

|* 2 | HASH JOIN | | 1 | 79 | | |

|* 3 | HASH JOIN | | 2 | 64 | | |

|* 4 | TABLE ACCESS FULL | TIMES | 6 | 90 | | |

| 5 | PARTITION RANGE SUBQUERY | | 587 | 9979 |KEY(SQ)|KEY(SQ)|

| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 587 | 9979 |KEY(SQ)|KEY(SQ)|

| 7 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 8 | BITMAP AND | | | | | |

| 9 | BITMAP MERGE | | | | | |

| 10 | BITMAP KEY ITERATION | | | | | |

| 11 | BUFFER SORT | | | | | |

|* 12 | TABLE ACCESS FULL | TIMES | 6 | 90 | | |

| 14 | BITMAP MERGE | | | | | |

| 15 | BITMAP KEY ITERATION | | | | | |

| 16 | BUFFER SORT | | | | | |

| 17 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 14 | 658 | | |

|* 18 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 14 | | | |

| 20 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 14 | 658 | | |

|* 21 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 14 | | | |

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

Predicate Information (identified by operation id):

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

2 - access("S"."PROD_ID"="P"."PROD_ID")

3 - access("S"."TIME_ID"="T"."TIME_ID")

4 - filter("T"."FISCAL_WEEK_NUMBER"=3 AND "T"."FISCAL_YEAR"=2000)

12 - filter("T"."FISCAL_WEEK_NUMBER"=3 AND "T"."FISCAL_YEAR"=2000)

13 - access("S"."TIME_ID"="T"."TIME_ID")

18 - access("P"."PROD_CATEGORY"='Hardware')

19 - access("S"."PROD_ID"="P"."PROD_ID")

21 - access("P"."PROD_CATEGORY"='Hardware')

Note

-----

- star transformation used for this statement

Dynamic Pruning with Nested Loop Joins

NESTED LOOP JOIN和分割槽修剪的例子:

SQL> explain plan for select t.time_id, sum(s.amount_sold)

from sales s, times t

where s.time_id = t.time_id and t.fiscal_year = 2000 and t.fiscal_week_number = 3

group by t.time_id;

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 50737729

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

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

| 0 | SELECT STATEMENT | | 6 | 168 | 126 (4)| 00:00:02 | | |

| 1 | HASH GROUP BY | | 6 | 168 | 126 (4)| 00:00:02 | | |

| 2 | NESTED LOOPS | | 3683 | 100K| 125 (4)| 00:00:02 | | |

|* 3 | TABLE ACCESS FULL | TIMES | 6 | 90 | 18 (0)| 00:00:01 | | |

| 4 | PARTITION RANGE ITERATOR| | 629 | 8177 | 18 (6)| 00:00:01 | KEY | KEY |

|* 5 | TABLE ACCESS FULL | SALES | 629 | 8177 | 18 (6)| 00:00:01 | KEY | KEY |

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

Predicate Information (identified by operation id):

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

3 - filter("T"."FISCAL_WEEK_NUMBER"=3 AND "T"."FISCAL_YEAR"=2000)

5 - filter("S"."TIME_ID"="T"."TIME_ID")

Partition Pruning Tips

當使用分割槽修剪的時候，你可能要考慮如下情況：

資料型別轉換
函式呼叫
集合表

資料型別轉換

為了從分割槽修剪中獲得最大的效能，你應該避免資料型別的轉換。靜態修剪的獲得的益處比動態修剪多。

一個很常見的例子是DATE型別。DATE型別並不是一個字串，但經常用字串表示。它的格式依賴於一個NSL設定。

請看如下例子：

explain plan for SELECT SUM(amount_sold) total_revenue

FROM sales,

WHERE time_id between '01-JAN-00' and '31-DEC-00';

The plan should now be similar to the following:

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

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

| 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')

在這個例子中，關鍵字KEY表示發生動態修剪。

explain plan for select sum(amount_sold)

from sales

where time_id between '01-JAN-2000' and '31-DEC-2000' ;

The execution plan now shows the following:

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 127 (4)| | |

| 1 | SORT AGGREGATE | | 1 | 13 | | | |

| 2 | PARTITION RANGE ITERATOR| | 230K| 2932K| 127 (4)| 13 | 16 |

|* 3 | TABLE ACCESS FULL | SALES | 230K| 2932K| 127 (4)| 13 | 16 |

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

Predicate Information (identified by operation id):

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

3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', "syyyy-mm-dd hh24:mi:ss'))

而這個例子發生的是靜態修剪，那是因為DATE格式和NLS格式一致，如下：

alter session set nls_date_format='fmdd Month yyyy';

explain plan for select sum(amount_sold)

from sales

where time_id between '01-JAN-2000' and '31-DEC-2000' ;

The execution plan now shows the following:

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 525 (8)| | |

| 1 | SORT AGGREGATE | | 1 | 13 | | | |

|* 2 | FILTER | | | | | | |

| 3 | PARTITION RANGE ITERATOR| | 230K| 2932K| 525 (8)| KEY | KEY |

|* 4 | TABLE ACCESS FULL | SALES | 230K| 2932K| 525 (8)| KEY | KEY |

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

Predicate Information (identified by operation id):

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

2 - filter(TO_DATE('01-JAN-2000')<=TO_DATE('31-DEC-2000'))

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

在這個計劃中使用的動態修剪不如靜態修剪有效，除非你能夠轉換資料型別到和分割槽鍵完全一致：

explain plan for select sum(amount_sold)

from sales

where time_id between to_date('01-JAN-2000','dd-MON-yyyy')

and to_date('31-DEC-2000','dd-MON-yyyy') ;

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 127 (4)| | |

| 1 | SORT AGGREGATE | | 1 | 13 | | | |

| 2 | PARTITION RANGE ITERATOR| | 230K| 2932K| 127 (4)| 13 | 16 |

|* 3 | TABLE ACCESS FULL | SALES | 230K| 2932K| 127 (4)| 13 | 16 |

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

Predicate Information (identified by operation id):

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

3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Function Calls

有幾種情況最佳化器不能執行分割槽修剪。如下例子：

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型別，這樣表示式被重寫為:

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

The explain plan for this statement is as follows:

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

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

| 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 | 16 |

|*3 | TABLE ACCESS FULL | SALES | 10 | 110 | 6 (17)| 00:00:01 | 1 | 16 |

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

Predicate Information (identified by operation id):

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

3 - filter(INTERNAL_FUNCTION("TIME_ID")=TO_TIMESTAMP('1-jan-2000',:B1))

15 rows selected

這樣導致SELECT訪問了所有的分割槽。

這個例子也有同樣的效果:

EXPLAIN PLAN FOR

SELECT SUM(amount_sold)

FROM sales

WHERE TO_CHAR(time_id,'yyyy') = '2000';

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 527 (9)| 00:00:07 | | |

| 1 | SORT AGGREGATE | | 1 | 13 | | | | |

| 2 | PARTITION RANGE ALL| | 9188 | 116K| 527 (9)| 00:00:07 | 1 | 28 |

|* 3 | TABLE ACCESS FULL | SALES | 9188 | 116K| 527 (9)| 00:00:07 | 1 | 28 |

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

Predicate Information (identified by operation id):

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

3 - filter(TO_CHAR(INTERNAL_FUNCTION("TIME_ID"),'yyyy')='2000')

Collection Tables

EXPLAIN PLAN FOR

SELECT p.ad_textdocs_ntab

FROM print_media_part p;

Explained.

PLAN_TABLE_OUTPUT

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

Plan hash value: 2207588228

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

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

| 0 | SELECT STATEMENT | | | |

| 1 | PARTITION REFERENCE SINGLE| | KEY | KEY |

| 2 | TABLE ACCESS FULL | TEXTDOC_NT | KEY | KEY |

| 3 | PARTITIONRANGEALL | | 1 | 2 |

| 4 | TABLE ACCESS FULL | PRINT_MEDIA_PART | 1 | 2 |

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

Note

-----

- dynamic sampling used for this statement

Oracle的分割槽修剪介紹：Partition Pruning

Oracle的分割槽修剪介紹：Partition Pruning

相關文章