Because it probably calculates it would take LESS i/o to do the full table
scan, instead of using the index.
Rough guideline is whenever you are returning more than 10 percent of the
records of a table use FTS.
Also, your select is using on column, albeit the leading one, of the index
ONLY.
Hth,
--
Sybrand Bakker, Oracle DBA
<jays_at_yardi.com> wrote in message news: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?
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Dec 08 1999 - 16:17:39 CST
