Index invalidation in Oracle partition table

Indexing is invalidated when some operations are performed on the partitioned table.

The global index is invalidated when there is a truncate/drop/exchange operation partition.

The temporary table for Exchange is not indexed, or
Index, no keyword with including indexes, will result in partial index invalidation, that is, a partition is invalid
Rebuilding a local index can only be done by using ALTER index LOCAL_IDX rebuild partition P1 this way

When the partition table is split, if there is already a record in the Max zone, then split will invalidate the local index of the newly recorded partition!

Searches for individual partition index states in a partitioned table usable/unusable

Select index_name, partition_name, status    from  where='indexname'

--Rebuilding the index

--Local index rebuildSelectB.table_name,a.index_name,a.partition_name,a.status,'ALTER INDEX' ||A.index_name|| 'Rebuild Partition' ||Partition_name|| ';'Rebuilding Columns fromuser_ind_partitions A, user_part_indexes bwhereA.index_name=B.index_name andB.table_nameinch('Part_tab_split') andSTATUS= 'unusable'ORDER  byB.table_name, A.index_name, A.partition_name;--Global Index RebuildAlter IndexIDX_PART_SPLIT_COL3 rebuild;

It can also be done by updating index directly for truncate and other operations.

ALTER TABLE PART_TAB_TRUNC truncate partition p2 Update GLOBAL  indexes;

　　

Index invalidation in Oracle partition table