During the join, sparseness (i.e., a large quantity of empty values) is recognized
inherently in the bitmaps, and the number of dimension tables isn??™t a problem. This
algorithm can also efficiently handle a snowflake schema, which is an extension of a
standard star schema in which there are multiple tables for each dimension.
Figure 10-3. Bitmap index operation in a compound WHERE clause
partno color size weight
001
002
003
004
005
006
GREEN
RED
RED
BLUE
RED
GREEN
MED
MED
SMALL
LARGE
MED
SMALL
98.1
1241
100.1
54.9
124.1
60.1
... ... ..... ...
PARTS table
3 bits in
index entries
SELECT count(*)
FROM parts
WHERE
AND size = ???MED??™
color = ???RED??™
1 1
1 1
Index on ???color??™
color =
color =
color =
'BLUE'
'RED'
'GREEN'
size =
size =
size =
'SMALL'
'MED'
'LARGE'
Index on ???size??™
0 0 0 1 0 0 1 0 1 0 1 0 0 0 1 0
0 1 0 0 0 1 0 0 0 0 1 0 0 1
1 0 0 0 0 1 0 0 0 1 0 1 0 1 0 0
0 0 1 0 0 1 0 1 0 1 0 0 0 1 0 1
1 0 0 0 1 0 0 0 0 1 0 1 0 0
0 0 0 1 0 0 0 0 1 0 1 0 1 0 1 0
232 | Chapter 10: Oracle Data Warehousing and Business Intelligence
To further speed queries, Oracle9i added a bitmap join index from fact tables to
dimension tables.
Pages:
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510