Star Transformation

A star transformation is another type of query rewrite performed by the Oracle optimizer. The star transformation is performed as a way to significantly improve the performance of queries directed against a standard star schema, which includes a fact table and a large number of dimensions.

To understand how a star transformation works, consider the following query, typical in a data warehouse scenario:

Select SUM(quantity_sold)
  From Sales s, Customers c, Products p, Times t
Where s.cust_id = c.cust_id
And     s.prod_id = p.prod_id
And     s.time_id = t.time_id
And     c.cust_city ='BOSTON'
And     p.product ='UMBRELLA'
And     t.month    ='MAY'
And     t.year        = 2008;

Normally, the optimizer would start out by looking to perform a four-way join in the most optimal way, between the SALES, CUSTOMERS, PRODUCTS, and TIMES tables. Although the dimension tables are much smaller than the SALES fact table, performing a multiple-way join can still be costly, especially since the point of the join is simply to use the Cartesian product of the dimensional join to identify the rows in the fact table for the SUM operation.