I have Oracle 12c database and partitioned table. There is a old partition which has 68734MB which is 27% of total.
I need to drop this partition. This is a production database and I does not have a downtime window for this.
I want to know if I drop this partition, will the indexes of this table be unusable or rebuild ? What would be the best strategy to drop this partition with minimum impact on the live traffic?
When you drop one or more partitions, local indexes are also dropped.
You need to rebuilt Global indexes unless the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause is specified in the ALTER TABLE DROP PARTITION statement.
For example, I have a table called TBL1 with range partition and global indexes.
SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i
where i.index_name=p.index_name
and table_name='TBL1';
INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_INDEX GI3 USABLE
T_INDEX GI2 USABLE
T_INDEX GI1 USABLE
SQL> alter table tbl1 drop partition p0 UPDATE GLOBAL INDEXES;
Table altered.
SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i
where i.index_name=p.index_name
and table_name='TBL1';
INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_INDEX GI3 USABLE
T_INDEX GI2 USABLE
T_INDEX GI1 USABLE
As you can see, if I specify UPDATE GLOBAL INDEXES clause, Global indexes don't become unusable.
After dropping the partition, indexes entries belongs to those partitions remain. When a query uses these indexes, they simply ignore such entries(orphaned). You can clean up such orphaned entries in your regular maintenance window. Details are also given in the following documentations.
The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION are optimized by making the index maintenance for metadata only.
In any case, the dropping or truncation of a partition completes immediately, the index is always available for use, and the maintenance of the index is deferred
Documentation: