No More Spool space


No More Spool space


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

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)

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)

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:
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!!!!!!!

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.

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...