Differing query results based on access path

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • db2query
    New Member
    • Jun 2007
    • 1

    Differing query results based on access path

    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_RS P
    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_RS P
    WHERE RSP_TPE_CD = 'HNG' ) )
Working...