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.
- 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
- 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
- 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:
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.