Status: In Progress
Affects Version/s: 3.0.0
Fix Version/s: None
This transformation detects RI joins and eliminates the parent/PK table if none of its columns, other than the PK columns, are referenced in the query.
Typical examples that benefit from this rewrite are queries over complex views.
View using TPC-DS schema:
The view returns customer purchases made in year 2002. It is a join between fact table store_sales and dimensions customer, item, store, and date. The tables are joined using RI predicates.
If we write a query that only selects a subset of columns from the view, for example, we are only interested in the items bought and not the stores, internally, the Optimizer, will first merge the view into the query, and then, based on the primary key – foreign key join predicate analysis, it will decide that the join with the store table is not needed, and therefore the store table is removed.
Internal query after view expansion:
Internal optimized query after join elimination:
The join with store table can be removed since no columns are retrieved from the table, and every row from the store_sales fact table will find a match in store based on the RI relationship.