Reference partitioning

From Oracle FAQ
Jump to: navigation, search

Reference partitioning is a partitioning method introduced in Oracle 11g. Using reference partitioning, a child table can inherit the partitioning characteristics from a parent table.

Example[edit]

Create a parent table with range partitioning:

CREATE TABLE orders (
  order_id    NUMBER PRIMARY KEY,
  order_date  DATE NOT NULL,
  customer_id NUMBER NOT NULL,
  shipper_id  NUMBER)
PARTITION BY RANGE (order_date) (
  PARTITION y1 VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY')),
  PARTITION y2 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')),
  PARTITION y3 VALUES LESS THAN (TO_DATE('01-JAN-2008', 'DD-MON-YYYY')));

Create child table with reference partitioning:

CREATE TABLE order_items (
  order_id    NUMBER NOT NULL,
  product_id  NUMBER NOT NULL,
  price       NUMBER,
  quantity    NUMBER,
  CONSTRAINT order_items_fk FOREIGN KEY (order_id) REFERENCES orders)
PARTITION BY REFERENCE (order_items_fk);

Also see[edit]

  • Foreign key - referential integrity constraint pointing a child table to its parent