Spool space issue

Database
Enthusiast

Spool space issue

CREATE MULTISET VOLATILE TABLE VolatileTable2
AS
(
 SELECT   ROW_NUMBER() OVER ( ORDER BY
   ............................
 FROM
 (
  SELECT    ....
  FROM  VolatileTable0 d0
  INNER JOIN VolatileTable1 d1
  ON  d0.DT BETWEEN d1.DT1 AND d1.DT2
  GROUP BY 1,2,3,4
 ) T1
)
WITH DATA
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?

2 REPLIES
Enthusiast

Re: Spool space issue

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.

Junior Contributor

Re: Spool space issue

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?