Index Techniques   «Prev  Next»
Lesson 6STAR Transformation Process
ObjectiveExecute STAR Transformation 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 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

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

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