Odd WHERE NOT EXISTS performance on DB2

Question

I am experiencing very odd performance on DB2 version 9.1 when running the query below:

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
)
;

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?

If I hard code the cycle in the where not exists, then the query performs as expected.

Thanks, Dave

Answer 1

if the planner is this easily confused, you need to try a few different formulations. this untested (I don't even have DB2, but CTEs originated there):

WITH hist AS (
    cycl_num
  , ac_num
  , auths_dte
  , pl_id
  , aprvd_rspn_cde
  , auths_amt
  , auths_sts_cde
  , tran_ctgr_cde
  , mrchn_ctgr_cde
  FROM auths_rev_hist b
)
, auths AS (
  SELECT
    cycl_num
  , ac_num
  , auths_dte
  , pl_id
  , aprvd_rspn_cde
  , auths_amt
  , auths_sts_cde
  , tran_ctgr_cde
  , mrchn_ctgr_cde
  FROM nwhd12.chldr_auths
  WHERE cycl_num = 200911
    AND aprvd_rspn_cde = 'APV'
  EXCEPT
  SELECT ... FROM hist
)
SELECT a.*, d.out_pu_au_amt
FROM auths a, nwhd12.w_chldr_ac d
WHERE a.ac_num = d.ac_num