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.

create table t1 as
select
	rownum				id,
	dbms_random.string('U',6)	v1,
	lpad(rownum,10)			v2,
	rpad('x',100)			padding
from
	all_objects
where
 	rownum <= 1000
;             

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

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 => 'T1',
		cascade		 => true,
		estimate_percent => null,
		granularity	 => 'default',
		method_opt	 => 'for all columns size 1'
	);
end;
/

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                  

select
        /*+ index(t1 t1_i1) */
        v2
from    t1
where   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. 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"]

3 Comments »

  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 BST 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 BST Dec 15,2009 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,266 other followers