Relation - Analytics Database - Teradata Vantage

SQL Request and Transaction Processing
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
2024-05-02
dita:mapPath
zfm1628111633230.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
evd1472255317510
lifecycle
latest
Product Category
Teradata Vantage™

Function of Relation

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

Relation Table Definition

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);

Attribute Definitions for Relation

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.
  • A means the relation is duplicated on one AMP (AllRowsOneAMP).
  • 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.

Text that exceeds the upper limit of 20,000 characters overflows to the QryRelX table. See the Overflow attribute in this table and QryRelX.
TableName The non-aliased name of the relation.

Compare with the Name attribute in this table.
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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146).

    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.

    Any overflow (indicated when the value for the Overflow attribute is T), then is stored in the QryRelX table (see QryRelX).
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, Vantage writes the string NUMOFPART to RelationAttributeType.

If Vantage 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 Vantage 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.