IBM Support

Error: Not enough memory for merge-style join  

Troubleshooting


Problem

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[04]: 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
https://sharepoint.netezza.com/sites/cskb/Pages/NZ272534.aspx

[{"Product":{"code":"SSULQD","label":"PureData System for Analytics"},"Business Unit":{"code":"BU029","label":"Data and AI"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":""}]

Historical Number

NZ272534

Document Information

Modified date:
17 October 2019

UID

swg21570439