Hints are CBO directives. If Oracle can use the hint, it will only produce explain plans that contain the hint directive. This article examines index hints. Knowing how to use these hints can help improve performance tuning.
About index hints
Oracle allows for specific indexes to be forced upon queries. This is probably the most commonly-used hint of all the hints.
The example below shows an explain plan from the above query with no indexes at all.
Explain Plan with No Indexes
After creating indexes on each of the where clause predicates, Oracle10g did an index combine operation, using all the available indexes in a single operation.
NOTE
Index hints are not needed as often with the newer Oracle databases. You should test your code in Oracle10+ using no hints at all.
Main index hints?
The index hint can be used to specify the use of particular indexes. The main index hints include:
- /*+INDEX(Index name)*/
- /*+INDEX_JOIN(emp index1, index2)*/
- Forces an index join where two or more indexed columns can be joined using single-column indexes; this can be useful to solve query columns
- /*+INDEX_COMBINE(emp bitmap1, bitmap2)*/
- Useful to join 2 or more bitmap indexes together
- /*+AND_EQUAL(emp index1, index2,…)*/
- List at least two, not more than five
- Useful to merge single column indexes together
Note that the INDEX_JOIN, INDEX_COMBINE, and AND_EQUAL hints are used to help Oracle do an Index Merge operation…using multiple indexes on the same table but not doing join operations on each step.
Index hint rules
There are several rules to be aware of when working with index hints:
- When specifying an index hint on a particular table, all other indexes for that table will be ignored.
- Columns can also be specified in the index hint.
- Be sure to use full table names, not table aliases here!
- You must reference base table columns, not a calculation or other SQL expression.
NOTE
Using Index hints inhibits the use of other indexes that are available for that particular table object.
?
?
?
Leave a Reply