Bitmap Indexes

[tabreaz]

Hi everyone,

A table structure of which two columns never gets modified, and the data rarely get deleted.

One columns has very low cardinality with 5 distinct values and the other column has more than 10000 distinct values. A query involves predicates which involves both the columns, I want to create Bitmap Index on those two columns to utilize Bitmap Merge feature of Oracle. The data for the second Index is scattered globally, with around 1 row in 30-50 blocks. The data is already partitioned on other column. And the query will always use one partition.

But the only concern is locking every day around 5-6 Millions of records are inserted, apporximately around 0.4 Millions in a single partition. Is it advisable to have local bitmap indexes on those two columns?

Any input will really be very helpful.

[slimdave]

What's the partitioning scheme?

[tabreaz]

Hi dave,

its Hash partition.

[slimdave]

so it's partitioned on the column having 10,000 distinct values?

[tabreaz]

No, its not partitioned on that column.

Sorry for not being more clear. The partition is on some other column and the column with 10000 distinct values is different.

[slimdave]

hmmm. it seems to me that you'd do well to list partition that table on the five distinct values and index it only on the 10,000 values column. Is that an option?

[tabreaz]

Most of the queries and also the query for which I'm considering using Bitmap indexes, utilizes the column on which the partition is done on the table. I dont feel any other column can replace the present partitioned column.

The query is like:

SELECT col1....
FROM tab
WHERE partcol = 'XYZABC'
AND dist5_col IN (20, 35, 75)
AND dist10000_col = '20060504'

[gamyers]

"But the only concern is locking every day around 5-6 Millions of records are inserted, apporximately around 0.4 Millions in a single partition. Is it advisable to have local bitmap indexes on those two columns?"
If the inserts are OLTP, then steer clear of bitmaps.
Are subpartitions a possibility ?

[tabreaz]

No subpartitions can't be applied, as the Table is presently partition using hash method.

[tamilselvan]

SELECT col1....
FROM tab
WHERE partcol = 'XYZABC'
AND dist5_col IN (20, 35, 75)
AND dist10000_col = '20060504'

How many rows does the above SQL return?

Tamil