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.
(SELECT exc.snb,
exc.dets,
EC.ccx,
EC.cls_NAME_TX,
EC.SHORT_DESCRIPTION_TX,
exc.SCORE_NB,
exc.sts_cd
FROM db2udb.online_XUSER_cls exc,
db2udb.online_cls EC
WHERE exc.cls_UUID_TX = E.PK_cls_UUID_TX
AND exc.cls = 'N'),
DATES AS
(SELECT snb,
ccx,
COUNT ( ccx ) AS TIMES_TAKEN,
MIN ( dets ) AS MinDATE,
MAX ( dets ) AS MaxDATE
FROM online
WHERE sts_cd <> 'E'
GROUP BY snb,
ccx)
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,
ONL.cls_NAME_TX,ONL.SHORT_DESCRIPTION_TX,ONL.SCORE _NB,
ONL.sts_cd,DT.TIMES_TAKEN,DT.MinDATE,DT.MaxDATE
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,
EM.FIRST_NM
any help is appreciated thanks

Before you try to rewrite it, you need to figure out why it is doing a table scan. How many rows are in db2udb.online_XUSER_cls? How many of those have cls = 'N'? Is there an index on cls?

Andy

the table is 1.5 million and cls is also almost the same and there is no
index on cls since its a flag i thoght there would not be much use.

You are better off not doing anything. Since you say that cls = 'N' is almost the entire table, than doing a table scan is not a bad plan to retrieve 98% of the table.

Andy