Learn the benefits of choosing necessary columns for optimal performance. Our query in question involves a join between two specified tables:
Part of the Teradata SQL Tuning and Query Rewriting guide.
CREATE SET TABLE TheDatabase. TheTable1
(
REF_PK BIGINT NOT NULL,
COL1 CHAR(20) NOT NULL,
COL2 INTEGER,
...
COL99 CHAR(100)
) PRIMARY INDEX (COl1);
Example scope. These schemas and EXPLAIN extracts are abbreviated historical material. Expand omitted columns, qualify both table names for the chosen database, and inspect a fresh plan before execution; the displayed historical estimates are not measured current performance.
CREATE MULTISET TABLE TheDatabase. TheTable2
(
PK BIGINT NOT NULL,
COL1 CHAR(20) NOT NULL,
COl2 INTEGER,
...
COL20 INTEGER
) PRIMARY INDEX (COL1)
INDEX (PK)
;
Both displayed tables have PI (COL1), while the join is on TheTable1.REF_PK = TheTable2.PK. A NUSI on TheTable2.PK may cover the required right-side key; join geography depends on those actual columns and the plan.
Our initial query retrieves all columns from both tables:
Explain
SELECT *
FROm
TheDatabase. TheTable1 t01
INNER JOIN
TheTable2 t02
ON
t01.REF_PK = t02.PK
1) First, we lock a distinct TheDatabase.” pseudo table” for read on a
RowHash to prevent global deadlock for TheDatabase.t01.
2) Next, we lock Q_DBS_SGL_WORK.TheTable2 in view TheDatabase. TheTable2
for access, and we lock TheDatabase.t01 for read.
3) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Q_DBS_SGL_WORK.TheTable2
in view TheDatabase. TheTable2 by way of an all-rows scan with
no residual conditions into Spool 2 (all_amps) (compressed
columns allowed) fanned out into 10 hash join partitions,
which is redistributed by the hash code of (
TheDatabase. TheTable2.PK) to all AMPs. The size of
Spool 2 is estimated with high confidence to be 16,206,188
rows (1,798,886,868 bytes). The estimated time for this step
is 7.86 seconds.
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 book2) We do an all-AMPs RETRIEVE step from TheDatabase.t01 by way
of an all-rows scan with no residual conditions into Spool 3
(all_amps) (compressed columns allowed) fanned out into 10
hash join partitions, which is redistributed by the hash code
of (TheDatabase.t01.REF_PK) to all AMPs. The input table
will not be cached in memory but is eligible for
synchronized scanning. The result spool file will not be
cached in memory. The size of Spool 3 is estimated with high
confidence to be 25,914,926 rows (17,259,340,716 bytes). The
estimated time for this step is 1 minute and 13 seconds.
4) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to Spool 3 (Last Use) by way of an
all-rows scan. Spool 2 and Spool 3 are joined using a hash join
of 10 partitions, with a join condition of (“REF_PK = PK”).
The result goes into Spool 1 (group_amps), which is built locally
on the AMPs. The result spool file will not be cached in memory.
The size of Spool 1 is estimated with low confidence to be
36,760,336 rows (30,731,640,896 bytes). The estimated time for
this step is 24.07 seconds.
5) 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 1 minute and 37 seconds.
The execution plan above indicates that both tables have their rows redistributed based on the hash code of the join column. No WHERE condition is applied. Therefore, a full table scan is necessary. The retrieve step estimations are reported with high confidence.
Improving The Teradata Column List
The second query selects a single column.
Explain SELECT
t01.COL1
FROm TheDatabase. TheTable1 t01
INNER JOIN
TheTable2 t02
ON
t01.REF_PK = t02.PK
1) First, we lock a distinct TheDatabase.” pseudo table” for read on a
RowHash to prevent global deadlock for TheDatabase.t01.
2) Next, we lock TheDatabase. TheTable2 in view TheDatabase. TheTable2
for access, and we lock TheDatabase.t01 for read.
3) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from TheDatabase. TheTable2
in view TheDatabase. TheTable2 by way of a traversal of index #
8 without accessing the base table with no residual
conditions into Spool 2 (all_amps) fanned out into 2 hash
join partitions, which is redistributed by the hash code of (
TheDatabase. TheTable2.PK) to all AMPs. The size of
Spool 2 is estimated with high confidence to be 16,206,188
rows (275,505,196 bytes). The estimated time for this step
is 1.62 seconds.
2) We do an all-AMPs RETRIEVE step from TheDatabase.t01 by way
of an all-rows scan with no residual conditions into Spool 3
(all_amps) fanned out into 2 hash join partitions, which is
redistributed by the hash code of (
TheDatabase.t01.REF_PK) to all AMPs. The input table
will not be cached in memory, but it is eligible for
synchronized scanning. The size of Spool 3 is estimated with
high confidence to be 25,914,926 rows (647,873,150 bytes).
The estimated time for this step is 8.72 seconds.
4) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to Spool 3 (Last Use) by way of an
all-rows scan. Spool 2 and Spool 3 are joined using a hash join of 2 partitions, with a join condition of (“REF_PK = PK”).
The result goes into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with low confidence
to be 36,760,336 rows (1,066,049,744 bytes). The estimated time
for this step is 1.85 seconds.
5) 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 10.57 seconds.
The second query’s execution plan has undergone multiple enhancements.
(1) While both cases retrieve 16,206,188 from “TheTable2”, the spool is much smaller as only the necessary columns are included (275,505,196 bytes instead of 1,798,886,868 bytes). This also applies to the spool of “TheTable1” (647,873,150 bytes instead of 17,259,340,716 bytes).
The join step only needs two hash join partitions rather than 10.
A covering index can satisfy the right-side columns available in that index, even if those indexed columns are projected. Selecting a noncovered right-side column can require base-row access.
Observe the decrease in resource consumption below:
| NumResultRows | TotalIOCount | AMPCPUTime | Spool Usage | |
| One Column | 47.314.937,00 | 118.289,00 | 120,63 | 2.367.786.496,00 |
| All Columns | 47.314.937,00 | 472.106,00 | 522,03 | 17.860.372.480,00 |
The second query requires significantly fewer resources: specifically, it necessitates 75% less IO, 77% fewer CPU seconds, and 87% less spool space.
Abbreviated historical schema/plan: expand ellipses before execution. Identifiers are corrected to REF_PK, PK and COL1. An adapted integer fixture executed; the historical resource percentages were not reproduced.
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