PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

Teradata Explain

When developing queries, always perform Teradata Explain on the query before you run it.Explains can give a lot of information the way optimizer will execute a query.To perform an Teradata Explain , simply add the explain keyword prior to your select/ insert/ update/ delete statement and execute it.

The Explain statement is used to aid in identifying potential performance issues, it analyses the SQL and breaks it down into its low level process. Unfortunately the output can be very difficult to understand for an untrained person, but there are some points to recognize: Confidence Level and Product Joins.

Below are the terminology  which needs to be understood for query execution plan :

Explain Terminology

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