In these examples, the personnel.employee table has a unique primary index defined on the emp_no column and a nonunique secondary index defined on the name column.
The EXPLAIN request modifier generates the following response for this request:
EXPLAIN SELECT name, dept_no
FROM employee
WHERE empno = 10009;
Explanation
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
1) First, we do a single‑AMP RETRIEVE step from Personnel.Employee by way of the unique primary index "PERSONNEL.Employee.EmpNo = 10009" with no residual conditions. The estimated time for this step is 0.04 seconds.
-> The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.04 seconds.
The WHERE condition in this example is based on a column that is defined as a nonunique index. Note that the system places a READ lock on the table.
The EXPLAIN request modifier generates the following response for this request:
EXPLAIN SELECT emp_no, dept_no
FROM employee
WHERE name = 'Smith T';
Explanation
---------------------------------------------------------------------------
1) First, we lock a distinct PERSONNEL."pseudo table" for read on a
RowHash to prevent global deadlock for PERSONNEL.employee.
2) Next, we lock PERSONNEL.employee for read.
3) We do an all-AMPs RETRIEVE step from PERSONNEL.employee by way of
index # 4 "PERSONNEL.employee.Name = 'Smith T '" with no residual
conditions into Spool 1 (group_amps), which is built locally on
the AMPs. The size of Spool 1 is estimated with low confidence to
be 33,563 rows (1,443,209 bytes). The estimated time for this
step is 0.34 seconds.
4) 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.34 seconds.
Assume that the employee table has another column (socsecno), where soc_sec_no is defined as a unique secondary index.
If the WHERE condition is based on this column, then the EXPLAIN request modifier generates the following response for this request:
EXPLAIN SELECT name, emp_no
FROM employee
WHERE soc_sec_no = '123456789';
Explanation
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
1) First, we do a two‑AMP RETRIEVE step from PERSONNEL.Employee by way of unique index # 20 "PERSONNEL.Employee.socSecNo = 123456789" with no residual conditions. The estimated time for this step is 0.09 seconds.
-> The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.09 seconds.
In this example, the WHERE clause defines an equality constraint that governs a join.
The rows of the department table are copied to a spool for use in the join operation.
The EXPLAIN request modifier generates the following response for this request:
EXPLAIN SELECT dept_name, name
FROM employee, department
WHERE employee.emp_no = department.mgr_no;
Explanation
--------------------------------------------------------------------
1) First, we lock a distinct PERSONNEL."pseudo table" for read on a RowHash to prevent global deadlock for PERSONNEL.department.
2) Next, we lock a distinct PERSONNEL."pseudo table" for read on a RowHash to prevent global deadlock for PERSONNEL.employee.
3) We lock PERSONNEL.department for read, and we lock PERSONNEL.employee for read.
4) We do an all-AMPs RETRIEVE step from PERSONNEL.department by way of an all-rows scan with no residual conditions into Spool 2, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated to be 8 rows. The estimated time for this step is 0.11 seconds.
5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to PERSONNEL.employee. Spool 2 and PERSONNEL.employee are joined using a merge join, with a join condition of ("PERSONNEL.employee.EmpNo = Spool_2.MgrNo"). The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated to be 8 rows. The estimated time for this step is 0.07 seconds.
-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0 hours and 0.19 seconds.
In this example, the constraint that governs the join is defined by a subquery predicate and the ORDER BY clause specifies a sorted result.
The EXPLAIN request modifier generates the following response for this request:
EXPLAIN SELECT name, emp_no
FROM employee
WHERE emp_no IN (SELECT emp_no
FROM charges)
ORDER BY name;
Explanation
--------------------------------------------------------------------
1) First, we lock a distinct PERSONNEL."pseudo table" for read on a RowHash to prevent global deadlock for PERSONNEL.charges.
2) Next, we lock a distinct PERSONNEL."pseudo table" for read on a RowHash to prevent global deadlock for PERSONNEL.employee.
3) We lock PERSONNEL.charges for read, and we lock PERSONNEL.employee for read.
4) We do an all-AMPs RETRIEVE step from PERSONNEL.charges by way of an all-rows scan with no residual conditions into Spool 2, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash and the sort key in spool field1 eliminating duplicate rows. The size of Spool 2 is estimated to be 12 rows. The estimated time for this step is 0.15 seconds.
5) We do an all-AMPs JOIN step from PERSONNEL.employee by way of an all-rows scan with no residual conditions, which is joined to Spool 2 (Last Use). PERSONNEL.employee and Spool 2 are joined using an inclusion merge join, with a join condition of ("PERSONNEL.employee.EmpNo = Spool_2.EmpNo"). The result goes into Spool 1, which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated to be 12 rows. The estimated time for this step is 0.07 seconds.
-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0 hours and 0.23 seconds.
The following example demonstrates a recursive query:
EXPLAIN WITH RECURSIVE temp_table (employee_number, depth) AS
( SELECT root.employee_number, 0 as depth
FROM Employee root
WHERE root.manager_employee_number = 801
UNION ALL
SELECT indirect.employee_number, seed.depth+1 as depth
FROM temp_table seed, Employee indirect
WHERE seed.employee_number = indirect.manager_employee_number
AND depth <= 20
)
SELECT employee_number, depth FROM temp_table;
EXPLAIN generates the following report for this request:
Explanation
---------------------------------------------------------------------------
1) First, we lock a distinct PERSONNEL."pseudo table" for
read on a RowHash to prevent global deadlock for PERSONNEL.root.
2) Next, we lock PERSONNEL.root for read.
3) We do an all-AMPs RETRIEVE step from PERSONNEL.root by
way of an all-rows scan with a condition of (
"PERSONNEL.root.manager_employee_number = 801") into
Spool 3 (all_amps), which is built locally on the AMPs. The size
of Spool 3 is estimated with no confidence to be 1 row. The
estimated time for this step is 0.06 seconds.
4) We do an all-AMPs RETRIEVE step from Spool 3 by way of an all-rows
scan into Spool 2 (all_amps), which is built locally on the AMPs.
The size of Spool 2 is estimated with no confidence to be 1 row.
The estimated time for this step is 0.07 seconds.
5) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by
way of an all-rows scan with a condition of ("(DEPTH <= 20)
AND (NOT (EMPLOYEE_NUMBER IS NULL ))") into Spool 4
(all_amps), which is duplicated on all AMPs. The size of
Spool 4 is estimated with no confidence to be 8 rows. The
estimated time for this step is 0.03 seconds.
2) We do an all-AMPs RETRIEVE step from
PERSONNEL.indirect by way of an all-rows scan with a
condition of ("NOT (PERSONNEL.indirect.manager_employee_number IS NULL)")
into Spool 5 (all_amps), which is built locally on the AMPs.
The size of Spool 5 is estimated with no confidence to be 8
rows. The estimated time for this step is 0.01 seconds.
6) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
all-rows scan, which is joined to Spool 5 (Last Use) by way of an
all-rows scan. Spool 4 and Spool 5 are joined using a single
partition hash join, with a join condition of ("EMPLOYEE_NUMBER =
manager_employee_number"). The result goes into Spool 6
(all_amps), which is built locally on the AMPs. The size of Spool
6 is estimated with no confidence to be 3 rows. The estimated
time for this step is 0.08 seconds.
7) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of
an all-rows scan into Spool 3 (all_amps), which is built locally
on the AMPs. The size of Spool 3 is estimated with no confidence
to be 4 rows. The estimated time for this step is 0.07 seconds.
If one or more rows are inserted into spool 3, then go to step 4.
8) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
an all-rows scan into Spool 7 (all_amps), which is built locally
on the AMPs. The size of Spool 7 is estimated with no confidence
to be 142 rows. The estimated time for this step is 0.07 seconds.
9) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 7 are sent back to the user as the result of
statement 1. The total estimated time is 0.38 seconds.
Step 3 indicates the processing of the seed statement inside the recursive query and produces the initial temporary result set.
Steps 4 through 7 correspond to the processing of the recursive statement inside the recursive query and repeat until no new rows are inserted into the temporary result set. Although steps 4 through 7 indicate a static plan, each iteration can produce spools with varying cardinalities; thus, the level of confidence for the spool size in these steps is set to no confidence.
Step 8 indicates the processing of the final result that is sent back to the user.
Assume that a table named main is very large and that its columns named numa, numb, kind, and event are each defined as nonunique secondary indexes.
The request in this example uses these indexes to apply a complex conditional expression.
The EXPLAIN request modifier generates the following response for this request:
EXPLAIN SELECT COUNT(*)
FROM main
WHERE numa = '101'
AND numb = '02'
AND kind = 'B'
AND event = '001';
The response indicates that bit mapping would be used.
Explanation
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
1) First, we lock TESTING.Main for read.
2) Next, we do a BMSMS (bit map set manipulation) step that intersects the following row id bit maps:
1) The bit map built for TESTING.Main by way of index # 12 "TESTING.Main.Kind = ’B’".
2) The bit map build for TESTING.Main by way of index # 8 "TESTING.Main.NumB = ’02’".
3) The bit map built for TESTING.Main by way of index # 16 "TESTING.Main.Event = ’001’".
The resulting bit map is placed in Spool 3. The estimated time for this step is 17.77 seconds.
3) We do a SUM step to aggregate from TESTING.Main by way of index # 4 "TESTING.Main.NumA = ’101’" and the bit map in Spool 3 (Last Use) with a residual condition of ("(TESTING.Main.NumB = ’02’) and ((TESTING.Main.Kind = ’B’) and (TESTING.Main.Event = ’001’))"). Aggregate Intermediate Results are computed globally, then placed in Spool 2.
4) We do an all‑AMPs RETRIEVE step from Spool 2 (Last Use) by way of an all‑rows scan into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated to be 20 rows. The estimated time for this step is 0.11 seconds.
5) 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 a result of statement 1.
In the following BTEQ multistatement request, which is treated as an implicit transaction, the statements are processed concurrently.
In Teradata session mode, the EXPLAIN request modifier generates the following response for this request:
EXPLAIN INSERT Charges (30001, 'AP2‑0004', 890825, 45.0)
; INSERT Charges (30002, 'AP2‑0004', 890721, 12.0)
; INSERT Charges (30003, 'AP2‑0004', 890811, 2.5)
; INSERT Charges (30004, 'AP2‑0004', 890831, 37.5
; INSERT Charges (30005, 'AP2‑0004', 890825, 11.0)
; INSERT Charges (30006, 'AP2‑0004', 890721, 24.5)
; INSERT Charges (30007, 'AP2‑0004', 890811, 40.5)
; INSERT Charges (30008, 'AP2‑0004', 890831, 32.0
; INSERT Charges (30009, 'AP2‑0004', 890825, 41.5)
; INSERT Charges (30010, 'AP2‑0004', 890721, 22.0) ;
Explanation
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
1) First, we execute the following steps in parallel.
1) We do an INSERT into PERSONNEL.charges.
2) We do an INSERT into PERSONNEL.charges.
3) We do an INSERT into PERSONNEL.charges.
4) We do an INSERT into PERSONNEL.charges.
5) We do an INSERT into PERSONNEL.charges.
6) We do an INSERT into PERSONNEL.charges.
7) We do an INSERT into PERSONNEL.charges.
8) We do an INSERT into PERSONNEL.charges.
9) We do an INSERT into PERSONNEL.charges.
10) We do an INSERT into PERSONNEL.charges.
2) 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.
No rows are returned to the user as the result of
statement 2.
No rows are returned to the user as the result of
statement 3.
No rows are returned to the user as the result of
statement 4.
No rows are returned to the user as the result of
statement 5.
No rows are returned to the user as the result of
statement 6.
No rows are returned to the user as the result of
statement 7.
No rows are returned to the user as the result of
statement 8.
No rows are returned to the user as the result of
statement 9.
No rows are returned to the user as the result of
statement 10.
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 a distinct PERSONNEL."pseudo table" for write
on a RowHash to prevent global deadlock for PERSONNEL.employee.
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 a distinct PERSONNEL."pseudo table" for write on a RowHash to prevent global deadlock for PERSONNEL.employee.
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.
In this example three tables t1, t2 and t3, and an AFTER row trigger with t1 as the subject table, are created. The trigger action modifies tables t2 and t3.
The EXPLAIN text for the INSERT operation, which is part of the trigger action, specifically marks the beginning and ending of the row trigger loop. The relevant phrases in the EXPLAIN report are highlighted in boldface type:
The DDL statements for creating the tables and the trigger are as follows.
CREATE TABLE t1(
i INTEGER,
j INTEGER);
CREATE TABLE t2(
i INTEGER,
j INTEGER);
CREATE TABLE t3(
i INTEGER,
j INTEGER);
CREATE TRIGGER g1 AFTER INSERT ON t1
FOR EACH ROW
(
UPDATE t2 SET j = j+1;
DELETE t2;
DELETE t3;
);
The EXPLAIN text reports the steps used to process the following INSERT … SELECT statement:
EXPLAIN INSERT t1 SELECT * FROM t3;
*** Help information returned. 50 rows.
*** Total elapsed time was 1 second.
Explanation
----------------------------------------------------------------
1) First, we lock a distinct EXP_TST1."pseudo table" for write on a RowHash to prevent global deadlock for EXP_TST1.t3.
2) Next, we lock a distinct EXP_TST1."pseudo table" for write on a RowHash to prevent global deadlock for EXP_TST1.t2.
3) We lock a distinct EXP_TST1."pseudo table" for write on a RowHash to prevent global deadlock for EXP_TST1.t1.
4) We lock EXP_TST1.t3 for write, we lock EXP_TST1.t2 for write, and we lock EXP_TST1.t1 for write.
5) We do an all-AMPs RETRIEVE step from EXP_TST1.t1 by way of an all-rows scan with no residual conditions into Spool 3 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 3 by the sort key in spool field1 eliminating duplicate rows. The size of Spool 3 is estimated with low confidence to be 2 rows. The estimated time for this step is 0.03 seconds.
6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 2 (all_amps), which is duplicated on all AMPs. The size of Spool 2 is estimated with no confidence to be 4 rows.
7) We do an all-AMPs JOIN step from EXP_TST1.t3 by way of an all-rows scan with no residual conditions, which is joined to Spool 2 (Last Use). EXP_TST1.t3 and Spool 2 are joined using an exclusion product join, with a join condition of ("((j = EXP_TST1.t3.j) OR (j IS NULL)) AND (((j = EXP_TST1.t3.j) OR (EXP_TST1.t3.j IS NULL)) AND (((i = EXP_TST1.t3.i) OR (EXP_TST1.t3.i IS NULL)) AND ((i = EXP_TST1.t3.i) OR (i IS NULL))))") where unknown comparison will be ignored. The result goes into Spool 1 (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with index join confidence to be 2 rows. The estimated time for this step is 0.03 seconds.
8) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 4 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated with index join confidence to be 2 rows. The estimated time for this step is 0.04 seconds.
9) We do an all-AMPs MERGE into EXP_TST1.t1 from Spool 4 (Last Use).
10) <BEGIN ROW TRIGGER LOOP>
we do an all-AMPs UPDATE from EXP_TST1.t2 by way of an all-rows scan with no residual conditions.
11) We do an all-AMPs DELETE from EXP_TST1.t2 by way of an all-rows scan with no residual conditions.
12) We do an all-AMPs DELETE from EXP_TST1.t3 by way of an all-rows scan with no residual conditions.
<END ROW TRIGGER LOOP> for step 10.
13) We spoil the parser's dictionary cache for the table.
14) We spoil the parser's dictionary cache for the table.
15) 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.
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:
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 ;
*** Help information returned. 20 rows.
*** Total elapsed time was 1 second.
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.