Introduction to the Teradata Nested Join
Teradata can select from several different join methods, including Merge Join, Hash Join, Product Join, and Rowkey-Based Merge Join or Sliding Window Merge Join for PPI tables.
The Teradata nested join is infrequently used due to its specific prerequisites.
As we will see in a moment, the nested join is particularly suitable for tactical workloads.
If you are interested in tactical workload tuning, you can learn more about it here: Tactical Workload Tuning with Teradata.
What is the Teradata Nested Join?
Teradata’s Nested Join is an economical method of joining as it utilizes indexes for access.
Teradata requires the fulfillment of certain conditions in order to employ nested joins:
- An “equi-join” is carried out.
- There is a restriction on the left table (WHERE condition), and a UPI, USI, or NUPI with as many different values as possible can be used for the retrieving step.
- On the right table, an index is defined that includes all join columns. It can be a UPI, NUPI, USI, NUSI, or global join index (a global join index contains the ROWID of the base table on which it is defined)
Teradata Nested Join Example
This example is ideal for a nested join.
The table on the left contains a UPI.
CREATE TABLE LEFT_TABLE ( PK INTEGER NOT NULL, ALT_PK INTEGER NOT NULL, COL1 INTEGER ) UNIQUE PRIMARY INDEX (COL1);
CREATE TABLE RIGHT_TABLE ( PK INTEGER NOT NULL, ALT_PK INTEGER NOT NULL ) UNIQUE PRIMARY INDEX (ALT_PK);
Executing the following query:
SELECT * FROM LEFT_TABLE l INNER JOIN RIGHT TABLE r ON l.ALT_PK = r.ALT_PK WHERE COL1=100;
Upon closer examination, it becomes evident that the table definitions and SQL statements are well-suited for a nested join.
- The UPI makes the restriction on the left table
WHERE COL1 = 100 - It’s an equijoin
l.ALT_PK = r.ALT_PK - The join is done via the columns defined on the left table as an index (in this case, the UPI).
(l.ALT_PK = r.ALT_PK)
The execution plan indicates that the optimizer employs a nested join to execute the SQL query.
First, we do a single-AMP JOIN step from l by way of the unique primary index "l.COL1 = 100" with no residual conditions, which is joined to all partitions of r by way of the primary index "r.ALT_PK = l.ALT_PK" with no residual conditions. l and r are joined using a nested join, with a join condition of ("(1=1)"). The result goes into Spool 1 (one-amp), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 1 row (41 bytes).The join does not necessitate redistribution of rows or a full table scan, despite utilizing columns that are not primary indexes for either table. However, if the optimizer opted for a merge or hash join, redistributing rows would be obligatory through copying or redistribution.
Accessing only one row requires only one AMP, making it highly efficient.
Various versions of nested join demonstrate exceptional performance. Our previous demonstration focused on the Local Nested Join. We will now explore the unique characteristics of each variation.
The Local Nested Join
Previously, we mentioned that the left table is constrained by a UPI, NUPI, or USI with a WHERE clause.
The Local Nested Join is more narrowly defined as it exclusively applies to UPI or USI. Moreover, only UPI, NUPI, and USI are permitted on the right table, with NUSI being strictly prohibited. The rationale behind this will be elaborated on in the subsequent section.
With the UPI or USI on the left table guaranteeing the selection of only one row under the WHERE condition, the subsequent algorithm can be implemented.
- Calculate the rowhash over the joined columns of the left table
- Based on this rowhash, send a message to the AMP which owns the rows of the right table
- The AMP holding the rows of the right table returns these rows to the calling AMP
- The calling AMP assembles the final rows and returns them to the resulting spool
As previously stated, the left table’s index need only be unique. Thus, our previous example’s query would also function with the following arrangement:
CREATE TABLE LEFT_TABLE ( PK INTEGER NOT NULL, ALT_PK INTEGER NOT NULL, COL1 INTEGER ) PRIMARY INDEX (PK); UNIQUE INDEX (COL1); --> USI works as well !
CREATE TABLE RIGHT_TABLE ( PK INTEGER NOT NULL, ALT_PK INTEGER NOT NULL ) UNIQUE PRIMARY INDEX (ALT_PK);
SELECT * FROM LEFT_TABLE l INNER JOIN RIGHT TABLE r ON l.ALT_PK = r.ALT_PK WHERE COL1=100;
First, we do a two-AMP JOIN step from l by way of unique index # 4 "l.COL1 = 100" with no residual conditions, which is joined to all partitions of r by way of the primary index "r.ALT_PK = l.ALT_PK" with no residual conditions. l and r are joined using a nested join, with a join condition of ("(1=1)"). The result goes into Spool 1 (one-amp), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 1 row (41 bytes).The only difference is USI usage (by way of index # 4) instead of UPI access.
The Remote Nested Join
The Remote Nested Join is applicable in the following scenarios:
- The index on the left table used in the WHERE condition is not unique OR
- The right table index containing the joined columns is a NUSI
In this case, the following algorithm is used:
- The left table rows are redistributed by the join column rowhash or duplicated to all AMPs.
The required distribution depends on the index type available on the right table.
Whenever the right table index is a NUSI, row duplication to all AMPs is needed. The NUSI index rows are not rowhash distributed, and matching rows could be available on any AMP.
Any other right table index will redistribute the left table rows by hashing on the join columns. - A join between the left table rows and the right table index is executed.
The base table ROWIDs are extracted from the index and copied into an intermediate spool. - The intermediate spool is the new left table for a second join step, the so-called RowId Join.
- The extracted ROWIDs are used to access the base table rows of the right table.
Depending on the kind of right table index, this step may need to access the base table rows on a different AMP (USI) or on the same AMP (NUSI – NUSI rows are always co-located together with their base table rows on the same AMP)
This is an example of a Remote Nested Join where the NUSI is defined on the join columns of the right table.
CREATE TABLE LEFT_TABLE ( PK INTEGER NOT NULL, ALT_PK INTEGER NOT NULL, COL1 INTEGER ) UNIQUE PRIMARY INDEX (COL1);
CREATE TABLE RIGHT_TABLE ( PK INTEGER NOT NULL, ALT_PK INTEGER NOT NULL ) UNIQUE PRIMARY INDEX (PK ) INDEX (ALT_PK);
SELECT *
FROM
LEFT_TABLE l
INNER JOIN
RIGHT TABLE r
ON
l.ALT_PK = r.ALT_PK
WHERE
COL1=100;3) We do a single-AMP RETRIEVE step from l by way of the unique primary index "l.COL1 = 100" with no residual conditions into Spool 2 (all_amps), which is duplicated on all AMPs. Then we do a SORT to order Spool 2 by the hash code of (l.ALT_PK). The size of Spool 2 is estimated with high confidence to be 72 rows (1,800 bytes). The estimated time for this step is 0.00 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 r by way of a traversal of index # 4 without accessing the base table extracting row ids only. Spool 2 and r are joined using a nested join, with a join condition of ("ALT_PK = r.ALT_PK"). The result goes into Spool 3 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 3 by field Id 1. The size of Spool 3 is estimated with low confidence to be 1 row (35 bytes). The estimated time for this step is 0.02 seconds. 5) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an all-rows scan, which is joined to r by way of an all-rows scan with no residual conditions. Spool 3 and r are joined using a row id join, with a join condition of ("(1=1)"). 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 1 row (41 bytes). The estimated time for this step is 0.04 seconds.The execution plan reveals two physical joins.
Spool 2 and r are joined using a nested join
Spool 3 and r are joined using a row id join
Nested Join Performance: Local or Remote?
The Remote Nested Join is less efficient as it requires the extraction of ROWIDs and entails two physical joins.
Given the limited number of rows, performance is insignificant unless the WHERE condition is highly restrictive, in which case a nested join will not be optimized.
Conclusion
The Teradata Nested Join is particularly suited for tactical workloads as it predominantly retrieves a single or minimal row, resulting in the involvement of only one or a small number of AMPs.
Analyzing requests for the suitability of nested joins and creating corresponding indexes (NUSI, USI) is a logical step.
It is possible to create WHERE conditions enabling nested joins without altering the significance of the SQL statement.
Teradata Nested Join Without WHERE Condition
Not all queries necessitate a WHERE condition for the left table.
Assuming the left table has a limited number of rows, the Teradata Optimizer can perform a Remote Nested Join even without a WHERE condition on said table.
The Teradata Optimizer may consider using a Nested join when the estimated number of rows in the left table is small, typically just a few.
