SPOOL SPACE ERROR

Database
Enthusiast

SPOOL SPACE ERROR

I am trying to generate a sequential no and it worked fine for first couple of inserts.
But when i run this query, I get a spool error. If I remove the seq_no generator, the query runs fine. Now the COl1 does contain many dupes. Any way to fix this?
INSERT INTO TABLEb
SELECT DISTINCT
cnt + row_number() over (order by 1),
H.COL1 ,
H.COL2 ,
H.COL3 ,
NULL,
NULL
FROM TABLE H
, (select coalesce(max(seq_NO), 0) as cnt from TABLEB) dt
WHERE H.COL1 IS NOT NULL

Thanks!
8 REPLIES
Junior Contributor

Re: SPOOL SPACE ERROR

"row_number() over (order by 1)" will send all rows to a single AMP, better order by another column with lots of distinct values.

And remove the DISTINCT, it's useless, because the row_number will be already unique.

Dieter
Enthusiast

Re: SPOOL SPACE ERROR

The reason I am adding a sequence number is because its difficult to find column/columns with distinct values. IS there any other way to genertae the sequence no instead? I did not want to use IDentity because the seq numbers might not be in sequence.
Enthusiast

Re: SPOOL SPACE ERROR

dnoeth, can you help me here?
Junior Contributor

Re: SPOOL SPACE ERROR

You don't need a unique column combination, just more than the current number, which is exactly 1.

Whitin an OLAP-function "ORDER BY 1" is *not* the ordinal position within the Select list, it's the value 1.
Teradata sends all the rows to a single AMP to do that ROW_NUMBER calculation and therefore runs out of spool.

So simply do something like
SELECT
cnt + row_number() over (order by H.Col1, H.Col2, H.Col3),
H.COL1 ,
H.COL2 ,
H.COL3 ,

Just as many columns to get enough distinct values.

Dieter
Enthusiast

Re: SPOOL SPACE ERROR

I tried using 5 columns but it stiill runs into spool error. Now I have a basic question. If my Select statement returns duplicates, then will these dupes be inserted in a SET table?When I say dupes, the entire record is a dupe , not just the key.
When I look at the table that I inserted, i find dupes in it. How does a SET tabel allow dupes?
How do I remove the dupes from the Select?
Enthusiast

Re: SPOOL SPACE ERROR

IS it the SEQ no which allows this dupes?
Enthusiast

Re: SPOOL SPACE ERROR

Dieter, I tried ordering by atleast 5/6 columns and it still gives me spool space error . I also tried
MAX_ID + CSUM(1,1) AS SEQ_NO
, (select case when max(SEQ_NO) is null then 0 else max(SEQ_NO) end MAX_ID from table1) D;

IS there any other way to generate seq no?
Junior Contributor

Re: SPOOL SPACE ERROR

When you create a kind of sequence, there are no more duplicate rows, because the sequence makes all of them distinct.
The DISTRINCT in your query is done *after* the calculation of the ROW_NUMBER.

MAX_ID + CSUM(1,1) is exactly the same as the ROW_NUMBER () OVER (ORDER BY 1).
If you don't have any column combination to get a better distribution for the OLAP-function, you might try RANDOM. But you can't nest RANDOM in an OLAP-function, so you have to nest it in a Derived Table:

INSERT INTO TABLE b
select cnt + row_number() over (order by r),
H.COL1 ,
H.COL2 ,
H.COL3 ,
NULL,
NULL
from
(
SELECT
H.COL1 ,
H.COL2 ,
H.COL3 ,
random(1,1000000000) as r
FROM TABLE H
) as H
, (select coalesce(max(seq_NO), 0) as cnt from TABLEB) dt
WHERE H.COL1 IS NOT NULL

If you need DISTINCT over (H.COL1 ,H.COL2 ,H.COL3) you have to nest it into another Derived Table...

Dieter