ITIS 5160

1
ITIS 5160

• Indexing

2
Indexing datacubes

• Objective speed queries up.
• Traditional databases (OLTP) B-Trees
• Time and space logarithmic to the amount of
  indexed keys.
• Dynamic, stable and exhibit good performance
  under updates. (But OLAP is not about
  updates.)
• Bitmaps
• Space efficient
• Difficult to update (but we dont care in DW).
• Can effectively prune searches before looking at
  data.

3
Bitmaps
4
Query optimization
Consider a high-selectivity-factor query with
predicates on two attributes. Query optimizer
builds plans (P1) Full relation scan (filter
as you go). (P2) Index scan on the predicate
with lower selectivity factor, followed by
temporary relation scan, to filter out
non-qualifying tuples, using the other predicate.
(Works well if data is clustered on the first
index key). (P3) Index scan for each
predicate (separately), followed by merge of RID.
5
Query optimization (continued)
6
Query optimization (continued)
When using bitmap indexes (P3) can be an easy
winner! CPU operations in bitmaps (AND, OR, XOR,
etc.) are more efficient than regular RID merges
just apply the binary operations to the
bitmaps (In B-trees, you would have to scan the
two lists and select tuples in both -- merge
operation--) Of course, you can build B-trees on
the compound key, but we would need one for every
compound predicate (exponential number of trees).
7
Bitmaps and predicates
A a1 AND B b2

AND
8
Tradeoffs
Dimension cardinality small dense
bitmaps Dimension cardinality large sparse
bitmaps
Compression (decompression)
9
Star-Joins
Select F.S, D1.A1, D2.A2, . Dn.An from
F,D1,D2,Dn where F.A1 D1.A1 F.A2
D2.A2 F.An Dn.An and D1.B1
c1 D2.B2 p2 .
Likely strategy For each Di find suitable values
of Ai such that Di.Bi xi (unless you have a
bitmap index for Bi). Use bitmap index on Ai
values to form a bitmap for related rows of F
(OR-ing the bitmaps). At this stage, you have n
such bitmaps, the result can be found AND-ing
them.
10
Bitmaps
11
Example
12
Encoding scheme
Equality encoding all bits to 0 except the one
that corresponds to the value Range Encoding
the vi rightmost bits to 0, the remaining to 1
13
Range encodingsingle component, base-9
?R (A) B8 B7 B6 B5
B4 B3 B2 B1 B0 3 1 1 1
1 1 1 0 0 0
2 1 1 1 1 1 1 1
0 0 1 1 1
1 1 1 1 1 1 0
8 1 0 0
0 0 0 0 0 0 0
1 1 1 1 1
1 1 1 1 7
1 1 0 0 0 0
0 0 0 5 1 1
1 1 0 0 0 0 0 6
1 1 1 0
0 0 0 0 0 4
1 1 1 1 1 0
0 0 0
14
RangeEval
Evaluates each range predicate by computing two
bitmaps BEQ bitmap and either BGT or
BLT RangeEval-Opt uses only lt A lt v is the
same as A lt v-1 A gt v is the same as Not( A lt
v) A gt v is the same as Not (A lt v-1)
15
Example (revisited)
16
Example
17
RangeEval-OPT
18
(No Transcript)