tuning Query :: No more spool space

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Highlighted
Fan

tuning Query :: No more spool space

Hi Team,

 

Could anyone please help me tuning the query, as I am getting an error as No more spool space for the user, even I have spool space of 8TB for the user.

Things which I have tried:

1. I am getting only required columns in select query

2. I have written where clause which fetching the table(select query)

 

even doing the above steps I get spool space issue, Could anyone plz help me in tunning the query provided below.

 

SELECT RAA.COLUMN1, RAA.COLUMN2,RAD.column1.... etc FROM (SELECT * FROM FP.TABLE_RAADDR WHERE FIN_PROD_CD = '1') RAA INNER JOIN (SELECT ACCT_STAT_CD,CURR_CR_LIM_AMT,OPN_TO_BUY_AMT,A_ID_VAL,B_ID,P_DT,P_FLG FROM FP.TABLE_RADTL WHERE P_FLG = 'D' AND P_DT = CURRENT_DATE - 1) RAD ON RAA.A_ID_VAL = RAD.A_ID_VAL AND RAA.SRC_B_ID = RAD.B_ID LEFT OUTER JOIN (SELECT SPCODE,CCODE FROM C1.SP WHERE (TRIM(CCODE) ='CAN' AND TRIM(SPCODE)<>'AS') OR TRIM(CCODE) ='USA') SP ON TRIM(RAA.ST_CD)=SP.SPCODE LEFT OUTER JOIN (SELECT SPCODE,CCODE FROM C1.SP WHERE (TRIM(CCODE) ='CAN' AND TRIM(SPCODE)<>'AS') OR TRIM(CCODE) ='USA') SHIPG ON TRIM(SHIPG_ST_CD)=SHIPG.SPCODE --US DCN LEFT OUTER JOIN (SELECT ACCT_NBR, P_FLG, P_DT, B_ID,DOMS_CUST_NBR,ACCT_STAT_TS_DT FROM FP.RADTL ) RAMS ON RAMS.ACCT_NBR = RAD.A_ID_VAL AND RAMS.P_FLG = RAD.P_FLG AND RAMS.P_DT = RAD.P_DT AND RAMS.B_ID = RAD.B_ID --CA DCN LEFT OUTER JOIN (SELECT LS_CNT_NBR_ID,S_CUS_ID,EFF_END_DT FROM FB.HCLCAN WHERE EFF_END_DT = '9999-12-31' ) HCL ON RAA.A_ID_VAL = HCL.LS_CNT_NBR_ID LEFT OUTER JOIN (SELECT S_CUS_ID,S_CUS_NBR,EFF_END_DT FROM FB.HCCAN WHERE EFF_END_DT = '9999-12-31')HCC ON HCL.S_CUS_ID = HCC.S_CUS_ID QUALIFY ROW_NUMBER() OVER (PARTITION BY RAA.A_ID_VAL ORDER BY RAMS.ACCT_STAT_TS_DT DESC, HCL.EFF_END_DT DESC) = 1;