Oracle Partition - Drop a partition (instead of records)

Card Puncher Data Processing


The conventional method is to delete the records, but deletion causes undo and redo information to be generated, which impacts database performance. This can also be a very time-consuming operation, depending on the volume of data being purged. An alternative to deleting records is to drop a partition.

See also: Oracle Partition - Partition Exchange (between tables)





  • The only subpartition of a partition cannot be dropped



After you execute a DROP Partition or Drop Subpartition command, the partition disappears from the table. This statement does not actually delete anything from the table; it merely updates the data dictionary to indicate that the Y05Q1 partition no longer belongs to the table. Because no data moves (except a small amount of information in the data dictionary), undo and redo generation is minimal, making the operation extremely fast compared to the deletion approach and virtually impact-free in terms of performance.

Note that a regular DELETE statement deletes table rows from wherever they exist, leaving empty spaces in the blocks. Although the table data may be sparse, the overall size of the segment may still be large and have a very high high-water mark (HWM, the largest size the table has ever occupied). A high HWM slows full-table scans, because Oracle Database has to search up to the HWM, even if there are no records to be found. When a partition is dropped, the segment itself is gone and the HWM of other segments remains the same, so the query runs faster.

While the DELETE statement deletes the rows, the corresponding index entries are also adjusted, causing fragmentation, which may impact performance.

Effect on Index

When a partition is dropped, the corresponding partition of any local index is also dropped. If there is a global index, however, that index can become unusable when you drop the partition. To prevent the index from becoming unusable, in Oracle9i Database and later, you can update the global index when you drop the partition. The following drops the Y05Q1 partition and updates the global index:

alter table trans drop partition y05q1 update global indexes;

If you decide not to do an automatic index update as part of dropping the partition, that global index will become unusable. You will have to rebuild the index to make it usable, but you can defer that task to later, after you've dropped other partitions or done some other operations.

You can manually rebuild the global index on TRANS, by using

alter index pk_trans rebuild;

Discover More
Card Puncher Data Processing
Oracle - Partitions (Table and Indexes)

partition in Oracle. Partitioning enables you to store one logical object – a table or index – transparently in several independent segments. Partitioning can provide great performance improvements...
Card Puncher Data Processing
Oracle Partition - Index (Local, Global)

You can create two types of indexes on partitioned tables: Usable: The index is partitioned in exactly the same way as the base table. For instance, you can create a local index on the...
Card Puncher Data Processing
Oracle Partition - Partition Exchange (between tables)

Partition Exchange permits to exchange partition between tables. After the EXCHANGE PARTITION command is executed, the partition is empty and you can drop it The data does not physically move from the...

Share this page:
Follow us:
Task Runner