Oracle 11g Reference Partitioning (original)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

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:

Reference to the: McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008

      

In this paper, the original, reproduced please indicate the source, author

If there is an error, please correct me

The mailbox

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Uriah at November 28, 2013 - 12:17 AM