15.10 - Relation - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

Describes all table and spool files in the access plan for the captured query.

The following CREATE TABLE request defines the Relation table.

   CREATE TABLE Relation( 
     RelationKey            INTEGER NOT NULL,
     QueryID                INTEGER NOT NULL,
     UDB_Key                INTEGER NOT NULL,
     Name                   VARCHAR128 CHARACTER SET UNICODE 
                            NOT CASESPECIFIC NOT NULL,
     RelationID             INTEGER NOT NULL,
     RelationKind           CHARACTER(1) CHARACTER SET LATIN
                            NOT CASESPECIFIC NOT NULL,
     SortInfo               CHARACTER(1) CHARACTER SET LATIN
                            NOT CASESPECIFIC,
     SortKind               CHARACTER(3) CHARACTER SET LATIN
                            NOT CASESPECIFIC,
     SortKey                VARCHAR(1024) CHARACTER SET UNICODE
                            NOT CASESPECIFIC,
     GeogInfo               CHARACTER(1) CHARACTER SET LATIN
                            NOT CASESPECIFIC,
     Cached                 CHARACTER(1) CHARACTER SET LATIN
                            NOT CASESPECIFIC, NOT NULL,
     SyncScan               CHARACTER(1) CHARACTER SET LATIN
                            NOT CASESPECIFIC NOT NULL,
     Cardinality            FLOAT,
     Confidence             CHARACTER(1) CHARACTER SET LATIN 
                            NOT CASESPECIFIC,
     MaxCardinality         FLOAT,
     ViewName               VARCHAR128 CHARACTER SET UNICODE 
                            UPPERCASE NOT CASESPECIFIC,
     TableDDL               VARCHAR(20000) CHARACTER SET UNICODE 
                            NOT CASESPECIFIC,
     TableName              VARCHAR128 CHARACTER SET UNICODE 
                            NOT CASESPECIFIC,
     PartitionInfo          CHARACTER(1) CHARACTER SET LATIN
                            NOT CASESPECIFIC NOT NULL,
     Overflow               CHARACTER(1) CHARACTER SET LATIN
                            NOT CASESPECIFIC,
     Complete               CHARACTER(1) CHARACTER SET LATIN,
                            NOT CASESPECIFIC,
     Version                SMALLINT,
     SpoolCompressedAllowed CHARACTER(1) CHARACTER SET LATIN
                            NOT CASESPECIFIC,
     SpoolSize              FLOAT,
     RelationAttributeType  CHARACTER(10) CHARACTER SET LATIN
                            NOT CASESPECIFIC
     RelationAttributeValue VARCHAR(100) CHARACTER SET LATIN
                            NOT CASESPECIFIC
     TemporalProperty       CHARACTER(1) CHARACTER SET LATIN 
                            NOT CASESPECIFIC NOT NULL,
     ResultTemporalProperty CHARACTER(1) CHARACTER SET LATIN
                            NOT CASESPECIFIC NOT NULL,
     NumCombinedPartitions  BIGINT,
     NumContexts            INTEGER,
     NumCPReferences        INTEGER)
   PRIMARY INDEX (QueryID),
   UNIQUE INDEX (RelationKey);

The following table defines the Relation table attributes.

 

Attribute

Definition

RelationKey

  • Unique identifier for the relation within its database.
  • USI for the Relation table.
  • QueryID

  • Unique identifier for the query generated by the system when the query plan is captured.
  • NUPI for the Relation table.
  • UDB_Key

    Identifier for the user or database containing the relation described by this row.

    Name

    One of the following:

  • Alias name of the table.
  • The word SPOOL.
  • The word ROWIDSPOOL.
  • RelationID

    Unique identifier for the relation or spool file within the database.

    RelationKind

    Distinguishes among derived tables, global temporary tables, hash indexes, join indexes, permanent tables, volatile tables, and spool files.

  • D is a derived table.
  • The Name attribute contains the name of the derived table.

  • G is a global temporary table.
  • H is a hash index.
  • J is a join index.
  • O is a NoPI table.
  • P is a permanent table.
  • This does not differentiate between ordinary permanent tables and queue tables.

    To make this discrimination, you can join Relation with DBC.TVM or with the DBC.Tables view, where TVM.QueueFlag=T or Tables.QueueFlag=T.

    T is the DBC.TVM.QueueFlag value that indicates a queue table.

  • S is a spool file.
  • V is a volatile table.
  • SortInfo

    Describes whether the relation is sorted or not.

  • If F, the relation is not sorted.
  • If T, the relation is sorted.
  • SortKind

    The way the relation is sorted.

    Only used when the value for SortInfo is T.

  • F1S is a Field1 sort.
  • F1U is a Field1 unique sort.
  • FHS is a Field1 Hash sort.
  • FID is a FieldID sort.
  • FHU is a Field1 Hash unique sort.
  • HN1 is a Field1 Hash min1 sort.
  • HN2 is a Field1 Hash min2 sort.
  • HX1 is a Field1 Hash max1 sort.
  • HX2 is a Field1 Hash max2 sort.
  • JIS is a JoinIndex sort.
  • MN1 is a Field1 min1 sort.
  • MN2 is a Field1 min2 sort.
  • MX1 is a Field1 max1 sort.
  • MX, is a Field1 max2 sort.
  • RF1 is a Rowhash field1 sort.
  • RHR is a RowHashRow sort.
  • RHS is a Rowhash sort.
  • UF1 is a Unique field1 sort.
  • UNK is an Unknown sort kind.
  • URS is a Unique rowID sort.
  • SortKey

    A list of sort information strings composed of the concatenated database, table, and column names that make up the sort key. Only the first 1,024 characters are captured: any remaining characters are truncated.

    The format for the SortKey list is the following:

      SortKey1, SortKey2, ..., SortKeyn

    There must be a SPACE character following each COMMA character in the list.

    The format for the individual SortKey strings is one of the following:

  • database_name.table_name.column_name
  • spool_number.column_name
  • Spool numbers are used in place of database.table names whenever the table information is not available.

    GeogInfo

    Describes the configuration geography of the relation.

  • D means the relation is duplicated on all AMPs.
  • H means the relation is hash‑distributed across the AMPs.
  • L means the relation is built locally.
  • Cached

    Describes whether the relation is cached or not.

  • If F, the relation is not cached.
  • If T, the relation is cached.
  • SyncScan

    Describes whether a relation is eligible for synchronized scanning or not.

  • If F, the relation is not eligible for synchronized scanning.
  • If T, the relation is eligible for synchronized scanning.
  • Cardinality

    Estimated cardinality of the relation.

    Confidence

    Describes the confidence level for the estimated cardinality.

  • H is High confidence.
  • I is Index Join confidence.
  • L is Low confidence.
  • N is No confidence.
  • MaxCardinality

    Estimated maximum cardinality of the relation.

    ViewName

    Name of a view, if any, used by the query to access the relation.

    TableDDL

    The SQL DDL text for the captured relation.

    If the text exceeds the upper limit of 20,000 characters, then it overflows to the QryRelX table. See “Overflow” on page 805 and “QryRelX” on page 785.

    TableName

    The non‑aliased name of the relation.

    Compare with “Name” on page 802.

    PartitionInfo

    Identifies whether a table or spool has a row‑partitioned primary index.

  • If F, the relation does not have a row‑partitioned primary index.
  • If T, the relation has a row‑partitioned primary index.
  • Overflow

    Specifies whether there is overflow query text stored in QryRelX or not.

  • If F, there is no overflow.
  • This is the default.

  • If T, any overflow text is stored in a QryRelX table.
  • Complete

    Identifies whether Relation stores the complete relation DDL or a truncated version.

  • If F, table DDL is truncated.
  • The upper boundary on the number of characters stored is controlled by the LIMIT clause of the DUMP EXPLAIN and INSERT EXPLAIN statements (see SQL Data Manipulation Language).

    If no limit is specified by DUMP EXPLAIN or INSERT EXPLAIN, then full DDL is captured and stored.

  • If T, full table DDL is stored.
  • If there is overflow (indicated when the value for the Overflow attribute is T), then it is stored in the QryRelX table (see “QryRelX” on page 785).

    Version

    Stores the version number of the table at the time the plan was captured.

    Used to ensure that any changes to the schema information captured for analysis are handled correctly.

    SpoolCompressedAllowed

    Identifies whether target spool files can contain compressed columns or not.

  • If F, the spool cannot contain compressed columns.
  • If T, the spool can contain compressed columns.
  • SpoolSize

    The size of the spool file for this relation in bytes.

    If there is no spool for the request, the column is null.

    RelationAttributeType

    Indicates that at least one row partition of a partitioned table was accessed by a query.

    Each time a row is written to Relation for a row‑partitioned table, Teradata Database writes the string NUMOFPART to RelationAttributeType.

    If Teradata Database does not access any row partitions for a query, RelationAttributeType is null.

    The function of this column is analogous to the function of QCD.QuerySteps.StepAttributeType.

    RelationAttributeValue

    The number of row partitions accessed by a query when that number is greater than zero.

    If Teradata Database does not access any row partitions for a query, RelationAttributeValue is null.

    The function of this column is analogous to the function of QCD.QuerySteps.StepAttributeValue.

    TemporalProperty

    The temporal attribute for the relation.

  • B is a Bitemporal relation.
  • N is not a temporal relation.
  • S is a system-versioned system-time table.
  • T is a Transaction Time relation.
  • U is a bitemporal (system-versioned system-time and valid-time) table.
  • V is a Valid Time relation.
  • W is a nontemporal table that contains a system-time derived period column but that is not system versioned.
  • X is a valid-time temporal table that contains a system-time derived period column but that is not system versioned.
  • ResultTemporalProperty

    The temporal attribute for the result relation.

  • B is a Bitemporal relation.
  • N is not a temporal relation.
  • T is a Transaction Time relation.
  • V is a Valid Time relation.
  • NumCombinedPartitions

    If there is static partition elimination for the relation or if the relation has column partitions, NumCombinedPartitions contains the number of combined partitions that were accessed, not the number of combined partitions that were eliminated.

    Otherwise, the column is null.

    NumContexts

    The number of contexts allocated, if any, to simultaneously access the partitions of a relation that has partitioning.

    Otherwise, the column is null.

    NumCPReferences

    The number of column partitions referenced in a column‑partitioned relation.

    Otherwise, the column is null.