EXPLAIN Request Modifier Phrase Terminology | VantageCloud Lake - EXPLAIN Request Modifier Phrase Terminology - Teradata Vantage

For this phrase to occur, the column-partitioned table or join index does not have row partitioning.

m1 ≥ 2. One of the column partitions accessed may be the delete column partition.

Not all of the m1 column partitions may need to be accessed if no rows qualify.

A USING ROWID SPOOL phrase may follow the table or join index name. If so, m1 is the number of column partitions accessed when using the rowID spool while m2 in the USING ROWID SPOOL phrase is the number of column partitions accessed to build the rowID spool.

This phrase is used in steps such as RETRIEVE, DELETE, JOIN, and MERGE that may read a column-partitioned source.

For a delete operation, Vantage does not immediately reclaim storage for any deleted rows other than LOBs and column partitions with ROW format. A subsequent fast path deletion of all the rows of the table or join index reclaims the storage of all deleted rows in the table, including previously deleted rows.

For this phrase to occur, the column-partitioned table or join index does not have row partitioning.

m1 ≥ 2.

2 < c1 < m1-1

c1 is equal to, or one less than, the number of available column partition contexts.

One of the column partitions accessed may be the delete column partition. Not all of the m1 column partitions may need to be accessed if no rows qualify.

A "using CP merge Spool" or "using covering CP merge Spool" phrase follows the table or join index name.

This phrase is used in steps such as RETRIEVE, DELETE, JOIN, and MERGE that may read a column-partitioned source.

To access the m1 column partitions, columns from the column partitions are merged up to c1 column partitions at a time into a column-partitioned spool until all the projected columns have been retrieved for the qualifying rows.

One or more of the merges may require accessing the delete column partition. For each such merge, the delete column partition is included in m1.

Performance can degrade if c1 is much less than m1 and the retrieve is not selective. For tables on the Block File System, consider decreasing the number of column partitions that need to be accessed, decreasing the data block size for the table, combining column partitions so there are fewer column partitions, or increasing PPICacheThrP if there is enough available memory to do so.

For a delete operation, Vantage does not immediately reclaim storage for any deleted rows other than LOBs and column partitions with ROW format. A subsequent fast path deletion of all rows of the table or join index reclaims the storage of all deleted rows in the table, including previously deleted rows.

This phrase only applies to a row-partitioned, primary-indexed (RPPI) table or join index.

For this phrase to occur, the table or join index is accessed using rowids.

A "using rowid Spool" phrase may follow the table or join index name. One column partition is accessed when using the rowID spool while m2 in the "using rowid Spool" phrase is the number of column partitions accessed to build the rowID spool.

The phrase is used in steps such as RETRIEVE, DELETE, JOIN, and MERGE that may read a column-partitioned source.

For the column-partitioning level, m1 column partitions may need to be accessed. There are column partition contexts available for each of the m1 column partitions.

For this phrase to occur, the column-partitioned table or join index has both row and column partitioning. One of the column partitions that may be accessed is the delete column partition.

Not all of the m1 column partitions may need to be accessed if no rows qualify.

A USING ROWID SPOOL phrase may follow the table or join index name. If so, m1 is the number of column partitions accessed when using the rowID spool while m2 in the USING ROWID SPOOL phrase is the number of column partitions accessed to build the rowID spool.

The phrase is used in steps such as RETRIEVE, DELETE, JOIN, and MERGE that may read a column-partitioned source.

For a delete operation, Vantage does not immediately reclaim storage for any deleted rows other than LOBs and column partitions with ROW format. A subsequent fast path deletion of all the rows in a row partition reclaims the storage of all deleted rows in that row partition, including previously deleted rows in that partition. A subsequent fast path deletion of all the rows of the table or join index reclaims the storage of all deleted rows in the table, including previously deleted rows.

For the column-partitioning level, m1 column partitions may need to be accessed. For this phrase to occur, the column-partitioned table or join index has both row and column partitioning.

For the column-partitioning level, c1 column partition contexts are used to access up to m1 column partitions. n1 ≥ 2.

m1 ≥ 2.

2 < c1 < m1-1 and is equal to or one less than the number of available column partition contexts.

One of the column partitions that may be accessed is the delete column partition. Not all of the m1 column partitions may need to be accessed if no rows qualify. A "using CP merge Spool" or "using covering CP merge Spool" phrase follows the table or join index name.

The phrase is used in steps such as RETRIEVE, DELETE, JOIN, and MERGE that may read a column-partitioned source. To access the m1 column partitions, columns from the column partitions are merged up to c1 column partitions at a time into a column-partitioned spool until all the projected columns have been retrieved for the qualifying rows.

One or more of the merges may require accessing the delete column partition. For each such merge, the delete column partition is included in m1.

Performance can degrade if c1 is much less than m1 and the retrieve is not selective. For tables on the Block File System, consider decreasing the number of column partitions that need to be accessed, decreasing the data block size for the table, combining column partitions so there are fewer column partitions, or increasing PPICacheThrP if there is enough available memory to do so.

For a delete operation, Vantage does not immediately reclaim storage for any deleted rows other than LOBs and column partitions with ROW format. A subsequent fast path deletion of all the rows in a row partition reclaims the storage of all deleted rows in that row partition, including previously deleted rows in that partition. A subsequent fast path delete of all the rows of the table or join index reclaims the storage of all deleted rows in the table, including previously deleted rows.

For this case, local aggregation is the only stage of the ARSA aggregation algorithm that Vantage performs.

This phrase is not applicable to column-partitioned tables or join indexes because those objects do not have a primary index.

A "using rowid Spool" phrase may follow the table or join index name. One column partition is accessed when using the rowID spool while m2 in the "using rowid Spool" phrase is the number of column partitions accessed to build the rowID spool.

This phrase is used in steps such as RETRIEVE, DELETE, JOIN, and MERGE that may read a column-partitioned source.

For a delete operation, Vantage does not immediately reclaim storage for any deleted rows, other than LOBs and column partitions with ROW format. A subsequent fast path deletion of all of the rows of the table or join index reclaims the storage of all deleted rows in the table, including previously deleted rows.

For this phrase to occur, the column-partitioned table or join index has to have both row and column partitioning. Also, the delete column partition need not be accessed either because the column partition has ROW format or because access is using rowids.

A "using rowid Spool" phrase may follow the table or join index name. One column partition is accessed when using the rowID spool while m2 in the "using rowid Spool" phrase is the number of column partitions accessed to build the rowID spool.

This phrase is used in steps such as RETRIEVE, DELETE, JOIN, and MERGE that may read a column-partitioned source.

This phrase is not applicable to a column-partitioned table or join index.

When the phrase is not given, the join is hash based. That is, there are equality constraints on the primary index columns from which the hash is derived.

With either method, the join conditions must still be validated.

All AMP steps from the current step through the step in which the phrase END ROW TRIGGER LOOP for step n appears constitute the row trigger loop.

Control moves to the next step outside the trigger.

The value of m3 ≥ 2.

In the following request, employee.empno = department.mgrno is the overall constraint governing the join:

SELECT deptname, name
FROM employee, department
WHERE employee.empno = department.mgrno;

Join conditions are also specified in the ON clause of an outer join or an ANSI SQL standard-formatted inner join.

m3 ≥ 2.

The Optimizer recommends the least costly method to use, but an AMP may change to the other method as the rows are being inserted if the other method may perform better.

m3 ≥ 2.

2 < c3 < m3.

The Optimizer recommends the method to use to the AMPs, but an AMP may change to the other method as the rows are inserted if the AMP determines that the other method may perform better.

• Both update and insert operations
• Insert operations only
• Update operations only

The step assumes that the source table is distributed on the join column of the source table, which is specified in the ON clause as an equality constraint with the primary index of the target table and sorted on the RowKey.

The step performs a RowKey-based Merge Join internally, identifying source rows that qualify for updating target rows and source rows that qualify for inserts, after which the step performs those updates and inserts.

This step is similar to the APPLY phase of MultiLoad in guaranteeing that the target table data block is read and written only once during the MERGE operation.

Foreign tables allow Vantage to access data from external object storage, such as AWS S3, without requiring you to manually move the data into the database first from where it natively resides. Foreign tables are identified by a hostname, path, and other metadata that point to the external object storage. Vantage can read and process semi-structured or unstructured external data in foreign tables using standard SQL. For example, you can use Teradata analytic functions to examine the data, join it to the relational data in the database, and issue queries against it as you can for other data in Vantage.

This phrase is not always reported for steps where a lock was acquired earlier.

n3 > 1.

n3 is greater than one.

The value of n is greater than one.

This may allow faster deletion of entire partitions.

This phrase is not applicable to column-partitioned tables or join indexes because other phrases are used in those cases.

This may allow faster deletion of the entire combined partition.

This phrase is not applicable to column-partitioned tables or join indexes because there must be at least two column partitions for a column-partitioned table or join index: at least one specified column partition and the delete column partition.

The phrase "in all partitions" indicates a proxy for a table-level lock.

The phrase "in n partitions" indicates a proxy for a partition-range lock.

The phrase "in a single partition" indicates a proxy for a single-partition lock.

For more information, see Proxy Locks.

For example, the system can probe a NUSI subtable to get the rowID values for base table rows that may qualify for the query, but the system must then also access the underlying base table for the NUSI to get the rows that definitely qualify.

Although not explicitly stated in the LOCK portion of the Explain text, a rowhash lock is required because the table is accessed using the unique primary index.

Another value that uniquely defines the spool row (used for complex queries involving aggregates and subqueries).

For example, SORT TO partition BY ROWKEY or SORT to order Spool 1 by the sort key in spool field1 (THU.JI1.b1).

For example, SORT TO partition BY ROWKEY or SORT to order Spool 1 by the sort key in spool field1 (THU.JI1.b1).

• Spool 1 is typically used to hold a result before the result is returned to the user.
• Spools 2, 3, and so on, contain intermediate data that produces a result.

For example:

personnel.employee.empno = spool_2.mgrno
Statement 1...

This term refers to the initiating request.

After the steps where the location of the Spool n statistics is reported and the statistics are consolidated, if the statistics are retrieved from additional runtime information, the size of the spool is given.

The time shown is not clock time, but an arbitrary unit of measure that you can use to compare different operations.

The time shown is not clock time, but an arbitrary unit of measure that you can use to compare different operations.

The spool size in bytes is calculated as follows:

Row size is an estimated average row size if the row includes variable length or compressed columns.

The phrase, if included, occurs before the first step of the generic plan in DBC.DBQLExplainTbl if the EXPLAIN text for the plan is being logged by DBQL. The phrase does not occur when you specify an EXPLAIN, STATIC EXPLAIN, or DYNAMIC EXPLAIN request modifier.

This is a specific static plan that is eligible for incremental planning and execution, but does not meet certain system-specified cost thresholds. If Vantage runs the request, this static plan is generated and run.

A generic plan is a static plan. If the request runs, a generic plan, a specific static plan, or a dynamic plan may be generated and run. This phrase does not occur in DBC.DBQLExplainTbl in the EXPLAIN text for a plan being logged by DBQL.

This is a specific static plan that is eligible for incremental planning and execution and, if the request runs, a dynamic plan may be generated and run instead of this static plan. However, this static plan is the one used for evaluating workload filters, throttles, and classification criteria.

If the request runs and IPE is still disabled, Vantage generates and runs a static plan; if the request runs and IPE is enabled, a dynamic plan is generated. To enable dynamic plans, contact Teradata Support.

• At most, a single row in the USI subtable is selected using the index value that hashes to the AMP on which the subtable row is stored.
• The hash value in the index rowID determines the AMP on which the data row is stored.

s1 indicates the number of intermediate subrow column partitions needed for the merges, while s1 + s2 indicates the number of merges needed.

If s 1 is not included, no intermediate subrow column partitions are needed for the merges, and s2 merges are required.

The number of merges needed depends on the number of column partitions that need to be accessed and the number of available column partition contexts as indicated in a preceding "from" phrase. This is the last usage of this column-partitioned merge spool, and can therefore be deleted.

q is the spool number for the column-partitioned merge spool.

s 1 indicates the number of intermediate subrow column partitions needed for the merges.

s 1 + s 2 indicates the number of merges needed.

If s1 is not included, no intermediate subrow column partitions are needed for the merges and in addition, there are s2 merges needed.

The number of merges needed depends on the number of column partitions that need to be accessed and the number of available column partition contexts as indicated in a preceding "from" phrase.

This is the last usage of both this column-partitioned merge spool and rowID spool so these spools can be deleted.

q is the spool number for the column-partitioned merge spool.

k is the spool number for the rowID spool.

If rowID Spool k is generated by this AMP step instead of a previous step, a built from phrase follows this using phrase.

s1 indicates the number of intermediate subrow column partitions needed for the merges.

s1 + s2 indicates the number of merges needed.

If s1 is not included, no intermediate subrow column partitions are needed for the merges, and there is also one or s2 merges needed.

The number of merges needed depends on the number of column partitions that need to be accessed and the number of available column partition contexts, as indicated in a preceding "from" phrase.

This is the last usage of this column-partitioned merge spool, so they can be deleted.

q is the spool number for the column-partitioned merge spool.

s1 indicates the number of intermediate subrow column partitions needed for the merges.

s1 + s2 indicates the number of merges needed.

If s1 is not included, no intermediate subrow column partitions are needed for the merges, and there is also one or s2 merges needed.

This is the last usage of both this column-partitioned merge spool and rowid spool so both can be deleted.

q is the spool number for the column-partitioned merge spool.

k is the spool number for the rowID spool.

If rowID Spool k is generated by this AMP step instead of a previous step, a "built from" phrase follows this "using" phrase.

This is the last usage of this rowID spool, so this rowID spool can be deleted.

k is the spool number for the rowID spool.

If rowID Spool k is generated by this AMP step instead of a previous step, a "built from" phrase follows this "using" phrase.

This phrase is used in AMP steps, such as RETRIEVE, JOIN, and MERGE, that may read a column-partitioned source.

Up to m2 column partitions of the column-partitioned table or join index may need to be accessed to evaluate predicates and build the rowID spool.

There are column partition contexts available for each of the m2 column partitions.

For this phrase to occur, the column-partitioned table or join index cannot have row partitioning.

m2 ≥ 2.

One of the column partitions accessed may be the delete column partition.

Not all of the m2 column partitions may need to be accessed if no rows qualify.

This phrase is used in AMP steps such as RETRIEVE, JOIN, and MERGE that may read a column-partitioned source.

Up to m2 column partitions of the column-partitioned table or join index may need to be accessed using c2 column-partition contexts to evaluate predicates and build the rowID spool. For this phrase to occur, the column-partitioned table or join index does not have row partitioning.

k is the spool number for the rowID spool.

m2 ≥ 2.

2 < c2 < m2-1 and is equal to or one less than the number of available column-partition contexts.

One of the column partitions accessed may be the delete column partition. Not all of the m2 column partitions may need to be accessed if no rows qualify.

This phrase is used in AMP steps such as RETRIEVE, JOIN, and MERGE that may read a column-partitioned source.

The rows and columns for up to n2 combined partitions of the table or join index (which has both row and column partitioning) may need to be accessed to evaluate predicates and build the rowID spool.

k is the spool number for the rowID spool.

There are column partition contexts available for each of the m2 column partitions.

m2 ≥ 2.

One of the column partitions accessed may be the delete column partition.

Not all of the m2 column partitions may need to be accessed if no rows qualify.

This phrase is used in AMP steps such as RETRIEVE, JOIN, and MERGE that may read a column-partitioned source.

k is the spool number for the rowID spool.

The rows and columns for up to n2 combined partitions of the table or join index, which has both row and column partitioning, may need to be accessed to evaluate predicates and build the rowID spool.

For the column-partitioning level, c2 column-partition contexts are used to access up to m2 column partitions.

n2 ≥ s and m2 ≥ 2.

2 < c2 < m2-1 and is equal to or one less than the number of available column-partition contexts.

One of the column partitions that may be accessed is the delete column partition. Not all of the m2 column partitions may need to be accessed if no rows qualify.

This phrase is used in AMP steps such as RETRIEVE, JOIN, and MERGE that may read a column-partitioned source.

BMSMS intersects the following rowID bit maps:

1)   The bit map built for … by way of index #  n...
2)   The bit map built for … by way of index #  n...
…

The resulting bit map is placed in Spool n...

BMSMS...

Indicates that two or more bit maps are intersected by ANDing to form one large bit map.

index #  n...

Identifies, in the order in which they are ANDed, each nonunique secondary index used in the intersection. 

resulting bit map is placed in Spool  n...

Identifies the temporary file in which the large bit map produced by the BMSMS is stored to make it available for use in producing the final result.

See also EXPLAIN Request Modifier: Examples.

For this case, column_name_1 and column_name_2 are highly nonunique. Therefore, the optimizer made a cost-based decision to pre-aggregate on those two columns and to roll up the result to individual columns. This optimization guarantees that the base table is read only once and the aggregations on the individual columns run faster because they are working on pre-aggregated data.

This optimization is used only for recollecting statistics, and is possible only when the applicable statistics are specified in a single COLLECT STATISTICS request.

For this case, single-column statistics are collected for column_name_1 and for column_name_2, and multicolumn statistics are collected for (column_name_1, column_name_2).

The aggregation for the multicolumn case is done first. The result of this aggregation is used to roll up the statistics for individual statistics on column_name_1 and column_name_2. This way, the base table is read only once and the aggregations on the individual columns runs faster because they are working on the pre-aggregated data.

This optimization is possible only when all the three statistics are specified in a single COLLECT STATISTICS request.

The first levels is used for non-row-partitioned table and levels 2 to 4 are used for row-partitioned tables.

The second phrase indicates a proxy lock that is used for a row-partitioned table.

Both the first and second phrases indicate serialization locks for the proxy lock in the third phrase. A table-level single writer lock allows concurrent reads on the data that is not rowhash write locked.

The fourth phrase indicates a proxy lock used to serialize the all-AMP single writer lock at phrase 5. This type of single writer lock is used in certain cases of partition-locked bulk DML, where NUSI, USI or RI maintenance on the target table is required.

The fifth phrase indicates a single-writer lock that allows concurrent reads on the data in a table that is not write locked during bulk DML processes.

n indicates the number of the plan fragment. There is no step in the plan to indicate the beginning of a plan fragment.

For a dynamic plan, the first step is the beginning of the first plan fragment. The first step after an END PLAN FRAGMENT step is the beginning of the next plan fragment. The last step of a dynamic plan is the end of the last plan fragment, and there is no END PLAN FRAGMENT step for the last plan fragment. The number of plan fragments in the plan and the number of the last plan fragment is the number in the last END PLAN FRAGMENT step plus 1.