CREATE MULTISET VOLATILE TABLE VolatileTable2
SELECT ROW_NUMBER() OVER ( ORDER BY
FROM VolatileTable0 d0
INNER JOIN VolatileTable1 d1
ON d0.DT BETWEEN d1.DT1 AND d1.DT2
GROUP BY 1,2,3,4
PRIMARY INDEX (ACCT_ID)
ON COMMIT PRESERVE ROWS
We are creating volatile tables where we run into spool space issues. The problem seems to be the join . Is there a better way of writing the join condition?
Few things to look at: Is any of the group by field a lengthy or oversized varchar?
You can check the explain of your select part by part and fix the issues.
See if you can use exists instead of joins. But it also depends on the data volume and demography.... See if you can use derived table.
Group by, between can cause performance issues. Use them judiciously.
My suggestion is not to increase spool space unneccesarily, until and unless it is really called for.
A join condition like yours based on non-equality will always result in a cross join, as preparation the smaller table will be "duplicated on all AMPs", followed by a product join cüpotentially creating a huge intermediate spool which is then aggregated.
Without knowing what you're trying to do it's hard to tell if you can fix it.
Can you add some more info, DDL, row counts, SQL and explain?