Hello Guys,
Am posting two queries here which are exactly same except for an always false condition( 0 = 1) to influence the access path selection, to my surprise i am getting 183 records if the query goes for a table space scan and the number of records fetched is 0 if the index scan happens. I am running the query in DB2 V8 for Z/OS. This is totally baffling as the option of adding a false condition is one of the tuning option suggested at so many place. Any justification for this behaviour would help us greatly.
Query 1 (No Records)
EN_ID FOR TABLE NFM.VWNFM_ENAME IS HAVING A INDEX AND THAT IS BEING USED IN THIS QUERY
SELECT *
FROM
NFM.VWNFM_ENAME A,
NFM.VWNFM_RLE B,
EFM.VWEFM_XEF D
LEFT OUTER JOIN
NFM.VWNFM_ARESS C
ON (D.CR_EN_ID_CD = C.EN_ID
AND C.AD_TPE = 'OC')
WHERE
B.CR_EN_ID_CD = D.CR_EN_ID_CD
AND D.EN_ID_CD = 'DIGRO'
AND D.RC_ST_CD = '1'
AND A.EN_ID = D.CR_EN_ID_CD
AND
A.EN_ID IN
(SELECT DISTINCT AT_MN_ID FROM
EFM.VWEFM_ET_RSP
WHERE RSP_TPE_CD = 'HNG' )
Query 2 (183 Records output)
EN_ID FOR TABLE NFM.VWNFM_ENAME IS HAVING A INDEX AND THAT IS BEING AVOIDED BY PUTTING A OR CLAUSE HENCE FORCING A TABLESPACE SCAN
SELECT *
FROM
NFM.VWNFM_ENAME A,
NFM.VWNFM_RLE B,
EFM.VWEFM_XEF D
LEFT OUTER JOIN
NFM.VWNFM_ARESS C
ON (D.CR_EN_ID_CD = C.EN_ID
AND C.AD_TPE = 'OC')
WHERE
B.CR_EN_ID_CD = D.CR_EN_ID_CD
AND D.EN_ID_CD = 'DIGRO'
AND D.RC_ST_CD = '1'
AND A.EN_ID = D.CR_EN_ID_CD
AND ( 0 = 1 OR
A.EN_ID IN
(SELECT DISTINCT AT_MN_ID FROM
EFM.VWEFM_ET_RSP
WHERE RSP_TPE_CD = 'HNG' ) )