Database Reference
In-Depth Information
To see interval reference partitioning in action, let's insert some data. First, we insert rows that will fit within
existing range partitions:
EODA@ORA12CR1> insert into orders values (1, to_date( '01-jun-2014', 'dd-mon-yyyy' ), 'xxx');
1 row created.
EODA@ORA12CR1> insert into orders values (2, to_date( '01-jun-2015', 'dd-mon-yyyy' ), 'xxx');
1 row created.
EODA@ORA12CR1> insert into order_line_items values( 1, 1, 'yyy' );
1 row created.
EODA@ORA12CR1> insert into order_line_items values( 2, 1, 'yyy' );
1 row created.
All of the prior rows fit into the partitions specified when creating the tables. The following query displays the
current partitions:
EODA@ORA12CR1> select table_name, partition_name from user_tab_partitions
2 where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS' )
3 order by table_name, partition_name;
TABLE_NAME PARTITION_NAME
------------------------- --------------------
ORDERS PART_2014
ORDERS PART_2015
ORDER_LINE_ITEMS PART_2014
ORDER_LINE_ITEMS PART_2015
Next, rows are inserted that don't fit into an existing range partition; therefore, Oracle automatically creates
partitions to hold the newly inserted rows:
EODA@ORA12CR1> insert into orders values (3, to_date( '01-jun-2016', 'dd-mon-yyyy' ), 'xxx');
1 row created.
EODA@ORA12CR1> insert into order_line_items values (3, 1, 'zzz' );
1 row created.
The following query shows that two interval partitions were automatically created, one for the parent table and
one for the child table:
EODA@ORA12CR1> select a.table_name, a.partition_name, a.high_value,
2 decode( a.interval, 'YES', b.interval ) interval
3 from user_tab_partitions a, user_part_tables b
4 where a.table_name IN ('ORDERS', 'ORDER_LINE_ITEMS')
5 and a.table_name = b.table_name
6 order by a.table_name;
