Re: Advice on a weird query plan

From: Jonathan Lewis <>
Date: Thu, 17 Apr 2008 12:54:37 +0100
Message-ID: <>

"Pat" <> wrote in message
>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 and where I've just checked it).


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Thu Apr 17 2008 - 06:54:37 CDT

Original text of this message