Oracle databases can have invalid indexes that are not usable. Such index unusable oracle state is UNUSABLE. Oracle DBA_INDEXES view returns all the indexes that are available in the database. Indexes are become unusable for various reasons. Such a situation can happen once partition maintenance operations, DDL changes, or errors during index creation or rebuilding.
Unusable indexes are not usable until re-build the index or need to be removed or recreated to use them again. Otherwise, can raise performance problems in the database due to an invalid index. We expect queries retrieved through the index but it is not in use.
Create an index correctly and check Oracle errors for build problems. Indexes have a huge impact on performance because of index can fetch data from databases. Order dataset (Order by Oracle), and many cases. Let’s see how to get the index unusable in Oracle.
Check Unusable Indexes:
- Query for DBA_INDEXES view to check the status of indexes:
-- SELECT INDEX_NAME, TABLE_NAME, STATUS FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE'; --
- The query for dba_ind_PARTITIONS view to check the status of indexes Partitions:
-- SELECT index_owner, index_name, partition_name, tablespace_name FROM dba_ind_PARTITIONS WHERE status = 'UNUSABLE'; --
- The query for dba_ind_SUBPARTITIONS view to check the status of index subpartitions:
-- SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name FROM dba_ind_SUBPARTITIONS WHERE status = 'UNUSABLE'; --
- If you have an error like below you may have a problem with the index being unusable in Oracle
-- 'ORA-01502: index `…` or partition of such index is in unusable state' --
Why indexes become unusable
Indexes have become invalidated due to the below reasons. Some DB tasks like those below can invalidate indexes.
- Table partition maintenance – When doing the maintenance of table partitions like move, split, or truncate partitions will shift ROWIDs, making the index invalid and unusable.
- Someone execute
ALTER INDEX <index name> UNUSABLE;
- CTAS maintenance – Table reorganization with “alter table move” or an online table reorganization (using the dbms_redefinition package) will shift ROWIDs, creating unusable indexes.
- Oracle imports – If Oracle database import using skip_unusable_indexes=y parameter then the index unusable in Oracle
- SQL*Loader (sqlldr utility) – Using direct path loads like skip_index_maintenance indexes can be invalidated.
Fixing Unusable Indexes:
- Rebuilding the Index: Rebuild the index by using the ALTER INDEX statement:
-- ALTER INDEX index_name REBUILD; --
Replace index_name with the name of the unusable index.
- Dropping and Recreating the Index: A simple way is you can easily remove the index and create the index correctly again
--Drop index DROP INDEX index_name; --Create index CREATE INDEX index_name ON table_name (columns); --
Replace index_name, table_name, and columns according to your required values
The query for altering invalid indexes
-- SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||' ONLINE PARALLEL;' sql_to_rebuild_index FROM dba_indexes WHERE status = 'UNUSABLE'; --
How to Prevent Index Unusable Oracle
Some best practices are used to prevent unusable indexes in Oracle. Below are some of them:
- Some DDL operations like table truncation can be index invalidated therefore careful index maintenance in such an operation.
- Regularly check and confirm unusable indexes using the above queries.
- Mindful of index creation or maintenance operations whether there are any errors.
Always follow best practices on database work, especially on critical DML operations or maintenance work. Taking backups, being mindful of errors, logging systems, and Online alerting are some of them. If indexes are unusable Performance can be dropped.
