Asim Jamal

• Apr 13th, 2006

 

The current status and details of an index could be accessed by

system table sys.dba_indexes. The status could be VALID,INVALID,N/A.

The invalid indexes unnecessarily occupies the space in a Tablespace.

aStewart

 Profile Answers by aStewart 

• Feb 23rd, 2010

 

The index status can be found in the dba_indexes, dba_objects, or the equivalent  user_ view. The possibilities are VALID, INVALID, UNUSABLE, or N/A.  Invalid or unusable indexes will not be ignored for DML and QUERY statements involving the table for which the index exists, assuming that the init parameter, skip_unusable_indexes, is set to TRUE.  The lack of the index may alter execution plans and negatively affect performance.