Understanding REFERENCE PARTITION In Oracle Database.
- Performance Tuning
- October 29, 2024
- 0 Comment
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