Please tell why this issue is happening that I am getting error as NO MORE SPOOL SPACE when I try to run the SQL from table (UDW2BASE.PROV_NPI_TXNMY) and when I create a volatile table of the same then it is running successfully.
I am unable to figure out the cause for “No more spool space”.
But on running the above SQL in UDWTEST, it gives error as “No more Spool Space.”
I tried to run the above SQL in Unix (in BTEQ env.) too using On-demand thinking spool space might be limited to me, but then also job got aborted with error as spool space issue.
Then, I tried to create a volatile table, to insert the records from (UDW2BASE.PROV_NPI_TXNMY) to a temporary table and then executed the SQL code by changing the name of the BASE table (UDW2BASE.PROV_NPI_TXNMY) to that of volatile table. Then, the SQL ran successfully and it fetched the results correctly.
Please suggest as why there isnt spool issue with volatile tables and there is issue with actual table, though skew factor is 0.90 for the table.
Spool Space will be allocated to each User. The actual query what we intended to do might have more I/O operations involved. Since you have Spilted the query with more than 2 to 3 steps. The I/O Operations would have been less.
Thanks & Regards,
This also might be happening at times due to stale stats on the tables. Please try collecting stats on the PI and the Join columns on the tables in the original query. As Adarsh mentioned splitting a complex single step into multiple simple steps would limit the usage of spool to the query specific spool. if you have a profile mentioned for the user, the users sharing the profile running any queries will use the spool allocated when ran concurrently.
Execute "Diagnostic helpstats on for session; " command and explain your SQL query to check whether any Low confidence statements exists. collect stats for all those columns and combinations shown up in explain plan.