what is the fastest way to count rows for a table as opposed to do count(*)?
db2 8.2 on AIX.

TIA

That is the only way to get an accurate count. If you want just an estimate and you RUNSTATS are current, then you can: SELECT CARD from syscat.tables where tabname = 'mytable' and tabschema = 'myschema'

HTH

Andy

In production environments runstats will be updated on every night.... and etc. Better to use select count(0) instead of count(*).

Why would runstats be updated every night? Maybe in your production environment, but not everyone's.

There is no difference in performance between select count(*) from table-name, and select count(0) from table-name.

I did not see select count(0) documented in the SQL Reference Vol 1. Although it does work like count(*), I would not use any undocumented syntax.

Hi,
I agree with Marcus_A, don't use undocumented syntax, because it can stop working in new version! So you can have problems when migrating the db2 to new version.

BTW, has anyone created a SQL access plan to prove the performance statement?
Hope this helps,
Grofaty

I ran visual explain with both (* and 0) and the timerons were the same. I tried it on a table with 10 million rows and another one with 37 rows.

DB2 will scan the leaf pages of the smallest index to do the count.

"select Count(0) from table1" is valid SQL ... It is the similar to as "select 0 from table1" ...

Sathyaram

OK, so any literal (numeric or character) will do. But there is no advantage over count(*) that I can see when trying to count the rows in a table.