Select Statement Fails with ORA-01502 Error After Moving Table

출처 :  https://metalink.oracle.com



fact: Oracle Server - Enterprise Edition
symptom: Select statement fails
symptom: ORA-01502: index '%s.%s' or partition of such index is in unusable state
change: Base table was moved using 'ALTER TABLE %s MOVE' command
cause: After moving table using 'ALTER TABLE %s MOVE' command, all indexes on this table become UNUSABLE.

This is because indexes use ROWIDs to reference actual rows. These ROWIDs are changed during the move to a different tablespace. Therefore the index cannot be used without recreating it.

'ALTER TABLE % MOVE' also invalidates all statistics on the table thus forcing the subsequent queries into using RBO, which does not do an unusable index check.


FIX:

1. Rebuild the indexes and reanalyze table statistics -  this is the cleanest option:

1.1. Find all indexes, that are in an 'UNUSABLE' state:
 SQL> SELECT owner, index_name FROM dba_indexes WHERE status='UNUSABLE'&#059;

1.2. Rebuild such indexes using:
 SQL> ALTER INDEX <owner>.<name> REBUILD;

1.3. Reanalyze table statistics:
 SQL> ANALYZE TABLE <owner>.<table_name> COMPUTE/ESTIMATE


STATISTICS:

2. Use hints or session level parameters like NO_INDEX, optimizer_mode = FIRST_ROWS/ALL_ROWS etc to force the queries to use CBO along with skip_unusable_indexes = TRUE.

e.g.
SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
SQL> ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;


Reference:
165917.1 Common Maintenance Commands That Causes Indexes to Become Unusable