Indexes can also be unsuitable for use at times. The index is essentially a pointer to the Physical Location. Let’s assume I created a table containing an index. These indexes are actually pointers to the Physical Location of this table, so if this table is moved from one Tablespace to another using the command:

ALTER TABLE … MOVE

it is natural for these indexes to become unsuitable for use (Unusable Indexes) because they point to incorrect locations. In earlier versions of the Oracle database, users would discover unusable indexes by reviewing error messages they received when attempting to use the index.

However, in Oracle 10g, this behavior has changed. When attempting to use some unusable indexes, the operation will proceed without requiring the indexes. It is understood that this choice negatively affects performance because it leads to a full table scan instead of using the indexes. This option is always enabled and can be controlled using the variable SKIP_UNUSABLE_INDEXES, which has the default value TRUE.

Of course, a Database Administrator (DBA) can distinguish unusable indexes through the DBA_INDEXES view.