Various Bitmap Indexes for Warehouse Data Sets

1
Various Bitmap Indexesfor Warehouse Data Sets

• Ivan Dyedov and Daniel Elozory

2
DEFINITIONS

• BITMAP Sequence of bits representing attribute
  values
• One bit for each record
• BITMAP INDEX collection of bitmaps used to
  represent an attribute

3
SAMPLE BITMAP INDEX

• Example from text book

4
Value-List Index

• Bitmap for each distinct attribute value
• Contains a 1 for each row in the relation where
  that attribute value is found
• Contains a 0 for all other rows
• Similar to RID-list for each key value
• ltkey value, RID-listgt

5
PROCESSING A SIMPLE QUERY

• Bitmap index on attribute gender
• Find all females (genderFemale)
• Find all 1s in the Female bitmap and return
  their rids
• Dont have to go to the data level

6
QUERY with COUNT()

• Find the number of females
• Return the number of bits set to 1 for the
  Female bitmap
• Dont have to go into the data level

7
QUERY with AND/OR/NOT/XOR in the WHERE clause

• Find all people that are married or divorced
  (status married or status divorced)
• Same as before, but scan multiple bitmaps
• Performance is much better than B trees!
• for (i 0 i lt len(Bmarried) i)
• Bresulti Bmarriedi OR Bdivorcedi
• NOT is performed similarly, requiring an extra
  Existence Bitmap

8
SPACE COMPLEXITY

• Size of each bitmap (in bits) is equal to the
  number of rows in the relation
• Number of bitmaps for an attribute is equal to
  the number of distinct attribute values of that
  attribute (cardinality)
• Total space needed (in bits)
• cardinality x number of rows

9
DENSITY

• Density 1 / cardinality
• Value-list indexes take up very small amount of
  space for high density attributes
• For low density attributes (32 distinct values)
  space usage is high
• Comparable to RID-list index (32 bits per RID)

10
TIME COMPLEXITY

• Time needed to read a bitmap
• Time to read one block x bitmap size / block size
• one 6KB block can hold 48K bits
• Has to scan all of the needed bitmaps only once

11
Projection Index

• Store the indexed attribute values separately
• In order of appearance in the relation
• To find a row containing a value of the attribute
• Scan the projection index instead of data level
• Number of blocks needed to scan is smaller
• Best for column product queries

12
BIT-SLICED INDEX

• A bitmap for each bit used to represent an
  attribute value
• Possible to use for numeric values
• Bin represents the i-th bit of the attribute
  value in row n of the relation

13
BIT-SLICED INDEX EXAMPLE
14
OTHER AGGREGATES

• SUM() and AVERAGE() can be calculated with a
  Bit-sliced index
• SUM 0.00
• For i 0 to N
• // N number of bits representing attribute
  value
• SUM 2i COUNT(Bi)
• If a condition in the WHERE clause exists
• AND Bi with the foundset
• Not useful for MIN() or MAX()

15
RANGE QUERIES

• Bit-sliced index can be used
• Generate bitmaps for LT,GT,EQ using logical
  operators in a single loop
• Best for wide ranges
• Good for narrow ranges
• Value-list is best

16
COMPRESSION

• Bitmaps are often compressed
• Because of the possible high space usage with low
  density attributes
• Compressing is an advanced topic

17
Applications

• Used in warehouse data sets which are large and
  are not updated frequently
• Updated in a batch fashion while the database is
  down
• Star-schema with fact and dimension tables
• Not good for data that is modified regularly
• Updates will require us to modify ALL the
  associated bitmap indexes

18
STAR SCHEMA JOIN INDEX

19
QUESTIONS / COMMENTS?

• In case you missed our names
• Ivan Dyedov and Daniel Elozory

20
References

• Bitmap Index
• http//en.wikipedia.org/wiki/Bitmap_index
• Bitmap Index vs. B-tree Index Which and When?
• http//www.oracle.com/technology/pub/articles/shar
  ma_indexes.html
• Understanding Bitmap Indexes
• http//www.dbazine.com/oracle/or-articles/jlewis3
• Database Management Systems (3rd) 2003
• Ramakrishnan, Gehrke