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.
Star Join

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:

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.