Question & Answer
Question
Why am I receiving "NOT ENOUGH MEMORY FOR MERGE-STYLE JOIN"
Cause
Query throws error - Not enough memory for merge-style join
Netezza supports different type of joins; for example HASH, Nested-Loop, and Merge. The optimizer
chooses the best algorithm to make all joins in a query. During an equi-join between two tables, a HASH join is used. Most table joins are performed as a HASH join, which is highly optimized for Netezza.
However, optimizer might choose merge join over HASH join in the following situations:
-- Materialized view
-- Data in each join table is sorted
-- Distribution key in the tables is defined as floating point data types (“double precision” data types).
Floating point numbers have the concept of a +0 and a -0, which makes hashing the values and comparing the values problematical.
A merge join can causes problem when a lot of new data is added to the underlying tables and a materialized view is not refreshed for some time. Then, new data that is sorted during the query execution might take some time and there is a higher cost that is associated with the sort operation that affects the performance.
Answer
Before the query execution, you can disable the Merge Join at the session level to force the optimizer to choose a better plan, as shown in the following example:
SET ENABLE_MERGEJOIN = off;
<QUERY GOES HERE> ;
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21998890