Union All & Spool Space Issue

Database
New Member

Union All & Spool Space Issue

Hi Guys,

 

I have a view, which has structure like:
Create View VIEW1 AS

Select Column1, Column2.. FROM TABLE1 T1  T JOIN TABLE2  T2 ON T1.ID=T2.ID JOIN TABLE3 T3 ON T1.ID2=T3.ID
UNION ALL
Select Column1, Column2.. FROM TABLE1 T1  T JOIN TABLE2  T2 ON T1.ID=T2.ID JOIN TABLE3 T3 ON T1.ID2=T3.ID


Individually if i execute each Select query, am getting the result.
First select would return around 30
,000,000 records, where as second select would return around

90,000,000 records in my Prod environment.

 


However, when executed as a view (select * from view1 sample 500), it would fail with No Spool Space error.


Is there a way to overcome this and get output as single result set ?

DB Version: 14.10

Thanks in Advance.

2 REPLIES
Supporter

Re: Union All & Spool Space Issue

The question is if you have enough spools space to hold the 120.000.000 rows?

If yes: Is the data skewed?

Can you share the explain?

Re: Union All & Spool Space Issue

Check your spool space allocated to you. As your record is huge after joins and query may be exceeding you spool space. Also try to jon in one sequel or tbl

 

Query:-
select * from dbc.users where username ='XYZ';