No question at this time
DBA Top 10
1 B. Vroman 14600
2 M. Cadot 11000
3 J. Schnackenberg 8200
4 T. Boles 7950
5 A. Kavsek 6200
6 M. Hidayathullah ... 2200
7 G. Lambregts 1100
8 T. P 1000
9 P. Wisse 900
10 B. Derous 500
10 . Lauri 500
10 R. Pattyn 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48286
Total active users1591
Act. users last 24h3
Act. users last hour0
Registered user hits last week242
Registered user hits last month1121
Go up

Understanding bitmap indexes usage
Next thread: About Oracle RDBMS 18c
Prev thread: ORA-3113 in Select with order by

Message Score Author Date
Hi, In a datawarehousing environment, bitmaps i...... Lauri Nov 05, 2018, 15:02
The greatest advantage of the bitmap index is that...... David Johnson Nov 05, 2018, 15:40
Hi Lauri, I agree with David except maybe the i...... Philip Wisse Nov 05, 2018, 19:07
Hello Lauri, about the low cardinality: because...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Bruno Vroman Nov 06, 2018, 08:22
Hi Lauri, I have no practical experience with b...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Jan Schnackenberg Nov 06, 2018, 09:33
Hi, Thanks to all for the answers. Once again,...... Lauri Nov 06, 2018, 11:42

Follow up by mail Click here


Subject: Understanding bitmap indexes usage
Author: Lauri, Netherlands
Date: Nov 05, 2018, 15:02, 13 days ago
Os info: All
Oracle info: Oracle 12c
Error info: Not applicable, this is question about concepts.
Message: Hi,

In a datawarehousing environment, bitmaps indexes are comonly.
I am trying to understand and investigate what and when benefits can be gained in the datawarehousing environment.
This is for me a new top, so far, I have always worked in B-tree indexes.
Correct me if I understood wrong. From the documentation, I understood that they are represented in the form of a two-dimensional array with a bitmap struncture.
Such an index contains for each row in a table, a bitmap for the column being indexed with its corresponding rowid.
At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values.
These matching values are delivered to Oracle in the form of a rowid list and these rowid values may directly access the required information from the table.
With this structure, there is no need to search through a three (like for the B-tree index).
The advantage seems to be when a table is accessed with multiple bitmap iindexes, as the rowid will be merged and stored in the RAM before retrieval from the table.
At first sight, it seems to process faster the retrival than with classical B-tree indexes.
What I do not understand is why it is meant for columns having a low cardinality only?
Reading a bitmap seems to be "easier" than searciing through a tree.
Bitmap indexes should be created on columns have no or few DML.
It makes probably sense as the bitmap would need to be re-created.

Does someone has experience with bitmap indexes?
Is the above description correct or incomplete?
Do you have any does and donts?
Does the size of the table matter when using bitmap indexes versus B-tree indexes?
Thanks by advance for sharing your experience.

Kind Regards

Lauri
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Understanding bitmap indexes usage
Author: David Johnson, United States
Date: Nov 05, 2018, 15:40, 13 days ago
Message: The greatest advantage of the bitmap index is that multiple indices can be utilized by a single query. Results from all indices are XORed together, great for fact tables with many dimensions.

Low Cardinality, Flag columns 'Y'/'N'/null value column queries perform especially well -- yes null values do get indexed.

But with bitmapped indexes, Low cardinality is a relative term, Compare the number of distinct values to the number of rows. Performance can be good even with cardinality in the millions, if there are billions of rows.

The down side of bitmapped indexes is when you do a lot of updating of the row values in a table, rather than inserts / deletes. In this case you may want to drop / rebuild the bitmaps after a major updating session as I've
noticed that the index can grow significantly, leaving behind artifacts of the old values (not exactly leaf browning as it is not a tree structure). The good thing is that the index rebuild is pretty fast.





Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Understanding bitmap indexes usage
Author: Philip Wisse, Netherlands
Date: Nov 05, 2018, 19:07, 13 days ago
Message: Hi Lauri,

I agree with David except maybe the index growth.
Some queries work nice with bitmap indexes, others don't.

Bitmap indexes require the enterprise edition (EE).

If you have time investigating new features have a look at the new in memory feature.
Oracle stores tables in a columnar format which reduces space and obsoletes indexes.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/in-memory-column-store-architecture.html#GUID-EEA265EE-8FBA-4457-8C3F-315B9EEA2224

HTH, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Understanding bitmap indexes usage
Author: Bruno Vroman, Belgium
Date: Nov 06, 2018, 08:22, 12 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hello Lauri,

about the low cardinality: because all values are "listed" in the bitmap, for each row. So the size of the index is proportional to the number of distinct values.
Example: mytable( ..., mygreekcol, ..., mynumbercol, ... )
with 4 distinct values for mygreekcol: Alpha, Lambda, Pi, Omega and 3 distinct values for mynumbercol: 1, 4, 5.
We have a bitmap index on both columns.
 r1 r2 r3 r4 r5 r6 r7 r8 rowid  

1 0 0 1 0 0 0 0 alpha
0 1 0 0 1 0 1 0 lambda
0 0 0 0 0 1 0 0 pi
0 0 1 0 0 0 0 1 omega

r1 r2 r3 r4 r5 r6 r7 r8 rowid
0 0 0 0 1 0 1 0 1
0 0 0 1 0 0 0 1 4
1 0 1 0 0 1 0 0 5
(You don't want the bitmap to list 20000 distinct values, do you? I am surprised by the comment of David on this point -but I have to admit that I lack of experience with bitmap indexes, I don't run DWH)

About the "power" of bitmap indexes used together: if we submit: SELECT ... FROM mytable WHERE mygreekcol = 'Pi' AND mynumbercol = 5;
Oracle has simply to look at the rows with "1" in the right line of the two indexes:
 r1 r2 r3 r4 r5 r6 r7 r8 rowid  

0 0 0 0 0 1 0 0 pi
1 0 1 0 0 1 0 0 5
--------------------------
1 -> r6 selected
instead of for example using a B-tree index on one for the column, go to the row, check value for other column (note that there are also other ways to use multiple B-tree indexes, this is a simplified view).

About updates: what if we add a new value (gamma) to the 4 existing values of mygreekcol? All the index has to be deeply modified.

As I wrote I lack experience so this is probably a bit theoretical...

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Understanding bitmap indexes usage
Author: Jan Schnackenberg, Germany
Date: Nov 06, 2018, 09:33, 12 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Lauri,

I have no practical experience with bitmap indexes.

But: No, your description is not correct.

You wrote:
From the documentation, I understood that they are represented in the form of a two-dimensional array with a bitmap struncture.
Such an index contains for each row in a table, a bitmap for the column being indexed with its corresponding rowid.


Correct would be:
* The index contains a bitmap for each distinct value (key) in the indexed column
* Each bit-position in the index can be translated to exactly one ROWID
* Each bit in the index tells the database if the row represented by that bit contains the value represented by the bitmap (1) or not (0)
* If you have many distinct values in the indexed column, you will have as many bitmaps, causing the index to be extremely inefficient.

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Understanding bitmap indexes usage
Author: Lauri, Netherlands
Date: Nov 06, 2018, 11:42, 12 days ago
Message: Hi,

Thanks to all for the answers.
Once again, I have no experience with bitmap indexes, I am just investigating and learning.

@Philip:
You are right, EE is needed.

@Bruno:
Well, I understand that if we have a "high" cardinality, the two-dimensional representation of the bitmap index will grow up rapidly, hence the size.
So, the size of the index in that situation makes it inefficient, right?
So, I understand it "must" be created for low cardinality to be efficient (but that's not enough).
The other "power" that makes it "efficient" is indeed that the Oracle engine does not search inside a tree struture but just the "1" bit in a two-dimensional, and retrieve the rowid.

@Jan:
* The index contains a bitmap for each distinct value (key) in the indexed column
=> So you mean that each distinct value of the key has a distinct bitmap representation, right? Then, it makes sense.

Well, so I understand that a bitmap index can be a candidate when:
1) The target column has a low cardinality.
2) Then can be combinated together in WHERE/AND/OR clauses.
3) Low DML on the tables on which the bitmap indexes are created (as the bitmap would need to be recreated by the Oracle engine).
I do think these are "rules of thumbs" and testing must be experienced.

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here