Query Optimization with COALESCE function

Hi All,

I had this SQL query given below which was taking about 1,888,869.5 timerons as per the DB2 Access Plan.

---------------------------------------------------------
Original Query
---------------------------------------------------------
SELECT DISTINCT
G.DCACARLINECODE AS DCACARLINECODE,
H.DCAMARKETCODE AS DCAMARKETCODE,
D.DCACALYEAR AS DCACALENDARYEAR
FROM AWVPS105.DCATLOADPLTGRP AS A,
AWVPS105.DCARPBSPLT AS B,
AWVPS105.DCARPLTGRPLUKUP AS C,
AWVPS105.DCAMMYCYBKT AS D,
AWVPS105.DCARSTVAMEPLT AS E,
AWVPS105.DCARCLPBS AS G,
AWVPS105.DCARPBSMT AS H
WHERE A.DCAPLANNO = 3500
AND B.DCAPLANNO = A.DCAPLANNO
AND E.DCAPLANNO = B.DCAPLANNO
AND G.DCAPLANNO = E.DCAPLANNO
AND H.DCAPLANNO = G.DCAPLANNO
AND D.DCACYMYBUCKID = A.DCACYMYBUCKID
AND D.DCAMODELYEAR = B.DCAMODELYEAR
AND D.DCAMODELYEAR = H.DCAMODELYEAR
AND B.DCAPLTCODE = E.DCAPLTCODE
AND C.DCAPLANTDESC = E.DCAPLANTDESC
AND A.DCALOADPLTGRPID = C.DCALOADPLTGRPID
AND G.DCAPCBSCODE = B.DCAPCBSCODE
AND G.DCAPCBSCODE = H.DCAPCBSCODE
ORDER BY
G.DCACARLINECODE,
H.DCAMARKETCODE,
DCACALENDARYEAR
-----------------------------------------------------------

I modified the query as below which is taking about 1,178.4 timerons as per the DB2 Access Plan.

-----------------------------------------------------------
Modified query
-----------------------------------------------------------

SELECT DISTINCT
G.DCACARLINECODE AS DCACARLINECODE,
H.DCAMARKETCODE AS DCAMARKETCODE,
D.DCACALYEAR AS DCACALENDARYEAR
FROM AWVPS105.DCATLOADPLTGRP AS A,
AWVPS105.DCARPBSPLT AS B,
AWVPS105.DCARPLTGRPLUKUP AS C,
AWVPS105.DCAMMYCYBKT AS D,
AWVPS105.DCARSTVAMEPLT AS E,
AWVPS105.DCARCLPBS AS G,
AWVPS105.DCARPBSMT AS H
WHERE A.DCAPLANNO = 3500
AND B.DCAPLANNO = A.DCAPLANNO
AND E.DCAPLANNO = B.DCAPLANNO
AND G.DCAPLANNO = E.DCAPLANNO
AND H.DCAPLANNO = G.DCAPLANNO
AND D.DCACYMYBUCKID = A.DCACYMYBUCKID
AND D.DCAMODELYEAR = B.DCAMODELYEAR
AND D.DCAMODELYEAR = H.DCAMODELYEAR
AND B.DCAPLTCODE = E.DCAPLTCODE
AND C.DCAPLANTDESC = E.DCAPLANTDESC
AND A.DCALOADPLTGRPID = C.DCALOADPLTGRPID
AND G.DCAPCBSCODE = B.DCAPCBSCODE
AND G.DCAPCBSCODE = H.DCAPCBSCODE

AND H.DCAMARKETCODE = COALESCE(H.DCAMARKETCODE,H.DCAMARKETCODE)
AND C.DCALOADPLTGRPID = COALESCE(C.DCALOADPLTGRPID,C.DCALOADPLTGRPID)
AND G.DCACARLINECODE = COALESCE(G.DCACARLINECODE,G.DCACARLINECODE)
AND D.DCACALYEAR = COALESCE(D.DCACALYEAR,D.DCACALYEAR)

ORDER BY
G.DCACARLINECODE,
H.DCAMARKETCODE,
DCACALENDARYEAR
-----------------------------------------------------------

I have used the coalesce statements on columns which are being compared e.g. C.DCALOADPLTGRPID
and columns part of some primary key which are not being used in the query e.g. H.DCAMARKETCODE.

Could anyone explain to me with details why the modified query executes faster?

Thanks in Advance,
Adrain