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:
The FACT table is sparse because many of the data columns are NULL.
There are many DIMENSION tables.
There are queries where some DIMENSION tables have no constraining predicates.
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 differences between STAR query and STAR transformation
The SlideShow below demonstrates some of the differences between a STAR query and a STAR transformation.
As we see in the SlideShow 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.
STAR transformation Process
The following steps occur during a STAR transformation:
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
Star Transformation Process
The STAR transformation works by generating new sub-queries that can be used to drive a bitmap index access path for the fact table.
Let's assume that we have a table called fact, with bitmapped indexes on the columns state and region.
As we see above, Oracle recognizes that the three columns in the WHERE clause are bitmapped indexes, and will re-write the query as follows. Note that three Boolean conditions are added to the query:
The query is serviced as follows:
Each value of "state" that is retrieved from the first sub-query and stored in a temporary segment.
The bitmap for North Carolina values is retrieved from the index on the FACT table and stored in memory. These bitmaps are then merged.
The results are merged into a bitmap that contains only those rows in the FACT table that match the state='North Carolina' condition.
Each value of "region" that is retrieved from the first sub-query (select dim1.region from dim2 where dim1.region = 'East') and stored in a temporary segment
The bitmap for "East" values is retrieved from the index on the FACT table and stored in memory. These bitmaps are then merged.
The results are merged into a bitmap that contains only those rows in the FACT table that match the region='East' condition.
The SlideShow below further illustrates how the query is serviced. Please note that this is a permutation of a hash join.
Star Transformation Steps
The STAR transformation is faster than a STAR join only if there are very few rows satisfying the simultaneous subqueries. If there are many rows returned from the sub-queries, then the STAR query execution plan is faster.
The next lesson examines function-based indexing techniques.