There are two tables. The first is a fact table, about 0.1 billion rows. The other is a dimension table with only about 100 rows. I have made a bitmap-join index on the fact table to optimize the query.
However, when I insert a few rows into the dimension table, the database hangs.
Does anyone know what could be the reason?
A bitmap index is a matrix, with a column for each distinct value and a row for each record in the indexed table. The same principle applies in a bitmap join index: you have one column for each distinct value in the DIMENSION table and one row in the FACT table.
From which it should be apparent that inserting one row into the DIMENSION table will generate 100 million entries in the index. That will take a long time.
You say you are inserting "a few rows". So, honestly, what do you think would be a reasonable time to generate all those entries?
This is the bitmap index trade-off: it can save a lot of time of queries but the overhead for maintenance is extremely high. Consequently we need to think carefully before deploying a bitmap index. Sometimes it is possible to ameliorate those costs, but that depends on the particular circumstances.
There is one further thing to bear in mind with bitmap join indexes, which might also appear to cause the server to hang:
"Only one table can be updated concurrently by different transactions when using the bitmap join index."
You should read the documentation. Find out more.
Bitmap indexes do have an associated performance penalty when loading large number of rows. To get the efficiency you are looking for, simply mark the index as unusable, load your records, then rebuild the index. You should see your load times improve with the amount of data you are talking about.
