A SPU has run out of available memory to do a MergeJoinNode operation.
Resolving The Problem
If you encounter the error message: "Not enough memory for merge-style join", examine the plan file and look for a snippet containing a MergeJoinNode operation. Determine whether this snippet is performing any data type conversions.
Here is an example:
15: spu MergeJoinNode
table 1000000301 tblField=38 joinField=1 joinType=2 preSorted=1
-- (PO.INVENTORY_ID = EXPR."FLOAT8(LI.INVENTORY_ID)")
The system is converting the LI.INVENTORY_ID to a FLOAT8 data type. This conversion indicates that the two columns being joined have different data types. In this particular example the PO.INVENTORY_ID was a DOUBLE, and the LI.INVENTORY_ID was a BIGINT. This data type mismatch is preventing the system from using the more efficient HashJoinNode operation.
The resolution in this example is to change the data type of PO.INVENTORY_ID from DOUBLE to BIGINT. This converted the MergeJoinNode into a HashJoinNode. The BIGINT data type also allowed the system to take advantage of zone maps.
This situation is an example of an important best practice. If a column appears in more than one table, use the same data type consistently for that column.
Internal Use Only
Joe Jonas, 9/21/2009
17 October 2019