Databases Reference
In-Depth Information
When to Create an Invisible Index
In Chapter 7, you'll learn how to monitor the usage of an index by using the
monitoring
clause in an
alter index
(or
create index
) statement. If your analysis reveals that a certain index isn't being used by
any queries, you may want to get rid of that index. You can use invisible indexes in any situation where
you're considering either dropping an index or making it unusable. Both of these actions are very
expensive if you need to use the index later on. If you drop the index, you have to recreate it; if you made
an index unusable, you have to rebuild it. Invisible indexes are very helpful when you have a situation
where specific modules of an application benefit from an index but the index adversely affects other
parts of the application. In this case, you can make the index visible only to those modules of the
application where the index is beneficial.
The biggest use of an invisible index is when you want to test if you should drop an index. There are
times when an index helps just one or two queries perform better but degrades the performance of a
bunch of other queries. In cases like this, you're faced with a dilemma as to whether to retain the index
or get rid of it. Well, invisible indexes let you eat the cake and eat it, too: you can specify the use of the
index only when you want to and let the index remain invisible the rest of the time! Before Oracle
introduced invisible indexes, you had to make an index unusable first and then test the performance of
your queries. If you decided that performance was better without the index, you could then drop the
index. If you decided to keep the index, you had to rebuild the unusable index to make it usable again.
All this takes time and effort. Of course, invisible indexes make all this work unnecessary. You simply
alter the status of a normal index to that of an invisible index and test.
A good time to use an invisible index is when one or two adhoc queries require an index. You can
make the index visible only to these queries and change its status to an invisible index for all other
queries.
Often you find that an application is running slow because of a large full table scan. You figure out
that you can avoid the full table scan by creating an index. However, this is not a simple decision in most
cases because that index, while it helps avoid the full table scan for the specific query you are
troubleshooting, may adversely affect other queries. Invisible indexes are perfect for cases such as this
where you want to selectively expose an index to the optimizer.
Creating an Invisible Index
You can create an invisible index by including the
INVISIBLE
clause in an index creation statement, like
so:
SQL> create index test_idx1 on products(prod_src_id) invisible;
Index created.
You can make an existing index invisible by using the following
alter index
statement:
SQL> alter index test_idx_1 invisible;
Index altered.
SQL>
