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