1. SQL Reference Guide
  2. Query Optimization
  3. Choosing the Best Applicable Index

Choosing the Best Applicable Index

To choose an index for a query, the query processor uses a simple heuristic together with any user-provided index hints.

Syntax

hints ::= '/*+' hint* '*/'

hint ::= (
   (PREFER_INDEXES "(" name_path index_name* ")") |
   (FORCE_INDEX "(" name_path index_name ")") |
   (PREFER_PRIMARY_INDEX "(" name_path ")") |
   (FORCE_PRIMARY_INDEX "(" name_path ")")
   ) [STRING]

There are 2 kinds of hints: a FORCE_INDEX hint and a PREFER_INDEXES hint. The FORCE_INDEX hint specifies a single index and the query is going to use that index without considering any of the other indexes (even if there are no index predicates for the forced index). The PREFER_INDEXES hint specifies one or more indexes. The query processor may or may not use one of the preferred indexes. Specifically, in the absence of a forced index, index selection works as follows.

The query processor uses the heuristic to assign a score to each applicable index and then chooses the one with the highest score. If two or more indexes have the same score, the index chosen is the one whose name is alphabetically before the others. In general, preferred indexes will get high scores, but it is possible that other indexes may still win. Describing the details of the heuristic is beyond the scope of this document, but a few high-level decisions are worth mentioning:
  • If the query has a complete primary key, the primary index is used.
  • Indexes that are preferred (via a PREFER hint), covering, or have a complete key (i.e., there is an equality predicate on each of its index fields) get high stores and will normally prevail over other indexes.
  • Among 2 indexes where one is a sorting index, the other is not, and the 2 indexes would otherwise have the same score, the sorting index is chosen.

The FORCE_INDEX and PREFER_INDEXES hints specify indexes by their name. Since the primary index has no explicit name, 2 more hints are available to force or to prefer the primary index: FORCE_PRIMARY_INDEX and PREFER_PRIMARY_INDEX. Hints are inserted in the query as a special kind of comment that appears immediately after the SELECT keyword. Here is the relevant syntax:

The '+' character immediately after (with no spaces) the comment opening sequence ('/*') is what turns the comment into a hint. The string at the end of the hint is just for informational purposes (a comment for the hint) and does not play any role in the query execution.