Index Techniques   «Prev  Next»
Lesson 5STAR Transformation Queries
ObjectiveExecute STAR Transformation Query

STAR Transformation Queries

STAR Transformation Execution Plan

As we demonstrated in the prior lesson, the STAR query indexing technique is very useful in cases where large, fully populated data warehouse tables are joined together. The STAR transformation is a cost-based optimizer operation to execute STAR queries more efficiently.
The STAR transformation has certain requirements. Oracle also calls the STAR transformation execution plan the "parallel bitmapped star query transformation". The STAR optimization works well for schemas with dense FACT tables and a small number of DIMENSIONS. The STAR transformation may be considered where these conditions apply:
  1. The FACT table is sparse because many of the data columns are NULL.
  2. There are many DIMENSION tables.
  3. There are queries where some DIMENSION tables have no constraining predicates.
  4. There are a large number of DIMENSION tables involved in the JOIN.

STAR query vs. STAR Transformation

The STAR transformation is like the STAR query, except that rather than relying on a join index, it uses bitmapped indexes and builds the intermediate results in temporary segments.

Differences between STAR Query and STAR transformation

The SlideShow below demonstrates some of the differences between a STAR query and a STAR transformation.

StarQuery versus Star Transformation
As we see in the Slide Show above, unlike the STAR query, the STAR transformation does not rely on computing a Cartesian product of the DIMENSION tables in RAM memory. In a STAR transformation, a join index provides immediate access to only the relevant rows of the FACT table, whereas a STAR query requires a large concatenated index on the FACT table. More efficient FACT table access makes a STAR transformation a better choice in the case where few rows have actual matches in the fact table.
In a STAR transformation, the SQL optimizer will see if there are bitmapped indexes on the FACT and DIMENSION tables and dynamically re-format the SQL query.