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.
|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
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
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").
Hope you understood about EXPLAIN.