Re: When does CBO use index vs. FTS?

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.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Doug C wrote in message ...


>You hit the nail on the head, only there's a problem.   I'm only selecting

 only


>the indexed column.  I would think therefore that the query could be

 satisfied


>by the index by itself, but that's not what Oracle is doing - it is doing a

 full


>table scan.  I can make it use the index with a histogram or a hint, but

 I'm


>just very curious as to why it is doing a FTS.   There must be something in

 it's


>calculated statistics that's telling it the index is a waste of time, but

 what?


>How can I see that?  The index is not perfectly contiguous, but what is the

>signal to Oracle, and what is it's value?

>

>- D

>