OCP 12C – Partitioning Enhancements

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)


Online Partition operations

  • Table Partitions and subpartitions can now be moved online.

  • Compression options can also be added during an online partition move.

Reference Partitioning Enhancements

Truncate or Exchange Partition with Cascade option

  • With Oracle 12c, it is now possible to use the CASCADE option to cascade operations to a child-referenced table when you TRUNCATE PARTITION or EXCHANGE PARTITION.
    • A parent table ORDERS
    • A child-referenced table INVOICE containing invoces for the corresponding orders in the first table.

Interval Reference Partitioning

  • Before Oracle 12c you can’t use an interval-partitioned table as a parent table for reference partitioning, it is now possible.

 Truncate with Cascade option

  • If you use the CASCADE option to truncate a parent table, all the child tables will be truncated too.

Multipartition Maintenance Operations

 Adding multiple partitions

  • You can now add multiple partitions at the same time with the ADD PARTTITION or ADD SUBPARTITION clauses:

 Truncate multiple partitions

  • You can now truncate multiple partitions at a time:

  •  Be careful if you don’t specify the UPDATE INDEXES clause in you ALTER command, you have to rebuild the indexes after.

Merging Multiple Partitions

  • You can now MERGE multiple partitions or subpartitions at a time:

 Splitting Into Multiple Partitions

  • You can split a partition or a subpartition into multiples partitions or subartition using the SPLIT PARTITION or SPLIT SUBPARTITION clause:

 Dropping Multiple Partitions

  • You can now drop multiple partitions or subpartitions at a time using the DROP PARTITION or DROP SUBPARTITION clause:

Index Enhancements for Partitioned Tables

Partial Index for Partitioned Tables

  •  Oracle 12c introduces the flexibility to create partial indexes on subset of a partition or subpartition.
  • These indexes can be either global or local indexes.
  • You can use the INDEXING FULL or INDEXING PARTIAL depending you want a partial or full index, the default is INDEXING FULL.
  • Partial Indexes can also be updated asynchronously speeding up the DROP/TRNCATE operations.

  •  When the indexing is turned off like for ORD_P2, the global partial indexes are not updated with corresponding values.
  • The local partial indexes are also created in UNUSABLE mode when indexing is turned off.
  • If the indexing clause is not specified, indexes are create UNUSABLE by default.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.