Query Plan Optimization with Star Joins

SAP ASE version 16.0 improves performance for star joins.

A star join is a commonly used data warehouse query that runs against a star schema database, which consists of a large table (also known as a fact table) surrounded by dimension tables. Fact tables typically include two types of columns: one that includes measurements, metrics, or facts about the business process, and another column that includes foreign keys to dimension tables. Dimension tables usually include descriptive attributes. For example, a fact table may include information about the number of hiking books sold by a particular author on a particular day. The corresponding dimension table includes the address, age, and gender of the author.

Star joins join the fact table with one or more dimension tables along the foreign keys. The star join may include filter predicates on the dimension tables (for example, where gender = 'M'), but it contains no joins between the dimension tables.

Snowflake schemas extend star joins by allowing each of the dimension tables to act as local fact tables and join with another set of dimension tables.

This star join joins the Orders fact table with 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

To improve performance for star join query evaluation these changes have been made in version 16.0:

For parallel plans, the bloom filter probe moves below the Xchg operator.
A new star join hint uses abstract plan to help the query processor better detect star join queries.
The final plan of any star join is now forced to use bloom filter under the use fact_table hint.
Hash joins and a join orders are forced based on the selectivities between fact and dimension tables for the star join query under the use fact_table hint. The query processor adjusts the query plan to provide the greatest benefit from the bloom filter.

The query optimizer uses star joins when appropriate. You need not configure SAP ASE to use star joins. However, SAP does recommend that you enable the set join_bloom_filter option and parallel query processing when using star joins.

Note: When you set the optimization goal to allrows_dss, the query processor enables set join_bloom_filter and parallel query processing.

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.
Star Join Query Plans Under the use fact_table Hint
The use fact_table abstract plan hint allows the query processor to chose a parallel hash join plan for the star join query. Parallel plans enable the query processor to push bloom filter probings (which allow for faster joins between dimension and fact tables) below the EXCHANGE operator, further reducing the number of qualifying rows from fact tables.