Nested Join Examples | Join Planning/Optimization | Teradata Vantage - 17.10 - Nested Join Examples - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Circumstances for the Nested Join Examples

This section provides the partial EXPLAIN outputs for the same two-table join under the following circumstances:
  • No nested join
  • A nested join where the USI for one table is joined on a column from the other table.

Table Definitions

For these examples, assume the following table definitions:

Table Name Column 1 Name Column 2 Name Primary Index Name Unique Secondary Index Name Number of Rows
table_1 NUPI        y_1 NUPI none defined                   2
table_2 NUPI        USI_2 NUPI          USI_2     1,000,000

Test Query

The following request is tested against this database, both using and without using nested join:

SELECT *
FROM table_1,table_2
WHERE y_1 = USI_2;