Interval-Reference Partitioning –

The top partitioning strategy in 12c is interval-reference.   Partitions in the referenced partitioned table link back to interval partition in the parent table.  Any operation that transform interval partitions in conventional partition of the parent table will construct the corresponding transformation in child table and create partition in child table as necessary.     This type of partitioning gives better performance.   COMPATIBLE parameter must be set to 12.0.0.0 or higher to use.

 

Cascade Functionality –

The commands TRUNCATE Partition and EXCHANGE Partition in reference or interval-reference partitions cascade to references in child table.  Thus child table inherit changes to parent table, by default cascade options are disabled for compatibility purposes.   CASCADE option is provided for the ALTER TABLE EXCHANGE PARTITION and ALTET TABLE EXCHANGE SUBPARTITION SQL commands.  All foreign keys must be defined with ON DELETE CASCADE option.

 

Maintenance Operations on Multiple Partitions –

Oracle 12c allows you to do multiple tasks with single command, where partition names are now separated by commas.

1. Add – multiple partitions and sub-partitions can be added with single add command.
2. Drop – multiple partitions and sub-partitions can be dropped with single drop command.
3. Truncate – ditto.
4. Merge – multiple partitions specified with single INTO PARTITION clause.
5. SPLIT – Single partition specified and INTO PARTITION clause can specify multiple partitions for division.

 

Online Move Partition –

Oracle 12c allows for DDL operation to move or redefine while DML operations continue. MOVE PARTITION clause of ALTER TABLE changes existing data through the creating new segments and dropping old.   However, MODIFY PARTITION clause of ALTER TABLE only changes future data.

MOVE PARTITION clause of ALTER TABLE can do the following:

1. Re-cluster data and reduce fragmentation.
2. Move a partition to another tablespace.
3. Modify create-time attribute
4. Compress data using table compression.

 

Larry Catt

OCP