Star Join Hint

The syntax is:

PLAN '(use fact_table fact_table_name_or_alias_name)'

where:

• fact_table – indicates that the query includes a fact table from a star join.
• fact_table_name_or_alias_name – specifies the name or alias name used for the central fact table. In this example, F is the alias name:

   use fact_table 'F'

For example, this joins the Orders fact table to the Time, Customer, and Location dimension tables:

select C.dattr, sum(O.measure1), count(*)
from Orders O
join Time T on O.key_dim1 = T.key_col
join Customer C on O.key_dim2 = C.key_col
join Location L on O.key_dim3 = L.key_col
where T.dattr between 1 and 1000
and C.dattr between 1001 and 2000
and L.dattr between 901 and 1900
group by C.dattr
plan '(use fact_table O)'

The use fact_table hint makes these changes to the query processor's star query detection algorithm and plan generation:

• The detection algorithm starts with the fact_table_alias_name. This table is always the center of a snowflake schema.
• Allows or clauses.
• Allows joins between the fact table and the dimension tables to be multicolumn equijoins. You need not include an explicit constraint definition between primary and foriegn keys.
• If the query processor cannot locate a good plan for the nested loop join while it generates the query plan for the star join (typically due to missing indexes or less restrictive selectivities), it choses a hash join plan with a forced join order. The join order is based on the selectivities between the dimension and the fact tables. The higher the selectivity (that is, a lower ratio value), the closer the dimension tables are in that join order to the fact table.

The query processor ignores the use fact_table hint unless the query meets these conditions:

• The query has at least three tables.
• The query has no outer joins or nested subqueries.
• The hinted fact table is the largest joining table within its query block.
• The dimension tables and the fact table are joined with equijoins.
• There are no join predicates between the dimension tables.