Question

construct a bitmap index for the color column.

Please please show steps or explain what you did as i really want to learn how to do this.

RecordID	Date	Color	Part Number
1	30-03-2014	Tiger stripes	WTL-538
2	29-09-2014	Blue	LER-137
3	04-05-2014	Green	UGN-537
4	18-03-2014	Tiger stripes	GBD-403
5	10-10-2014	Green	IAJ-568
6	18-12-2014	Red	HOE-123
7	05-04-2014	Tiger stripes	LFT-811
8	16-01-2014	Tiger stripes	XAV-564
9	11-12-2014	Red	OHJ-400
10	07-04-2014	Blue	BKX-628
11	27-11-2014	Green	FZX-549
12	04-04-2014	Blue	SJM-622
13	15-08-2014	Red	XVC-474
14	14-03-2014	Tiger stripes	UQR-421
15	20-04-2014	Tiger stripes	CFT-658
16	31-07-2014	Green	EUV-759
17	05-06-2014	Tiger stripes	XSG-479
18	19-03-2014	Blue	ODD-953
19	25-05-2014	Green	YDS-862
20	10-09-2014	Red	LTP-337
21	11-09-2014	Tiger stripes	BTL-198
22	25-04-2014	Blue	ZNO-843
23	24-03-2014	Green	TOX-402
24	28-08-2014	Red	FKL-572
25	10-11-2014	Red	PVT-512

26	15-03-2014	Tiger stripes	ZLM-790
27	14-06-2014	Tiger stripes	PER-701
28	07-11-2014	Green	CLU-499
29	23-04-2014	Red	YJV-990
30	19-11-2014	Blue	JQR-979
31	02-02-2014	Green	HZK-103
32	04-08-2014	Green	PRK-631
33	25-06-2014	Red	XBL-745
34	03-09-2014	Green	NXK-247
35	06-03-2014	Blue	PDJ-970
36	22-04-2014	Green	TUM-443
37	19-11-2014	Red	LZP-290
38	10-11-2014	Red	WJQ-620
39	09-11-2014	Red	OZF-584
40	01-12-2014	Blue	ALF-413
41	08-06-2014	Red	IIV-220
42	09-04-2014	Red	CNW-296
43	25-10-2014	Tiger stripes	YKB-311
44	30-05-2014	Tiger stripes	ACA-257
45	15-02-2014	Red	TXE-796
46	22-03-2014	Tiger stripes	ONN-909
47	18-12-2014	Red	VHQ-888
48	09-02-2014	Green	DOL-764
49	22-09-2014	Tiger stripes	KKI-825
50	03-07-2014	Red	BPE-622

Answer 1

Bitmap indices are used for columns with low cardinality. Here for each unique column entry, we'll store 1 at positions where that entry exists and in the rest positions put 0.

Uniques values of Color are:

Color

Tiger stripes

Blue

Green

Red

The positions where each of these colors exists is:

TIGER STRIPES:

1
4
7
8
14
15
17
21
26
27
43
44
46
49

BLUE:

2
10
12
18
22
30
35
40

GREEN:

3
5
11
16
19
23
28
31
32
34
36
48

RED:

6
9
13
20
24
25
29
33
37
38
39
41
42
45
47
50

Now, make the bitmaps by placing 1's and 0's.

RecordID	Blue	Green	Red	Tiger Stripes
1	0	0	0	1
2	1	0	0	0
3	0	1	0	0
4	0	0	0	1
5	0	1	0	0
6	0	0	1	0
7	0	0	0	1
8	0	0	0	1
9	0	0	1	0
10	1	0	0	0
11	0	1	0	0
12	1	0	0	0
13	0	0	1	0
14	0	0	0	1
15	0	0	0	1
16	0	1	0	0
17	0	0	0	1
18	1	0	0	0
19	0	1	0	0
20	0	0	1	0
21	0	0	0	1
22	1	0	0	0
23	0	1	0	0
24	0	0	1	0
25	0	0	1	0
26	0	0	0	1
27	0	0	0	1
28	0	1	0	0
29	0	0	1	0
30	1	0	0	0
31	0	1	0	0
32	0	1	0	0
33	0	0	1	0
34	0	1	0	0
35	1	0	0	0
36	0	1	0	0
37	0	0	1	0
38	0	0	1	0
39	0	0	1	0
40	1	0	0	0
41	0	0	1	0
42	0	0	1	0
43	0	0	0	1
44	0	0	0	1
45	0	0	1	0
46	0	0	0	1
47	0	0	1	0
48	0	1	0	0
49	0	0	0	1
50	0	0	1	0