[IQUG] Hash PushDown Join in IQ16

Rittenhouse, David d.rittenhouse at sap.com
Wed Jan 4 07:07:34 MST 2017


Hi Leonid,

Hash Pushdown Join:

Advantages:
Ÿ  Is very fast, when appropriate
Ÿ  Can be used for INNER JOIN or RIGHT OUTER JOIN
Disadvantages:
Ÿ  Requires equality join condition where both sides are identical data types
Ÿ  Expression from the large side must be column with an LF or HG index
Constraints:
Ÿ  The small side must fit within available Temp Cache


It looks like for the HPDJ the optimiser is reporting “identical datatypes” for the join keys:

Column 1

CE.MI_MM_Main_Table.LOOKUP_ID

Column 1 Data Type

varchar(12)

Column 1 Column's Declared Data Type

varchar(15)


And:

Output 1

CE.MI_CDR1.C_ID`(1)

Output 1 Data Type

varchar(12)

Output 1 Column's Declared Data Type

varchar(15)



However – for the SMJ the optimiser is reporting something else:

Output 1

CE.MI_MM_Main_Table.LOOKUP_ID`(17)

Output 1 Data Type

varchar(9)

Output 1 Column's Declared Data Type

varchar(15)


And:

Output 1

CE.MI_CDR1.C_ID`(1)

Output 1 Data Type

varchar(10)

Output 1 Column's Declared Data Type

varchar(15)



Is it possible your datasets for these tests helped the optimiser “get lucky” (HPDJ) by having similar lengths, or maybe “got unlucky” (SMJ) due to shorter values for one of the keys ?

Or – maybe you could be running into a vagary of the “varchar narrowing feature.”

IQ 16 has a varchar narrowing feature whereby any operations that need to be done in temp (aka SMJ, order by, group by, etc) are done on a “shortened” datatype (I think intended to be the maximum size of varchar actually used as opposed to declared.)  The idea being that the smaller things are the faster they go through temp operations…

Now then – I’m not sure whether it’s even possible that the choice of join algorithm selected can be affected by the varchar narrowing feature, but I have seen significant enough problems with varchar narrowing in the past where Engineering recommended switching it off..


You might try removing this possibility from the equation by setting these :   (I’ve done these as permanent options at many sites..)

set temporary option DML_Options14 = 8        ;       --switch off varchar narrowing
set temporary option CORE_Options14 = 256  ;       --switch off related fast-hash algorithm (sounds bad, but is actually good)


Hope that helps – let us know what happens…


David Rittenhouse
Senior Consultant, SAP Data & Technology Services
Service & Support
SAP (UK) Limited
Clockhouse Place, Bedfont Road, Feltham, TW14 8HD Middlesex, UK
E:  d.rittenhouse at sap.com<mailto:d.rittenhouse at sap.com>
M: +44 (0) 7899 948 295
www.sap.com/uk
Please consider the environment before printing this email.











From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of Leonid Gvirtz
Sent: 22 December 2016 08:03
To: IQ Users Group <iqug at iqug.org>
Subject: [IQUG] Hash PushDown Join in IQ16

Hi

I have faced with an optimizer problem during the migration from IQ 15.2 GA to IQ 16 SP11 PL04. A number of queries work slower IQ 16, mostly because in some cases the query optimizer switched from Hash PushDown Join (HPDJ) to Sort Merge Join (SMJ). In fact, HPDJ is not even considered as a valid join algorithm anymore in IQ 16.

After a number of tests and a major simplification of the problem, I discovered that the problem is somehow dependent on the data distribution in involved tables. I prepared two test data sets. With one data set the optimizer chooses HPDJ and the query runs very fast. However, with another data set, the optimizer doesn't even consider HPDJ, as well as other pushdown algorithms, to be a valid join algorithm and chooses SMJ as result. Does somebody knows why?

What a necessary conditions for HPDJ to be valid? I attach query plans to demonstrate the problem? Just to be sure, the only different factor between the two test cases is the data itself, table and index structures are exactly the same.

Thanks in advance
Leonid Gvirtz
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20170104/1a60dd9c/attachment-0001.html>


More information about the IQUG mailing list