Teradata Space Concepts
Teradata is designed to reduce the DBA's administrative functions when it comes to space management. Space concepts are configured in the following ways in the Teradata system.
Permanent Space (Perm Space)
Permanent space is the maximum amount of space available for the user and database to hold data rows. Permanent tables, journals, fallback tables, and secondary index sub-tables use permanent space.
Permanent space is not pre-allocated for the database and user. The amount of permanent space is divided by the number of AMPs. Whenever per AMP limit exceeds, an error message is generated.
Equal distribution is necessary because there is a high percentage that the objects will be shared across all the AMPs, and at the time of data retrieval, all AMPs will work parallel to fetch the data.
Unlike other relational databases, the Teradata database does not physically define the Perm space at the time of object creation. Instead of that, it represents the upper limit for the Perm space, and then Perm space is used dynamically by the objects.
Suppose we have a Teradata system having 500 GB Perm space. Initially, DBC owns 100% of the perm space of a new system. As in Teradata, everything is parallel. Perm space will be distributed evenly across all AMPs.
Teradata system having 500GB perm space and 5 AMPs, each AMP will get 100GB of perm space to execute a query in parallel if DBC creates two users as sysdba and appdba with 20GB and 100GB perm space respectively.
Space will be taken from parent i.e., DBC. So now DBC has 280GB of perm space. This mechanism ensures enough memory to execute all processes in the Teradata system.
Spool space is the amount of space on the system that has not been allocated. It is used by the system to keep the intermediate results of the SQL query. Users without spool space cannot execute any query.
When executing a conditional query, all the qualifying rows which satisfy the given condition will be store in the Spool space for further processing by the query. Any Perm space currently unassigned is available as a Spool space.
Spool space defines the maximum amount of space the user can use. Establishing a Spool space limit is not required when Users and Databases are created. But it is highly recommended to define the upper limit of Spool space for any object.
Suppose a 10GB permanent space has been allocated to a database. However, the actual perm space occupied by tables took up 50% of perm space i.e., 5GB.
The remaining 5GB space is available system-wide for a spool. Spool space for the child will not be subtracted from its immediate parent. And the child database spool space can be equal as its parent. Spool space for sysdba and appdba is the same as DBC.
Temp space is the unused permanent space that is used by Global Temporary tables. Temp space is also divided by the number of AMPs.
Unused Perm space is used as Temp space for storing the table and the data in it. If we are specifying some value for Temp space, it should not exceed the value of the parent database and user.
If we do not specify a value, then the maximum value is inherited from that of the parent.