To be helpfull you have to give extra information:
1. Are there any indexes on tables b, c and d (index on join column can really help!) ?
2. Size of tables (approximate number of rows)
As a firstsuggestion: move the a.AGENT_CODE not like '%00007' condition to the start of the where clause. If that condition is false, the rest does not have to be evaluated.
Quote:
Originally Posted by kmugunda
Hi below is a query where certain set of data is fetched from a table based on certain condition, but the execution takes lot of time , is there a way we can optimize the code below
SELECT DISTINCT a.CNOTE_BOOK_ID, a.CNOTE_SERIES, a.AGENT_CODE, a.OPERATOR
FROM db2inst1.CNGC_BOOK a LEFT OUTER JOIN db2inst1.CNGC_NOTE b
ON a.CNOTE_BOOK_ID = b.CNOTE_BOOK_ID LEFT OUTER JOIN db2inst1.CMCN_SERIES c
ON a.CNOTE_SERIES = c.CODE LEFT OUTER JOIN db2inst1.MKAG_PROFILE d
ON a.AGENT_CODE =d.AGENT_CODE
WHERE b.EASC_DDATE IS NULL AND c.EASC_IND = 'Y' AND d.EAMS_IND = 'Y' AND a.AGENT_CODE NOT LIKE '%00007'
AND b.EASC_DTIME IS NULL AND b.DLOAD_OPERATOR IS NULL
