If you have identified an index that is no longer being used, you can mark it UNUSABLE. From that point forward, Oracle will not maintain the index, nor will the optimizer consider the index for use in SELECT statements.

The advantage of marking the index UNUSABLE (rather than dropping it) is that if you later determine that the index is being used, you can alter it to a USABLE state and rebuild it without needing the DDL on hand to re-create it.

Here is an example of marking an index UNUSABLE:

SQL> alter index cust_idx1 unusable;

You can verify that it is unusable via this query:

SQL> select index_name, status from user_indexes;

The index has an UNUSABLE status:

INDEX_NAME                  STATUS

——————– ——–CUST_IDX1                             UNUSABLE

If you determine that the index is needed (before you drop it), then it must be rebuilt to become usable again:

SQL> alter index cust_idx1 rebuild;

Another common scenario for marking indexes UNUSABLE is that you are performing a large data load. When you want to maximize table-loading performance, you can mark the indexes UNUSABLE before performing the load. After you have loaded the table, you must rebuild the indexes to make them usable again.

Note the alternative to setting an index to UNUSABLE is to drop and re-create it. this approach requires the CREATE INDEXddL.

Dropping an Index

If you have determined that an index is not being used, then it is a good idea to drop it. Unused indexes take up space and can potentially slow down DML statements (because the index must be maintained as part of those DML operations). You can always test the performance by making an index invisible first before dropping. Remember there is time and resources involved in creating a large index on a widely used table, so the validation is a good set up before hours of poor performance while you rebuild an index. Use the DROP INDEX statement to drop an index:

SQL> drop index cust_idx1;

Dropping an index is a permanent DDL operation; there is no way to undo an index drop other than to re-create the index. Before you drop an index, it does not hurt to quickly capture the DDL required to re-create the index. Doing so will allow you to re-create the index in the event you subsequently discover that you did need it after all.

Indexing Foreign Key Columns

Foreign key constraints ensure that when inserting into a child table, a corresponding parent table record exists. This is the mechanism for guaranteeing that data conforms to parent–child business relationship rules. Foreign keys are also known as referential integrity constraints.

Unlike primary key and unique key constraints, Oracle does not automatically create indexes on foreign key columns. Therefore, you must create a foreign key index manually, based on the columns defined as the foreign key constraint. In most scenarios, you should create indexes on columns associated with a foreign key. Here are two good reasons:

•     Oracle can often make use of an index on foreign key columns to improve the performance of queries that join a parent table and child table (using the foreign key columns).

•     If no B-tree index exists on the foreign key columns, when you insert or delete a record from a child table, all rows in the parent table are locked for the duration of the statement, for the update or delete. For applications that actively modify both the parent and child tables, this can cause locking and deadlock issues, but for the duration of the statement.

One could argue that if you know your application well enough and can predict that queries will not be issued and that join tables on foreign key columns and that certain update/delete scenarios will never be encountered (that result in entire tables being locked), then, by all means, do not place an index on foreign key columns.

In my experience, however, this is seldom the case: developers rarely think about how the “black-box database” might lock tables; some DBAs are equally unaware of common causes of locking; teams experience high turnover rates, and the DBA de jour is left holding the bag for issues of poor database performance and hung sessions.

Considering the time and resources spent chasing down locking and performance issues, it does not cost that much to put an index on each foreign key column in your application.

I know some purists will argue against this, but I tend to avoid pain, and an unindexed foreign key column is a ticking bomb.

Having made my recommendation, I’ll first cover creating a B-tree index on a foreign key column. Then, I’ll show you some techniques for detecting unindexed foreign key columns.