This article will be discussing a new feature of Oracle 11g, which is invisible indexes. Invisible indexes simply means indexes that are not visible to optimizer, meaning the optimizer will not consider such index while preparing query execution plans. It is possible to force the optimizer to consider invisible indexes, but let’s cover the benefits of invisible indexes first:
Benefits
- Test the benefit of an index: Every DML statement will have an extra cost to maintain an index. To ensure that an index is actually useful for our system before dropping it, we can test it by simply making it invisible. The index will not be considered by optimizer anymore. If SQL statements that were using this index perform acceptably, then we should drop the index.
- Introduce New Index: We can introduce a new index without effecting execution plans of existing sql statements.