Query - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
ykx1561500561173.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1142
lifecycle
previous
Product Category
Teradata Vantage™

Function of Query

Describes information about captured queries.

Query Table Definition

The following CREATE TABLE request defines the Query table:

   CREATE TABLE Query(
     QueryID                   INTEGER NOT NULL,
     UDB_Key                   INTEGER NOT NULL,
     MachName                  VARCHAR(30) CHARACTER SET UNICODE
                               NOT CASESPECIFIC NOT NULL,
     NumAMPs                   INTEGER NOT NULL,
     NumPEs                    INTEGER NOT NULL,
     NumNodes                  INTEGER NOT NULL,
     ReleaseInfo               VARCHAR(256) CHARACTER SET UNICODE
                               NOT CASESPECIFIC NOT NULL,
     VersionInfo               VARCHAR(256) CHARACTER SET UNICODE
                               NOT CASESPECIFIC NOT NULL,
     PENum                     INTEGER NOT NULL,
     QueryText                 VARCHAR(20000) CHARACTER SET UNICODE
                               NOT CASESPECIFIC,
     CaptureTimeStamp          TIMESTAMP(0) NOT NULL,
     QueryName                 VARCHAR128 CHARACTER SET UNICODE
                               NOT CASESPECIFIC,
     Frequency                 INTEGER NOT NULL,
     StatementTypes            VARCHAR(120) CHARACTER SET LATIN
                               NOT CASESPECIFIC NOT NULL,
     DefaultDBName             VARCHAR128 CHARACTER SET UNICODE
                               NOT CASESPECIFIC NOT NULL,
     Overflow                  CHARACTER(1) CHARACTER SET LATIN,
                               NOT CASESPECIFIC,
     Complete                  CHARACTER(1) CHARACTER SET LATIN,
                               NOT CASESPECIFIC,
     ValidatedPlan             CHARACTER(1) CHARACTER SET LATIN,
                               NOT CASESPECIFIC,
     ImportedPlan              CHARACTER(1) CHARACTER SET LATIN,
                               NOT CASESPECIFIC,
     SessionTemporalQualifier  VARCHAR(1024) CHARACTER SET LATIN
                               NOT CASESPECIFIC,
     TemporalTimestamp         TIMESTAMP(0),
     TotalCost                 FLOAT DEFAULT 0),
   UNIQUE PRIMARY INDEX PK_QueryID (QueryID);

Attribute Definitions for Query

The following table defines the Query table attributes:

Attribute Definition
QueryID
  • Unique identifier for the query generated by the system when the query plan is captured.
  • UPI for the table.
UDB_Key Identifier for the user who captured the plan by submitting either a DUMP EXPLAIN, INSERT EXPLAIN, or BEGIN QUERY CAPTURE statement (see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for descriptions of these statements).
MachName Name of the test machine on which the query plan is captured.
NumAMPs Number of AMPs in the configuration.
NumPEs Number of PEs in the configuration.
NumNodes Number of nodes in the configuration.
ReleaseInfo Teradata Database release number under which the query was captured.

The value is defined in DBC.DBCInfo.

VersionInfo Teradata Database version number under which the query was captured.

The value is defined in DBC.DBCInfo.

PENum Number of the parsing engine on which the query was processed.
QueryText The SQL DML text of the captured query.

If the text exceeds the upper limit of 20,000 characters, then it overflows to the QryRelX table. See QryRelX.

CaptureTimeStamp Timestamp that identifies when the captured query was performed.

Useful for distinguishing among multiple performances of the same query on the same machine under the same software version and release.

QueryName The name of the query, if provided, as specified in the AS clause.
Frequency The number of times the query is performed in the workload to which it is assigned.

The value is specified by the INSERT EXPLAIN statement that is used to create the row.

StatementTypes A comma-separated list of 3-character statement type codes.
  • ABT is an ABORT statement.
  • BTS is a BEGIN TRANSACTION statement.
  • DEL is a DELETE statement.
  • ETS is an END TRANSACTION statement.
  • INS is an INSERT statement.
  • MRG is a MERGE statement.
  • NUL is a null statement.
  • OTR is an other statement.

    This code describes any statement type that is not described by the other 9 statement type codes.

  • RET is a retrieve (SELECT) statement.
  • UPD is an UPDATE statement.
  • URT is an Update-Retrieve statement.
DefaultDBName The name of the default database at the time the query plan is captured.
Overflow Specifies whether QueryText exceeds 20 000 characters.
If QueryText exceeds 20,000 characters, then all text beyond that boundary is truncated.
  • Code F indicates that QueryText is <= 20 000 characters.
  • Code T indicates that QueryText is > 20 000 characters and has been truncated.
Complete Identifies whether Query stores the complete query text or a truncated version.
  • F indicates that the query text 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. For more information, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

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

  • T indicates that the full query text 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).

ValidatedPlan Specifies whether the plan was captured in validation or non-validation mode.
The value is always set to F during the capture.
  • F indicates that the query plan was captured in non-validation mode.
  • T indicates that the query plan was captured in validation mode.
ImportedPlan Specifies whether the query plan was captured on the current system or imported from another system.
The system always sets the flag to F during the capture.
  • F indicates that the query plan was captured on the current system.
  • T indicates that the query plan was imported to the current system from another system.
TemporalTimestamp The timestamp value derived from evaluating the TEMPORAL_TIMESTAMP function at the time the query was captured.

This column is only populated for requests that either reference temporal tables or that invoke the functions TEMPORAL_TIMESTAMP or TEMPORAL_DATE.

The column is set null if the captured query does not reference temporal tables or invoke the TEMPORAL_TIMESTAMP or TEMPORAL_DATE functions.

See Teradata Vantage™ - Temporal Table Support , B035-1182 for details about temporal timestamping.

SessionTemporalQualifier Specifies the temporal qualifier for the session specified by the most recent SET SESSION temporal_qualifier request.
The valid session temporal qualifiers are indicated in the following list:
  • ANSIQUALIFIER
  • AS OF date_expression
  • AS OF timestamp_expression
  • CURRENT TRANSACTIONTIME
  • CURRENT TRANSACTIONTIME AND CURRENT VALIDTIME
  • CURRENT VALIDTIME
  • CURRENT VALIDTIME AND CURRENT TRANSACTIONTIME
  • CURRENT VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
  • CURRENT VALIDTIME AND TRANSACTIONTIME AS OF timestamp_expression
  • NONSEQUENCED TRANSACTIONTIME
  • NONSEQUENCED TRANSACTIONTIME AND CURRENT VALIDTIME
  • NONSEQUENCED VALIDTIME
  • NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME
  • NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
  • NONSEQUENCED VALIDTIME AND TRANSACTION TIME AS OF timestamp_expression
  • SEQUENCED VALIDTIME
  • SEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME
 
  • SEQUENCED VALIDTIME period_of_applicability
  • SEQUENCED VALIDTIME period_of_applicability AND CURRENT TRANSACTIONTIME
  • SEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
  • SEQUENCED VALIDTIME period_of_applicability AND NONSEQUENCED TRANSACTIONTIME
  • SEQUENCED VALIDTIME period_of_applicability AND TRANSACTIONTIME AS OF timestamp_expression
  • TRANSACTIONTIME AS OF timestamp_expression
  • VALIDTIME
  • VALIDTIME period_of_applicability
  • VALIDTIME AND CURRENT TRANSACTIONTIME
  • VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
  • VALIDTIME AND TRANSACTIONTIME AS OF timestamp_expression
  • VALIDTIME AS OF date_expression
  • VALIDTIME AS OF timestamp_expression
  • VALIDTIME AS OF date_expression AND CURRENT TRANSACTIONTIME
  • VALIDTIME AS OF timestamp_expression AND CURRENT TRANSACTIONTIME
TotalCost The estimated total cost of performing this query, expressed in milliseconds.