15.00 - Classes of Tables - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

Classes of Tables

The Teradata Database differentiates among the following five classes of tables. You can specify a table classification when defining the SCOPE parameters of an action.

 

Table Type

Description

Permanent Journal (PJ) Tables

Tables that can survive system restarts, but do not contain user-visible data.

Each PJ table contains data generated internally by the Teradata Database. The PJ data is usually used to restore the journaled tables to a given checkpointed state by rolling transactions forward or backwards from an archived copy of the tables.

Permanent Tables

Tables containing the real data, which can survive system restarts.

Temporary Tables

Tables that can exist as either global or volatile temporary tables as defined below:

  • Global temporary tables exist only during the duration of the SQL session in which they are used.
  • Volatile temporary tables reside in memory and do not survive a system restart. They are treated like spool tables, discussed below.
  • Spool Tables

    Tables that contain non-permanent data and can be divided into classes according to their scope of persistence.

    Intermediate result spool tables hold temporary results during the processing of a single SQL query and persist only for the duration of that processing. Response spool tables hold the final answer set from a query and a limited number can optionally persist across further queries in the same session. Spool tables can be discarded as follows:

  • Normally, when they are no longer needed.
  • As part of a specific resource cleanup on a transaction abort or session logoff.
  • As part of a general resource cleanup every time the system restarts.
  • Rows for volatile tables are placed in spool spaces and are discarded at the end of a transaction or at the end of a session (depending on a table option or by a DROP TABLE statement).
  • Volatile table definitions reside in memory and do not survive a system restart.
  • Persist ant Spool Tables

    These are treated like spool tables, but they survive system restarts.

    The attributes associated with each class of tables can affect system performance, since the attributes are set individually, and each class of tables is used for a different purpose.

    For example, you might want to pack only Permanent and PJ tables. Therefore, you would specify these tables when defining the SCOPE of the PACKDISK command.