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 partitioning document
Oracle Database VLDB and Partitioning Guide 11g Release 2 (11.2)-310
Oracle数据库10g中的Partitioning数据分区
Partitioning can provide tremendous benefits to a wide variety of applications by improving manageability, performance, and availability.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SHOW USER; //查看当前用户 USER is "SYS“ SQL> ...
Oracle 分区知识培训文档 pdf版 英文资料
包括表分区方法、索引分区方法。 范围分区(range partitioning); 哈希分区(hash partitioning); 列表分区(list partitioning); 范围-哈希组合分区(composite range-hash ...11g中自动增加新分区。
• 数据分区概念 • 数据分区的好处 • Oracle 数据分区技术的发展 • Oracle11g数据分区的增强功能
《Oracle分区技术和11g分区新特性》,53页,解决大表问题 为何要数据分区(Data Partitioning) • 表与索引数据的爆炸性增长 • 在大型数据库系统中 表数据量通常 >> 10GB • 现在表的数据量比以前中等规模的数据库...
VLDB and Partitioning Guide11g Release 2 (11.2)E16541-05August 2010Oracle Database VLDB and Partitioning Guide, 11g Release 2 (11.2)E16541-05Copyright :copyright: 2008, 2010, Oracle and/or its ...
Partitioning Trust in Network Testbeds
This paper describes basic database partitioning concepts, and provides examples of some of the tasks required to implement partitioning with Oracle E-Business Suite. Partitioning can bring ...
Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition Now in its second edition, this best-selling book by Tom Kyte of Ask Tom ...
ORACLE常用傻瓜问题1000问: 1. Oracle安裝完成后的初始口令? internal/oracle ... With the Partitioning option JServer Release 9.0.1.0.0 - Production SQL> select * from v$version; BANNER
( g , g p ) (e.g., transforming a floorplan to a new one) Simulated annealing engine o A variable T, analogous to temperature o An initial temperature T 0 (e.g., T 0 = 40,000) o A freezing ...
10g Release 2 (10.2) Contents: Part I Concepts 1 Data Warehousing Concepts Part II Logical Design 2 Logical Design in Data Warehouses Part III Physical Design 3 Physical Design in Data Warehouses ...
This is taught in ECE241 class. It's useful for both computer science major and double E major. Partitioning is a important method to reduce state in electronic problems.
Video Partitioning by Temporal Slice Coherency 图像检测,边缘检测 论文,侵权删
里面的勾给取消掉,其次是在"Review and modify partitioning layout"前面 打勾,便于后期的系统分区与规划,点击"Next",点击"Yes" 6.从这里可以看出,前面选择了的磁盘/dev/sda这里为可编辑状态,前面没有选上的 ...
工作流partitioning算法,可以应用在网格计算或者云计算