sqloracle-databasedata-warehouse

Using bitmap index in oracle db when many inserts/updates in single thread mode


I have a question about using bitmap indexes in oracle...

Context: my app is analyst system with too many reads to view calculated stats in web app. Data is loading to db in singlethread mode from one microservice, which reads events from queue.. events - 500k per day (approximately) and average speed about 20rps

Problem: various sql queries are used to calculate statistics and performance is low.. I try to speed up performance using btree index but unsuccessfully.. queries take tens seconds to complete..

I consider to use bitmap index to improve permormance.. could you advice me some practice about it... I read a lot of docs about bitmap indexes, about concurent transactions... and problems.. but i have single thread mode.. maybe someone faced with the same problem.. will i have problems or not if i start using bitmap..


Solution

  • Thanks everybody for advice What I did.

    1. created several bitmap indexes on a table
    2. change architecture of data processing to minimize problem with bitmap indexes You can watch final architecture below final architecture

    This architecture helps me to solve two problem:

    1. minimize concurrent transations on write
    2. unified processing of all events from different sources