Index Techniques   «Prev  Next»
Lesson 6STAR Transformation Process
ObjectiveExecute STAR Transformation Query

STAR Transformation Process

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.
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.
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
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
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.

This shows the sql statements using bitmapped indexes.
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:
This shows the full re-written sql statement
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:
  1. Each value of "state" that is retrieved from the first sub-query 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
  4. 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
  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.

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

Each value of state that is retrieved from the first sub-query
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.

The bitmap for North Carolina values is retrieved from the index on the FACT Table
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.

The results are merged into a bitmap that contains only those rows in the FACT table
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');

Each value of region that is retrieved from the first sub-query
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

The bitmap for East values is retrieved from the index on the FACT table and stored in memory
5) 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
6) The results are merged into a bitmap that contains only those rows in the FACT table that match the region ='East' condition

The two in-memory bitmaps are then merged with an AND
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.

The rows are fetched from the in-memory bitmap by ROWID
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.

SEMrush Software