Rebuilding a Partitioned Index

Rebuilding a partitioned index is slightly different then rebuilding a normal (non-partitioned) index.

Let see what happen when we try to rebuild a partitioned index in usual way

SQL> ALTER INDEX TEST_ID_IDX REBUILD ONLINE COMPUTE STATISTICS;
ORA-14086: a partitioned index may not be rebuilt as a whole

Cause: User attempted to rebuild a partitioned index using ALTER INDEX REBUILD statement, which is illegal.

Action: Rebuild the index, a partition at a time or drop and recreate the entire index


If you choose to rebuild that partition then run the following SQL

SQL> ALTER INDEX TEST_ID_IDX REBUILD PARTITION TEST_2005 ONLINE COMPUTE STATISTICS;

Here is a SQL for finding/ rechecking partitioned index

SELECT uip.index_name
, utp.table_name
, uip.partition_name
, ui.index_type
, ui.global_stats
, uip.last_analyzed
FROM USER_IND_PARTITIONS uip
, USER_TAB_PARTITIONS utp
, USER_INDEXES ui
WHERE uip.partition_name= utp.partition_name
AND ui.index_name=uip.index_name
AND ui.table_name = utp.table_name
AND uip.index_name= 'TEST_ID_IDX'
AND utp.table_name= 'TEST';