Re: Advice on a weird query plan

"Pat" <pat.casey_at_service-now.com> wrote in message news:54a55e5f-0dc2-4628-8f06-bf1982f95e31_at_b64g2000hsa.googlegroups.com...
>I have a bit of a followup I was hoping folks could help me with. I
> created histograms on the hidden tables in question and my test case
> started using an index range scan. I think my test case was incomplete
> though because a couple of other queries against the store are showing
> different behavior.
>
> Specifically:
>
> select * from sysevent where state='a' <- index range scan
> select * from sysevent where state='ab' <- index range scan
> select * from sysevent where state='abc' <- index range scan
> select * from sysevent where state='abcd' <- index range scan
> select * from sysevent where state='abcde' <- index range scan
> select * from sysevent where state='abcdef' <- table access full
>
> There are no records in the database meeting either of those criteria
> e.g. all queries return nothing.
>
> Seems like any query term with length >=6 is doing a table access
> full.

This looks like a piece of "special case" code that Oracle has put in for the case of character-based histograms. As Mathew Butler pointed out, a character-based histogram effectively records information about just the first six BYTES of the character string, and because of the hextoraw() conversion that's going on, your 'abcdef' turns into a seven byte string. Even without using NLS settings, you can see this type of thing happening - although you'd have to be using a seven character string if you're using the typical single-byte character set.

The full scan is because the arithmetic changes at that break point and predicts a much larger number of rows. It is possible that the change of strategy is deliberate, but the resulting arithmetic is NOT a good strategy.

I'll take a guess that (assuming your stats are perfectly up to date) the predicted cardinality in the execution plan is roughly:

    number of rows in table / (2 * number of rows with value 'ready')

Bottom Line: you haven't done anything wrong, it's just the way that Oracle works (at least in 9.2.0.8 and 10.2.0.3 where I've just checked it).

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