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

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.