Index Hints

In my last post I made a comment about how the optimizer will use the new format of the index hint to identify an index that is an exact match if it can, and any index that starts with the same columns (in the right order) if it can’t find an exact match. It’s fairly easy to demonstrate the behaviour in 11g by examining the 10053 (CBO) trace file generated by a simple, single table, query – in fact, this is probably a case that Doug Burns might want to cite as an example of how, sometimes, the 10053 is easy to interpret (in little patches):

create table t1
as
select
        rownum          n1,
        rownum          n2,
        rownum          n3,
        rownum          n4,
        rpad('x',100)   padding
from
        all_objects
where
        rownum <= 10000 ; execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1')

create index t1_n1n2 on t1(n1, n2);

create index t1_n1n2n3 on t1(n1, n2, n3);
create index t1_n1n2n4 on t1(n1, n2, n4);

create index t1_n2n1 on t1(n2, n1);

I’ve created 4 indexes on this table, three of them start the same way, and two of them use exactly the same columns but in a different order. I’m going to run three tests to show the effects of supplying Oracle the hint /*+ index(t1(n1,n2)) */, a descriptive hint that identifies the first index exactly, the leading edges of the 2nd and 3rd indexes, and gets the columns of the last index in the wrong order. Here’s the query I’m going to use in every test:

select
        /*+ index(t1(n1,n2)) */
        *
from
        t1
where
        n1 = 99
and     n2 = 99
;

Running the first test after creating the data set gives me a plan that uses the index (n1, n2) – and here’s a key section of the 10053 trace file from an 11.2.0.3 instance that tells me how the optimizer got to that plan:

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  170  AvgRowLen:  117.00  ChainCnt:  0.00
Index Stats::
  Index: T1_N1N2  Col#: 1 2
    LVLS: 1  #LB: 27  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 170.00
    User hint to use this index
  Index: T1_N1N2N3  Col#: 1 2 3
    LVLS: 1  #LB: 33  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 170.00
  Index: T1_N1N2N4  Col#: 1 2 4
    LVLS: 1  #LB: 33  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 170.00
  Index: T1_N2N1  Col#: 2 1
    LVLS: 1  #LB: 27  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 170.00
Access path analysis for T1

Note the contents of line 9 – which tells me that the optimizer has taken the hint to apply ONLY to the index on (n1,n2).

Next test – drop the index on (n1, n2) – and this is what the trace file show (after giving a plan that used the index on (n1, n2, n3)):

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  170  AvgRowLen:  117.00  ChainCnt:  0.00
Index Stats::
  Index: T1_N1N2N3  Col#: 1 2 3
    LVLS: 1  #LB: 33  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 170.00
    User hint to use this index
  Index: T1_N1N2N4  Col#: 1 2 4
    LVLS: 1  #LB: 33  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 170.00
    User hint to use this index
  Index: T1_N2N1  Col#: 2 1
    LVLS: 1  #LB: 27  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 170.00
Access path analysis for T1

In the absence of an exact, ordered, match, lines 9 and 12 tell us that the hint now applies to the two indexes that start with the correct columns in the correct order.

Finally, after I drop the two indexes that start the right way – leaving me with just the index on (n2, n1) the trace file shows the following:

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  170  AvgRowLen:  117.00  ChainCnt:  0.00
Index Stats::
  Index: T1_N2N1  Col#: 2 1
    LVLS: 1  #LB: 27  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 170.00
Access path analysis for T1

There is no suggestion that the optimizer is going to give the index on (n2, n1) any special consideration – based on the standard optimizer arithmetic it may or may not be used.

Moreover, if you examine the “Dumping Hints” section of the trace files you see the following (in turn)

Dumping Hints (first test)
=============
  atom_hint=(@=0x7f91537a3510 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("T1" "T1_N1N2") )

Dumping Hints (second test)
=============
  atom_hint=(@=0x7f915357f7e8 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("T1" "T1_N1N2N3") )
  atom_hint=(@=0x7f91537a3510 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("T1" "T1_N1N2N4") )

Dumping Hints (third test)
=============
  atom_hint=(@=0x7f91537a3510 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("T1" ???) )

I particularly like the last dump: I saw your hint, I ‘used’ it, but I was baffled by the index description you gave me. If you want to do a little further testing (make n4 non-null and create an index on it, for example), you’ll find that this last option doesn’t even behave like a naked /*+ index(t1) */ hint.

So, Doug, I have to agree that sometimes it IS easy get some useful information from the 10053 trace file – especially in the increasingly verbose later versions of Oracle.