17.10 - EXPLAIN Request Modifier: Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)
This section examines the usefulness of the EXPLAIN request modifier in different situations.
The precise EXPLAIN output in these examples may differ from what you encounter because execution plans can vary, depending on the system configuration and database release. To simplify the examples, some EXPLAIN phrases (such as in map_name) and steps (such as locking and END TRANSACTION) are omitted from some of the examples, where only the relevant steps are shown.

You should always use EXPLAIN reports to analyze any new queries under development. Subtle differences in the way a query is structured can produce enormous differences in its resource impact and performance while at the same time returning the identical result set.

EXPLAIN request modifier terms are defined in EXPLAIN Request Modifier Phrase Terminology.

In the examples that use the personnel.employee table, it is defined to have a unique primary index defined on the emp_no column and a nonunique secondary index defined on the name column.

ANSI Versus Teradata Session Mode Update Example

This example shows the EXPLAIN differences between running the session in ANSI versus Teradata session modes:

EXPLAIN 
UPDATE Employee
SET deptno = 650
WHERE deptno = 640;

In ANSI mode, EXPLAIN generates the following response for this request:

Explanation
--------------------------------------------------------------------
1) First, we lock PERSONNEL.employee for write
   on a reserved RowHash to prevent global deadlock.
2) Next, we lock PERSONNEL.employee for write.
3) We do an all-AMPs UPDATE from PERSONNEL.employee by way of an
   all-rows scan with a condition of
   ("PERSONNEL.employee.DeptNo = 640").
-> No rows are returned to the user as the result of statement 1.

In Teradata session mode, EXPLAIN generates this response for the same request:

Explanation
--------------------------------------------------------------------
1) First, we lock PERSONNEL.employee for write on a
   reserved RowHash to prevent global deadlock.
2) Next, we lock PERSONNEL.employee for write.
3) We do an all-AMPs UPDATE from PERSONNEL.employee by way of an
   all-rows scan with a condition of
   ("PERSONNEL.employee.DeptNo = 640").
4) Finally, we send out an END TRANSACTION step to all AMPs involved
   in processing the request.
-> No rows are returned to the user as the result of statement 1.

In ANSI session mode the transaction is not committed, therefore it is not ended, whereas in Teradata session mode, no COMMIT is required to end the transaction.

Few-AMPs Row Redistribution Example

Assume that you create a multiset table and a second table, as follows:

CREATE MULTISET TABLE customer_order (customer_name CHAR(10), order_no INTEGER)
PRIMARY INDEX (customer_name);

CREATE TABLE order_detail (order_no INTEGER, product_name  CHAR(20))
PRIMARY INDEX (order_no);
The EXPLAIN request modifier generates the few AMPs text in step 1, showing that the step uses few AMPs for row redistribution. Query step 1 uses few AMPs because of the following factors:
  • the step is initiated in a single AMP that has rows with ‘Smith’ (NUPI), AND
  • the Smith rows are redistributed by the hash code of the order_no column, AND
  • the number of the redistributed rows is small and the rows are hashed to only few AMPs.
    EXPLAIN
    SELECT customer_name, product_name
    FROM customer_order CO, order_detail OD
    WHERE CO.customer_name = 'Smith' AND CO.order_no = OD.order_no;
    Explanation
    --------------------------------------------------------------------
      1) First, we do a single-AMP RETRIEVE step from JW.CO by way of
         the primary index "JW.CO.customer_name = 'Smith '" with a
         residual condition of ("NOT (JW.CO.order_no IS NULL)") into 
         Spool 2 (group_amps), which is redistributed by the hash code
         of (JW.CO.order_no) to few AMPs. Then we do a SORT to
         order Spool 2 by row hash. The size of Spool 2 is estimated
         with low confidence to be 2 rows (54 bytes). The estimated
         time for this step is 0.01 seconds.
      2) Next, we do a group-AMPs JOIN step from JW.OD by way of a
         RowHash match scan, which is joined to Spool 2 (Last Use) by
         way of a RowHash match scan. JW.OD and Spool 2 are joined using
         a merge join, with a join condition of
         ("order_no = JW.OD.order_no"). The result goes into Spool 1
         (group_amps), which is built locally on that AMP. The size of
         Spool 1 is estimated with index join confidence to be 3 rows
         (153 bytes). The estimated time for this step is 0.11 seconds.
      3) Finally, we send out an END TRANSACTION step to all AMPs
         involved in processing the request.
      -> The contents of Spool 1 are sent back to the user as the
         result of statement 1. The total estimated time is 0.11 seconds.

One-Step Multisource RETRIEVE for a UNION ALL

5) We do an all-AMPs RETRIEVE step in TD_MAP1 from 2 sources:
      a) TEST.t1 by way of an all-rows scan with a condition of (
         "NOT (TEST.t1.a1 IS NULL)").
      b) TEST.t2 by way of an all-rows scan with a condition of (
         "NOT (TEST.t2.a2 IS NULL)").
     The result goes into Spool 1 (all_amps), which is built
     locally on the AMPs.  The size of Spool 1 is estimated with
     low confidence to be 126 rows (3,150 bytes).  The estimated
     time for this step is 0.15 seconds.

One-Step Multisource JOIN

6) We do an all-AMPs JOIN step in TD_Map1 from 2 left sources, which
   is joined to TEST.t1 by way of an all-rows scan using a dynamic
   hash join.  The left sources are:
      a) Spool 3 (Last Use) by way of an all-rows scan, with a join
         condition of ("TEST.t1.b1 = a2").
      b) Spool 4 (Last Use) by way of an all-rows scan, with a join
         condition of ("TEST.t1.b1 = a3").
     The result goes into Spool 2 (group_amps), which is built
     locally on the AMPs.  The size of Spool 2 is estimated with
     no confidence to be 90 rows (2,880 bytes).  The estimated
     time for this step is 0.23 seconds.

One-Step Multisource SUM

4) We do an all-AMPs SUM step in TD_MAP1 to aggregate from 2 sources:
      a) TEST.t1 by way of a cylinder index scan with no residual
         conditions.
      b) TEST.t2 by way of a cylinder index scan with no residual
         conditions.
     Aggregate Intermediate Results are computed globally, then
     placed in Spool 9 in TD_Map1.  The size of Spool 9 is
     estimated with high confidence to be 1 row (23 bytes).  The
     estimated time for this step is 0.18 seconds.