Locating and deleting unused indexes in Oracle9i
One of the most serious
causes of poor DML performance is the existence of unused indexes.
All SQL inserts, updates and delete will run slower if they have to
update a large number of indexes each time a row in a table is changed.
Sadly, many Oracle
professionals will allocate indexes whenever they see a column that is
referenced in the WHERE clause of an SQL query. While this approach makes SQL run fast, function-based Oracle
indexes make it possible to over-allocate indexes on table columns.
This over-allocation of indexes can cripple the performance of
loads on critical Oracle tables.
Until Oracle9i, there
was no way to identify those indexes that were not being used by SQL
queries. This tip describes the Oracle9i method that allows the DBA to
locate and delete un-used indexes.
The approach is quite
simple. Oracle9i has a tool
that allows you to monitor index usage with an alter index command.
You can then query and find those indexes that are unused and drop
them from the database.
Here is a script that
will turn on monitoring of usage for all indexes in a system:
Next, we wait until a
significant amount of SQL has executed on our database, and then query the
new v$object_usage view:
Here we see that
v$object_usage has a single column called used, which will be set to YES
or NO. Sadly, this will not tell you how many times the index has
been used, but this tool is useful for investigating unused indexes.
INDEX_NAME TABLE_NAME MON USED
--------------- --------------- --- ----
CUSTOMER_LAST_NAME_IDX CUSTOMER YES NO
If you like Oracle
tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think
it is right to charge a fortune for books!) and you can buy it right now
at this link: