Re: wrong results from "Select count(*) from tbl"

John Muller wrote:
>
> I am the user of an Oracle DB and I am getting what
> look like wrong counts using "Select count(*)" statments.
>
> It first occurred when I tried to get monthly record counts with
> Select count(*), month
> from tbl
> group by month
> All the monthly counts but one looked like it was 2x what it should be.
>
> Oddly enough if I tried
> Select count(*), month
> from tbl
> where month = 200302
> group by month
> OR
> Select count(*), month
> from tbl
> where month in (200301 200302)
> group by month
> I got what looked correct.
>
> I even got the 2X problem when I asked for a count on
> the entire table using
> Select count(*)
> from tbl
>
> One of our DBAs suggested the problem has somehting to do with
> the indexes and not running analyze on the table recently.
> I'll note also that the table is partitioned.
>
> I think it's Oracle version 8.x running on a Sun box.
>
> Has anyone seen this before or does anyone have any
> suggestions on the cause of the problem.
>
> Thanks,
>
> - john

check the explain plans for each scenario. there were some bugs with index fast full scans and index desc scans a while back which gave wrong results.

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"