16.10 - EXPLAIN Request Modifier and Standard Indexed Access - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

The EXPLAIN request modifier is useful in determining whether the indexes defined for a table are properly defined, useful, and efficient.

As illustrated in other join examples, EXPLAIN identifies any unique indexes that might be used to process a request.

When conditional expressions use nonunique secondary indexes, EXPLAIN also indicates whether the data rows are retrieved using spooling or bit mapping.

This feature is illustrated in the following examples. Compare the two requests and their corresponding EXPLAIN descriptions.

Note that in the first request the table is small (and that dept_no, salary, yrs_exp, and ed_lev have been defined as separate, nonunique indexes), so a full-table scan is used to access the data rows.

In the second request, however, the table is extremely large. Because of this, the Optimizer determines that bit mapping of the subtable rowIDs is the faster retrieval method.

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.
2) Next, we do a 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.
3) 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 1 rows. The estimated time
   for this step is 0.06 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.

New terminology in this explanation is defined as follows:

Phrase Definition
SUM step to aggregate The table is searched row by row, the qualifying rows are counted for each AMP on which they were found, and each count is held in a local spool.
computed globally The final computation involves all the intermediate spool data.

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 
------------------------------------------------------ 
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.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.
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.

New terminology in this explanation is defined as follows:

Phrase Definition
A BMSMS (bit map set manipulation step) that intersects the following Row-Id bit maps:

The bit map built for … by way of index # n

The bit map built for … by way of index # n

On each nonunique secondary index subtable, each data rowID is assigned a number from 0-32,767. This number is used as an index into a bit map in which the bit for each qualifying row is turned on.

BMSMS indicates that the intersection of sets of qualifying rows is computed by applying the logical AND operation to the bitmap representation of the sets.

residual condition Selected rows are further qualified by one or more conditional expressions.