Difference between revisions of "Reference partitioning"
Jump to navigation
Jump to search
Line 32: | Line 32: | ||
</pre> | </pre> | ||
− | * Create Child table with reference to the Parent table | + | * Create Child table with reference to the Parent table. |
<pre> | <pre> | ||
create table sales | create table sales | ||
Line 70: | Line 70: | ||
---------- --------------- ---------- | ---------- --------------- ---------- | ||
CUSTOMERS PA 'A' | CUSTOMERS PA 'A' | ||
− | CUSTOMERS PB ' | + | CUSTOMERS PB 'B' |
CUSTOMERS PC 'C' | CUSTOMERS PC 'C' | ||
Line 80: | Line 80: | ||
---------- --------------- ---------- | ---------- --------------- ---------- | ||
SALES PA 'A' | SALES PA 'A' | ||
− | SALES PB ' | + | SALES PB 'B' |
SALES PC 'C' | SALES PC 'C' | ||
Latest revision as of 19:43, 14 March 2011
What is Reference Partitioning?
- A table can now be partitioned based on the partitioning method of a table referenced in its referential constraint.
- The partition key is resolved through an existing parent/child relationship.
- The partitioning key is enforced by active primary key and foreign key constraints. It is not possible to disable the FK of a reference-partitioned table.
- Tables with a parent/child relationship can be equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns
- Partitions are automatically maintained – any partition maintenance operations on the parent table will be cascaded to the child table.
Demo
- Create Parent Table
create table customers ( cust_id number primary key, cust_name varchar2(200), rating varchar2(1) not null ) partition by list (rating) ( partition pA values ('A'), partition pB values ('B') ); SQL> SELECT table_name, partition_name, high_value FROM USER_tab_partitions WHERE table_name='CUSTOMERS'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------- --------------- ---------- CUSTOMERS PA 'A' CUSTOMERS PB 'B'
- Create Child table with reference to the Parent table.
create table sales ( sales_id number primary key, cust_id number not null, sales_amt number, constraint FK_SALES_01 foreign key (cust_id) references customers ) partition by reference (FK_SALES_01);
- Same partition key is created.
SQL> SELECT table_name, partition_name, high_value FROM USER_tab_partitions WHERE table_name='SALES'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------- --------------- ---------- SALES PA 'A' SALES PB 'B'
- Change in partitions of the parent table will also cascade to that of child table.
SQL> alter table CUSTOMERS add PARTITION pC values ('C'); Table altered SQL> SELECT table_name, partition_name, high_value FROM USER_tab_partitions WHERE table_name='CUSTOMERS'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------- --------------- ---------- CUSTOMERS PA 'A' CUSTOMERS PB 'B' CUSTOMERS PC 'C' SQL> SELECT table_name, partition_name, high_value FROM USER_tab_partitions WHERE table_name='SALES'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------- --------------- ---------- SALES PA 'A' SALES PB 'B' SALES PC 'C'