What is the Teradata Explain Statement?
Optimizing performance can be challenging, often requiring significant effort to identify the root issues. Thankfully, Teradata provides the EXPLAIN statement as a robust tool for assessing the execution of SQL statements. In Teradata, SQL statements are processed in a series of distinct tasks that correspond to specific components of the statement, such as retrieving rows, joining tables, or aggregating results. By utilizing our knowledge of how Teradata handles these tasks and scrutinizing the execution plan furnished by EXPLAIN, we can swiftly pinpoint weak points within a query and utilize this data to enhance performance.
The EXPLAIN statement can be intimidating for novices. At first, the result may seem disorderly and incomprehensible. Nonetheless, deciphering the steps is easier than it seems. We must evaluate each step’s effect on performance and whether Teradata has made ideal selections, such as choosing an inappropriate join strategy.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookOur analysis will entail a thorough evaluation of every Execution Plan element, identification of crucial operations, and support in identifying potential issues.
EXPLAIN describes estimated retrieval, join, aggregation and other operations; it does not measure execution. Steps obey their dependencies, and independent operations may run in parallel. Compare estimates with actual row counts and logged resource usage.
Decoding the Plan Steps
Teradata’s blocks correspond to distinct operations, including fetching rows, joining tables, and conducting aggregations. In addition to the operation category, we collect information on the number of AMPs involved, including All-AMPs, Single-AMP, or Group-AMP.
Each block’s EXPLAIN output will show something akin to:
AMPs retrieve, and single AMP retrieve steps, AMPs join, and single AMP join steps, and an AMPs step for aggregation.
By identifying the various types of operations, we can analyze and obtain detailed information about each one.
Some plans require redistribution or sorting, but neither is a prerequisite for every transaction or join. The selected access and join methods determine the preparation work.
Join Preparation
Join inputs must be available where the join executes. Already colocated inputs can avoid redistribution; other plans redistribute or duplicate rows as needed.
The EXPLAIN result for row movement might show something like:
duplicated on all AMPs, redistributed by the hash code of (new hash column(s)), redistributed by rowkey, etc.
The EXPLAIN output’s sorting section will show something akin to this:
Sort according to hash code, order by row hash, partition by rowkey, etc.
Row retrieval strategy
A vital aspect of all operations is retrieving rows from disks. The Teradata Database offers various access paths to obtain data, such as full table scans, primary and secondary index access, and more.
The EXPLAIN output for row retrieval may contain the following information:
by way of an all-row scan, by way of a rowhash match scan, by way of the primary index, by way of the hash value, etc.
Join Type
The EXPLAIN output will provide information on the selected join strategy if the operation is a join.
using a product join, a single partition hash join, a merge join, a rowkey-based merge join, etc.
Confidence
Each block denotes the level of confidence in its corresponding rows. Although desirable, attaining a high level of confidence is not always possible.
The EXPLAIN output will display a similar format:
high confidence, low confidence, no confidence, and index join confidence
These are common EXPLAIN elements, not a quantified percentage of everything a plan may contain.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
Hi Roland,
Nice article. Could you please also explain product join indicator ie. what it means and how it affects query performance. What causes high PJI for a query. Does only product join is cause high PJI or there are other factors that cause high PJI?
Thank you