-
Bitmap Indexes
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.
-
What's the partitioning scheme?
-
Hi dave,
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:
Code:
SELECT col1....
FROM tab
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.
-
Originally Posted by tabreaz
Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|