Index Techniques   «Prev 

Oracle STAR Transformation

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 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') is 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 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. No Cartesian product is needed.