Performance Guide 2

Explain Plan

What is EXPLAIN?
EXPLAIN statement is the Parsing Engines (PE’s) plan to the AMPs.A step-by-step
analysis of the queries being executed in the database.
The EXPLAIN facility provides an "English" translation of the plan the SQL Optimizer develops to service a request.

The EXPLAIN is an excellent facility for the following activities:
• Determining Access Paths
• Validating the use of indexes
• To determine locking profiles
• Showing Triggers and Join Index access
• Estimate the query runtime

Sample Syntax for Explain is.
EXPLAIN Select Emp_ID, Emp_Name from Emp_Table ;

If we pass an explain command in front of a query statement or Simply Press F6 key, there are a several estimated confidence messages that the optimizer will relay to the user is

High Confidence - Statistics available on an Index or Column.
Low Confidence - Random sampling of INDEX, or Statistics available butAND/OR condition exists.
No Confidence - Random sampling based on AMP row count. No statistics are collected.

Understanding the EXPLAIN Terminology you should know the Keywords.

Keyword	Explanation
Locking Pseudo Table	Serial lock on a symbolic table. Every table has one. Used to prevent deadlocks situations between users.
Locking table for	Indicates that an ACCESS, READ, WRITE, or EXCLUSIVE lock has been placed on the table
Locking rows for	Indicates that an ACCESS, READ, or WRITE, lock is placed on rows read or written
Do an ABORT test	Guarantees a transaction is not in progress for this user
All AMPs retrieve	All AMPs are receiving the AMP steps and are involved in providing the answer set
By way of an all rows scan	Rows are read sequentially on all AMPs
By way of primary index	Rows are read using the Primary index column(s)
By way of index number	Rows are read using the Secondary index – number from HELP INDEX
BMSMS	Bit Map Set Manipulation Step, alternative direct access technique when multiple NUSI columns are referenced in the WHERE clause
Residual conditions	WHERE clause conditions, other than those of a join
Eliminating duplicate rows	Providing unique values, normally result of DISTINCT, GROUP BY or subquery
Where unknown comparison will be ignored	Indicates that NULL values will not compare to a TRUE or FALSE. Seen in a subquery using NOT IN or NOT = ALL because no rows will be returned on ignored comparison.
Nested join	The fastest join possible. It uses a UPI to retrieve a single row after using a UPI or a USI in the WHERE to reduce the join to a single row.
Merge join	Rows of one table are matched to the other table on common domain columns after being sorted into the same sequence, normally Row Hash
Product join	Rows of one table are matched to all rows of another table with no concern for domain match
ROWID join	A very fast join. It uses the ROWID of a UPI to retrieve a single row after using a UPI or a USI in the WHERE to reduce the join to a single row.
Duplicated on all AMPs	Participating rows for the table (normally smaller table) of a join are duplicated on all AMPS
Hash redistributed on all AMPs	Participating rows of a join are hashed on the join column and sent to the same AMP that stores the matching row of the table to join
SMS	Set Manipulation Step, result of an INTERSECT, UNION, EXCEPT or MINUS operation
Last use	SPOOL file is no longer needed after the step and space is released
Built locally on the AMPs	As rows are read, they are put into SPOOL on the same AMP
Aggregate Intermediate Results computed locally	The aggregation values are all on the same AMP and therefore no need to redistribute them to work with rows on other AMPs
Aggregate Intermediate Results computed globally	The aggregation values are not all on the same AMP and must be redistributed on one AMP, to accompany the same value with from the other AMPs

Below are the some examples

Example for FULL TABLE SCAN    

EXPLAIN SELECT * FROM Emp_Table;

1. First, we lock a distinct SQL_CLASS."pseudo table" for read on a RowHash to prevent global deadlock for SQL_CLASS.Emp_Table.

2. Next, we lock SQL_CLASS.Emp_Table for read.

3. We do an all-AMPs RETRIEVE step from SQL_CLASS.Emp_Table by way of an all-rows scan 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 6 rows (342 bytes). The estimated time for this step is 0.03 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.03 seconds.

If you see all-AMPs RETRIEVE by way of an all-rows scan in your plan, that means teradata is doing a Full Table Scan. So it is reading every row in the table.


Explain Example – Unique Primary Index (UPI)
 EXPLAIN SELECT * FROM Emp_Table WHERE Emp_No = 123000;

 1. First, we do a single-AMP RETRIEVE step from SQL_CLASS.Emp_Table by way of the unique primary index "SQL_CLASS.Emp_Table.Emp_No = 123000" with no residual conditions. The estimated time for this step is 0.01 seconds.
-> The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.01 seconds.

In the Emp_table, Emp_no is the Unique Primary Index. So USI used in where clause. So it gives Single-AMP retrieve by way of the Unique Primary Index. This is the fastest query!

Explain Example – Non-Unique Primary Index (NUPI)
 EXPLAIN SELECT * FROM Sales_Table WHERE Product_ID = 1000;

 1. First, we do a single-AMP RETRIEVE step from SQL_CLASS.Sales_Table by way of the primary index"SQL_CLASS.Sales_Table.Product_ID = 1000" with no residual conditions into Spool 1 (one-amp), which is built locally on that AMP. The size of Spool 1 is estimated with low confidence to be 2 rows (66 bytes). The estimated time for this step is 0.02 seconds.
-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.02 seconds.

Explain Example – Unique Seconday Index (USI)
Explain SELECT * FROM Emp_Table WHERE
First_name="Sachin" and
Last_name=""tendulkar"
1. First, we do a two-AMP RETRIEVE step from SQL_CLASS.Emp_Table by way of unique index # 12 "SQL_CLASS.Emp_Table.Last_name = 'tendulkar ' , SQL_CLASS.Emp_Table.First_name = 'Sachin'" with no residual conditions. The estimated time for this step is 0.01 seconds.
->The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.01 seconds.

Explain Example – Redistributed to All-AMPs
 
  EXPLAIN SELECT Emp.*, Dept**
         FROM Emp_Table as Emp
           INNER JOIN
          Department_Table as Dept
          ON Emp.Dept_No = Dept.Dept_No;

 4. We do an all-AMPs RETRIEVE step from SQL_CLASS.Emp by way of an all-rows scan with a condition of ("NOT (SQL_CLASS.Emp.Dept_No IS NULL)") into Spool 2 (all_amps), which is redistributed by the hash code of (SQL_CLASS.Emp.Dept_No) to all 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 6 rows (294 bytes). The estimated time for this step is 0.01 seconds.
 
5 We do an all-AMPs JOIN step from SQL_CLASS.Dept by way of a RowHash match scan, which is joined to Spool 2 (Last Use) by way of a RowHash match scan. SQL_CLASS.Dept and Spool 2 are joined using a merge join, with a join condition of ("Dept_No = SQL_CLASS.Dept.Dept_No").