Can we create Bitmap Index on a table column in Oracle 11 which reloads daily using a Job

Question

We have table which stores information about clients which gets loaded using a scheduled job on daily basis from Data warehouse. There are more than 1 million records in that table. I wanted to define BitMap Index on Country column as there would be limited number of values. Does it have any impact on the indexes if we delete and reload data into table on daily basis. Do we need to explicitly rebuild the index after every load

Answer 1

I wanted to define BitMap Index on Country column as there would be limited number of values.

Just because a column is low cardinality does not mean it is a candidate for a bitmap index. It might be, it might not be.

Good explanation by Tom Kyte here.

Bitmap indexes are extremely useful in environments where you have lots of ad hoc queries, especially queries that reference many columns in an ad hoc fashion or produce aggregations such as COUNT. For example, suppose you have a large table with three columns: GENDER, LOCATION, and AGE_GROUP. In this table, GENDER has a value of M or F, LOCATION can take on the values 1 through 50, and AGE_GROUP is a code representing 18 and under, 19-25, 26-30, 31-40, and 41 and over.

For example,

You have to support a large number of ad hoc queries that take the following form:

select count(*)
  from T
 where gender = 'M'
   and location in ( 1, 10, 30 )
   and age_group = '41 and over';

select *
  from t
 where (   ( gender = 'M' and location = 20 )
        or ( gender = 'F' and location = 22 ))
   and age_group = '18 and under';

select count(*) from t where location in (11,20,30);

select count(*) from t where age_group = '41 and over' and gender = 'F';

You would find that a conventional B*Tree indexing scheme would fail you. If you wanted to use an index to get the answer, you would need at least three and up to six combinations of possible B*Tree indexes to access the data via the index. Since any of the three columns or any subset of the three columns may appear, you would need large concatenated B*Tree indexes on

• GENDER, LOCATION, AGE_GROUP: For queries that used all three, or GENDER with LOCATION, or GENDER alone
• LOCATION, AGE_GROUP: For queries that used LOCATION and AGE_GROUP or LOCATION alone
• AGE_GROUP, GENDER: For queries that used AGE_GROUP with GENDER or AGE_GROUP alone

Answer 2

Bitmap index is dangerous when the table is frequently updated (the indexed column) because DML on a single row can lock many rows in the table. That's why it is more data warehouse tool than OLTP. Also the true power of bitmap indexes comes with combining more of them using logical operations and translating the result into ROWIDs (and then accessing the rows or aggregate them). In Oracle in general there is not so many reasons to rebuild an index. When frequently modified it will always adapt by 50/50 block split. It doesn't make sense to try to compact it to smallest possible space. One million rows today is nothing unless each row contains big amount of data.

Also be aware that BITMAP indexes requires Enterprise edition license.

Answer 3

Having only a single Bitmap Index on a table is useless in most times. The benefit of Bitmap Indexes you get when you have several created on a table and your query combines them.

Maybe a List-Partition is more suitable in your case.

Answer 4

The rationale for defining a bitmap index is not a few values in a column, but a query(s) that can profit from it accessing the table rows.

For example if you have say 4 countries equaly populated, Oracle will not use the index as a FULL TABLE SCAN comes cheaper.

If you have some "exotic" countries (very few records) BITMAP index could be used, but you will most probably spot no difference to a conventional index.