Oracle Unusable Index will slow down the performance?

Question

I saw the unusable index slow down the performance of a query by more than 4 hrs...

In general, will unusable index will slow down the query? In which conditions it will improve the performance.

Thanks.

Answer 1

When an index, or some partitions or subpartitions of an index, are created UNUSABLE, no segment is allocated for the unusable object. The unusable index or index partition consumes no space in the database.

For more documentation you can refer below link :

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-INDEX.html#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE

You can check query optimizer plan or query execution plan to check the performance of the query.

Having many indexes on a table degrades DML performance because the database must also update the indexes. Indexes can be usable (default) or unusable. An unusable index is not maintained by DML operations and is ignored by the optimizer. An unusable index can improve the performance of bulk loads.

I can see you can try below two scenarios:

  1)  Deleting from large table without having index

Drop indexes - Dropping indexes before a mass delete and rebuilding them afterwards can improve delete performance because each individual delete would have to remove itself from the index, causing slowdowns. Oracle removes index entries without re-balancing the index tree (a "logical delete"), but this is still time-consuming, especially if you have lots of indexes on the target table

   2) Parallel Delete

You can also check your delete query performance using parallel dml:

ALTER SESSION ENABLE PARALLEL DML;
DELETE /*+ parallel(table, 20) */
  FROM  table

You can then decide from the above two scenario which one works faster for your delete query