The following series of images describe the STAR transformation process:
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 us assume that we have a table called fact, with bitmapped indexes on the columns state and region.
a) This shows the SQL statements using bitmapped indexes
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:
b) This shows the full re-written sql statement
select * from
fact f,
dim1 d1,
dim2 d2,
where f.state = d1.state AND
f.region = d2.region AND
d1.state = 'North Carolina' AND
d2.region = 'East' AND
fact.state in (select dim1.state from dim1
where dim1.state = 'North Carolina') AND
fact.region in (select dim1.state from dim2
where d2.region = 'East');
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 series of images below further illustrates how the query is serviced. Please note that this is a permutation of a hash join.
Oracle STAR Transformation
1)
select * from
fact f,
dim1 d1,
dim2 d2,
where f.state = d1.state AND
f.region = d2.region AND
d1.state = 'North Carolina' AND
d2.region = 'East' AND
fact.state in (select dim1.state from dim1
where dim1.state = 'North Carolina') AND
fact.region in (select dim1.state from dim2
where d2.region = 'East');
1) Each value of state that is retrieved from the first sub-query (select dim1.state from dim1.state = 'North Carolina') and stored in a temporary segment.
2) The bitmap for North Carolina values is retrieved from the index on the FACT Table and stored in memory.
These bitmaps are then merged.
3) The results are merged into a bitmap that contains only those rows in the FACT table that match the state = ;North Carolina' condition.
select * from
fact f,
dim1 d1,
dim2 d2,
where f.state = d1.state AND
f.region = d2.region AND
d1.state = 'North Carolina' AND
d2.region = 'East' AND
fact.state in (select dim1.state from dim1
where dim1.state = 'North Carolina') AND
fact.region in (select dim1.state from dim2
where d2.region = 'East');
4) Each value of region that is retrieved from the first sub-query (select dim1.region from dim2 where dim1.region = 'East') is stored in a temporary segment
5) The bitmap for East values is retrieved from the index on the FACT table and stored in memory.
These bitmaps are then merged.
6) The results are merged into a bitmap that contains only those rows in the FACT table that match the region ='East' condition
7) The two in-memory bitmaps are then merged with an AND. This results in a single bitmap that corresponds to those rows in FACT that meet the conditions in both sub-queries simultaneously only containing the requested rows.
8)
select * from
fact f,
dim1 d1,
dim2 d2,
where f.state = d1.state AND
f.region = d2.region AND
d1.state = 'North Carolina' AND
d2.region = 'East' AND
fact.state in (select dim1.state from dim1
where dim1.state = 'North Carolina') AND
fact.region in (select dim1.state from dim2
where d2.region = 'East');
8) The rows are fetched from the in-memory bitmap by ROWID. No Cartesian product is needed.
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.
