Hi Experts,
I'm reading this section "Index Characteristics" from Oracle docs on this page:Indexes and Index-Organized Table
which says:
Indexes have the following properties:
- Usability
Indexes are usable (default) or unusable. An unusable index is not maintained by DML operations and is ignored by the optimizer. An unusable index can improve the performance of bulk loads. Instead of dropping an index and later re-creating it, you can make the index unusable and then rebuild it. Unusable indexes and index partitions do not consume space. When you make a usable index unusable, the database drops its index segment.
- Visibility
Indexes are visible (default) or invisible. An invisible index is maintained by DML operations and is not used by default by the optimizer. Making an index invisible is an alternative to making it unusable or dropping it. Invisible indexes are especially useful for testing the removal of an index before dropping it or using indexes temporarily without affecting the overall application.
1- I'm not getting the exact difference between both.
2- Also what is the need of making it invisible? We can always make it "UNUSABLE" and avoided by optimizer right?
3- What does it mean by "maintained by DML operations" ?
Please help me understand this concept. Help much appreciated.
-- Ranit
(Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production)