Please tune this quey as it is taking terabytes spool space

Database
Enthusiast

Please tune this quey as it is taking terabytes spool space

Hi All,

I have a query which was working fine till the time we were having small volume data in table A. It started taking terabytes of spool space when there was Million rows in Table A. Can someone please look in to this and tune it or let me know how can we modify this:

UPDATE Z                                                      

FROM                                                          

    Z,                         

(SELECT DISTINCT A.CL1,(SELECT COUNT(*)                 

    FROM                                                      

          B,                  

          C,                  

          D                   

WHERE                                                         

       B.CL2        = C.CL2         

   AND B.CL3     = C.CL3  

   AND B.CL4     = C.CL4    

   AND B.CL1     = C.CL1              

   AND B.CL5     = C.CL1          

   AND B.CL6     = C.CL6            

   AND B.CL7     = C.CL7

   AND B.CL8     = C.CL8      

   AND B.CL2     = D.CL2         

   AND B.CL3     = D.CL3  

   AND B.CL4     = D.CL4    

   AND B.CL1     = D.CL1              

   AND B.CL5     = D.CL5          

   AND B.CL6     = D.CL6            

   AND B.CL7     = D.CL7

   AND B.CL8     = D.CL8      

   AND (C.CL9 = ' '                                   

   AND C.CL10 IS NULL                                  

   AND C.CL11 IS NULL                             

   AND C.CL12 = '  ')                             

   AND A.CL1 = B.CL1)                             

   FROM A) TBL(TID,CNT)             

   SET STS_CD    =                                   

       (CASE WHEN TBL.CNT = 0                                 

             THEN '99'                                        

             ELSE '00'                                        

             END)                                             

   WHERE                                                      

       Z.STS_CD   = '88'   

   AND Z.CL1         = TBL.TID

Thanks.

2 REPLIES
Enthusiast

Re: Please tune this quey as it is taking terabytes spool space

Here Table A and Table B are the same table.

The requirement was:

Update Status in Z if data from A loaded to C and D. To check that data from A loaded to C and D we are matching the column values between A and C,D.Table A and Z have one column in common depending upon which we are trying to update the status in Z.

Thanks

Enthusiast

Re: Please tune this quey as it is taking terabytes spool space

Check in the step table (dbqlsteptbl) which step is creating huge spool file. There might be skew or some duplication might be happening.