Index Techniques   «Prev 

STAR Transformation Process

The star transformation is a cost-based query transformation aimed at executing star queries[1] efficiently. Whereas the star optimization works well for schemas with a small number of dimensions and dense fact tables, the star transformation may be considered as an alternative if any of the following holds true:
  1. The number of dimensions is large
  2. The fact table is sparse
  3. There are queries where not all dimension tables have constraining predicates
The star transformation does not rely on computing a Cartesian product of the dimension tables, which makes it better suited for cases where fact table sparsity and/or a large number of dimensions would lead to a large Cartesian product with few rows having actual matches in the fact table. In addition, rather than relying on concatenated indexes, the star transformation is based on combining bitmap indexes on individual fact table columns.
The transformation can thus combine indexes corresponding precisely to the constrained dimensions. There is no need to create many concatenated indexes where the different column orders match different patterns of constrained dimensions in different queries.

1) The cost based optimizer generates the best execution plan it can produce without a transformation.

2) If star_transformation_enabled= true, the optimizer then tries to apply the transformation to the query.

3) If all of the conditions are met, the optimizer generates the best plan using the transformed query

4) The optimizer then compares the estimated costs of the transformed and the untransformed execution plans, and executes the one with the lowest cost
[1] star query: In a star query each dimension table is joined to the fact table using a primary key to foreign key join.