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
