no more spool space in recursive query

Database
Enthusiast

no more spool space in recursive query

Hi all,

I keep getting no more spool space error while using a recrusive query on a table which has like 24224872 rows. Is there any other better way to do it or how do i fix this? Thanks.

Regards,

Sangeeta

3 REPLIES
Teradata Employee

Re: no more spool space in recursive query

Can you give more details about the logic you are trying to implement ? Usually its about the logic that you are trying to implement. 

-Irfan

Enthusiast

Re: no more spool space in recursive query

Thanks for your help Irfan. Below is my sql -

CREATE MULTISET VOLATILE TABLE vt_temp AS

( SELECT

CUST_ID,state_id,

ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY state_id) AS rn

FROM myud.vt_allocpct)

WITH DATA

PRIMARY INDEX(CUST_ID)

ON COMMIT PRESERVE ROWS;

create RECURSIVE view rec_test(CUST,state,LVL)

AS (

SELECT CUST_ID,state_ID (VARCHAR(1000)), 1

FROM vt_temp WHERE rn = 1 UNION ALL SELECT CUST_ID, TRIM(state_ID)

|| ', ' || state,LVL+1

FROM vt_temp INNER JOIN rec_test ON state_ID = ALLOC AND vt_temp.rn = rec_test.lvl+1 ) ;

SELECT CUST,state,LVL

FROM rec_test QUALIFY RANK() OVER(PARTITION BY CUST ORDER BY LVL DESC) = 1;

My table myud.vt_allocpct has 24224872 values .

Format is -

cust_id       state

1                  abc

1                  ad

2                   we

2                   ws

Expected output is -

cust_id        state

1                  abc||ad

2                   we||ws

Junior Contributor

Re: no more spool space in recursive query

Using recursion for this task will result in a spool file with an approximate size of

(COUNT(*) + 1) * SUM(CHAR_LENGTH(state)+1) / 2 for each cust_id

Is there a known maximum number of states per customer?

Then you better use the old approach concatenating lots of

MAX(CASE MAX(CASE WHEN rn = x THEN ',' || state_id ELSE '' END