COUNT(*) Not accurate?
in [DB2]
|
Prev: Recursive SQL PL ?
Next: db2look and trusted context
From: shorti on 7 Apr 2010 13:39 On Apr 7, 12:33 am, "Mark A" <no...(a)nowhere.com> wrote: > "Tonkuma" <tonk...(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. We do have the following variables on: [i] DB2_SKIPINSERTED=ON [i] DB2_EVALUNCOMMITTED=ON [i] DB2_SKIPDELETED=ON So if I understand you correctly, the behavior we are seeing is normal? I am doing some research to see if these were turned on fairly recently.
From: Mark A on 7 Apr 2010 20:55 >"shorti" <lbryan21(a)juno.com> wrote in message >news:7b4f9997-46d6-45fc-85d6-abdcd6bfd278(a)s9g2000yqa.googlegroups.com... > > >We do have the following variables on: > > [i] DB2_SKIPINSERTED=ON > [i] DB2_EVALUNCOMMITTED=ON > [i] DB2_SKIPDELETED=ON > > > > So if I understand you correctly, the behavior we are seeing is > normal? I am doing some research to see if these were turned on > fairly recently. I don't know for sure if the above variables are causing the issue. My understanding is that DB2 will count the number of entries in an index rather than the table if it thinks it is faster (which I am happy about). If you want DB2 to look only at the table rows (albeit somewhat slower) then you probably could craft an SQL statement that forces DB2 to look at the data pages and not just one of the indexes. For example, if you had an integer column that was not indexed, you could add the following to the predicate of the count(*) statement: WHERE COL1 <= 2147483647
From: shorti on 8 Apr 2010 14:33 On Apr 7, 5:55 pm, "Mark A" <no...(a)nowhere.com> wrote: > >"shorti" <lbrya...(a)juno.com> wrote in message > >news:7b4f9997-46d6-45fc-85d6-abdcd6bfd278(a)s9g2000yqa.googlegroups.com... > > >We do have the following variables on: > > > [i] DB2_SKIPINSERTED=ON > > [i] DB2_EVALUNCOMMITTED=ON > > [i] DB2_SKIPDELETED=ON > > > So if I understand you correctly, the behavior we are seeing is > > normal? I am doing some research to see if these were turned on > > fairly recently. > > I don't know for sure if the above variables are causing the issue. My > understanding is that DB2 will count the number of entries in an index > rather than the table if it thinks it is faster (which I am happy about). If > you want DB2 to look only at the table rows (albeit somewhat slower) then > you probably could craft an SQL statement that forces DB2 to look at the > data pages and not just one of the indexes. For example, if you had an > integer column that was not indexed, you could add the following to the > predicate of the count(*) statement: > > WHERE COL1 <= 2147483647 I am actually perplexed on this. We do experience slow COUNT(*) returns at times as well but I always thought it was at least accurate at all times. It seems a simple thing to have an accurate count of the number of records on a table without having to worry about if it needs to use an index or not. I would think a simple thing like a total_row_count_p pointer should have that information stored in a system table (or some other mechanism). That type of information would be important for most database users. Now, I am finding out it may not be accurate at times without a stats refresh. I would like to hear DB2's opinion on this. If its slow (without a reorg/runstats) and not always accurate then why not just get the data from the system table Cardinality count? Its much faster! We would keep track of our own inserts/deletes and keep a count if it were not so difficult to do with bulk inserting and deleting. It would be easier for DB2 to do this internally.
From: Mark A on 8 Apr 2010 21:25 "shorti" <lbryan21(a)juno.com> wrote in message news:3879c455-5975-41b1-a1b0-7992e22a4f83(a)j21g2000yqh.googlegroups.com... > It would be easier for DB2 to do this internally. Yes it would, but it would slow DB2 down having to keep track of the row count at all times, and I don't want that extra overhead on my DB2 databases (too much overhead already).
From: Serge Rielau on 9 Apr 2010 08:13
DB2 doesn't have an opinion. (Although sometimes it feels like it ;-) Teh registry variables liek DB2_SKIP_DELETED and EVALUNCOMMITTED do have side effects. If theyhand;t they woudl be always on. Let's assume for a moment DB2 had such a global variable that holds the row count for each table. That variable would have to be incremented and incremented by each insert/delete from each concurrent connection. Latch contention would kill concurrency. You cannot run a stock exchange with 100k Tx/sec with that sort of bottlenecks. Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab |