Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    121

    Unanswered: count rows fo a table

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

    TIA

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jan 2004
    Posts
    49

    Use select count(0)

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


    Quote Originally Posted by ARWinner
    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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by bala_e
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    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

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by grofaty
    Hi,
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    "select Count(0) from table1" is valid SQL ... It is the similar to as "select 0 from table1" ...

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by sathyaram_s
    "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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •