Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012

    Question Unanswered: Why combined index improves performance of SUM ?


    I have a question concerning combined index and SUM. The following query needs about 20 minutes:

    SELECT vendorid, count(*), SUM(value) from TABLE1 WHERE vendorid=1112233 group by vendorid;

    I have an index for the column vendorid. If I skip the SUM part then the query is finished below one second. The amount of entries to sum up is about 300.000.

    db2advis told me to make a combined index of (vendorid ASC, value DESC). Now the query is very fast. But I do not understand why the combined index makes the SUM operation faster?

  2. #2
    Join Date
    May 2003
    Because with the combined index DB2 can satisfy the entire query by only accessing the index and not even having to go to the table to get the results. You should probably determine how often this query runs and whether it is worth adding the value column to the index, especially since it might make other SQL run slightly slower. DB2 advisor can produce misleading results unless you provide it the entire SQL workload against the 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

  3. #3
    Join Date
    Nov 2011
    Can you publish the access plan here ?

    1、without combined indexes and without sum,
    2、without combined indexes and with sum
    3、with combined indexes


Tags for this Thread

Posting Permissions

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