Hi,

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?

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.

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

thx