Teradata - Permanent Space VS Temporary Space VS Spool Space

Teradata space usage:

----------------------

3 kinds of Space(s) in Teradata database:

1. Permanent Space

2. Spool Space

3. Temporary Space

Example: create user xyz perm = 1e4, spool = 1e3, temporary = 1e3, password = xyz;

-> Permanent space is used for database object (tables, indexes etc) creation and to hold their data.

-> Spool space is used to hold data for Intermediate Query results.

Holds only data.

Active upto the current session only.

Spool space is same as that of the root, unless specified.

-> Temporary Space is used to store Global temporary tables data.

Data is active upto the current session only.

5 kinds of Tables in Teradata database:

1. SET Table

2. Multiset Table

3. Global Temporary Table (GTT)

4. Temporary Table

5. Volatile Temporary Table

Create tables go into data dictionary (Permament Space)

Tables

------

SET table:

----------

1. Table defintion : In data dictionary (permanent space)

2. Data storage : Permanent space

3. After Database restart: Both table and data survives

4. Example : Can directly create with 'create set table ….'

5. Collect stats : Yes

Mutliset table:

---------------

1. Table defintion : In data dictionary (permanent space)

2. Data storage : Permanent space

3. After Database restart: Both table and data survives

4. Example : Can directly create with 'create multiset table ….'

5. Collect stats : Yes

Global Temporary Table(GTT):

----------------------------

1. Table defintion : In data dictionary (permanent space)

2. Data storage : Temporary space

3. After Database restart: Table definition survives

4. Example : Can directly create with 'create global temporary table ….'

5. Collect stats : Yes

Temporary Table:

----------------------------

1. Table defintion : No table definition

2. Data storage : Spool

3. After Database restart: Nothing survives

4. Example : Indirectly ( derived tables )

5. Collect stats : No

Volatile Table:

----------------------------

1. Table defintion : System Cache (primary memory - RAM)

2. Data storage : Spool

3. After Database restart: Nothing survives

4. Example : Indirectly ( derived tables )

5. Collect stats : No