Merge Joins and Performance - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

Merge Joins and Nested Join

In a large join operation, a merge join requires less I/O and CPU time than a nested join. A merge join usually reads each block of the inner table only once, unless a large number of hash collisions occur.

A nested join performs a block read on the inner table for each outer row being evaluated. If the number of rows selected from the outer table is large, this can cause each block of the inner table to be read multiple times.

Merge Join with Covering NUSI

When large outer tables are being joined, a merge join of a table with a covering index of another table can realize a significant performance improvement.

The Optimizer considers a merge join of a base table with a covering NUSI, which gives the Optimizer an additional join method and costing estimate to choose from.

Logging Problematic Queries

You can log problematic queries in several ways. See Logging Problematic Queries.