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

Your DBA is clueless and I am left wondering whether the SQL you put into your post is the actual SQL or something you put together as a simple example: Is it possible you are selecting from a view and not a table? Or, in any case, whether the query is actually hitting more than a single table?

When you respond include the version and edition out to at least four decimal places.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)