i have created a temp table ABC, want to insert records in it. it is inserting only 80000 records but while selecting the insert 120000 records r getting selected. is it because of space allocation?
Simple Way Of Putting....
Spool & Temp Both Are UnUsed Perm Space Throughout The System.
Spool Table & Temp Table have Different Features.
Spool Table Occupy Spool Space, So If The Concern For Someone Is To Reduce The Spool Consumption, One Can Create Temp Table, Where The Data Is Stored In The Temporary Space, Thereby Allowing Valuable Spool Space To Be Used For Other Operations.
Also, Persistent Image Is Stored In DBC Metadata Tables For Temp Tables & They Also Survive Restart & Newer Teradata Release Allows Stats Collections Also...All These Features Are NOT Available For Spool Table/Volatile Table.
Could you please explain how the below one works?
System has 100GB space. 10GB has been allocated to user and 20GB allocated to user1. How spool is availabe?
If you allocate 10GB of perm space to the user that means they can use up to 10 GB of space for storing data on your system .
Spool is different, it does not pre-allocate space for the user. It is a thresholdlimit that the user is allowed to use if there is available free space on the system. Available space comes from unused perm and is temporary in nature. That means that the spool is only used for the query that is running and then released as it is no longer needed.
If you have a lot of users running queries with the same spool limit , they could use up all the available spool and your current user may not be able to get the 20GB requested, possibly causing the job to fail.
This makes it important to understand what the users will be running when you allocate spool to them.