Search code examples
oracle-databaseindexingvisibilityusability

Usability vs Visibility in Oracle


Oracle states that 'Invisible indexes are especially useful for testing the removal of an index before dropping it or using indexes temporarily without affecting the overall application.'

I don't understand why visibility is 'especially' useful for this, wouldn't making an index unusable be especially useful since DML operations are not maintained therefor it resembles dropping an index more so than making it simply invisible. I've never actually worked with this, I'm guessing that making an index invisible/visible is easier than making it usable/unusable because you have to rebuild an index somehow when you make it usable?


Solution

  • It is referring to the impact on your queries via statistics and the optimizer.

    Many Oracle databases have complex schemas, user bases, as well as really large tables and indexes. Some even have very controlled schema statistics. Dropping a big index can be an expensive step (time-wise).

    The statistics gatherer collects statistics to populate the data dictionary, which is used for the optimizer. Index stats are one of the key inputs to the Cost Based Optimizer. "Faking" the drop will cause the optimizer to act as if the index is gone, and you can then see the impact on the query plans. If you find that the drop wasn't such a good idea, you can immediately revert it. On the other hand, some indexes take hours to build, so you can see how it is valuable to be able to test it out first.