Re: Advice on a weird query plan
Date: Thu, 17 Apr 2008 12:54:37 +0100
"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.
> 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
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 220.127.116.11 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.htmlReceived on Thu Apr 17 2008 - 06:54:37 CDT