EXPLAIN Request Modifier and Standard Indexed Access - Advanced SQL Engine - Teradata Database

Full-Table Scan Example

This request returns the following EXPLAIN report:

EXPLAIN 
SELECT COUNT(*)
FROM employee
WHERE dept_no = 500
AND salary > 25000
AND yrs_exp >= 3
AND ed_lev >= 12;

Explanation 
--------------------------------------------------------
1) First, we lock PERSONNEL.employee for read on a resereved 
   RowHash to prevent global deadlock.
2) Next, we lock PERSONNEL.employee for read.
3) We do an all-AMPs SUM step to aggregate from PERSONNEL.Employee by
   way of an all-rows scan with a condition of
   ("(PERSONNEL.employee.DeptNo = 500) AND
   ((PERSONNEL.employee.Salary > 25000.00) AND
   ((PERSONNEL.employee.YrsExp >= 3) AND
   (PERSONNEL.employee.EdLev >= 12 )))").
   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 with high confidence to be 1 row (32 bytes). 
   The estimated time for this step is 0.06 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 the result of statement 1.

New terminology in this explanation is defined as follows:

Unique Primary Index Example

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.

SELECT Example With a Condition on a Nonunique Index

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 PERSONNEL.employee for read on a
   RowHash to prevent global deadlock.
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.

SELECT Example With a Condition on a Unique Secondary Index

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.

Indexed Access With Bit Mapping Example

This request returns the following EXPLAIN report:

EXPLAIN 
SELECT COUNT(*)
FROM main
WHERE num_a = '101'    
AND num_b  = '02'    
AND kind  = 'B'    
AND event = '001';

Explanation 
------------------------------------------------------ 
...
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.Num_B = ’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.Num_A = ’101’" and the bit map in Spool
   3 (Last Use) with a residual condition of
   ("(TESTING.Main.Num_B = ’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.
...

New terminology in this explanation is defined as follows: