Re: When does CBO use index vs. FTS?

Huh. My experiment has been caught dead in the water. After rebuilding the index, everything now functions normally.

#1

SELECT STATEMENT   Cost = 82              
  INDEX FAST FULL SCAN THEINDEXINQUESTION      

for the original query..

#2

SELECT STATEMENT   Cost = 674                  
  TABLE ACCESS BY INDEX ROWID THETABLEINQUESTION      
    INDEX RANGE SCAN THEINDEXINQUESTION             

for snagging a different column with the same query.

and..

#3

SELECT STATEMENT   Cost = 680                    
  SORT ORDER BY                                  
    TABLE ACCESS BY INDEX ROWID THETABLEINQUESTION      
      INDEX RANGE SCAN THEINDEXINQUESTION             

for
A the same query as #2 with a sort and a dummy othercolumn=othercolumn statment.

Oh well. I still have an export as well as a cold backup I can restore with the "bad" index..

Should I be looking for something? Or should I try the trace events with the "bad" database.

Thanks,
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 Sun Apr 01 2001 - 21:42:42 CDT