title: Odd WHERE NOT EXISTS performance on DB2
body: <p>I am experiencing very odd performance on DB2 version 9.1 when running the query below:</p>
  
  <pre><code>select  a.CYCL_NUM
  ,   a.AC_NUM
  ,   a.AUTHS_DTE
  ,   a.PL_ID
  ,   a.APRVD_RSPN_CDE
  ,   a.AUTHS_AMT
  ,   a.AUTHS_STS_CDE
  ,   a.TRAN_CTGR_CDE
  ,   a.MRCHN_CTGR_CDE
  ,   d.out_pu_au_amt
  from nwhd12.chldr_auths a, nwhd12.w_chldr_ac d 
  where cycl_num = 200911
  and a.ac_num = d.ac_num
  and APRVD_RSPN_CDE = 'APV'
  and not exists (
      select 1 from auths_rev_hist b
   where a.cycl_num = b.cycl_num
          and a.auths_dte = b.auths_dte
          and a.TRAN_CTGR_CDE = b.TRAN_CTGR_CDE
          and a.PL_ID = b.pl_id
          and a.APRVD_RSPN_CDE = b.APRVD_RSPN_CDE
   and a.AUTHS_AMT = b.auths_amt
          and a.TRAN_CTGR_CDE = b.TRAN_CTGR_CDE
          and a.MRCHN_CTGR_CDE = MRCHN_CTGR_CDE
  )
  ;
  </code></pre>
  
  <p>What is supposed to happen is that the query accesses partion 97 of nwhd12.chldr_auths, since that is the partition corresponding to cycle 200911.  Instead, after accessing partition 97, it starts accessing every other partition in nwhd12.chldr_auths.  Now, I was told that this is because of the "WHERE NOT EXISTS", but there is still the restriction on cycles in this statement (a.cycl_num = b.cycl_num), so why is it scanning all the partitions?</p>
  
  <p>If I hard code the cycle in the where not exists, then the query performs as expected.</p>
  
  <p>Thanks,
  Dave </p>