The Geek Diary

How to Drop/Truncate Multiple Partitions in Oracle 12C

by

The following are the partitioning related enhancements introduced in Oracle 12c:

  • Online partition operations
  • Reference partitioning enhancements
  • Multi-partition maintenance operations
    • Adding multiple partitions
    • Truncating multiple partitions
    • Merging multiple partitions
    • Splitting into multiple partitions
    • Dropping multiple partitions

Dropping a Partition

Dropping a partition will discard the rows stored in that partition as a DDL statement. This cannot be rolled back. It executes quickly and uses few system resources (Undo and Redo). You must be the owner of the table or have the DROP ANY TABLE privilege to drop a partition.

You cannot drop a partition of a hash-partitioned table. You cannot drop a partition from a reference-partitioned table. If a drop partition is performed on a parent table, this operation cascades to all descendant tables. For range-partitioned tables, dropping a partition does not make inserts of the dropped range invalid; they are now part of the next-higher partition. If the dropped partition was the highest partition, possibly even if it had MAXVALUE as its end range, then inserts to the missing partition do fail. You cannot drop the highest range partition in an interval-partitioned table because this would imply a change to the transition point.
If a table contains only one partition, you cannot drop the partition. You must drop the table.

Dropping Multiple Partitions

You can use multi-partition maintenance operations which enable dropping multiple partitions and truncating multiple partitions using a single SQL data definition language (DDL) statement.

Note: If a domain index is defined on the table, then you can drop/truncate only one partition at a time. Rows inside the partition(s) being dropped are eliminated, so if you want to keep the rows you must use the MERGE PARTITIONS instead.

In addition, Global index maintenance can now be delayed and decoupled using DROP and TRUNCATE partition without making a global index unusable. This new feature provides faster DROP and TRUNCATE partition operations. Index maintenance can be delayed to off-peak time.

You can remove multiple partitions or subpartitions from a range or list partitioned table with the DROP PARTITIONS and DROP SUBPARTITIONS clauses of the ALTER TABLE statement.
For example, the example below drops multiple partitions from the Range-partitioned table, range_sales.

CREATE TABLE range_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , quantity_sold  NUMBER(3)
    , amount_sold         NUMBER(10,2)
    ) 
PARTITION BY RANGE (time_id)
  (PARTITION SALES_Q2_2011 VALUES LESS THAN (TO_DATE('01-APR-2011','DD-MON-YYYY')),
   PARTITION SALES_Q3_2011 VALUES LESS THAN (TO_DATE('01-OCT-2011','DD-MON-YYYY')),
   PARTITION SALES_Q4_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')),
   PARTITION SALES_Q1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','DD-MON-YYYY')),
   PARTITION SALES_Q2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')),
   PARTITION SALES_Q3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','DD-MON-YYYY')),
   PARTITION SALES_Q4_2012 VALUES LESS THAN (MAXVALUE));
ALTER TABLE range_sales DROP PARTITIONS SALES_Q2_2011, SALES_Q3_2011, SALES_Q4_2011;

Note: You cannot drop all the partitions of a table. If the table contains only one partition, then you cannot drop the partition. You must drop the table. When you drop multiple partitions, local and global index operations are the same as when you drop a single partition. That is the corresponding partitions of local indexes are also dropped in the operation, and the Global indexes must be rebuilt unless the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause is specified.

Truncating Partitions

Use the ALTER TABLE … TRUNCATE PARTITION statement to remove all rows from a table partition. The ALTER TABLE … TRUNCATE PARTITIONS statement does this for multiple partitions. Truncating a partition is similar to dropping a partition, except that the partition is emptied of its data, but not physically dropped.

Using the same example as before:

CREATE TABLE range_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , quantity_sold  NUMBER(3)
    , amount_sold         NUMBER(10,2)
    ) 
PARTITION BY RANGE (time_id)
  (PARTITION SALES_Q2_2011 VALUES LESS THAN (TO_DATE('01-APR-2011','DD-MON-YYYY')),
   PARTITION SALES_Q3_2011 VALUES LESS THAN (TO_DATE('01-OCT-2011','DD-MON-YYYY')),
   PARTITION SALES_Q4_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')),
   PARTITION SALES_Q1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','DD-MON-YYYY')),
   PARTITION SALES_Q2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')),
   PARTITION SALES_Q3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','DD-MON-YYYY')),
   PARTITION SALES_Q4_2012 VALUES LESS THAN (MAXVALUE));
ALTER TABLE range_sales TRUNCATE PARTITIONS SALES_Q2_2011, SALES_Q3_2011, SALES_Q4_2011;

The corresponding partitions of local indexes are truncated in the operation. Global indexes must be rebuilt unless the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause is specified.

Index maintenance

Asynchronous global index maintenance for DROP and TRUNCATE is performed by default; however, the UPDATE INDEXES clause is still required for backward compatibility. Global index maintenance is being decoupled from the DROP and TRUNCATE partition maintenance operation without rendering a global index unusable. Index maintenance is done asynchronously and can be delayed to a later point-in-time. Delaying the global index maintenance to off-peak times without impacting the index availability makes DROP and TRUNCATE partition and subpartition maintenance operations faster and less resource intensive at the point-in-time of the partition maintenance operation.

The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION that supports the updating of global indexes are optimized by making the index maintenance for metadata only. This functionality enables maintenance of a list of data object numbers in metadata, where index entries corresponding to the drop and truncated objects that are invalid are ignored.

Maintenance operations on indexes can be performed with the automatic scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB to clean up all global indexes. This job is scheduled to run at 2:00 A.M. on a daily basis by default. You can run this job at any time using DBMS_SCHEDULER.RUN_JOB if you want to proactively clean up the indexes. You can also modify the job to run with a different schedule based on your specific requirements. However, Oracle recommends that you do not drop the job.

The DBMS_PART package provides an interface for maintenance and management operations on partitioned objects. As a consequence of prior partition maintenance operations with asynchronous global index maintenance, global indexes can contain entries pointing to data segments that no longer exist. These stale index rows will not cause any correctness issues or corruptions during any operation on the table or index, whether these are queries, DMLs, DDLs or analyze.

The DBMS_PART.CLEANUP_GIDX procedure will identify and clean up these global indexes to ensure efficiency in terms of storage and performance.

Partial Global Index Optimization

The column ORPHANED_ENTRIES is added to the dictionary views USER_INDEXES and USER_IND_PARTITIONS. This column specifies whether or not a global index
(partition) contains stale entries due to deferred index maintenance during DROP/TRUNCATE PARTITION, or MODIFY PARTITION operations.

The column can have one of three values:

  • YES: The index (partition) contains orphaned entries
  • NO: The index (partition) does not contain any orphaned entries
  • N/A: The property is not applicable – this is the case for local indexes, or indexes on non-partitioned tables.

You can also force a cleanup of an index needing maintenance using one of the following options:
1. DBMS_PART.CLEANUP_GIDX: This PL/SQL package procedure gathers the list of global indexes in the system that may require cleanup and runs the operations
necessary to restore the indexes to a clean state. 

exec DBMS_PART.CLEANUP_GIDX('SCOTT','ORDERS_GIDX_ORDERTOTAL');

2. ALTER INDEX REBUILD [PARTITION]: This SQL statement rebuilds the entire index or index partition as was done prior to Oracle Database 12.1 releases; the resulting index (partition) does not contain any stale entries.

ALTER INDEX ORDERS_GIDX_ORDERTOTAL REBUILD;

3. ALTER INDEX COALESCE [PARTITION] CLEANUP: This SQL statement cleans up any orphaned entries in index blocks.

ALTER INDEX  ORDERS_GIDX_ORDERTOTAL COALESCE CLEANUP;

You May Also Like