Oracle 12c, Will dropping partition which has 25% of total data, rebuild the indexes

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: