Oracle-Developer.com Forums

[vj.jain]

Oracle 11g introduces a new feature for indexes that is useful in several different situations. An invisible index is an index that maintained by the database but ignored by the optimizer unless explicitly specified. Using an invisible index is an alternative to dropping or making an index unusable. This feature can also prove useful when certain modules of an application require a specific index without affecting the rest of the application.

One possible use of this feature is to test the removal of an index before dropping it. Prior to 11g, this was typically achieved by making an index unusable during a set period of time. During this observation period, the DBA would monitor the database performance to make the determination of whether to drop the index. If performance was negatively affected, the index would need to be rebuilt before it could be used again. Beginning with Oracle 11g, the DBA has the option of making the index invisible, as opposed to unusable, during this observation period. If performance degradation is observed, the index can be made visible again without rebuilding the index. This can minimize the period of performance degradation while also preventing an expensive operation to rebuild or recreate an index.


These and more Oracle 11g features can be found at http://www.oracle-developer.com or in the Oracle 11g New Features guide being released in March 2008 at http://www.oracle-developer.com/oracle_11g_new_features.html