Star Join Hint

SAP ASE version 16.0 introduces the use fact_table abstract plan hint, which specifies the central fact table in a star join query, and triggers special query plan optimization strategies for the query.

The syntax is:
PLAN '(use fact_table fact_table_name_or_alias_name)'
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 query processor ignores the use fact_table hint unless the query meets these conditions: