Partitioning - Expert Oracle Database Architecture

Database Reference

In-Depth Information

EODA@ORA12CR1> insert into order_line_items values ( 2, 1, 'zzz' );

1 row created.

The magic is on line 10 of the CREATE TABLE statement. Here, we replaced the range partitioning statement with

PARTITION BY REFERENCE .

■ If you are using Oracle Database 11 g release 1 and you receive an error " ORA-14652: reference

partitioning foreign key is not supported" , it is due to the fact that release 1 necessitated a “ NOT NULL ”

constraint on every foreign key column. Since ORDER# is part of our primary key, we know it is not null, but release 1 did

not recognize that. You need to define the foreign key columns as NOT NULL .

Note

This allows us to name the foreign key constraint to use to discover what our partitioning scheme will be. Here

we see the foreign key is to the ORDERS table—the database read the structure of the ORDERS table and determined that

it had two partitions—therefore, our child table will have two partitions. In fact, if we query the data dictionary right

now, we can see that the two tables have the same exact partitioning structure:

EODA@ORA12CR1> select table_name, partition_name

2 from user_tab_partitions

3 where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS' )

4 order by table_name, partition_name

5 /

TABLE_NAME PARTITION_NAME

-------------------- --------------------

ORDERS PART_2014

ORDERS PART_2015

ORDER_LINE_ITEMS PART_2014

ORDER_LINE_ITEMS PART_2015

Further, since the database understands these two tables are related, we can drop the parent table partition and

have it automatically clean up the related child table partitions (since the child inherits from the parent, any alteration

of the parent's partition structure cascades down):

EODA@ORA12CR1> alter table orders drop partition part_2014 update global indexes;

Table altered.