The Geek Diary

Oracle database 12c : How to Drop Partition(s)

by

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.

Indexes

You cannot drop local indexes directly. Corresponding local index partitions are dropped regardless of status when table partition is dropped. You can drop a partition of a global index. The dropped index entries are re-created in the next-higher partition on rebuilding.

When to Drop a Partition

– For tables with rolling time frames, you need to drop a partition with the old data as time passes.
– For a list-partitioned table, you can drop a partition when some partition key values are of no further use.
– When you drop or add a hash partition, it is recommended that you try to end up with a number of hash partitions that is a power of two, for optimal data spread across partitions.

Not Dropping a Partition

If you want to remove the range key but want to keep the data (that is, if you want to have all the data in fewer partitions), you should merge the partition.

Dropping Interval Partitions

Only range and list partitions can be dropped. Interval partitions can be converted to range partitions, and then dropped after they are converted. There are two methods to move the transition point between the range partitions and the interval partitions to the end of the existing interval partitions. This is effect converts all the interval partitions into range partitions so that they can be dropped.

The first method was mentioned in the lesson titled “Implementing Partitioned Tables” and involves issuing a merge command to combine the last two interval partitions. When this is done, the resulting partition becomes a range partition and the transition point is moved to be after the resulting partition.

The second method is by issuing an ALTER TABLE command that sets the interval to what it is already set to, converting all existing interval partitions into range partitions. Standard rules apply when dropping range partitions after they have been converted from interval partitions.

Dropping Multiple Partitions

You can remove multiple partitions or subpartitions from a range or list partitioned table with the DROP PARTITION and DROP SUBPARTITION clauses of the ALTER TABLE statement. For example, the statement in the example shown below drops multiple partitions from the Range-partitioned table, sales. Note that you cannot drop all the partitions of a table. When you drop multiple partitions, local and global index operations are the same as when you drop a single partition.

Remove multiple partitions or subpartitions from a range- or list-partitioned table with the following clauses of the ALTER TABLE statement:
– DROP PARTITION
– DROP SUBPARTITION

SQL> ALTER TABLE sales DROP PARTITIONS sales_q1_2012, 
                                       sales_q2_2012, 
                                       sales_q3_2012, 
                                       sales_q4_2012;

Truncating Multiple Partitions

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

SQL> ALTER TABLE sales TRUNCATE PARTITIONS
                       sales_q1_2012, 
                       sales_q2_2012,
                       sales_q3_2012, 
                       sales_q4_2012;

The corresponding partitions of local indexes are truncated in the operation. Global indexes must be rebuilt unless the UPDATE INDEXES clause is specified. The example above truncates four partitions in the range-partitioned sales table.

Asynchronous Global Index Maintenance

The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION requesting global indexes maintenance are optimized making the index maintenance a metadata-only operation. 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.

You May Also Like