上篇中，我们介绍了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 -------------------------------------------------------------------------------- |> | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| T -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 238K| 18M| | 642 (3)| 0 | 1 | PARTITION LIST ALL | 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> --------------------------------------------------- 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 -------------------------------------------------------------------------------- |> | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2600 | 198K| 495 | 1 | PARTITION LIST SINGLE |* 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> --------------------------------------------------- 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 -------------------------------------------------------------------------------- |> | 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> --------------------------------------------------- 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> 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 ------------------------------------------------ |> | 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 -------------------------------------------------------- --------------------------------------------------- 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> PL/SQL procedure successfully completed SQL> exec dbms_stats.gather_table_stats(user,'T_DETAIL',cascade => true,method_opt => 'for all columns> 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 ------------------------------------------------------------------------------------------------------------------------ |> | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 35 | 2730 | 136 | 1 | PARTITION LIST SINGLE | 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 |> | 6 | TABLE ACCESS BY LOCAL INDEX ROWID| T_DETAIL | 2 | 80 | 3 (0)| 00:00:01 | KEY | KEY | ------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------- 4 - filter("OWNER"='SCOTT') 5 - access("A"."OBJECT_ID"="B"."MASTER_ID") 19 rows selected 索引路径走到，执行计划成本下降到 136 。 说明：在使用 Reference Partition 的情况下，主子表外键连接的语句的确可以得到一定程度的性能提升。更容易生成更好的执行计划。 5 、管理角度 从管理角度看， Reference Partition 将主子表记录“牢牢”的绑定在一起，对应的分区也紧密关系在一起。 如果我们对主表分区进行操作处理，对应的子表分区也会进行自动的操作。下面我们操作 t_master 对象分区。 我们将主表分区 p1 摘除，仅从主从表记录关系看，数据库应该让先删除子表记录。 摘除成功，说明 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 推出的一个重要的新特性。借助该特性，我们从定义分区表、管理分区表和使用分区表等多个方面，都可以得到很多好处。
运维网声明
1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003运维网
QQ群⑧：
窥视卡
雷达卡
发表于 2015-12-16 15:02:55
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡