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