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.
What's the partitioning scheme?
its Hash partition.
so it's partitioned on the column having 10,000 distinct values?
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.
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?
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:
WHERE partcol = 'XYZABC'
AND dist5_col IN (20, 35, 75)
AND dist10000_col = '20060504'
Last edited by tabreaz; 09-12-2006 at 03:24 PM.
"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 ?
No subpartitions can't be applied, as the Table is presently partition using hash method.
How many rows does the above SQL return?
Originally Posted by tabreaz
Click Here to Expand Forum to Full Width