15.10 - Query - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

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.