In this post I would like to remove some misinformation about the utilization of hints with invisible indexes.
Let’s start by providing you what two sources say about that topic:
- Oracle Database 11g: New Features for DBAs and Developers (by Sam R. Alapati and Charles Kim, page 132)
“If you want the optimizer to take the invisible index into account, you must use the index hint, as shown in the following example:“
- Oracle Database 11g: The Top New Features for DBAs and Developers – Schema Management (by Arup Nanda)
“To make the optimizer use the index again, you have to explicitly name the index in a hint:”
Both sources point out that a hint can be used to compel the query optimizer to use an invisible index. Both also provide an example (not shown here). But, what is more important, both are wrong! In other words, a hint cannot be used to compel the query optimizer to use an invisible index.
What’s going on?!?
The problem is that this specific information used to be correct for the beta release of Oracle Database 11g. But that behavior was a bug, not a feature! Therefore, once the bug was fixed, this piece of information was no longer valid.
This is what happens when authors use beta releases for doing their tests…