17.10 - How the Optimizer Determines Nested Join Utilization - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Geospatial Data Types

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1181-171K
Language
English (United States)
The Teradata Optimizer weighs the costs against the benefits of using a nested join strategy for satisfying a query. The Optimizer uses factors such as the following to judge whether to construct a nested join:
  • The cardinality and size of the duplicated table.

    If there is a geospatial index defined on only one of the tables involved in the join operation, the Optimizer will duplicate the non-indexed table.

    If there is a geospatial index on both tables, the optimizer will calculate the costs of duplicating each table and using the index of the other table, and choose the most cost-effective combination.

  • The cardinality and size of the row ID spool.

    The number of rows populating the row ID spool depends on the selectivity of the join.

    The row size of a row lying within the row ID spool depends on the number of columns belonging to the duplicated table, which were referenced in the query.

For more information on how the Teradata Optimizer chooses execution paths for queries, see Teradata Vantageā„¢ - SQL Request and Transaction Processing, B035-1142.