STAR TRANSFORMATION

An extension of the use of subqueries as an alternative to a join is to enable the star
transformation often needed in data warehouse applications. Consider a large table
recording sales. Each sale is marked as being of a particular product to a particular
buyer through a particular channel. These attributes are identified by codes, used as
foreign keys to dimension tables with rows that describe each product, buyer, and
channel. To identify all sales of books to buyers in Germany through Internet orders,
one could run a query like this:

select … from sales s, products p, buyers b, channels c
where s.prod_code=p.prod_code
and s.buy_code=b.buy_code
and s.chan_code=c.chan_code
and p.product=’Books’
and b.country=’Germany’
and c.channel=’Internet’;

This query uses the WHERE clause to join the tables and then to filter the results.
The following is an alternative query that will yield the same result:

select … from sales
where prod_code in (select prod_code from products where product=’Books’)
and buy_code in (select buy_code from buyers where country=’Germany’)
and chan_code in (select chan_code from channels where channel=’Internet);

The rewrite of the first statement to the second is the star transformation. Apart
from being an inherently more elegant structure (most SQL developers with any sense of aesthetics will agree with that), there are technical reasons why the database
may be able to execute it more efficiently than the original query. Also, star queries
are easier to maintain; it is very simple to add more dimensions to the query or to
replace the single literals (‘Books,’ ‘Germany,’ and ‘Internet’) with lists of values.

There is an instance initialization parameter, STAR_TRANSFORMATION_
ENABLED, which (if set to true) will permit the Oracle query optimizer to
re-write code into star queries.