No More Spool Space Issues

Database

No More Spool Space Issues

Hi All,

I need urgent help in resolving the SPOOL Space Error Issues while Deleting the data from a Table having billions of records.

The query is something like this:

 DEL FROM TARGET_TBL

WHERE S_COL IN 

( SEL S_COL 

  FROM XYZ_TBL A

  INNER JOIN PQR_TBL B 

ON A.COMM_ID=B.COMM_ID

WHERE B.NAME='KSFDJHSAKJDFH'

);

Here , The Tables are having millions of records. If we simply select and execute the subquery it will give some billions of records easily without any spool space error however when we run the Complete query its failing with the Spool Error.

The Stats are collected on all the Columns  used in the query.

The Target_tbl is defined as Multiset NUPI.

XYZ_tbl is defined as Multiset NUPI.

PQR_TBL is defined as SET NUPI.

I am not sure when we are able to get the results of  the subquery very easily then why we are getting spool while deletion of data.

The SEG_COL FROM TARGET_TBL is not the part of index.

I have tried to delete the data for single date too but its giving a spool space error as that also contains Millions of Records.

DEL FROM TARGET_TBL

WHERE S_COL IN 

( SEL S_COL 

  FROM XYZ_TBL A

  INNER JOIN PQR_TBL B 

ON A.COMM_ID=B.COMM_ID

WHERE B.NAME='KSFDJHSAKJDFH'

)AND DATE=CURRENT_DATE-1

;


This is also failing with the Spool Error.

Please help what can be done for this Issue? Any help would be really appreciable.

3 REPLIES

Re: No More Spool Space Issues

Can any experts provide help on this issue??

Enthusiast

Re: No More Spool Space Issues

Can you try removing the where condition and add it as a AND in the join.

SEL S_COL 

  FROM XYZ_TBL A

  INNER JOIN PQR_TBL B 

ON A.COMM_ID=B.COMM_ID

AND B.NAME='KSFDJHSAKJDFH'

Cheers,

Mani

Senior Apprentice

Re: No More Spool Space Issues

What's the Explain and the actual resource usage from QryLogStepsV?

The Tables are having millions of records. If we simply select and execute the subquery it will give some billions of records 

If you got millions before the join, but billions after it, you do something wrong. From which table is S_COL?

Which statistics exist?