Re: When does CBO use index vs. FTS?

People seem to have lost interest in this.. but in any case.. I have tossed the trace file to Oracle for deciphering - and I did make a mistake -
The query using just the index does a FAST scan.. But the query in question
was
select ATOTALLYDIFFERENTINDEXEDCOLUMN from THETABLEINQUESTION WHERE THE INDEXEDCOLUNIWASTALKINGABOUT = 'Whatever';

An "expert" says it should use the index. Without a histogram - I don't see how, but in any case - the way I'm looking at it is that the blks_get_per_access is higher that the blocks in the table - but I'm sure that's not the whole story.

I aplogize for getting the syntax of this query wrong in the first place, but heck, with any luck, Oracle will help be figure out how to decipher a '10053 trace name context forever, level 1'.

I very much appreciate everyone's thoughts and contributions!!

• Dc

On Sun, 1 Apr 2001 19:37:49 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>The arithmetic is quite straight-forward in principal,
>but made more complicated by the number of different
>factors you have to check before you decide which
>sums you should be doing.
>
>In the simplest case, if you run a query:
> select columns from table where indexed_col = constant,
>then, (plus or minus a little bit) Oracle looks at
> avg_leaf_blocks_per_key
>plus avg_data_blocks_per_key
>
>and compares the with (plus or minus 1)
> blocks_below_hwm in table / multiblock read count.
>
>If the first is smaller you get an index range scan and
>table access. If the second is smaller you get a full tablescan.
>
>However, since your query can be satisfied from the index
>alone, I would have expected Oracle to examine
> blocks_below_hwm in INDEX / multiblock read count.
>
>and compare this with avg_leaf_blocks_per_key to determine
>whether to use an index fast full scan or an index range scan.
>
>
>The description you've given us to date, though, suggests
>that Oracle isn't doing exactly what I would expect it to.
>Perhaps there's some detail you haven't mentioned yet
>that is significant.
>
>If you want to check out the assumptions the CBO is making,
>use a 10053 trace:
> alter session set events '10053 trace name context forever, level 1';
>
>then run a NEW version of the query - a 10053 trace dumps the
>CBO calculations the first time a new query is parsed.
Received on Mon Apr 02 2001 - 23:26:16 CDT