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 |
|
|||
QueryID |
|
|||
UDB_Key |
Identifier for the user or database containing the relation described by this row. |
|||
Name |
One of the following: |
|||
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. The Name attribute contains the name of the derived 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. |
|||
SortInfo |
Describes whether the relation is sorted or not. |
|||
SortKind |
The way the relation is sorted. Only used when the value for SortInfo is T. |
|||
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. |
|||
Cached |
Describes whether the relation is cached or not. |
|||
SyncScan |
Describes whether a relation is eligible for synchronized scanning or not. |
|||
Cardinality |
Estimated cardinality of the relation. |
|||
Confidence |
Describes the confidence level for the estimated cardinality. |
|||
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. |
|||
Overflow |
Specifies whether there is overflow query text stored in QryRelX or not. This is the default. |
|||
Complete |
Identifies whether Relation stores the complete relation DDL or a truncated version. 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 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. |
|||
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. |
|||
ResultTemporalProperty |
The temporal attribute for the result 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. |