How the Optimizer Determines Nested Join Utilization - Analytics Database - Teradata Vantage

Geospatial Data Types
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-08-30
dita:mapPath
qgk1628112272483.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ghz1472251264557
lifecycle
latest
Product Category
Teradata Vantage™
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.