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, EXPLAIN phrases (such as "in map_name") and steps (such as locking and END TRANSACTION) may be omitted from examples, where only the relevant steps are shown.
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.
The personnel.employee table 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 and therefore 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).
- The Smith rows are redistributed by the hash code of the order_no column.
- The number of the redistributed rows is small and the rows are hashed to 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.