Features of Oracle Bitmap Index
A bitmap index is a special kind of database index that uses bitmaps.
Bitmap indexes have traditionally been considered to work well for data which has a small number of distinct values but with many occurrences, such as column region on table person, only with values East, North, West or South but each of them has many occurrences.
However, some researchers argue that Bitmap indexes are also useful for unique valued data which is not updated frequently. Bitmap indexes have a significant space and performance advantage over other structures for such data.
Following are the main features of a bitmap index:
- Bitmap indexes use bit arrays (commonly called "bitmaps") and answer queries by performing bitwise logical operations on these bitmaps.
- Bitmap indexes are suitable for indexing low-cardinality data. But as demonstrated by Oracle, a bitmap index on a column with 100-percent unique values (a column candidate for primary key) is as efficient as a B-tree index.
(Refer: http://www.oracle.com/technology/pub/articles/sharma_indexes.html)
- Bitmaps are created by doing a full-table scan of the indexed table, and they are very slow for updates. So bitmap index is suitable for those static columns with less update.
- Bitmap index have a highly compressed structure, this represent a trade-off between CPU usage and disk space usage. A compressed structure is faster to read from disk but takes additional CPU cycles to decompress for access.
- Overhead on maintaining bitmap index is enormous. In addition, the concurrency for modifications on bitmap indexes is dreadful.
- Bitmap index can deal with “null” value, but B-tree index can not. Because B-tree index can not store “null” value.
A sample about how different indexes deal with “null” value
Consider following situation:
select count(*) from customer where customer_long_name is null;
With a bitmap index on column “customer_long_name”, oracle could directly get result form the index, even need not to access table “customer”.
With a b-tree index, oracle can only count the result from table, and may a full table scan should be taken.
A sample about Bitmap Index caused deadlock:
Bitmap indexes are not appropriate for tables that have lots of single row DML operations (inserts) and especially concurrent single row DML operations. Deadlock situations are the result of concurrent inserts as the following example shows: Open two windows, one for Session 1 and one for Session 2
|
Session 1 |
Session 2 |
|
create table bitmap_index_demo ( |
|
|
insert into bitmap_index_demo |
|
|
create bitmap index |
|
|
insert into bitmap_index_demo |
|
|
|
insert into bitmap_index_demo |
|
insert into bitmap_index_demo |
|
|
ERROR at line 1: |
insert into bitmap_index_demo |
(Refer: http://www.akadia.com/services/ora_bitmapped_index.html)
A sample about bitmap index
Table suitable for bitmap index:
|
CUST # |
MARITAL |
REGION |
GENDER |
INCOME_ LEVEL |
|
101 |
single |
east |
male |
bracket_1 |
|
102 |
married |
central |
female |
bracket_4 |
|
103 |
married |
west |
female |
bracket_2 |
|
104 |
divorced |
west |
male |
bracket_4 |
|
105 |
single |
central |
female |
bracket_2 |
|
106 |
married |
central |
female |
bracket_3 |
Most attributes have low cardinality. A B-Tree would be very large on these attributes and not very efficient.
Bitmap Index on Region
|
Cust# |
REGION='east' |
REGION='central’ |
REGION='west' |
|
101 |
1 |
0 |
0 |
|
102 |
0 |
1 |
0 |
|
103 |
0 |
0 |
1 |
|
104 |
0 |
0 |
1 |
|
105 |
0 |
1 |
0 |
|
105 |
0 |
1 |
0 |
A bit is 1 if the tuple has the value represented by the column. Very little space is required to store these bits.
Executing a Query
SELECT COUNT(*) FROM CUSTOMER
WHERE MARITAL_STATUS = 'married' AND REGION IN ('central','west');
Star Schema
Wed, 12 Aug 2020 21:24:44 +0800
Fantastic blog! Do you have any tips and hints for aspiring writers? I’m planning to start my own website soon but I’m a little lost on everything. Would you propose starting with a free platform like WordPress or go for a paid option? There are so many options out there that I’m completely overwhelmed .. Any suggestions? Many thanks!
Sat, 12 Sep 2020 20:28:03 +0800
Just admiring your work and wondering how you managed this blog so well. It’s so remarkable that I can't afford to not go through this valuable information whenever I surf the internet!
Sat, 23 Jan 2021 23:08:47 +0800
Nice to read your article! I am looking forward to sharing your adventures and experiences.
Mon, 25 Jan 2021 22:49:51 +0800
Thank you so much for sharing this great blog.Very inspiring and helpful too.Hope you continue to share more of your ideas.I will definitely love to read.
Wed, 27 Jan 2021 00:35:15 +0800
Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative. I can't wait to read lots of your posts.
Thu, 28 Jan 2021 00:28:01 +0800
I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post.
Sat, 30 Jan 2021 14:07:57 +0800
I have read your blog it is very helpful for me. I want to say thanks to you. I have bookmark your site for future updates.
Thu, 25 Mar 2021 22:15:43 +0800
I’ve been searching for some decent stuff on the subject and haven't had any luck up until this point, You just got a new biggest fan!..
Mon, 29 Mar 2021 17:40:04 +0800
Great write-up, I am a big believer in commenting on blogs to inform the blog writers know that they’ve added something worthwhile to the world wide web!..
Sat, 03 Apr 2021 20:38:27 +0800
This article gives the light in which we can observe the reality. This is very nice one and gives indepth information. Thanks for this nice article.
Mon, 05 Apr 2021 23:41:14 +0800
Great write-up, I am a big believer in commenting on blogs to inform the blog writers know that they’ve added something worthwhile to the world wide web!..
Thu, 08 Apr 2021 05:08:45 +0800
Great write-up, I am a big believer in commenting on blogs to inform the blog writers know that they’ve added something worthwhile to the world wide web!..
Sun, 11 Apr 2021 19:48:58 +0800
Thanks for sharing this information. I really like your blog post very much. You have really shared a informative and interesting blog post with people..
Tue, 20 Apr 2021 11:28:54 +0800
Thank you for sharing this useful article information.I am really impressed with the article you provided.
<a href="https://ofertastop.eu/aspiradoras/">Aspiradora para coche</a>
Mon, 24 May 2021 18:31:45 +0800
I was reading your article and wondered if you had considered creating an ebook on this subject. Your writing would sell it fast. You have a lot of writing talent. soap 2day
Wed, 11 May 2022 18:43:20 +0800
After reading through this post, I was able to find some details about the main features of a bitmap index. You guys have been doing pretty well with the latest information and details available. Thanks for the share.click this link
Sat, 09 Jul 2022 12:40:08 +0800
I like the way you conduct your posts. LED補光燈
==================
I enjoy your writing type, do keep on writing! I’ll be back! iphone回收價
===================
You created some decent points there. I looked over the internet for your issue and discovered most people will go along with together with your site. 手機回收價格
Wed, 20 Jul 2022 04:27:06 +0800
Very efficiently written information. It will undoubtedly be good for anyone who utilizes it, including me. Continue the good work. Without a doubt i'll have a look at more posts. This web site seems to get a great deal of visitors. Woking to Heathrow Taxi