Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Anubha.

Asked: April 03, 2017 - 12:15 pm UTC

Last updated: September 14, 2023 - 1:01 pm UTC

Version: 11gR2

Viewed 1000+ times

You Asked

Hi Tom,

In one of the interview, interviewer asked me about the scenario where bitmap index is useful in OLTP system.
and I was totally clueless.....
I wasn't able to think any of the scenario.

Later I googled every possible combination to get some info about the scenario. But didn't find any answer.

Could you please help me to get the answer, where bitmap index is useful in OLTP.

Thanks in advance.

and Chris said...

Clearly the interviewer had something in particular in mind. We can only guess at what that was!

A bitmap index is unsuitable when you have more than one session inserting/updating/deleting the same table at the same time. This can happen in reporting environments as well as OLTP.

There can be tables in OLTP environments that are only ever changed by one process at a time e.g. by a batch job. But these are rare in a pure OLTP database.

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9533571800346380617
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2049072400346231785

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Anubha Awadhiya, April 04, 2017 - 3:20 am UTC

Thanks Chris. Answer is really useful.
Connor McDonald
April 04, 2017 - 12:30 pm UTC

glad we could help

Example for useful Bitmap Indices in OLTP

Dr. Jens Oehlschlägel, September 13, 2023 - 2:49 pm UTC

Say your database handles <transactions> (highly frequent OLTP) of <shoppers> (frequent OLTP) on <outlets> (rare changes, single process). Say outlets are geolocated on an x,y grid, and you have frequent Queries searching for outlets nearby a geolocation. then you can benefit from from creating two bitmap indices on x and y when searching like
WHERE outlet.x BETWEEN x-d AND x+d 
AND outlet.y BETWEEN y-d AND y+d;

While this can also be done using geospatial indexing, the bitmap model can be extended to further attributes of outlets such as availability of product category a, parking b, child care c, etc.
Chris Saxon
September 14, 2023 - 1:01 pm UTC

Good example, thanks for sharing