Index Hints Seems to Be Ignored by Optimizer (Doc ID 2381118.1)

Last updated on JUNE 23, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

Customer have a very large SQL statement with many INDEX hints in many subquery.

    SELECT
    ... ...
        (SELECT    /*+ INDEX(<ALIAS1> <INDEX_NAME1>) */
    ... ...
            (SELECT    /*+ INDEX(<ALIAS1> <INDEX_NAME2>) */
    ... ...
            (SELECT /*+ INDEX(<ALIAS1> <INDEX_NAME3>)*/
    ... ...
                (SELECT    /*+ INDEX(<ALIAS2> <INDEX_NAME4>) */
    ... ...
        (SELECT    /*+ INDEX(<ALIAS3> <INDEX_NAME5>) */
    ... ...
        (SELECT    /*+ INDEX(<ALIAS3> <INDEX_NAME6>) */
    ... ...
        (SELECT    /*+ INDEX(<ALIAS4> <INDEX_NAME1>) */
    ... ...
        (SELECT    /*+ INDEX(<ALIAS4> <INDEX_NAME2>) */
    ... ...
        (SELECT /*+ INDEX(<ALIAS4> <INDEX_NAME3>) */
    ... ...
            (SELECT    /* INDEX(<ALIAS5> <INDEX_NAME4>) */
    ... ...
        (SELECT    /*+ INDEX(<ALIAS6> <INDEX_NAME5>) */
    ... ...
        (SELECT    /*+ INDEX(<ALIAS6> <INDEX_NAME6>) */
    ... ...
    ;

According the INDEX used access path of Plan Table of 10053 events trace, it seems that INDEX hints work well and choose the specified index:

   ============
    Plan Table
    ============
     ---------------------------------------------------------------------------+
     | Id | Operation                                            | Name        |
     ---------------------------------------------------------------------------+
     | 0   | SELECT STATEMENT                                     |             |
    ... ...
     | 60 |              INDEX RANGE SCAN                        | <INDEX_NAME5>|
    ... ...
     | 66 |             INDEX RANGE SCAN                         | <INDEX_NAME6>|
    ... ...
     | 75 |              INDEX RANGE SCAN                        | <INDEX_NAME1>|
    ... ...
     | 82 |             INDEX RANGE SCAN                         | <INDEX_NAME2>|
    ... ...
     | 90 |                 INDEX RANGE SCAN                     | <INDEX_NAME3>|
    ... ...
     | 97 |              INDEX RANGE SCAN                        | <INDEX_NAME4>|
    ... ...
     | 106 |           INDEX RANGE SCAN                           | <INDEX_NAME5>|
    ... ...
     | 111 |           INDEX RANGE SCAN                           | <INDEX_NAME6>|
    ... ...
     | 122 |            INDEX RANGE SCAN                          | <INDEX_NAME1>|
    ... ...
     | 135 |            INDEX RANGE SCAN                          | <INDEX_NAME2>|
    ... ...
     | 148 |              INDEX RANGE SCAN                        | <INDEX_NAME3>|
    ... ...
     | 155 |           INDEX RANGE SCAN                           | <INDEX_NAME4>|
     ---------------------------------------------------------------------------+

But some INDEX hints was not output in Dumping Hints section of 10053 events trace.

Dumping Hints
=============
atom_hint=(@=ffffffff7b3e26f0 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("<ALIAS6>" "<INDEX_NAME6>") )
atom_hint=(@=ffffffff7b390418 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("<ALIAS6>" "<INDEX_NAME5>") )
atom_hint=(@=ffffffff7b365188 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("<ALIAS4>" "<INDEX_NAME3>") )
atom_hint=(@=ffffffff7b321c88 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("<ALIAS4>" "<INDEX_NAME2>") )
atom_hint=(@=ffffffff7b314828 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("<ALIAS4>" "<INDEX_NAME1>") )
atom_hint=(@=ffffffff7b592008 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("<ALIAS3>" "<INDEX_NAME6>") )
atom_hint=(@=ffffffff7b5408a0 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("<ALIAS3>" "<INDEX_NAME5>") )
atom_hint=(@=ffffffff7b504ca8 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("<ALIAS1>" "<INDEX_NAME3>") )
atom_hint=(@=ffffffff778034b0 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("<ALIAS1>" "<INDEX_NAME2>") )
atom_hint=(@=ffffffff777134f8 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("<ALIAS1>" "<INDEX_NAME1>") )

Cause

	To view full details, sign in with your My Oracle Support account.
	Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Index Hints Seems to Be Ignored by Optimizer (Doc ID 2381118.1)

Applies to:

Symptoms

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!