15.10 - Relation - Teradata Database

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.