Virtual indexes allow us to simulate the existence of an index and test its impact without actually building the actual index.
Only sessions marked for Virtual Index usage will be affected by their existence. Their creation does not affect new sessions. Virtual indexes will be used only when the initialization parameter “_use_nosegment_indexes” is set to TRUE. The Rule based optimizer does not recognize Virtual Indexes but the CBO does recognize them.
Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. An index can be made invisible to the Optimizer, so that the optimizer can’t see it. If an adhoc query requires the usage of the index it can explicitly specify the index as a part of hint.
Basic Difference Between Virtual Index and Invisible Index.
- In case of virtual index, we dont actually create the index, its just the place holder. In case of invisible index we actually create the index.
- In case of virtual index, we can check if the index is getting used or not using explain plan and performance of the query can be mentioned statistically by explain plan. In case of invisible index, we can actually run the query and check the performance benefit.
- We cannot make existing index as virtual, unless we have to drop it and create a no_segment index. We can make any index invisible.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
Anuradha’s LinkedIn: Anuradha’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp