i have the following query running which was running a bit slow.(it was not written by me)can any one suggest me how i could improve the performance.its not doing any table scans except inthe first CTE.even though there is a index on the join column its doing table scan as there are no predicates except the flag.how can i rewrite this query with out CTE's.
db2 V8.2fp5 solaris environment
with online AS.
FROM db2udb.online_XUSER_cls exc,
WHERE exc.cls_UUID_TX = E.PK_cls_UUID_TX
AND exc.cls = 'N'),
COUNT ( ccx ) AS TIMES_TAKEN,
MIN ( dets ) AS MinDATE,
MAX ( dets ) AS MaxDATE
WHERE sts_cd <> 'E'
GROUP BY snb,
SELECT EM.EMPLOYEE_WORK_LOCATION,EM.snb,EM.EMPLOYEE_NB,EM .FIRST_NM,
EM.LAST_NM,ET.dets,EM.sts_cd AS DEFAULT_sts_cd,ONL.ccx,
FROM EMPLOYEES EM
LEFT JOIN online ONL ON ONL.snb = EM.snb AND ONL.ccx = ES.ccx
LEFT JOIN DATES DT ON DT.snb = EM.snb AND DT.ccx = ONL.ccx
ORDER BY ONL.sts_cd,
any help is appreciated thanks