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

 

上篇中，我们介绍了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推出的一个重要的新特性。借助该特性，我们从定义分区表、管理分区表和使用分区表等多个方面，都可以得到很多好处。

时间： 2024-10-31 14:28:08

聊聊Oracle 11g中的Reference Partition（下）的相关文章

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

  Data Partition是Oracle早期提出的一项针对大数据对象的解决方案.经过若干版本的演变,Partition依然是目前比较流行.应用广泛并且接受程度较高的技术策略. 从Oracle产品线角度,Partition的成功是与Oracle不断丰富完善分区技术和方案是分不开的.在每一个版本中,Partition技术都推出一些新的进步和发展.无论是8.8i还是11g.12c,Partition技术都是在不断的向前进步,来满足更加复杂的实际应用需求. 本篇主要介绍11g新推出的Referen

盘点 Oracle 11g 中新特性带来的10大性能影响

盘点 Oracle 11g 中新特性带来的10大性能影响 原创 2017-08-02 盖国强 数据和云 Oracle的任何一个新版本,总是会带来大量引人瞩目的新特性,但是往往在这些新特性引入之初,首先引起的是一些麻烦,因为对于新技术的不了解.因为对于旧环境的不适应,从Oracle产品到技术服务运维,总是要走过一个磨合的长期过程. 请注意:我们并不推荐大家盲目的关闭和摒弃Oracle的新特性,我们建议大家在遇到问题时,做出适合自己的调整. 就此盘点一下 Oracle 11g 中,那些新特性带来的新

如何在oracle 11g 中导出空表

  如何在oracle 11g 中导出空表 由于oracle 11g的 延迟段创建的新特性,导致在没有数据插入时,oracle是不会分配数据段的,进而导致exp 是不能导出11g数据库的空表的. 当然采用expdp就不存在这个问题了. expdp hr/hr schemas=hr dumpfile=expdp.dmp directory=dbtest conn hr/hr select TABLE_NAME,NUM_ROWS from user_tables; TABLE_NAME NUM_RO

Oracle 11g中recyclebin参数的微小变化

SQL> show parameter recyclebin NAME      TYPE  VALUE ------------------------------------ ----------- ------------------------------ recyclebin      string  on SQL> alter system set recyclebin=off; alter system set recyclebin=off                    

【Oracle】Oracle 11g 中的自动数据库维护任务管理

对于11g版本的oracle 有三个自动维护定时任务. 1 自动优化器统计收集:为所有方案对象收集陈旧的或缺少的统计数据,所收集的统计信息将被用来提高sql的执行的性能,任务名是"auto optimizer stats collection" 2 自动分段顾问:标识数据库中的段是否有可以回收的空间,并以此信息统计为基础做出怎样整理段的碎片以节约空间.你也可以手动的执行此job来获取最新的建议信息,或者获取自动段advisor 不检测的但又可以回收的段的信息,任务名是"aut

Oracle 11g中CTE的应用示例

关于SQL SERVER中的CTE中的CTE应用,请看这里:http://www.cnblogs.com/downmoon/archive/2009/10/23/1588405.html 其实,ORACLE的CTE语法完全一样,看示例: 一.创建示例数据表如下: declare tableExistedCount number; begin select count(1) into tableExistedCount from user_tables where TABLE_NAME ='Dem

如何解决oracle 11g中的ORA-00845错误

oracle11g数据库,修改了内存参数memory_max_target=943718400,关闭数据库重新启动的时候报错 [oracle@instuctor shm]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 19 09:34:11 2012 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to an idl

Oracle 11g中密码过期问题详解_oracle

密码过期的原因一般有两种可能: 一.由于Oracle中默认在default概要文件中设置了"PASSWORD_LIFE_TIME=180天"所导致. 二.由于Oracle中默认在default概要文件中设置了"FAILED_LOGIN_ATTEMPTS=10次",当输入密码错误次数达到设置值将导致此问题. 第一种情况解决方法如下: 1.查看用户用的哪种profile策略,一般是default: 复制代码 代码如下: SQL> select username,p

解决Windows 7下安装Oracle 11g相关问题的方法_oracle

很多DBA目前还停留在Oracle 9i或者10g,究其原因有可能是Oracle 11g的价格问题.本文将为大家讲解在Windows 7下安装Oracle 11g,也希望大家能从中看到11g的一些新特点. 前不久卸载掉了自己的Windows Vista系统,装上了Windows7 旗舰版,在装机过程中也遇到了很多问题,有些问题是自己不曾遇到过的,但自己尝试着去解决,一步一步尝试着去努力.自己的不懈努力,也换回了成果.比如在装SQL Server 2005和Oracle 11g时就碰到了问题,在装