15.00 - Tables - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Tables

A table is referred to in set theory terminology as a relation, from which the expression relational database is derived.

Every relational table consists of one row of column headings (more commonly referred to as column names) and zero or more unique rows of data values.

Formally speaking, each row represents what set theory calls a tuple. Each column represents what set theory calls an attribute.

The number of rows (or tuples) in a table is referred to as its cardinality and the number of columns (or attributes) is referred to as its degree or arity.

Defining Tables

Use the CREATE TABLE statement to define base tables. This SQL statement specifies a table name, one or more column names, and the attributes of each column. The CREATE TABLE statement can also specify data block size, percent free space, and other physical attributes of the table.

The CREATE/MODIFY USER and CREATE/MODIFY DATABASE statements provide options for creating permanent journal tables.

Defining Indexes for a Table

An index is a physical mechanism used to store and access the rows of a table. When you define a table, you can define a primary index and one or more secondary indexes.

If you define a table and you do not specify a PRIMARY INDEX clause, NO PRIMARY INDEX clause, PRIMARY KEY constraint, or UNIQUE constraint, the default behavior is for the system to create the table using the first column as the nonunique primary index. (If you prefer that the system creates a NoPI table without a primary index, use DBS Control to change the value of the PrimaryIndexDefault General field. For details, see Utilities: Volume 1 (A-K).)

For details on indexes, see “Indexes” on page 27. For details on NoPI tables, see “No Primary Index (NoPI) Tables” on page 22.

Duplicate Rows in Tables

Though both set theory and common sense prohibit duplicate rows in relational tables, the ANSI standard defines SQL based not on sets, but on bags, or multisets.

A table defined not to permit duplicate rows is called a SET table because its properties are based on set theory, where set is defined as an unordered group of unique elements with no duplicates.

A table defined to permit duplicate rows is called a MULTISET table because its properties are based on a multiset, or bag, model, where bag and multiset are defined as an unordered group of elements that may be duplicates.

 

For more information on …

See …

rules for duplicate rows in a table

CREATE TABLE in SQL Data Definition Language.

the result of an INSERT operation that would create a duplicate row

INSERT in SQL Data Manipulation Language.

the result of an INSERT using a SELECT subquery that would create a duplicate row

Temporary Tables

Temporary tables are useful for temporary storage of data. Teradata Database supports these types of temporary tables.

 

Type

Usage

Global temporary

A global temporary table has a persistent table definition that is stored in the Data Dictionary. Any number of sessions can materialize and populate their own local copies that are retained until session logoff.

Global temporary tables are useful for storing temporary, intermediate results from multiple queries into working tables that are frequently used by applications.

Global temporary tables are identical to ANSI global temporary tables.

Volatile

Like global temporary tables, the contents of volatile tables are only retained for the duration of a session. However, volatile tables do not have persistent definitions. To populate a volatile table, a session must first create the definition.

Global temporary trace

Global temporary trace tables are useful for debugging external routines (UDFs, UDMs, and external stored procedures). During execution, external routines can write trace output to columns in a global temporary trace table.

Like global temporary tables, global temporary trace tables have persistent definitions, but do not retain rows across sessions.

Materialized instances of a global temporary table share the following characteristics with volatile tables:

  • Private to the session that created them.
  • Contents cannot be shared by other sessions.
  • Optionally emptied at the end of each transaction using the ON COMMIT PRESERVE/DELETE rows option in the CREATE TABLE statement.
  • Activity optionally logged in the transient journal using the LOG/NO LOG option in the CREATE TABLE statement.
  • Dropped automatically when a session ends.
  • For details about the individual characteristics of global temporary and volatile tables, see “Global Temporary Tables” on page 14 and “Volatile Tables” on page 18.

    Global Temporary Tables

    Global temporary tables allow you to define a table template in the database schema, providing large savings for applications that require well known temporary table definitions.

    The definition for a global temporary table is persistent and stored in the Data Dictionary. Space usage is charged to login user temporary space. Each user session can materialize as many as 2000 global temporary tables at a time.

    How Global Temporary Tables Work

    To create the base definition for a global temporary table, use the CREATE TABLE statement and specify the keywords GLOBAL TEMPORARY to describe the table type. Although space usage for materialized global temporary tables is charged to temporary space, creating the global temporary table definition requires an adequate amount of permanent space.

    Once created, the table exists only as a definition. It has no rows and no physical instantiation.

    When any application in a session accesses a table with the same name as the defined base table, and the table has not already been materialized in that session, then that table is materialized as a real relation using the stored definition. Because that initial invocation is generally due to an INSERT statement, a temporary table—in the strictest sense—is usually populated immediately upon its materialization.

    There are only two occasions when an empty global temporary table is materialized:

  • A CREATE INDEX statement is issued on the table.
  • A COLLECT STATISTICS statement is issued on the table.
  • The following table summarizes this information.

     

    WHEN this statement is issued on a global temporary table that has not yet been materialized …

    THEN a local instance of the global temporary table is materialized and it is …

    INSERT

    populated with data upon its materialization.

    CREATE INDEX ON TEMPORARY

    COLLECT STATISTICS ON TEMPORARY

    not populated with data upon its materialization.

    Note: Issuing a SELECT, UPDATE, or DELETE on a global temporary table that is not materialized produces the same result as issuing a SELECT, UPDATE, or DELETE on an empty global temporary table that is materialized.

    Example  

    Suppose there are four sessions, Session 1, Session 2, Session 3, and Session 4 and two users, User_1 and User_2. Consider the scenario in the following two tables.

     

    Step

    Session

    Action

    Result

    1

    1

    The DBA creates a global temporary table definition in the database scheme named globdb.gt1 according to the following CREATE TABLE statement:

    CREATE GLOBAL TEMPORARY TABLE globdb.gt1,
    LOG
    (f1 INT NOT NULL PRIMARY KEY,
    f2 DATE,
    f3 FLOAT)
    ON COMMIT PRESERVE ROWS;

    The global temporary table definition is created and stored in the database schema.

    2

    1

    User_1 logs on an SQL session and references globdb.gt1 using the following INSERT statement:

    INSERT globdb.gt1 (1, 980101, 11.1);

    Session 1 creates a local instance of the global temporary table definition globdb.gt1. This is also referred to as a materialized temporary table.

    Immediately upon materialization, the table is populated with a single row having the following values.

    f1=1

    f2=980101

    f3=11.1

    This means that the contents of this local instance of the global temporary table definition is not empty when it is created.

    From this point on, any INSERT/DELETE/UPDATE statement that references globdb.gt1 in Session 1 maps to this local instance of the table.

    3

    2

    User_2 logs on an SQL session and issues the following SELECT statement.

    SELECT * FROM globdb.gt1;

    No rows are returned because Session 2 has not yet materialized a local instance of globdb.gt1.

    4

    2

    User_2 issues the following INSERT statement:

    INSERT globdb.gt1 (2, 980202, 22.2);

    Session 2 creates a local instance of the global temporary table definition globdb.gt1.

    The table is populated, immediately upon materialization, with a single row having the following values.

    f1=2

    f2=980202

    f3=22.2

    From this point on, any INSERT/DELETE/UPDATE statement that references globdb.gt1 in Session 2 maps to this local instance of the table.

    5

    2

    User_2 logs on again and issues the following SELECT statement:

    SELECT * FROM globdb.gt1;

    A single row containing the data (2, 980202, 22.2) is returned to the application.

    6

    1

    User_1 logs off from Session 1.

    The local instance of globdb.gt1 for Session 1 is dropped.

    7

    2

    User_2 logs off from Session 2.

    The local instance of globdb.gt1 for Session 2 is dropped.

    User_1 and User_2 continue their work, logging onto two additional sessions as described in the following table.

     

    Step

    Session

    Action

    Result

    1

    3

    User_1 logs on another SQL session 3 and issues the following SELECT statement:

    SELECT * FROM globdb.gt1;

    No rows are returned because Session 3 has not yet materialized a local instance of globdb.gt1.

    2

    3

    User_1 issues the following INSERT statement:

    INSERT globdb.gt1 (3, 980303, 33.3);

    Session 3 creates a local instance of the global temporary table definition globdb.gt1.

    The table is populated, immediately upon materialization, with a single row having the following values.

    f1=3

    f2=980303

    f3=33.3

    From this point on, any INSERT/DELETE/UPDATE statement that references globdb.gt1 in Session 3 maps to this local instance of the table.

    3

    3

    User_1 again issues the following SELECT statement:

    SELECT * FROM globdb.gt1;

    A single row containing the data (3, 980303, 33.3) is returned to the application.

    4

    4

    User_2 logs on Session 4 and issues the following CREATE INDEX statement:

    CREATE INDEX (f2) ON TEMPORARY globdb.gt1;

    An empty local global temporary table named globdb.gt1 is created for Session 4.

    This is one of only two cases in which a local instance of a global temporary table is materialized without data.

    The other would be a COLLECT STATISTICS statement—in this case, the following statement:

    COLLECT STATISTICS ON TEMPORARY globdb.gt1;

    5

    4

    User_2 issues the following SELECT statement:

    SELECT * FROM globdb.gt1;

    No rows are returned because the local instance of globdb.gt1 for Session 4 is empty.

    6

    4

    User_2 issues the following SHOW TABLE statement:

    SHOW TABLE globdb.gt1;
    CREATE SET GLOBAL TEMPORARY TABLE globdb.gt1, FALLBACK, LOG
    (
    f1 INTEGER NOT NULL,
    f2 DATE FORMAT 'YYYY-MM-DD',
    f3 FLOAT)
    UNIQUE PRIMARY INDEX (f1)
    ON COMMIT PRESERVE ROWS;

    7

    4

    User_2 issues the following SHOW TEMPORARY TABLE statement:

    SHOW TEMPORARY TABLE globdb.gt1;
    CREATE SET GLOBAL TEMPORARY TABLE globdb.gt1, FALLBACK, LOG
    (
    f1 INTEGER NOT NULL,
    f2 DATE FORMAT 'YYYY-MM-DD',
    f3 FLOAT)
    UNIQUE PRIMARY INDEX (f1)
    INDEX (f2)
    ON COMMIT PRESERVE ROWS;

    Note that this report indicates the new index f2 that has been created for the local instance of the temporary table.

    With the exception of a few options (see “CREATE TABLE” in SQL Data Definition Language for an explanation of the features not available for global temporary base tables), materialized temporary tables have the same properties as permanent tables.

    After a global temporary table definition is materialized in a session, all further references to the table are made to the materialized table. No additional copies of the base definition are materialized for the session. This global temporary table is defined for exclusive use by the session whose application materialized it.

    Materialized global temporary tables differ from permanent tables in the following ways:

  • They are always empty when first materialized.
  • Their contents cannot be shared by another session.
  • The contents can optionally be emptied at the end of each transaction.
  • The materialized table is dropped automatically at the end of each session.
  • Limitations

    You cannot use the following CREATE TABLE options for global temporary tables:

  • WITH DATA
  • Permanent journaling
  • Referential integrity constraints
  • This means that a temporary table cannot be the referencing or referenced table in a referential integrity constraint.

    References to global temporary tables are not permitted in FastLoad, MultiLoad, or FastExport.

    The Table Rebuild utility and the Archive/Recovery utility (with the exception of online archiving) operate on base global temporary tables only. Online archiving does not operate on temporary tables.

    Non-ANSI Extensions

    Transient journaling options on the global temporary table definition are permitted using the CREATE TABLE statement.

    You can modify the transient journaling and ON COMMIT options for base global temporary tables using the ALTER TABLE statement.

    Privileges Required

    To materialize a global temporary table, you must have the appropriate privilege on the base global temporary table or on the containing database or user as required by the statement that materializes the table.

    No access logging is performed on materialized global temporary tables, so no access log entries are generated.

    Volatile Tables

    Neither the definition nor the contents of a volatile table persist across a system restart. You must use CREATE TABLE with the VOLATILE keyword to create a new volatile table each time you start a session in which it is needed.

    What this means is that you can create volatile tables as you need them. Being able to create a table quickly provides you with the ability to build scratch tables whenever you need them. Any volatile tables you create are dropped automatically as soon as your session logs off.

    Volatile tables are always created in the login user space, regardless of the current default database setting. That is, the database name for the table is the login user name. Space usage is charged to login user spool space. Each user session can materialize as many as 1000 volatile tables at a time.

    Limitations

    The following CREATE TABLE options are not permitted for volatile tables:

  • Permanent journaling
  • Referential integrity constraints
  • This means that a volatile table cannot be the referencing or referenced table in a referential integrity constraint.

  • Check constraints
  • Compressed columns
  • DEFAULT clause
  • TITLE clause
  • Named indexes
  • References to volatile tables are not permitted in FastLoad or MultiLoad.

    For more information, see “CREATE TABLE” in SQL Data Definition Language.

    Non-ANSI Extensions

    Volatile tables are not defined in ANSI.

    Privileges Required

    To create a volatile table, you do not need any privileges.

    No access logging is performed on volatile tables, so no access log entries are generated.

    Volatile Table Maintenance Among Multiple Sessions

    Volatile tables are private to a session. This means that you can log on multiple sessions and create volatile tables with the same name in each session.

    However, at the time you create a volatile table, the name must be unique among all global and permanent temporary table names in the database that has the name of the login user.

    For example, suppose you log on two sessions, Session 1 and Session 2. Assume the default database name is your login user name. Consider the following scenario.

     

    Stage

    Session 1

    Session 2

    Result

    1

    Create a volatile table named VT1.

    Create a volatile table named VT1.

    Each session creates its own copy of volatile table VT1 using your login user name as the database.

    2

    Create a permanent table with an unqualified table name of VT2.

    Session 1 creates a permanent table named VT2 using your login user name as the database.

    3

    Create a volatile table named VT2.

    Session 2 receives a CREATE TABLE error, because there is already a permanent table with that name.

    4

    Create a volatile table named VT3.

    Session 1 creates a volatile table named VT3 using your login user name as the database.

    5

    Create a permanent table with an unqualified table name of VT3.

    Session 2 creates a permanent table named VT3 using your login user name as the database.

    Because a volatile table is known only to the session that creates it, a permanent table with the same name as the volatile table VT3 in Session 1 can be created as a permanent table in Session 2.

    6

    Insert into VT3.

    Session 1 references volatile table VT3.

    Note: Volatile tables take precedence over permanent tables in the same database in a session.

    Because Session 1 has a volatile table VT3, any reference to VT3 in Session 1 is mapped to the volatile table VT3 until it is dropped (see Step 10).

    On the other hand, in Session 2, references to VT3 remain mapped to the permanent table named VT3.

    7

    Create volatile table VT3.

    Session 2 receives a CREATE TABLE error for attempting to create the volatile table VT3 because of the existence of that permanent table.

    8

    Insert into VT3.

    Session 2 references permanent table VT3.

    9

    Drop VT3.

    Session 2 drops volatile table VT3.

    10

    Select from VT3.

    Session 1 references the permanent table VT3.

    Queue Tables

    Teradata Database supports queue tables, which are similar to ordinary base tables, with the additional unique property of behaving like an asynchronous first-in-first-out (FIFO) queue. Queue tables are useful for applications that want to submit queries that wait for data to be inserted into queue tables without polling.

    When you create a queue table, you must define a TIMESTAMP column with a default value of CURRENT_TIMESTAMP. The values in the column indicate the time the rows were inserted into the queue table, unless different, user-supplied values are inserted.

    You can then use a SELECT AND CONSUME statement, which operates like a FIFO pop:

  • Data is returned from the row with the oldest timestamp in the specified queue table.
  • The row is deleted from the queue table, guaranteeing that the row is processed only once.
  • If no rows are available, the transaction enters a delay state until one of the following occurs:

  • A row is inserted into the queue table.
  • The transaction aborts, either as a result of direct user intervention, such as the ABORT statement, or indirect user intervention, such as a DROP TABLE statement on the queue table.
  • To perform a peek on a queue table, use a SELECT statement.

    For details about creating a queue table, see “CREATE TABLE (Queue Table Form)” in SQL Data Definition Language. For details about the SELECT AND CONSUME statement, see SQL Data Manipulation Language.

    Error Logging Tables

    You can create an error logging table that you associate with a permanent base table when you want Teradata Database to log information about the following:

  • Insert errors that occur during an SQL INSERT … SELECT operation on the permanent base table
  • Update and insert errors that occur during an SQL MERGE operation on the permanent base table
  • To enable error logging for an INSERT … SELECT or MERGE statement, specify the LOGGING ERRORS option.

     

    IF a MERGE operation or INSERT … SELECT operation generates errors that …

    AND the request …

    THEN the error logging table contains …

    the error logging facilities can handle

     

    completes

  • an error row for each error that the operation generated.
  • a marker row that you can use to determine the number of error rows for the request.
  • The presence of the marker row means the request completed successfully.

    aborts and rolls back when referential integrity (RI) or unique secondary index (USI) violations are detected during index maintenance

  • an error row for each USI or RI violation that was detected.
  • an error row for each error that was detected prior to index maintenance.
  • The absence of a marker row in the error logging table means the request was aborted.

    reach the error limit specified by the LOGGING ERRORS option

    aborts and rolls back

    an error row for each error that the operation generated, including the error that caused the error limit to be reached.

    the error logging facilities cannot handle

    aborts and rolls back

    an error row for each error that the operation generated until the error that the error logging facilities could not handle.

    You can use the information in the error logging table to determine how to recover from the errors, such as which data rows to delete or correct and whether to rerun the request.

    For details on how to create an error logging table, see “CREATE ERROR TABLE” in SQL Data Definition Language. For details on how to specify error handling for INSERT … SELECT and MERGE statements, see SQL Data Manipulation Language.

    No Primary Index (NoPI) Tables

    For better performance when bulk loading data using Teradata FastLoad or through SQL sessions (in particular, using the INSERT statement from TPump with the ArraySupport option enabled), you can create a No Primary Index (NoPI) table to use as a staging table to load your data. Without a primary index (PI), the system can store rows on any AMP that is desired, appending the rows to the end of the table.

    By avoiding the data redistribution normally associated with loading data into staging tables that have a PI, NoPI tables provide a performance benefit to applications that load data into a staging table, transform or standardize the data, and then store the converted data into another staging table.

    Applications can also benefit by using NoPI tables in the following ways:

  • As a log file
  • As a sandbox table to store data until an appropriate indexing method is determined
  • A query that accesses the data in a NoPI table results in a full-table scan unless you define a secondary index on the NoPI table and use the columns that are indexed in the query.

     

    For more information on …

    See …

    creating a NoPI table

    the NO PRIMARY INDEX clause for the CREATE TABLE statement in SQL Data Definition Language.

    loading data into staging tables from FastLoad

    Teradata FastLoad Reference.

    loading data into staging tables from TPump

    Teradata Parallel Data Pump Reference.

    primary indexes

    “Primary Indexes” on page 33.

    secondary indexes

    “Secondary Indexes” on page 36.

    Temporal Tables

    Temporal tables store and maintain information with respect to time.

    For more information about temporal tables, see Temporal Table Support and ANSI Temporal Table Support.