Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: How to avoid COUNT(*) .. Optimization

    Hi All,

    I need to optimize this query ....

    SELECT
    COL1,
    COL2,
    COL3,
    COL4,
    COUNT(*) OVER ( PARTITION BY COL1, COL2 ) as RECORD_COUNT
    FROM ORDB.SOURCE_TABLE ;

    The output of this query is feed to a DB2 UDF . I need the count(*) , I can't discard it from the query .
    The table has 150 millions of rows . While I am trying to run this query , it takes lot of time .
    Probably "COUNT(*) OVER ( PARTITION BY COL1, COL2 ) as RECORD_COUNT" thing takes the whole time .
    Is there any possible way to optimize this query .
    I have unique index on (COL1,COL2) .
    The table is partitioned properly .

    Thanks in advance .. Any help would be appreciated .

    Thanks
    Mridul
    himridul

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You might create the unique index with the INCLUDE option for COL3 and COL4. This should allow for index-only access. You will have to drop the old index and create a new one.
    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
  •