Bitmap Indexes - Expert Indexing in Oracle Database 11g - page 64

Geography Dimension

Row Geo_ID Country Region State City

1 10 USA East Maine Portland

2

11 USA East Maryland Baltimore

3 12 USA North North Dakota Fargo

4

13 USA South Texas Houston

5 14 USA West Oregon Portland

Table 3-5. Bitmap Join Index Values Between Star Schema Fact Table and Joined Dimension Table

BILLING FACT ROWID GEOGRAHY DIMENSION ROWID Matching GEO_ID

AAARm6ABDAAAMz1ABB

AAAUWMABiAAAS9KAAr 23099

AAARm6ABCAAAUiHAA6 AAAUWMABiAAAS9KAAr 23099

AAARm9AAAABAUjBAA9

AAAUWMABiAAAS9CBAm 24010

AAARm9AAAABCUhBACC AAAUWMABiAAAS9CBAm 24010

AAAHm6ACBAABCUbBBB AAAUWMABiAAAS9CBAm 24010

If you employ the star schema within your environment, bitmap join indexes should seriously be
considered. They offer distinct performance benefits in data warehouse environments employing the
star schema. Join operations take time, and when you have a central fact table and many dimensions on
which to join, the time to perform join operations can significantly increase as tables are added to a join
condition. Because the bitmap join index is essentially a materialized join between join columns on the
fact and dimension tables, join operations at execution time are already complete because the join
operation was done and stored in the bitmap join index when the bitmap join index was created. Bitmap
join indexes are tailor-made for star schemas and should be considered to help the query efficiency
when accessing tables within the star schema.