本文探讨了Oracle 11g的ReferencePartition特性在执行计划优化和管理方面的优势。通过实验对比了不同情况下的执行计划成本，并展示了在使用分区索引和直方图统计后的效果改善。
上篇中，我们介绍了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
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| T
--------------------------------------------------------------------------------
| 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
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 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
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 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
------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-----------------------------------------------------------
| 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
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 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推出的一个重要的新特性。借助该特性，我们从定义分区表、管理分区表和使用分区表等多个方面，都可以得到很多好处。
扫一扫
1414
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
