Create Reference-Partitioned Tables

    Reference partitioning allows tables related by foreign keys to be logically equi-partitioned. The child table is partitioned using the same partitioning key as the parent table without having to duplicate the key columns. Partition maintenance operations performed on the parent table are reflected on the child table, but no partition maintenance operations are allowed on the child table.
    To create a reference-partitioned table, you specify a PARTITION BY REFERENCE clause in the CREATE TABLE statement. This clause specifies the name of a referential constraint and this constraint becomes the partitioning referential constraint that is used as the basis for reference partitioning in the table. The referential constraint must be enabled and enforced.

As with other partitioned tables, you can specify object-level default attributes, and you can optionally specify partition descriptors that override the object-level defaults on a per-partition basis.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.

SQL> CREATE TABLE new_orders
  2      ( order_id           NUMBER(12),
  3        order_date         TIMESTAMP(6),
  4        order_mode         VARCHAR2(8),
  5        customer_id        NUMBER(6),
  6        order_status       NUMBER(2),
  7        order_total        NUMBER(8,2),
  8        sales_rep_id       NUMBER(6),
  9        promotion_id       NUMBER(6),
 10        CONSTRAINT new_orders_pk PRIMARY KEY(order_id)
 11      )
 12    PARTITION BY RANGE(order_date)
 13      ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-Feb-2005','DD-MON-YYYY')),
 14        PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-May-2005','DD-MON-YYYY')),
 15        PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-Sep-2005','DD-MON-YYYY')),
 16        PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-Dec-2005','DD-MON-YYYY'))
 17      );
Table created.

SQL> CREATE TABLE new_orders_items
  2      ( order_id           NUMBER(12) NOT NULL,
  3        line_item_id       NUMBER(3)  NOT NULL,
  4        product_id         NUMBER(6)  NOT NULL,
  5        unit_price         NUMBER(8,2) NOT NULL,
  6        quantity           NUMBER(8) NOT NULL,
  7        CONSTRAINT new_orders_items_fk
  8        FOREIGN KEY(order_id) REFERENCES new_orders(order_id)
  9      )
 10      PARTITION BY REFERENCE(new_orders_items_fk);
Table created.

child table:
SQL> SELECT table_name, partition_name, high_value, num_rows
  2  FROM   user_tab_partitions
  3  where  table_name='NEW_ORDERS_ITEMS'
  4  ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
NEW_ORDERS_ITEMS          Q1_2005
NEW_ORDERS_ITEMS          Q2_2005
NEW_ORDERS_ITEMS          Q3_2005
NEW_ORDERS_ITEMS          Q4_2005

SQL> select count(*) from NEW_ORDERs_ITEMS;
  COUNT(*)
----------
         3

the parent table:
SQL> SELECT table_name, partition_name, high_value, num_rows
  2  FROM   user_tab_partitions
  3  where  table_name='NEW_ORDERS'
  4  ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
NEW_ORDERS                Q1_2005              TIMESTAMP' 2005-02-01 00:00:00'
NEW_ORDERS                Q2_2005              TIMESTAMP' 2005-05-01 00:00:00'                   1
NEW_ORDERS                Q3_2005              TIMESTAMP' 2005-09-01 00:00:00'                   1
NEW_ORDERS                Q4_2005              TIMESTAMP' 2005-12-01 00:00:00'                   1

SQL>  select table_name, partitioning_type, ref_ptn_constraint_name
  2     from user_part_tables
  3      where table_name in ('NEW_ORDERS','NEW_ORDERS_ITEMS');

TABLE_NAME                PARTITION REF_PTN_CONSTRAINT_NAME
------------------------- --------- ------------------------------
NEW_ORDERS                RANGE
NEW_ORDERS_ITEMS          REFERENCE NEW_ORDERS_ITEMS_FK
SQL>
SQL>  select table_name, partition_name, high_value
  2      from user_tab_partitions
  3      where table_name='NEW_ORDERS'
  4      or
  5      table_name='NEW_ORDERS_ITEMS'
  6      /

TABLE_NAME                PARTITION_NAME       HIGH_VALUE
------------------------- -------------------- ----------------------------------------
NEW_ORDERS                Q1_2005              TIMESTAMP' 2005-02-01 00:00:00'
NEW_ORDERS                Q2_2005              TIMESTAMP' 2005-05-01 00:00:00'
NEW_ORDERS                Q3_2005              TIMESTAMP' 2005-09-01 00:00:00'
NEW_ORDERS                Q4_2005              TIMESTAMP' 2005-12-01 00:00:00'
NEW_ORDERS_ITEMS          Q1_2005
NEW_ORDERS_ITEMS          Q2_2005
NEW_ORDERS_ITEMS          Q3_2005
NEW_ORDERS_ITEMS          Q4_2005

8 rows selected.

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.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-2144143/，如需轉載，請註明出處，否則將追究法律責任。