Strange optimizer results

From: <jays_at_yardi.com>
Date: Wed, 08 Dec 1999 21:20:21 GMT
Message-ID: <82mi2g$q4j$1@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 - 15:20:21 CST