15.10 - Query - Teradata Database

Describes information about captured queries.

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

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 SQL Data Definition Language 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 “Overflow” on page 788 and “QryRelX” on page 785.
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 (see SQL Data Manipulation Language). 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” on page 785).
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 Temporal Table Support 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
SessionTemporalQualifier (continued)	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.