RelationalDBDesign RelationalDBDesign 

Index Techniques   «Prev 

STAR transformation queries

Utilize the Star Schema to leverage Star Query Transformation
  1. Oracle optimizer prunes a query's results set with its conversion of many logical joins into a single operation with Bitmap Indexes
  2. Bitmap Indexes are up to 100 times smaller in size and hence up to 100 times faster
  3. Bitmaps are not just for low cardinality
  4. Central table in a star schema is called a FACT table
  5. A fact table typically has two types of columns: 1) those that contain facts and 2) those that are foreign keys to dimension tables
  6. In a star schema every dimension will have a primary key
  7. In a star schema, a dimension table will not have any parent table
  8. Whereas in a snow flake schema, a dimension table will have one or more parent tables

STAR query versus STAR Transformation

1) Unlike the STAR query, the STAR transformation does not rely on computing a Cartesian product of the DIMENSION tables in RAM memory

2) A STAR transformation is a better choice where a sparse FACT table

3) Many DIMENSION tables, would result in a large Cartesian product, with few rows having actual matches in the fact table.

4) Where a STAR query requires a large concatenated index on the FACT table

5) The STAR transformation method does not, and is instead based on combining bitmap indexes on individual FACT table columns

6) Where the STAR query uses shared pool memory

7) The STAR transformation uses temporary segments to hold the intermediate row ID lists