Re: What index access path does INDEX_SS yield ?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:dYmdnStXo_nsuwPYnZ2dnUVZ8sKlnZ2d_at_bt.com...
> Spendius -
> If you care to email me the full trace 10053 file of the
> misbehaving execution, I'll see if I can determine what
> the problem is.
>

Spendius sent me the trace file.
I believe this is a bug, although Oracle might decide to call it expected behaviour.

The hint IS being obeyed - you can see all over the trace file that Oracle is NOT doing calculations about bitmap indexes and tablescans it does when the hint is omitted. However, there are THREE places where Oracle does an INDEX FULL SCAN calculation when it should be doing a skip scan calculation. All of them at the point of

    Now Joining TF using a nested loop.
Here are the six join orders:

Join order[1]:  [TT]#0  [OT]#1  [TF]#2
Join order[2]:  [TT]#0  [TF]#2  [OT]#1
Join order[3]:  [OT]#1  [TT]#0  [TF]#2
Join order[4]:  [OT]#1  [TF]#2  [TT]#0

Join order[5]: [TF]#2 [TT]#0 [OT]#1
Join order[6]: [TF]#2 [OT]#1 [TT]#0

The last two are driven by TF (which uses a skip scan calc) The first three incorrectly use the full-scan calc.

So what's special about join order 4 ?

The join predicate:
>> 38 WHERE TF.OTHE_ID = TT.OTHE_ID
>> 39 AND OT.DSCR = TT.DSCR)
>> 40* WHERE ID = 5485186
Note line 38 - there is a second predicate into TF, which becomes an ACCESS predicate into TF when TT appears before TF in the join order - which it does in the first three join orders (which do the wrong thing) but not in the fourth (which does the right thing)

So: probably a bug relating to the extra access predicate that appears; but possibly "expected behaviour" from the Oracle Support point of view.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html