Join Optimization | Teradata Vantage - 17.10 - Large Table/Small Table Joins - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
English (United States)

Definition of Large Table/Small Table Joins

A large table/small table join (LT/ST join) joins 3 or more small tables (or relations), then joins the result with 1 large table. This is the type of join typically made between a large fact table and its smaller dimensional tables in a dimensional modeling schema. In some cases, this can be further optimized to join the small tables (or relations) to the large table as a single-step, n-way join.

The LT/ST algorithm used by the Optimizer performs the following high-level tasks:
  • Looks for the large relation in the set of tables to be joined
  • Analyzes connections to each index
  • Analyzes unindexed cases

About Large Table/Small Table Joins

It is important to collect statistics on the following items to optimize LT/ST joins:
  • All indexes
  • Small table primary [AMP] indexes
  • Columns to be selected, especially if the join is highly selective
  • Join columns, especially if the join to the large table is weakly selective
Consider the following points about LT/ST joins and indexes:
  • Indexes are an important factor in join performance.
  • Consider the choice of indexes.
  • Consider indexes on common-join column sets in large tables.

If the primary index of a large, or fact, table is a composite of elements from the smaller, or dimension, tables, as is generally the case for dimensional modeling where the primary index of the fact table is a composite of the primary indexes of its associated dimension tables, the Optimizer uses a product join on the small tables. With the primary index of the large table, the Optimizer can apply a merge join and not read the entire large table, which is much more efficient use of system resources.

For example, suppose you want to examine the sales of 5 products at 5 stores for a one-week time period. This requires joining the stores table, the week_ending_date table, and the product_list table with the daily_sales table. The following graphic illustrates this join:

Join three tables to a fourth table

Selected portions of the stores table, week_ending_date table and product_list table are product-joined. The result creates the primary index for the daily_sales table. The joined small tables are then joined with the large table, and an answer set is returned. This plan uses significantly fewer system resources and requires less processing time.