Does DB2 index cover JOIN, WHERE and GROUP BY columns. I have a query similar to below one which is not using the full index.
Query:
Select A.REF, A.MARKET, MAX(CREATE_TS) FROM ACTION A
INNER JOIN ACCESS AC ON A.ACC_ID = AC.ACC_ID AND AC.GROUP = 10 AND AC.PROF = 1
WHERE A.IND = 'MAND'
GROUP BY A.REF, A.MARKET
Index:
ON ACTION (ACC_ID, IND, REF, MARKET)
ON ACCESS (ACC_ID, GROUP, PROF)
Query is using index ACTION (ACCT_ID, IND) and ignoring the group by columns. It is using temp table for group by sorting.
6
The following indexes to optimize the query:
create index ix1 on action (ind, ref, market);
create index ix2 on access (group, prod, acc_id);