How to Specify an INDEX Hint oracle官方文件

How to Specify an INDEX Hint (Doc ID 50607.1)
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.2 [Release 9.2 to 11.2]
Information in this document applies to any platform.
Purpose

This article explains how to specify index hints successfully.
Troubleshooting Steps

The format for an index hint is:
select /*+ index(TABLE_NAME INDEX_NAME) */ col1...

There are a number of rules that need to be applied to this hint:

    The TABLE_NAME is mandatory in the hint
    The table alias MUST be used if the table is aliased in the query
    If TABLE_NAME or alias is spelled incorrectly then the hint will not be used.
    The INDEX_NAME is optional.
    If an INDEX_NAME is entered without a TABLE_NAME then the hint will not be applied.
    If a TABLE_NAME is supplied on its own then the optimizer will decide which index to use based on statistics.
    If the INDEX_NAME is spelt incorrectly but the TABLE_NAME is spelled correctly then the hint will not be applied even though the TABLE_NAME is correct.
    If there are multiple index hints to be applied, then the simplest way of addressing this is to repeat the index hint syntax for each index e.g.:
    SELECT /*+ index(TABLE_NAME1 INDEX_NAME1) index(TABLE_NAME2 INDEX_NAME2) */ col1...
     
    Remember that the parser/optimizer may have transformed/rewritten the query or may have chosen an access path which make the use of the index invalid and this may result in the index not being used.

Legacy Note: As long as the index() hint structure is correct this will force the use of the Cost Based Optimizer (CBO). This will happen even if the alias or table name is incorrect.

Examples

The examples below use a table CBOTAB with a unique single column index called CBOTAB1 on column COL1.

Correct hint to force use of the index:

 explain plan for select /*+ index(cbotab) */ col1 from cbotab;
 explain plan for select /*+ index(cbotab cbotab1) */ col1 from cbotab;
 explain plan for select /*+ index(a cbotab1) */ col1 from cbotab a;

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=151
  INDEX FULL SCAN CBOTAB1 [ANALYZED]  Cost=151 Card=10000 Bytes=100000

 

    The TABLE_NAME is mandatory in the hint.
    In the following example the TABLE_NAME was omitted so the index was not used.

    SQL>  explain plan for select /*+ index() */ col1 from cbotab;
    Query Plan
    --------------------------------------------------------------------------------
    SELECT STATEMENT   [CHOOSE] Cost=10
      TABLE ACCESS FULL CBOTAB [ANALYZED]  Cost=10 Card=10000 Bytes=100000

    The INDEX_NAME is optional.
    Both of the following examples use the index:

     explain plan for select /*+ index(cbotab) */ col1 from cbotab;
     explain plan for select /*+ index(cbotab cbotab1) */ col1 from cbotab;

    Query Plan
    --------------------------------------------------------------------------------
    SELECT STATEMENT   [CHOOSE] Cost=151
      INDEX FULL SCAN CBOTAB1 [ANALYZED]  Cost=151 Card=10000 Bytes=100000

    The table alias MUST be used if the table is aliased in the query

    SQL>  explain plan for select /*+ index(cbotab) */ col1 from cbotab mytable;
    Query Plan
    --------------------------------------------------------------------------------
    SELECT STATEMENT   [CHOOSE] Cost=10
      TABLE ACCESS FULL CBOTAB [ANALYZED]  Cost=10 Card=10000 Bytes=100000

    Correct use of alias in hint:

    SQL>  explain plan for select /*+ index(mytable) */ col1 from cbotab mytable;
    Query Plan
    --------------------------------------------------------------------------------
    SELECT STATEMENT   [CHOOSE] Cost=151
      INDEX FULL SCAN CBOTAB1 [ANALYZED]  Cost=151 Card=10000 Bytes=100000

    If TABLE_NAME or alias is spelled incorrectly then the hint will not be used.

    SQL>  explain plan for select /*+ index(COBTAB) */ col1 from cbotab;
    SQL>  explain plan for select /*+ index(MITABLE) */ col1 from cbotab mytable;
    Query Plan
    --------------------------------------------------------------------------------
    SELECT STATEMENT   [CHOOSE] Cost=10
      TABLE ACCESS FULL CBOTAB [ANALYZED]  Cost=10 Card=10000 Bytes=100000

    If an INDEX_NAME is entered without a TABLE_NAME then the hint will not be applied.

    SQL>  explain plan for select /*+ index(cbotab1) */ col1 from cbotab;
    Query Plan
    --------------------------------------------------------------------------------
    SELECT STATEMENT   [CHOOSE] Cost=10
      TABLE ACCESS FULL CBOTAB [ANALYZED]  Cost=10 Card=10000 Bytes=100000

    If a TABLE_NAME is supplied on its own then the optimizer will decide which index to use based on statistics.

    explain plan for select /*+ index(cbotab) */ col1 from cbotab;

    Query Plan
    --------------------------------------------------------------------------------
    SELECT STATEMENT   [CHOOSE] Cost=151
      INDEX FULL SCAN CBOTAB1 [ANALYZED]  Cost=151 Card=10000 Bytes=100000

    If the INDEX_NAME is spelt incorrectly but the TABLE_NAME is spelt correctly then the hint will not be applied even though the TABLE_NAME is correct.

    SQL>  explain plan for select /*+ index(cbotab COBTAB1) */ col1 from cbotab;
    Query Plan
    --------------------------------------------------------------------------------
    SELECT STATEMENT   [CHOOSE] Cost=10
      TABLE ACCESS FULL CBOTAB [ANALYZED]  Cost=10 Card=10000 Bytes=100000