Reference Partitioning
Reference partitioning is a new partitioning scheme in Oracle Database 11g that lets you partition a table on the basis of the partitioning scheme of the table that its reference constraint refers to. Reference partitioning is probably the hardest new partitioning scheme to grasp. The partitioning key is determined through the parent- child relationship between the tables, as enforced by the active primary key or foreign key constraints. Reference partitioning thus lets you logically equipartition a table inheriting the partitioning the key from its parent table. You thus don’t have to duplicate the key columns. Partition maintenance operations are no problem because the database automatically maintains the logical dependency between the two tables during those operations.
You can’t use interval partitioning with reference partitioning.
Unlike in Oracle Database 10g, where partition-wise joins would work only if the partitioning and predicates were identical, reference partitioning has no such limitation. That is, a partition-wise join will work even when query predicates are different.
The following code contains a partitioned parent table and a dependent reference partitioned child table.
SQL> CREATE TABLE parent_tab (
id NUMBER NOT NULL,
code VARCHAR2(10) NOT NULL,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT parent_tab_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
);
SQL> CREATE TABLE child_tab (
id NUMBER NOT NULL,
parent_tab_id NUMBER NOT NULL,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE,
CONSTRAINT child_tab_pk PRIMARY KEY (id),
CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)
REFERENCES parent_tab (id)
)
PARTITION BY REFERENCE (child_parent_tab_fk);
Child records that foreign key to rows in the first partition of the parent table should be placed in the first partition of the child table. So we insert two rows into the first partition and one row into the second of the parent table. We then insert three rows into the child table, with one foreign keyed to a row in the first partition and two foreign keyed to a row in the second partition of the master table.
SQL> INSERT INTO parent_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);
SQL> INSERT INTO parent_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);
SQL> INSERT INTO parent_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE,12));
SQL> INSERT INTO child_tab VALUES (1, 1, 'ONE', '1 1 ONE', SYSDATE);
SQL> INSERT INTO child_tab VALUES (2, 3, 'TWO', '2 3 TWO', SYSDATE);
SQL> INSERT INTO child_tab VALUES (3, 3, 'THREE', '3 3 THREE', SYSDATE);
SQL> COMMIT;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'PARENT_TAB');
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'CHILD_TAB');
We now expect the parent table to have 2 records in the 2007 partition and 1 in the 2008 partition, while the child table should have 1 row in the 2007 partition and 2 rows in the 2008 partition. The following query confirms out expectation.
SQL> COLUMN table_name FORMAT A25
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN high_value FORMAT A40
SQL> SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
CHILD_TAB PART_2007 1
CHILD_TAB PART_2008 2
PARENT_TAB PART_2007 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PARENT_TAB PART_2008 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M 1
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
4 rows selected.
Note :You don’t see a high value for the partitions in the child table
When to Use Reference Partitioning
You can benefit from reference partitioning in the following types of situations:
Whenever you are thinking of duplicating a column in a child table to get partition pruning benefits, you might want to consider reference partitioning instead. For example, you might want to duplicate a column such as ORDER_DATE that’s already in the parent table ORDERS, in the child table ORDER_ITEMS, so the ORDER_ITEMS table can utilize partition pruning. With reference partitioning, you can avoid this duplication of data.
When a query joins the ORDERS and ORDER_ITEMS tables and uses a predicate on the ORDER_ITEMS column, it automatically takes advantage of the partition pruning for both tables.
In cases where you frequently join two large tables that aren’t partitioned on the join key, you can use reference partitioning to take advantage of partition-wise joins. This is because reference partitioning implicitly enables the use of full partition-wise joins.
Reference partitioning helps manage tables that share the same life cycle, by automatically cascading partition operations on the master table to its descendants.
Conditions and Restrictions
The following conditions and restrictions apply to reference partitioning:
- The child table must specify a referential integrity constraint defined on the table being created. This constraint must be in ENABLE VALIDATE NOT DEFERRABLE state (the default) and refer to a primary or unique key on the parent table.
- The foreign key columns referenced in constraint must be NOT NULL.
- The constraint cannot use the ON DELETE SET NULL clause.
- The parent table referenced must be an existing partitioned table. All partitioning methods except interval partitioning are supported.
- The foreign key cannot contain any virtual columns.
- The referenced primary key or unique constraint on the parent table cannot contain any virtual columns.
- Reference partitioning cannot be used for index-organized tables, external tables, or domain index storage tables.
- A chain of reference partitioned tables can be created, but constraint used can't be self-referencing.
- The ROW MOVEMENT setting for both tables must match.
- Reference partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.
- If you don’t specify a tablespace for the new table, the database creates its partitions in the same tablespace as the corresponding partition of the parent table.
- You can’t specify partition bounds for the partitions of a reference-partitioned table.
- You can name the partitions of a reference-partitioned table as long as there’s no conflict with any inherited names. In the case of a conflict, the database will assign the partition a system-generated name.
- You can’t disable the foreign key constraint of a reference-partitioned table.
- You can’t directly perform a partition management operation such as adding or dropping a partition belonging to a reference partitioned table. However, when you perform a partition maintenance operation on the parent table, the operation automatically cascades to the child table.
- The new table will have one partition for each partition in the parent table. If the parent table is subpartitioned, the new partitioned table will have one partition for each subpartition in the child table.
参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》
http://www.oracle-base.com/articles/11g/partitioning-enhancements-11gr1.php
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
Oracle 11g是Oracle公司推出的数据库管理系统的一个重要版本,专为满足企业级数据管理需求而...通过《Oracle Database 11g The Complete Reference》这本书,你可以深入学习和掌握这些知识,为你的职业生涯添砖加瓦。
- **新特性**:Oracle 11g引入了参考分区(reference partitioning)、区间分区(interval partitioning)和虚拟列分区(virtual column partitioning),以及增强了子分区(subpartitioning)功能。 - **好处**:这些新特性...
Oracle 11g中的表分区新特性是数据库管理系统在处理大规模数据时提升性能和管理效率的重要工具。分区表允许将一个大表逻辑上划分为多个较小的部分,每个部分称为分区,这些分区可以独立管理和查询,从而优化数据存储...
### DBA手册(Oracle 11g)关键知识点解析 #### 一、Oracle 11g新特性及其应用价值 1. **新特性综述**: - **Real Application Clusters (RAC)**:增强的集群功能使得多实例能够共享同一个数据库,提高可用性和...
1. **Ora 10G SQL Reference.chm** 这份文档详细介绍了Oracle10g中的SQL语言使用,包括标准的SQL语法以及Oracle特有的扩展。SQL是Structured Query Language的缩写,是用于管理和操作数据库的语言。在Oracle10g中,...
《Oracle SQL Reference 9i+10g》是Oracle公司为数据库管理员、开发人员和分析师提供的官方SQL查询语言指南,涵盖了9i和10g两个重要版本。这两个版本的SQL特性在很多方面都有所增强,使得数据库管理和开发更加高效...
### Oracle Database Reference 10g Release 2(10.2):关键技术知识点解析 #### 一、概述 Oracle Database 10g Release 2(10.2)是Oracle公司于2007年12月发布的数据库管理系统的一个重要版本。此版本不仅在功能...
### Oracle Database 11g Release 2 (11.2) 概念手册知识点解析 #### 一、概述 Oracle Database 11g Release 2(版本号为11.2)是Oracle公司发布的一款数据库管理系统,其核心目标在于提供高性能、高可用性和安全...
7. **分区(Partitioning)**:Oracle 10g提供了多种分区策略,如范围分区、列表分区、哈希分区和复合分区,有助于大规模数据的管理和查询性能提升。 8. **高级复制(Advanced Replication)**:提供同步和异步的...
Oracle Database Reference 12c Release 1 (12.1)是Oracle公司为数据库管理员、开发人员和其他IT专业人员提供的一本权威指南,涵盖了Oracle 12c版本的主要特性和功能。这本书旨在帮助用户理解并有效地利用Oracle 12c...
《Oracle SQL:核心参考》(Oracle SQL: The Essential Reference)是一本由David C. Kreines撰写的书籍,于2000年由O'Reilly出版社出版。该书为Oracle SQL的学习者提供了一个全面深入的指南,覆盖了Oracle SQL的...
hash partitioning, list partitioning, interval partitioning, reference partitioning, composite partitioning等,相应的还有分区索引,借助分区技术,数据库设计人员和后期维护管理员解决因海量数据数据部署和...
6. **Partitioning Improvements**:增强了分区特性,支持异步维护全局索引的DROP和TRUNCATE操作,并引入了Interval+Reference分区方式。 7. **Adaptive Execution Plans**:根据实际运行时的数据分布调整执行计划,...
- Oracle官方文档:Oracle提供了详细的在线文档,包括Oracle Database Concepts、SQL Reference、PL/SQL Language Reference等,是学习和解决问题的重要资源。 - SQL*Plus手册:SQL*Plus是Oracle提供的命令行工具...
"SQL.rar_oracle"这个压缩包显然包含了一些关于在Oracle环境中使用SQL的资料,特别是"SQL+Language+Reference.pdf"这个文件,很可能是Oracle SQL的官方语言参考手册。下面,我们将深入探讨Oracle SQL的一些关键知识...
若想了解您的版本包含了哪些特性,应参考您的 MySQL 5.7 许可协议或联系 Oracle 销售代表。对于每个版本的更新变更,可以查阅 MySQL 5.7 发行说明。 在 MySQL 5.7 中,一些显著的特性包括: 1. **增强的性能**:...
如果用户对所持许可证的版本中包含的特性有任何疑问,应参照自己的MySQL 5.6许可协议或联系Oracle的销售代表。 手册中提及的MySQL 5.6的一些特性可能没有包含在每个版本的MySQL 5.6中,因此手册描述了一些不在每个...
具体包含哪些特性,应参照你的MySQL 5.6许可协议或咨询Oracle的销售代表。 手册分为多个部分,以满足不同类型的用户需求: 1. **管理员**:这部分内容主要针对负责MySQL服务器管理的人员,包括服务器的安装、升级...