No More Spool space

Database
Enthusiast

No More Spool space

Hello,

I'm running the following query and I'm receiving the error "No more spool space on DIW07"

create volatile table dt as (

SELECT clm.*,

Coalesce(cida.n_lst, gida.n_lst, idv.n_lst, '') As N_LST,
Coalesce(cida.n_fst, gida.n_fst, idv.n_fst, '') As N_FST,
Coalesce(cida.i_ssn, gida.i_ssn, CAST(idv.i_ssn AS INTEGER), 0) As I_SSN,
Coalesce(cida.d_bth, gida.d_bth, idv.d_bth, CAST( '1900-01-01' As Date)) As D_BTH

FROM bcpm_ddbo.vltc900a_clm clm
LEFT JOIN bcpm_ddbo.vltc917a_cid cida
ON clm.i_sys_idv = cida.i_sys_idv AND clm.c_typ_sys_idv = cida.c_typ_sys_idv
LEFT JOIN bcpm_ddbo.vltc907a_gid gida
ON clm.i_sys_idv = gida.i_sys_idv AND clm.c_typ_sys_idv = gida.c_typ_sys_idv
LEFT JOIN bcpm_ddbo.vltc01a_idv idv
ON clm.i_sys_idv = idv.i_sys_idv AND clm.c_typ_sys_idv = idv.c_typ_sys_idv
) with data on commit preserve rows;

select clm.*,
Coalesce(ercov.d_eff_cov, idvcov.d_eff_cov, profl.cov_eff_dt, CAST ('1900-01-01' As Date)) As D_COV
FROM dt clm

/*Group*/
LEFT JOIN (select i_ird ,d_eff_cov from bcpm_ddbo.vltc_er_cov_mo
where UDF_IsNumeric(substr(i_ird,9,9))=0) ercov
ON clm.I_SSN = cast(substr(ercov.i_ird, 9, 9) as integer)

/*OPAL*/
LEFT JOIN (select i_ird ,d_eff_cov from bcpm_ddbo.vltc_idv_cov_mo
where UDF_IsNumeric(substr(i_ird,9,9))=0) idvcov
ON clm.I_SSN = cast(substr(idvcov.i_ird, 9, 9) as integer)

/*CCP*/
LEFT JOIN bcpm_ddbo.ltc_profl profl
ON clm.i_ssn = profl.txpay_id

It's It was running fine until I added the join:
/*CCP*/
LEFT JOIN bcpm_ddbo.ltc_profl profl
ON clm.i_ssn = profl.txpay_id

after this it ran out of spool space... It is not indexed on txpay_id, but rather a more unique column... It's driving me crazy... Any suggestions would be greatly appreciated!!!!!!!
2 REPLIES
Enthusiast

Re: No More Spool space

Try joining only the first 2 tables and putting the results into a temp table. Then join your 3rd table to the temp table after collecting stats on the temp table.
Enthusiast

Re: No More Spool space

Try to avoid the derived tables in your query. becoz all derived tables will be kept in spool for processing. that will occupy some spool space. still u cannot avoid the error use temporary tables...

Thanks,
Senthil