上篇中，我們介紹了Reference Partition的建立、使用和原理。本篇將從效能和管理兩個角度，討論Reference Partition的作用。

4、Reference Partition與執行計劃

直觀上看，Reference Partition應當是有益於執行計劃的。主子表之間透過外來鍵進行關聯，最常用的業務場景就是藉助外來鍵列進行關聯查詢。如果主表記錄是在一個或者幾個分割槽上，那麼子表對應的記錄應該是在一個或者幾個分割槽上。

這樣，就從定義層面減少了資料訪問量。下面透過一系列的實驗進行證明。

SQL> explain plan for select * from t_master a, t_detail b where a.object_id=b.master_id;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2684484261

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

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

| 0 | SELECT STATEMENT | | 238K| 18M| | 642 (3)| 0

| 1 | PARTITION LIST ALL | | 238K| 18M| | 642 (3)| 0

| 2 | MERGE JOIN | | 238K| 18M| | 642 (3)| 0

| 3 | SORT JOIN | | 120K| 4936K| 12M| 222 (3)| 0

| 4 | TABLE ACCESS FULL| T_MASTER | 120K| 4936K| | 217 (1)| 0

|* 5 | SORT JOIN | | 240K| 9403K| 23M| 419 (3)| 0

| 6 | TABLE ACCESS FULL| T_DETAIL | 240K| 9403K| | 412 (1)| 0

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

Predicate Information (identified by operation id):

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

5 - access("A"."OBJECT_ID"="B"."MASTER_ID")

filter("A"."OBJECT_ID"="B"."MASTER_ID")

19 rows selected

第一個語句我們沒有加入分割槽條件，訪問了所有的分割槽，路徑中出現了“Partition List All”語句。注意：分割槽表的全表掃描，成本要大於掃描一張大表。分割槽表最大意義在於加入分割槽條件的查詢語句。

第二個語句，我們加入主表的owner分割槽條件。

SQL> explain plan for select * from t_master a, t_detail b where a.object_id=b.master_id and owner='SCOTT';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 3648887064

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

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

| 0 | SELECT STATEMENT | | 2600 | 198K| 495 (1)| 00:00:06

| 1 | PARTITION LIST SINGLE| | 2600 | 198K| 495 (1)| 00:00:06

|* 2 | HASH JOIN | | 2600 | 198K| 495 (1)| 00:00:06

|* 3 | TABLE ACCESS FULL | T_MASTER | 1312 | 49856 | 82 (0)| 00:00:01

| 4 | TABLE ACCESS FULL | T_DETAIL | 240K| 9403K| 412 (1)| 00:00:05

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

Predicate Information (identified by operation id):

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

2 - access("A"."OBJECT_ID"="B"."MASTER_ID")

3 - filter("OWNER"='SCOTT')

17 rows selected

注意：該語句中，利用owner條件，掃描的重點集中在t_master的單一分割槽，進行了“Partition List Single”操作，掃描之後的結果，在子表中“一定對應”一個子表分割槽。所以對T_Detail的掃描也是Partition List Single。

如果子表沒有分割槽，我們進行一下實驗。

SQL> create table t_normal as select * from t_detail;

Table created

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats(user,'T_NORMAL',cascade => true);

PL/SQL procedure successfully completed

檢視執行計劃：

SQL> explain plan for select * from t_master a, t_normal b where a.object_id=b.master_id and owner='SCOTT';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1706510341

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

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

| 0 | SELECT STATEMENT | | 2600 | 198K| 494 (1)| 00:00:0

|* 1 | HASH JOIN | | 2600 | 198K| 494 (1)| 00:00:0

| 2 | PARTITION LIST SINGLE| | 1312 | 49856 | 82 (0)| 00:00:0

|* 3 | TABLE ACCESS FULL | T_MASTER | 1312 | 49856 | 82 (0)| 00:00:0

| 4 | TABLE ACCESS FULL | T_NORMAL | 240K| 9403K| 411 (1)| 00:00:0

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

Predicate Information (identified by operation id):

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

1 - access("A"."OBJECT_ID"="B"."MASTER_ID")

3 - filter("OWNER"='SCOTT')

17 rows selected

請注意對T_NORMAL表的“Table Access Full”操作，它並沒有涉及到分割槽。也就是說，從t_master中篩選到的記錄owner=’SCOTT’到T_NORMAL中，進行的是全表掃描操作。

從現在執行計劃，我們的確看到了Reference Partition在執行計劃上的優勢。那麼，還有無提升空間？

餘地就是外來鍵索引！為了避免大規模併發過程中出現死鎖的情況，外來鍵列是要求加索引的。筆者如果在Reference Partition的情況下，加入索引，會如何呢？

SQL> create index idx_t_detail_mas on t_detail(master_id) local;

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T_DETAIL',cascade => true);

PL/SQL procedure successfully completed

執行計劃：

SQL> explain plan for select * from t_master a, t_detail b where a.object_id=b.master_id and owner='SCOTT';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 3648887064

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

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

| 0 | SELECT STATEMENT | | 2600 | 198K| 495 (1)| 00:00:06

| 1 | PARTITION LIST SINGLE| | 2600 | 198K| 495 (1)| 00:00:06

|* 2 | HASH JOIN | | 2600 | 198K| 495 (1)| 00:00:06

|* 3 | TABLE ACCESS FULL | T_MASTER | 1312 | 49856 | 82 (0)| 00:00:01

| 4 | TABLE ACCESS FULL | T_DETAIL | 240K| 9403K| 412 (1)| 00:00:05

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

Predicate Information (identified by operation id):

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

2 - access("A"."OBJECT_ID"="B"."MASTER_ID")

3 - filter("OWNER"='SCOTT')

17 rows selected

執行計劃沒有改變，說明在CBO計算過程中，索引策略被分割槽策略成本值“打敗”了。這個在很多沒有特殊調優的分割槽表語句中十分常見。

筆者嘗試了一下直方圖路徑，看是否可以生成更好的執行計劃。

SQL> exec dbms_stats.gather_table_stats(user,'T_MASTER',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T_DETAIL',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

執行計劃情況：

SQL> explain plan for select * from t_master a, t_detail b where a.object_id=b.master_id and owner='SCOTT';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2296204501

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

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

| 0 | SELECT STATEMENT | | 35 | 2730 | 136 (0)| 00:00:02 | | |

| 1 | PARTITION LIST SINGLE | | 35 | 2730 | 136 (0)| 00:00:02 | KEY | KEY |

| 2 | NESTED LOOPS | | 35 | 2730 | 136 (0)| 00:00:02 | | |

| 3 | NESTED LOOPS | | 36 | 2730 | 136 (0)| 00:00:02 | | |

|* 4 | TABLE ACCESS FULL | T_MASTER | 18 | 684 | 82 (0)| 00:00:01 | 3 | 3 |

|* 5 | INDEX RANGE SCAN | IDX_T_DETAIL_MAS | 2 | | 1 (0)| 00:00:01 | KEY | KEY |

| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| T_DETAIL | 2 | 80 | 3 (0)| 00:00:01 | KEY | KEY |

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

Predicate Information (identified by operation id):

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

4 - filter("OWNER"='SCOTT')

5 - access("A"."OBJECT_ID"="B"."MASTER_ID")

19 rows selected

索引路徑走到，執行計劃成本下降到136。

說明：在使用Reference Partition的情況下，主子表外來鍵連線的語句的確可以得到一定程度的效能提升。更容易生成更好的執行計劃。

5、管理角度

從管理角度看，Reference Partition將主子表記錄“牢牢”的繫結在一起，對應的分割槽也緊密關係在一起。

如果我們對主表分割槽進行操作處理，對應的子表分割槽也會進行自動的操作。下面我們操作t_master物件分割槽。

我們將主表分割槽p1摘除，僅從主從表記錄關係看，資料庫應該讓先刪除子表記錄。

SQL> alter table t_master drop partition p1;

Table altered

摘除成功，說明Reference Partition連帶影響子表分割槽被刪除。

SQL> col num_rows for a10;

SQL> col high_value for a10;

SQL> col partition_name for a10;

SQL> col table_name for a10;

SQL> select table_name, partition_name, high_value,num_rows from dba_tab_partitions where table_owner='SYS' and table_name in

('T_DETAIL','T_MASTER');

TABLE_NAME PARTITION_ HIGH_VALUE NUM_ROWS

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

T_DETAIL P0 67992

T_DETAIL P3 97096

T_MASTER P0 'PUBLIC' 33996

T_MASTER P3 default 48548

主子表分割槽聯動處理。

6、結論

Reference Partition是Oracle 11g推出的一個重要的新特性。藉助該特性，我們從定義分割槽表、管理分割槽表和使用分割槽表等多個方面，都可以得到很多好處。

聊聊Oracle 11g中的Reference Partition（下）

相關文章