Re: Strange optimizer results

Probably because on a simple averaging
set of stats Oracle determines that the range from 400,000,000 to 499,999,999
is a significant fraction of the whole table. Under all_rows optimisation that dictates a tablescan. Under first_rows the existence of an appropriate index allows the first row to be returned immediately. Under rule-based Oracle uses the index because it is there.

If you want to run you system with a DSS bias (i.e. all_rows instead of first_rows) try generating a histogram on the indexed columns and see if the path changes to
an indexed access.

--

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

jays_at_yardi.com wrote in message <82mi2g$q4j$1_at_nnrp1.deja.com>...
>I have a table with 6.5m rows. When I run the following select, it is
>fast, and uses index I_TRANS_10 (hmy, hacct)
>
>select hmy from trans where hmy between 400000000 and 499999999 and
>uref = 'abc'
>
>However, when I run analyze index compute/estimate statistics on it,
>the same query does a full table scan and takes a long time.
>
>Setting the optimizer to FIRST_ROWS or RULE makes it choose the correct
>index.
>
>What's going on? Why does analyze stats make the optimizer choose a
>full table scan?
Received on Wed Dec 08 1999 - 16:25:17 CST