Understanding REFERENCE PARTITION In Oracle Database.

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.

For REFERENCE-PARTITION,explicit PRIMARY KEY-FOREIGN KEY relationship should be present.In earlier oracle versions,if INTERVAL partitioning is created on parent table,then we cannnot create REFERENCE partitioning in child table.Oracle will throw error : ORA-14659 [Partitioning Method Of The Parent Table Is Not Supported].This is fixed in 12c.

SQL> CREATE TABLE orders
  2  (
  3   order_id NUMBER PRIMARY KEY,
  4   order_date DATE NOT NULL,
  5   customer_id NUMBER NOT NULL,
  6   shipped_id NUMBER
  7  )
  8   PARTITION BY RANGE(order_date)
  9   (
 10    PARTITION Y1 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
 11    PARTITION Y2 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
 12    PARTITION Y3 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
 13   );

Table created.

SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'ORDERS';

no rows selected

SQL> SELECT partition_name,num_rows FROM user_tab_partitions WHERE table_name='ORDERS';

PARTITION_NAME  NUM_ROWS
--------------- ----------
Y1
Y2
Y3

SQL> CREATE TABLE order_items
  2  (
  3   order_id NUMBER NOT NULL,
  4   product_id NUMBER NOT NULL,
  5   price NUMBER,
  6   quantity NUMBER,
  7   CONSTRAINT order_items_fk FOREIGN KEY(order_id) REFERENCES orders
  8  )
  9   PARTITION BY REFERENCE(order_items_fk);

Table created.

SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'ORDER_ITEMS';

no rows selected

SQL> SELECT partition_name,num_rows FROM user_tab_partitions WHERE table_name='ORDER_ITEMS';

PARTITION_NAME  NUM_ROWS
--------------- ----------
Y1
Y2
Y3

SQL> EXEC dbms_stats.gather_table_stats('COURSE','ORDERS');

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_table_stats('COURSE','ORDER_ITEMS');

PL/SQL procedure successfully completed.

SQL> SELECT partition_name,num_rows FROM user_tab_partitions WHERE table_name='ORDERS';

PARTITION_NAME  NUM_ROWS
--------------- ----------
Y1                 0
Y2                 0
Y3                 0

SQL> SELECT partition_name,num_rows FROM user_tab_partitions WHERE table_name='ORDER_ITEMS';

PARTITION_NAME  NUM_ROWS
--------------- ----------
Y1                 0
Y2                 0
Y3                 0

SQL> INSERT INTO orders VALUES(1,TO_DATE('31-DEC-2015','DD-MON-YYYY'),1,1);

1 row created.

SQL> INSERT INTO orders VALUES(2,TO_DATE('31-DEC-2016','DD-MON-YYYY'),2,2);

1 row created.

SQL> INSERT INTO orders VALUES(3,TO_DATE('31-DEC-2017','DD-MON-YYYY'),3,3);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO order_items VALUES(1,1,100,1);

1 row created.

SQL> INSERT INTO order_items VALUES(2,2,200,2);

1 row created.

SQL> INSERT INTO order_items VALUES(3,3,300,3);

1 row created.

SQL> INSERT INTO order_items VALUES(4,4,400,4);
INSERT INTO order_items VALUES(4,4,400,4)
*
ERROR at line 1:
ORA-02291: integrity constraint (COURSE.ORDER_ITEMS_FK) violated - parent key not found

SQL> COMMIT;

Commit complete.

SQL> EXEC dbms_stats.gather_table_stats('COURSE','ORDERS');

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_table_stats('COURSE','ORDER_ITEMS');

PL/SQL procedure successfully completed.

SQL> SELECT partition_name,num_rows FROM user_tab_partitions WHERE table_name='ORDERS';

PARTITION_NAME  NUM_ROWS
--------------- ----------
Y1                 1
Y2                 1
Y3                 1

SQL> SELECT partition_name,num_rows FROM user_tab_partitions WHERE table_name='ORDER_ITEMS';

PARTITION_NAME  NUM_ROWS
--------------- ----------
Y1                 1
Y2                 1
Y3                 1

SQL> SELECT * FROM orders;

  ORDER_ID ORDER_DATE CUSTOMER_ID SHIPPED_ID
---------- ---------- ----------- ----------
         1 31-DEC-15           1          1
         2 31-DEC-16           2          2
         3 31-DEC-17           3          3

SQL> SELECT * FROM orders PARTITION(Y1);

  ORDER_ID ORDER_DATE CUSTOMER_ID SHIPPED_ID
---------- ---------- ----------- ----------
         1 31-DEC-15           1          1

SQL> SELECT * FROM orders PARTITION(Y2);

  ORDER_ID ORDER_DATE CUSTOMER_ID SHIPPED_ID
---------- ---------- ----------- ----------
         2 31-DEC-16           2          2

SQL> SELECT * FROM orders PARTITION(Y3);

  ORDER_ID ORDER_DATE CUSTOMER_ID SHIPPED_ID
---------- ---------- ----------- ----------
         3 31-DEC-17           3          3

SQL> SELECT * FROM order_items;

  ORDER_ID PRODUCT_ID      PRICE   QUANTITY
---------- ---------- ---------- ----------
         1          1        100          1
         2          2        200          2
         3          3        300          3

SQL> SELECT * FROM order_items PARTITION(Y1);

  ORDER_ID PRODUCT_ID      PRICE   QUANTITY
---------- ---------- ---------- ----------
         1          1        100          1

SQL> SELECT * FROM order_items PARTITION(Y2);

  ORDER_ID PRODUCT_ID      PRICE   QUANTITY
---------- ---------- ---------- ----------
         2          2        200          2

SQL> SELECT * FROM order_items PARTITION(Y3);

  ORDER_ID PRODUCT_ID      PRICE   QUANTITY
---------- ---------- ---------- ----------
         3          3        300          3