Taking a Hint

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"]