COUNT(*) Not accurate?
in [DB2]
|
Prev: Recursive SQL PL ?
Next: db2look and trusted context
From: shorti on 6 Apr 2010 13:38 I am running DB2 V9.5 FP 4 on AIX. I just found a strange case where my SELECT COUNT(*) was giving me different totals on a table. I was checking the count on this parent table and the two child tables. The two child tables were accurately showing 130k records as all times, however, the parent table kept fluxuating. I could see it go down to 129,996 then slowly it would go back up to 129,999 or 130,000 but then it would go back down again. During this time there are updates going on but no inserts or deletes. When I did a runstats on the table it stopped the madness and stayed at the 130,000 as expected. How can this be? I thought COUNT(*) was always an accurate showing of the table's record count but it is acting like the cardinality count. Has any one seen this behaviour and/or can explain why it would behave like this? Ive never seen this in the last 6 years of using DB2 and it will cause a problem in some areas as we expect the COUNT(*) to be accurate. Should we open a DB2 PMR? Thanks!
From: Serge Rielau on 6 Apr 2010 18:58 On 4/6/2010 1:38 PM, shorti wrote: > I am running DB2 V9.5 FP 4 on AIX. I just found a strange case where > my SELECT COUNT(*) was giving me different totals on a table. I was > checking the count on this parent table and the two child tables. The > two child tables were accurately showing 130k records as all times, > however, the parent table kept fluxuating. I could see it go down to > 129,996 then slowly it would go back up to 129,999 or 130,000 but then > it would go back down again. During this time there are updates going > on but no inserts or deletes. When I did a runstats on the table it > stopped the madness and stayed at the 130,000 as expected. How can > this be? I thought COUNT(*) was always an accurate showing of the > table's record count but it is acting like the cardinality count. > > Has any one seen this behaviour and/or can explain why it would behave > like this? Ive never seen this in the last 6 years of using DB2 and > it will cause a problem in some areas as we expect the COUNT(*) to be > accurate. Should we open a DB2 PMR? Which Isolation level are you running? It is interesting to note that an UPDATE looks like an INSERT/DELETE to an INDEX if the index key is changed... PMR may be a good idea. -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
From: Tonkuma on 6 Apr 2010 22:53 Serge, are those variables DB2_SKIPDELETED, DB2_SKIPINSERTED or DB2_EVALUNCOMMITTED not related?
From: Mark A on 7 Apr 2010 03:33 "Tonkuma" <tonkuma(a)fiberbit.net> wrote in message news:97618c77-af59-4122-a049-b870a406fc2b(a)y17g2000yqd.googlegroups.com... > Serge, > are those variables DB2_SKIPDELETED, DB2_SKIPINSERTED or > DB2_EVALUNCOMMITTED not related? If you happen to be updating a column that is part of the index that DB2 is using for the COUNT(*) (DB2 will usually count the index entries rather than the rows because it is faster) and you have above environment variables turned on, then I could see that the number of index entries could change while the number of rows remains the same. This is because if the index is updated, then the index entry has to be deleted and re-inserted somewhere else with the new values.
From: shorti on 7 Apr 2010 13:21
On Apr 6, 3:58 pm, Serge Rielau <srie...(a)ca.ibm.com> wrote: > On 4/6/2010 1:38 PM, shorti wrote: > > > > > I am running DB2 V9.5 FP 4 on AIX. I just found a strange case where > > my SELECT COUNT(*) was giving me different totals on a table. I was > > checking the count on this parent table and the two child tables. The > > two child tables were accurately showing 130k records as all times, > > however, the parent table kept fluxuating. I could see it go down to > > 129,996 then slowly it would go back up to 129,999 or 130,000 but then > > it would go back down again. During this time there are updates going > > on but no inserts or deletes. When I did a runstats on the table it > > stopped the madness and stayed at the 130,000 as expected. How can > > this be? I thought COUNT(*) was always an accurate showing of the > > table's record count but it is acting like the cardinality count. > > > Has any one seen this behaviour and/or can explain why it would behave > > like this? Ive never seen this in the last 6 years of using DB2 and > > it will cause a problem in some areas as we expect the COUNT(*) to be > > accurate. Should we open a DB2 PMR? > > Which Isolation level are you running? > It is interesting to note that an UPDATE looks like an INSERT/DELETE to > an INDEX if the index key is changed... > > PMR may be a good idea. > > -- > Serge Rielau > SQL Architect DB2 for LUW > IBM Toronto Lab- Hide quoted text - > > - Show quoted text - We mainly run with dynamic SQL...we use a wide variaty of isolation levels on SELECTs depending on the task but mainly WITH UR, RS, CS and WITH RS USE AND KEEP UPDATE LOCK (followed by an update, of course). Otherwise, we do not specifically use SET ISOLATION. Does COUNT(*) work off of an indexing basis? I thought it was more like a row count type function....otherwise, it seems it would be much faster to get the mostly [inaccurate] count from cardinality in the system table!!! I guess I shoulld open the PMR? |