Social Icons

Explain Plan

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

KeywordExplanation
Locking Pseudo TableSerial lock on a symbolic table. Every table has one. Used to prevent deadlocks situations between users.
Locking table forIndicates 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 testGuarantees a transaction is not in progress for this user
All AMPs retrieveAll AMPs are receiving the AMP steps and are involved in providing the answer set
By way of an all rows scanRows are read sequentially on all AMPs
By way of primary indexRows are read using the Primary index column(s)
By way of index numberRows are read using the Secondary index – number from HELP INDEX
BMSMSBit Map Set Manipulation Step, alternative direct access technique when multiple NUSI columns are referenced in the WHERE clause
Residual conditionsWHERE clause conditions, other than those of a join
Eliminating duplicate rowsProviding unique values, normally result of DISTINCT, GROUP BY or subquery
Where unknown comparison will be ignoredIndicates 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 joinThe 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 joinRows of one table are matched to the other table on common domain columns after being sorted into the same sequence, normally Row Hash
Product joinRows of one table are matched to all rows of another table with no concern for domain match
ROWID joinA 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 AMPsParticipating rows for the table (normally smaller table) of a join are duplicated on all AMPS
Hash redistributed on all AMPsParticipating 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
SMSSet Manipulation Step, result of an INTERSECT, UNION, EXCEPT or MINUS operation
Last useSPOOL file is no longer needed after the step and space is released
Built locally on the AMPsAs rows are read, they are put into SPOOL on the same AMP
Aggregate Intermediate Results computed locallyThe 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 globallyThe 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.

9 comments:

  1. Hi,
    Nice Post.
    I think the 2 amp retrieve specified for UPI is USI case, right ??
    Also, we can include the PPI case for explains as well. The explain clearly states the number of partitions being leveraged.

    Regards,
    Tarun Trehan
    http://www.allzhere.in

    ReplyDelete
    Replies
    1. Hi Tarun,

      It was Type and corrected now, Thanks :) Will Include PPI aswell..

      Delete
  2. Replies
    1. Thank you so much Praveen :)

      Delete
  3. Oh man,

    Very neat and good. Thanks a lot.

    ReplyDelete
  4. You are my hero,thank you

    ReplyDelete
  5. If you can add UNION, Cartesian Product Join, Exclusion Join and PPI to PPI join.. it would be great..

    ReplyDelete
  6. Great work done brother. Thanks alot

    ReplyDelete