Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: wrong results from "Select count(*) from tbl"

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

From: John Muller <john.muller_at_bankofamerica.com>
Date: 17 Jul 2003 06:57:44 -0700
Message-ID: <270abe86.0307170557.ff6c652@posting.google.com>


Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3F168070.3C3E_at_yahoo.com>...
> 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

Thanks for all the suggestions.
The version of Oracle is 8.1.7.4.0

You are right that what I showed is not exactly the query but all I chaged were the name of the field month (actually month_key, and it's numeric)
and the name of the table (actually Loan_History) and it is definitely a table not a view.

I think Conner is on the right track since we get the "odd" results when we look at explain plan and see it using a particular index ... and I know there are lots of indexes for this table, more than 20. I think the table is also partitioned.
Does that help solve the mystery?

Can you point me to more info on the bug? Would there be info on the Oracle OTN site?

Thanks again,
- john Received on Thu Jul 17 2003 - 08:57:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US