Tables - Analytics Database - Teradata Vantage

Database Introduction

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-09-27
dita:mapPath
gtm1628096154303.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
dsm1472253642401
lifecycle
latest
Product Category
Teradata Vantage™

Tables are two-dimensional objects with rows and columns. Data is organized in table format and presented to the users of a relational database. The following table describes basic table types:

Table Type Description
Analytical Result Table (ART)
  • Result of Unbounded Array Framework (UAF) functions.
  • Permanent or temporary table that can be used as input for other UAF functions.
  • Has no primary index (NoPI).
  • Can have multiple layers, each with its own row composition.
  • You can assign a name to the table and to some columns, but Teradata software sets other table attributes.
  • You cannot manipulate ART tables (for example, insert, delete, or update rows).
ANSI Temporal
  • ANSI-compliant.
  • Analytics Database can use it to process statements and queries that include time-based reasoning.
  • Records system time (when information was recorded in database) and valid time (when information is in effect or true in real-world application).
Derived
  • Temporary table obtained from one or more other tables as the result of a subquery.
  • Specified in an SQL SELECT statement.
  • Avoids need to use CREATE and DROP TABLE statements for storing retrieved information.
  • Useful when coding complex queries.
Error Logging
  • Stores information about errors on an associated permanent table.
  • Logs information about insert and update errors.
Global Temporary
  • Private to the session.
  • Dropped automatically at end of session.
  • Has persistent table definition stored in Data Dictionary.

    Multiple users and sessions may share saved definition. Each session has its own instance of the table.

Global Temporary Trace
  • Stores trace output for the length of session.
  • Has persistent table definition stored in Data Dictionary.
  • Useful for debugging SQL stored procedures (by calling external stored procedure written to trace output) and external routines (UDFs, UDMs, and external stored procedures).
NoPI
  • Permanent table without defined primary index.
  • Has performance advantage when used as staging table to load data from FastLoad or TPump Array INSERT.
  • Can have defined secondary indexes to avoid full-table scans during row access.
Permanent
  • Enables different sessions and users to share table content.
Queue
  • Permanent table with timestamp column showing when each row was inserted.
  • Establishes first-in first-out (FIFO) ordering of table contents, needed for customer applications that process events.
Volatile Used in the following situations:
  • 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.
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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - Database Design, B035-1094.