Re: When does CBO use index vs. FTS?

I will indeed do this.. But actually.. I was busy reading Chapter 6 of your book before going further. I thought this might be relevant - the blks_gets_per_access is actually higher the the blocks in the table. Pertinent?

Also, I rebuilt this index online, and the blks_gets_per_access dropped ever so slightly, while the clustering factor went up. I don't get this.. I would assume a freshly built index would be quite well-organized. (That's another subject of course).

In any case, I will work on your suggestions, but I wanted to toss this info out to the group in case it was relevant.

You mention in your book, that you use a different formula (pg. 103), to get an indication of how much work would need to be done, but I *assume* that the CBO is using the blks_get_per_Access. Maybe not -

Thanks again Jon.
- Doug

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 Sun Apr 01 2001 - 20:41:46 CDT