Explain Plan in Teradata

Explain plan is the step-by-step English translation of a query plan generated by the parsing engine. Explain plan can give you a clear picture the way optimizer will execute a query.

It is a good practice to analyze the explain plan for a newly developed query before running it in the Teradata system. Explain plan can be obtained by 2 ways. First is by adding an “EXPLAIN” keyword before any queries and Second is simply pressing F6 from the keyboard.

Explain plan is very useful to analyze the performance issue of a query as it breaks down any complex query into lowest level. It’s provides a lot of information like-

• Access path – If data is fetching using full table scan or using any index like primary index path, secondary index path or any other kind.
• Confidence level – If the optimizer is able to use the statistics or any statistics is missing.
• Joins information – What kind of join is going to happen.
• Time estimation – Provides estimated of query completion time.

Below is the explain plan of a simple inner join query and we can divide terminologies into few group-

Locking information:-
Pseudo table lock, Read Lock

Row retrieval strategy:-
all-rows scan, Row-hash match scan

Join Preparation information:-
redistributed by the hash code, Row hash match scan

Joins Information:-
Merge join

Confidence Level information:-
low confidence, index join confidence

Amps involvement information:-
group_amps

Time & Size estimation information:-
96 rows (2,784 bytes), 0.01 seconds, 0.04 seconds etc.

From the above explain plan of a simple query, we can see some terms like all-rows scan, group_amps, build locally, low confidence, etc. It is very difficult for a newbie to understand all the terms. We will discuss very frequent explain terminology below.

Locking Information:-

Explain plan provides information about locking, which is placed by the Teradata lock manager on the database objects during query processing. This lock can be like Pseudo table which is placed to avoid global deadlock conditions, read, write, access and exclusive lock.

You can read more about Teradata Locks.

Row retrieval Strategy:- 

From explain plan you will get the information about how Teradata will retrieve the rows from the disks. As we know there may be several access paths to fetch data. Based on the availability of the index, Teradata may fetch rows by full table scan, using primary index, using secondary index or any other access path.

In the above example, you can see Teradata decided to retrieve rows from table emp by full table scan and retrieved rows from the department table using a RowHash match. Here RowHash match scan means one row is retrieved from the first table; hash code of that row is used to locate the row from the other table.

Join Preparation:-

As all the AMP work independently, they can’t access other AMP data directly. So for join processing rows should be in the same AMP. Teradata decides to redistribute, duplicate to bring the rows to be joined on the same AMP.

Join information:-

In case of join operation, explain plan will show you what kind of join operation is chosen by the optimizer base on the situation.

You will see something like product join, single partition hash join, merge join etc.

Confidence Level:-

In the explain of a query, you will find something like high confidence, low confidence, no confidence. We will see below how the optimizer generate these information.

High Confidence – Optimizer estimate the explain steps with high confidence if below condition matches

• find statistics on index and certain column.
• only one selection criteria is there in the query.

Low Confidence – Statistics are existing on the index but AND/OR condition are there in the query.

No confidence –  Statistics not found in the step, finds AND condition on non-index columns.

Join Index confidence – Unique index is used or foreign key relationship between the two tables.

AMPs involvement information:-

During any kind of operation like retrieving rows, joining tables, aggregation you will get information about how many  amps take part in that operation. Depends on the work, it can be single amp, group amp or all amps.

In the Explain plan you will see terminology like All-AMPs retrieve step, group_amps, Single-AMP retrieve step, All-AMP join step, Single-AMP join step etc. The operation group amp means more than 1 but fewer than all AMPs will participate.

Time & Size estimation information:-

Explain plan also provides the information about estimated row counts and estimated time to complete a particular step and query.

You may be interested in:-

Primary Index in Teradata

Join Index in Teradata