15.00 - 15.10 - Table Types - Teradata Database

Teradata Database Introduction to Teradata

Product
Teradata Database
Release Number
15.00
15.10
Content Type
User Guide
Publication ID
B035-1091-151K
Language
English (United States)
Last Update
2018-09-25
 

Table Type

Description

ANSI Temporal

ANSI-compliant support for temporal tables. Using temporal tables, Teradata Database can process statements and queries that include time-based reasoning. Temporal tables record both system time (the time period when the information was recorded in the database) and valid time (the time period when the information is in effect or true in a real-world application).

Derived

A derived table:

  • Is a type of temporary table obtained from one or more other tables as the result of a subquery.
  • Is specified in an SQL SELECT statement.
  • Avoids the need to use the CREATE and DROP TABLE statements for storing retrieved information.
  • Is useful when you are coding more sophisticated, complex queries.
  • Error Logging

    Error logging tables:

  • Store information about errors on an associated permanent table.
  • Log information about insert and update errors.
  • Global Temporary

    Global temporary tables:

  • Are private to the session.
  • Are dropped automatically at the end of a session.
  • Have a persistent table definition stored in the Data Dictionary. The saved definition may be shared by multiple users and sessions with each session getting its own instance of the table.
  • Global Temporary Trace

    Global temporary trace tables:

  • Store trace output for the length of the session.
  • Have a persistent table definition stored in the Data Dictionary.
  • Are useful for debugging SQL stored procedures (via a call to an external stored procedure written to the trace output) and external routines (UDFs, UDMs, and external stored procedures).
  • NoPI

    NoPI tables are permanent tables that do not have primary indexes defined on them.

    They provide a performance advantage when used as staging tables to load data from FastLoad or TPump Array INSERT.

    They can have secondary indexes defined on them to avoid full-table scans during row access.

    Permanent

    Permanent tables allow different sessions and users to share table content.

    Queue

    Queue tables:

  • Are permanent tables with a timestamp column. The timestamp indicates when each row was inserted into the table.
  • Establish first-in first-out (FIFO) ordering of table contents, which is needed for customer applications requiring event processing.
  • Volatile

    Volatile tables are used when:

  • Only one session needs the table.
  • Only the creator needs to access the table.
  • You want better performance than a global temporary table.
  • You do not need the table definition after the session ends.
  • Note: The definition of a volatile table can survive across a system restart if it is contained in a macro.

    For more information about table types, see SQL Data Definition Language and Database Design.