Large Table/Small Table (LT/ST) joins combine 3 or more small tables 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.
The LT/ST algorithm used by the Optimizer performs these high‑level tasks.
It is important to collect statistics on the following to optimize LT/ST joins.
Consider the following points about LT/ST joins and indexes.
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.