Skip to Content
Author's profile photo Jong-kil Park

[Share] Incorrect result problem when choosing HPDJ.

This blog is to share an incorrect result problem when IQ optimizer chooses a HPDJ.
It happens in all the IQ versions including SAP SYBASE IQ 16.0 SP08.23.

  • Query
        select distinct
           left(t2.DRUG_ATC7_CODE,5) as COMPARE_CODE,
       from DWM_ORDER_INF as t1,
            DWM_DRUG      as t2
    where t2.DRUG_CODE = t1.ORDER_CODE
        and COMPARE_CODE in (select CODE from DAMD_CODE where CODE_TYPE = ‘Q0000367’)
        and substr(t1.ORDER_CODE,8,1) = ‘1’ ;
  • Conditions to meet this problem (might be limited for this query)
    * HG index should be created on ORDER_CODE column of DWM_ORDER_INF table.
    * Table row count of DWM_ORDER_INF table should be more than 100,000.
    * HPDJ should be chosen.
  • Table Row Count
    DWM_ORDER_INF : 100,000 (1 row will be added later for reproduction)
    DWM_DRUG : 32,560
    DAMD_CODE : 4,338
  • At this step, IQ returns the correct result.
    1 row is additionally loaded into the DWM_ORDER_INF table.
    After 1 row was additionally loaded, the IQ server does not return any rows.

Found that this problem happens only when HPDJ(Hash Push Down Join) was chosen.
So, setting a temporary option Join_Preference to ‘-5’ will resolve this problem as a workaround.
Fortunately, it has been fixed in SP08.24.
Please refer to CR 770322 in the Coverletter of SP08.24 for detail.

Best Regards

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.