Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bitmap Index Architecture

Dev_SQLSep 5 2019 — edited Sep 6 2019

Hi All,

It is recommended to go for this Bitmap Indexes when we have a low cardinality like Gender, Marital status, Region columns  and it performs better than B-Tree index.

I would like to understand its internals, like the Bitmap Index architecture, how it gets the RowIds faster for a particular data using bit values like 0 or 1, how it navigates internally, searches, fetches the RowIDs and gets the data finally.

I've a good understanding about B-Tree architecture, same way trying to understand for Bitmap indexes as well.

Below is an example for Region column on Person table.

create bitmap index person_region on person (region);

As shown in the below table, does it maintain a separate column for each unique value in the Region column, like North, East, West and South?

When we query for a particular value in the region column as shown below, does it query on the internal North column and search for the bit as '1', get the RowIDs for them and finally get the data? Is it correct?

Select * from person Where Region='North'

        Row Person ID  Person Name    Region   North   East   West   South

        1   100                Daniel         North        1      0      0       0

        2   101                Samuel       East          0      1      0       0

        3   102                Daves         West         0      0      1       0

        4   103                Steven        West         0      0      1       0

        5   104                Stephen      South        0      0      0       1

        6   104                Jeff              North        1      0      0       0

Could you please shed some light on this? Thank you.

This post has been answered by Jonathan Lewis on Sep 6 2019
Jump to Answer
Comments
Post Details
Added on Sep 5 2019
10 comments
1,381 views