Results 1 to 5 of 5

Thread: table count

  1. #1
    Join Date
    May 2004
    Posts
    2

    Question Unanswered: table count

    Hi,

    Is there a better / faster way of doing a table count?

    Curently using:
    select count(*)
    from table
    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Depends on what you need it for. If a good estimate is sufficient (and provided statistics are current) you can use
    Code:
    select card from syscat.tables where tabname='TABLENAME'

  3. #3
    Join Date
    May 2004
    Posts
    2
    Thanks!

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I've wondered this myself. You could replace * with a constant, something like 'count(1)', or maybe just select an indexed column rather than *, i.e. count(indexed_column).

    I tend to use count(*) or count(1), depending on which way the wind is blowing.

    I've read articles that say that any one of the 3 concepts is the best but nothing to date has convinced me.

    Maybe someone in this forum could enlighten us?

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you get the card (row count) form syscat.tables, it is only accurate as of the last time runstats command was executed on that table.

    When running a count(*) opn a table with no WHERE clause (or if a WHERE clause is used, all predicates are in the index), DB2 will read an index rather than the table to get the count of rows (in most situations).
    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
  •