Oracle Scratchpad

October 28, 2006

Taking a Hint

Filed under: CBO,Execution plans,Hints,Ignoring Hints,Indexing — Jonathan Lewis @ 10:32 pm BST Oct 28,2006

It seems the word is gradually getting around the Oracle world that “hints” are really absolute directives to the optimizer that cannot be ignored – if they are correctly used.

Unfortunately the documentation on hints is far from complete, so it is very easy to produce examples that suggest that a hint has been ignored. Here’s one I came across recently in a note about 10g.

rem     Script:         ignore_hint.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2006

reate table t1 as
        rownum                          id,
        dbms_random.string('U',6)       v1,
        lpad(rownum,10)                 v2,
        rpad('x',100)                   padding
        rownum <= 1000  -- > comment to avoid wordpress format issue

-- alter table t1 modify v1 not null;
create index t1_i1 on t1(v1);             

                ownname          => user,
                tabname          =>'T1',
                cascade          => true,
                estimate_percent => null,
                granularity      => 'default',
                method_opt       => 'for all columns size 1'

A simple query that should “obviously” use the index does not do so – even when hinted. So is Oracle ignoring the hint ?

set autotrace traceonly explain                  

        /*+ index(t1 t1_i1) */
        v1 = 'MMMMMM'
Execution Plan
Plan hash value: 3617692013                  

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |     1 |    18 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    18 |     7   (0)| 00:00:01 |

If you don’t check the predicates section of a plan, you can’t really be sure what’s going on. In this case, the predicate looks very different from the original where clause:

Predicate Information (identified by operation id):
   1 - filter(NLSSORT("V1",'nls_sort=''BINARY_CI''')=HEXTORAW('6D6D6D6D6D6D00') )

Unfortunately, this session wasn’t running with the normal values for nls_comp and nls_sort so, as a consequence, the optimizer had to transform the original predicate to something that made the hint illegal.

If you want to repeat the demonstration, you need only make the following changes to your environment:

alter session set nls_sort=binary_ci;
alter session set nls_comp=ansi;

Note the way I have commented out the line in the original script that adds a not null constraint to the v1 column. If you create this constraint the optmizer will use the index when hinted (although with a full scan rather than a range scan – and note that the predicate is still a filter predicate, not an access predicate).

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT            |       |     1 |    18 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    18 |     5   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | T1_I1 |     1 |       |     4   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   2 - filter(NLSSORT("V1",'nls_sort=''BINARY_CI''')=HEXTORAW('6D6D6D6D6D6D0') )      

The reason why the constraint makes the hint legal is because function(colx) may be non-null even when colx itself is null so (in principle) there could have been rows that matched the nlssort() predicate even though they didn’t appear in the index. In the absence of the constraint Oracle has to visit the table for any rows that may not be in the index – because ordinary B-tree indexes do not hold completely null entries.

[Further reading on “ignoring hints”]


  1. Enlightening!
    Keep up this blog, absolutely a must for every Oracle guy (coupled with Tom Kyte site, on the “proof” side of the reality, like you) :-)

    Comment by Franco — October 30, 2006 @ 7:55 am GMT Oct 30,2006 | Reply

  2. […] postings on hints: Hints – again Ignoring Hints What is a hint ? Taking a hint Comments […]

    Pingback by Quiz Night « Oracle Scratchpad — December 15, 2009 @ 9:34 pm GMT Dec 15,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: