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 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
