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.

The following series of images demonstrates some of the differences between a STAR query and a STAR transformation query.

STAR Query versus STAR Transformation

1) Unlike the STAR query, the STAR transformation does not rely on computing a Cartesian product of the DIMENSION tables in RAM memory
1) Unlike the STAR query, the STAR transformation does not rely on computing a Cartesian product of the DIMENSION tables in RAM memory

2) A STAR transformation is a better choice where a sparse FACT table
2) A STAR transformation is a better choice where a sparse FACT table

3) Many DIMENSION tables, would result in a large Cartesian product, with few rows having actual matches in the fact table.
3) Many DIMENSION tables, would result in a large Cartesian product, with few rows having actual matches in the fact table.

4) Where a STAR query requires a large concatenated index on the FACT table
4) Where a STAR query requires a large concatenated index on the FACT table

5) The STAR transformation method does not, and is instead based on combining bitmap indexes on individual FACT table columns
5) The STAR transformation method does not, and is instead based on combining bitmap indexes on individual FACT table columns

6) Where the STAR query uses shared pool memory
6) Where the STAR query uses shared pool memory

7) The STAR transformation uses temporary segments to hold the intermediate row ID lists
7) The STAR transformation uses temporary segments to hold the intermediate row ID lists

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.