Requirements for STAR Transformation

For the STAR transformation execution plan, Oracle documentation lists the following restrictions and requirements:

Restrictions and Requirements

Restrictions	Requirements
Cannot have a CONNECT BY and START WITH	Must set the value of the initialization parameter star_transformation_enabled to true in the init.ora file
Cannot have a BIND VARIABLE in SELECT statement	Must be bitmap index defined on FACT table columns involved in EQUIJOIN predicate; more than two bitmap index on FACT table
Cannot include a remote FACT table	Must have more than 15,000 rows in the FACT table
Cannot include a FACT table which is also a view
Cannot have a hint FULL on FACT table
Cannot have a hint STAR; forcing a STAR query excludes a STAR transformation

If all of these conditions apply, the star_transformation hint can be used to invoke this access method.

Using Star Transformation

The star transformation is a powerful optimization technique that relies upon implicitly rewriting (or transforming) the SQL of the original star query. The end user never needs to know any of the details about the star transformation. Oracle Database's query optimizer automatically chooses the star transformation where appropriate. The star transformation is a query transformation aimed at executing star queries efficiently. Oracle Database processes a star query using two basic phases. The first phase retrieves exactly the necessary rows from the fact table (the result set). Because this retrieval utilizes bitmap indexes, it is very efficient. The second phase joins this result set to the dimension tables. An example of an end user query is: "What were the sales and profits for the grocery department of stores in the west and southwest sales districts over the last three quarters?" This is a simple star query.

Star Transformation with Bitmap Index

A prerequisite of the star transformation is that there be a single-column bitmap index on every join column of the fact table. These join columns include all foreign key columns. For example, the sales table of the sh sample schema has bitmap indexes on the time_id, channel_id, cust_id, prod_id, and promo_id columns. Consider the following star query:

SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'CA'
AND ch.channel_desc in ('Internet','Catalog')
AND t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;