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):
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:
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:
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)):
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:
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)
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.
