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,
           t2.DRUG_CODE,
           t1.ORDER_PKNO
       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.

[Findings]  
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
Jerry

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply