Index Techniques   «Prev 

Oracle STAR Transformation

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



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');


3) 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


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



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.