All Forums Database
Tnewbee 215 posts Joined 05/10
15 Jan 2015
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?
 
 
 
 

Raja_KT 1246 posts Joined 07/09
15 Jan 2015

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
15 Jan 2015

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?
 
 

Dieter

You must sign in to leave a comment.