QuerySteps - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Function of QuerySteps

Each row in the table lists the attributes of any step run by the system corresponding to the request. If the step has more than one attribute, then multiple rows are stored for the step, and the row set is linked by means of its common StepID and QueryID values. The individual rows are distinguished by their row type codes.

QuerySteps Table Definition

The following CREATE TABLE request defines the QuerySteps table:

   CREATE TABLE QuerySteps (
     StepID             INTEGER NOT NULL,
     QueryID            INTEGER NOT NULL,
     StepNum            INTEGER,
     ParallelStepNum    INTEGER DEFAULT 0,
     StepText           VARCHAR(32000) CHARACTER SET LATIN
                        NOT CASESPECIFIC,
     RowType            CHARACTER(1) CHARACTER SET LATIN
                        NOT CASESPECIFIC NOT NULL,
     StepKind           CHARACTER(2) CHARACTER SET LATIN
                        NOT CASESPECIFIC,
     ParallelKind       CHARACTER(1) CHARACTER SET LATIN
                        NOT CASESPECIFIC,
     AMPUsage           CHARACTER(1) CHARACTER SET LATIN
                        NOT CASESPECIFIC,
     TriggerType        CHARACTER(1) CHARACTER SET LATIN
                        NOT CASESPECIFIC,
     EstCPUCost         FLOAT,
     EstIOCost          FLOAT,
     EstNetworkCost     FLOAT,
     EstHRCost          FLOAT
     Cost               FLOAT,
     MaxCost            FLOAT,
     SourceRelation1    INTEGER,
     SourceRelation2    INTEGER,
     TargetRelation1    INTEGER,
     TargetRelation2    INTEGER,
     StepAttributeType  CHARACTER(10) CHARACTER SET LATIN
                        NOT CASESPECIFIC,
     StepAttributeValue VARCHAR(100) CHARACTER SET LATIN
                        NOT CASESPECIFIC,
     LockType           CHARACTER(1) CHARACTER SET LATIN
                        NOT CASESPECIFIC,
     RowHashFlag        CHARACTER(1) CHARACTER SET LATIN
                        NOT CASESPECIFIC,
     NoWaitFlag         CHARACTER(1) CHARACTER SET LATIN
                        NOT CASESPECIFIC
     Cardinality        DECIMAL(18,0) DEFAULT 0,
     IndexMaintCostEst  FLOAT DEFAULT 0)
   PRIMARY INDEX (QueryID)
   INDEX SK_StepID (StepID);

Attribute Definitions for QuerySteps

The following table defines the QuerySteps table attributes:

Attribute Definition
StepID
  • Unique identifier for the step.
  • NUSI for the table.
QueryID
  • Unique identifier for the query.
  • NUPI for the table.
StepNum The number of the step whose text is reported by this QuerySteps row.
  • If the step is not performed in parallel, StepNum indicates the step number.
  • If the step is performed in parallel, StepNum indicates the number of the main step.
ParallelStepNum The number of the parallel step whose text is reported by this QuerySteps row.
  • If the step is not performed in parallel, then ParallelStepNum is 0.
  • If the step is performed in parallel, then ParallelStepNum is the number of the parallel step.
StepText Stores text describing the step.
RowType Describes the type of detail this row characterizes.
  • If A, the row describes attributes for a query plan step beyond the first row for that step, which is coded with a RowType of G.

    The additional attributes are described by the StepAttributeType and StepAttributeValue columns. The remaining columns in an A row type are set null.

  • If G, the row describes the first row of step information for a step of the query plan.

    All steps have one row of this type.
StepKind Describes the step characterized by this row:
  • AB is an abort step.
  • AF is an AllRowsOneAMP In-Memory Hash Join Fly step.
  • AH is an AllRowsOneAMP In-Memory Hash Join step
  • BM is a bitmap step.
  • CE is a correlated inclusion merge join step.
  • CI is a correlated exclusion product join step.
  • CJ is a correlated join step. Mel comments: DR 178936 TEXT has never been reviewed or approved, in spite of multiple attempts
  • CP is a correlated exclusion merge join step.
  • DE is a delete step.
  • EF is an exclusion dynamic hash join step. Mel comments: DR 178936 TEXT has never been reviewed or approved, in spite of multiple attempts
  • EH is an exclusion hash join step.
  • EJ is an exists join step.
  • EM is an exclusion merge join step.
  • EP is an exclusion product join step.
  • FD is a flush database step.
  • HF is a dynamic hash join step.
  • HJ is a hash star join step.
  • HS is a hash join step.
  • IF is an inclusion dynamic hash join step. Mel comments: TEXT has never been reviewed or approved, in spite of multiple attempts
  • IH is an inclusion hash join step.
  • IJ is an intersect all join step.
  • IM is an inclusion merge join step.
  • IN is an insert step.
  • IP is an inclusion product join step.
  • LK is a lock step.
  • MD is a merge delete step.
  • MF is an in-memory hash fly join
  • MG is a merge step.
  • MH is an in-memory hash join
  • MI is a minus all join step.
  • MJ is a merge join step.
  • MR is a merge into step. Mel comments: DR 178936 TEXT has never been reviewed or approved, in spite of multiple attempts
  • MS is an other step.

    The MS code describes any type of step not described by the other StepKind codes.

  • MT is a materialize temporary table step.
  • MU is a merge update step.
  • NJ is a nested join step.
  • OJ describes a not exists join step.
  • PJ is a product join step.
  • RJ is a row ID join step.
  • SA is a sample step.
  • SO is a sort step.
  • SP is a spoil step.
  • SR is a single retrieve step.
  • ST is a statistics step.
  • SU is a sum retrieve step.
  • UP is an update step.
  • US is an upsert step.
ParallelKind Describes whether the step can be done in parallel with its preceding step or not.
  • B is a beginning parallel step.
  • E is an ending parallel step.
  • P is a parallel step and can be performed in parallel with the preceding step.
  • S is a non-parallel step that is performed sequentially and cannot be performed in parallel with the preceding step.
AMPUsage Describes how AMPs are used to process the step.
  • A is an all-AMPs operation.
  • G is a step that operates on a group of AMPs.
  • O is a step that is a one-AMP operation.
  • T is a step that is a two-AMPs operation.
TriggerType Describes triggering associated with this step.
  • C is a cascaded triggering statement.
  • N indicates that no triggering is involved with this statement.
  • R is a triggered statement.
  • T is a triggering statement.
EstCPUCost The CPU time for the step as estimated by the Optimizer, expressed in milliseconds.
EstIOCost The I/O service time for the step as estimated by the Optimizer, expressed in milliseconds.
EstNetworkCost The BYNET service time for the step as estimated by the Optimizer, expressed in milliseconds.
EstHRCost Other miscellaneous costs for the step as estimated by the Optimizer, expressed in milliseconds.
Cost Estimated cost of performing this step, expressed in milliseconds.

Cost values are not absolute times, but relative values to evaluate as proportions with respect to other cost estimates.
MaxCost Estimated worst case cost of performing this step.
SourceRelation1 Number of the principal source relation in this step.

For example, if the step is a join step and StepAttributeType is SingleRowL, then SourceRelation1 is the number of the left relation in the join.

If StepAttributeType is SingleRowR, then SourceRelation1 is null and SourceRelation2 contains the number of the right relation in the join.
SourceRelation2 Number of an additional source relation in this step.
TargetRelation1 Number of the spool file or table on which the step operation results or acts.
TargetRelation2 Number of an additional spool file, if required, to hold additional results of this step.
StepAttributeType Indicates the attribute that is described by the row.

When the StepAttributeType column contains SnglPart, PartRg, or AllParts, the StepAttributeValue column specifies the sequence (1, 2, ...) of such a lock in the LK StepKind.

  • AllParts specifies that Teradata locks all partitions. When RowHashFlag is T and StepAttributeType is AllParts, StepAttributeType specifies a RowHash-in-All-Partitions lock.
  • BeginRQ occurs at the first step of the recursive block.
  • EndRQ is the step number of the corresponding BeginRQ and occurs at the last step of the recursive block.
  • GlobalFlag has the following codes that describe the Sum step.
    • F means the flag is not set.

      Intermediate aggregate results are computed locally.

    • T means the flag is set.

      Intermediate aggregate results are computed globally.

  • GroupKey is the Grouping column and occurs at the Sum step.
  • IndexNum is the Index number and occurs at one of the following steps, which indicates whether the index was used.
    • Abort
    • BitMap
    • Delete
    • Single retrieve
  • JoinType has the following codes that describe the join type in the join step.
    • F is a full outer join.
    • I is an inner join.
    • L is a left outer join.
    • R is a right outer join.
  • Kind has the following codes and indicates whether the samples are specified as a fraction of rows or as an absolute number of rows in the sample step.
    • F is a fraction sample.
    • I is a fixed sample.
  • LeftIndex is the index number and occurs when the left relation is used in the join step.
  • MergeMode has the following codes and occurs for the merge delete and merge update steps occur, indicating the merge type used in the step.
    • H is a match row hash.
    • R is a match row ID.
    • W is a match whole row.
  • Multisrc shows whether the current JOIN, SUM, or RET step was performed as a multisource step. A value of T in the StepAttributeValue column for a Multisrc query step means that multiple JOIN, SUM, or RET steps, all of the same type but from multiple sources, were performed as a single-step optimization.
  • PartCount describes the number of hash join partitions used for a hash join and occurs only for a hash join step.
  • PartRg specifies that Teradata locks a partition range. When RowHashFlag is T and StepAttributeType is PartRg, PartRg specifies a RowHash-in-Partition-Range lock.
  • RightIndex is the index number and occurs when the right relation is used in the join step.
  • SnglPart specifies that Teradata locks a single partition. When RowHashFlag is T and StepAttributeType is SnglPart, SnglPart specifies a RowKey lock, that is, a RowHash-in-Single-Partition lock
  • SingleRowL indicates whether single-row optimization was applied to the left relation in the join step during a DUMP EXPLAIN or an INSERT EXPLAIN operation.
    • If the name of the left relation is available, then StepAttributeValue contains that name.
    • If the name of the left relation is not available, then StepAttributeValue is null.
  • SingleRowR indicates whether single-row optimization was applied to the right relation in the join step during a DUMP EXPLAIN or an INSERT EXPLAIN operation.
    • If the name of the right relation is available, then StepAttributeValue contains that name.
    • If the name of the right relation is not available, then StepAttributeValue is null.
  • INSERT EXPLAIN and DUMP EXPLAIN are supported only on the Block File System on the primary cluster, not on the Object File System.
  • SMSKind describes one of the following types and occurs for a BitMap step indicating the set manipulation operation performed. The types are:
    • Intersect
    • Minus
    • Union
  • SourceIndex describes the index number and occurs when the index is used in the source relation in the Stat step.
  • StatOpt describes which of the following kinds of optimization was used at the Stat function step.
    • L is a Load distribution optimization.
    • S is a Single AMP optimization.
    • Svalue describes the sample size and occurs in the Sample step.
    • TableIndex describes the index number and occurs at the Update step, indicating the index the step used.
    • True or False describes the text containing the error returned with an abort and occurs at the Abort step, indicating whether to abort when the condition is true or when the condition is false.
StepAttributeValue Indicates the value of the attribute described by the row.
LockType Defines the severity of the lock specified by the query plan for this step.

See Transaction Processing for information about lock severities.

  • A is an ACCESS lock.
  • R is a READ lock.
  • S is a SingleWriter lock.
  • W is a WRITE lock.
  • X is an EXCLUSIVE lock.
RowHashFlag Indicates if the table is locked on a row hash.
  • F indicates that the table is not locked on a row hash.
  • T indicates that the table is locked on a row hash.
NoWaitFlag Indicates if the no wait option is set for the lock step.
  • F indicates that the no wait option is disabled.
  • T indicates that the no wait option is enabled.
Cardinality The estimated number of output rows or affected rows estimated for this given step by the Optimizer.

This value is applicable only to steps that retrieve or modify rows.
IndexMaintCostEst The cost estimated to maintain the indexes affected by this step, expressed in milliseconds.

This value is applicable only to steps that modify rows.