-
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.
-
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
-
HTML Code:
Hi tamil,
How many rows does the above SQL return?
Approximately, 0.2-0.3 Million.
Dave, its not easy to change the design of the table as of now. We need to test it and take approval which may take much time. I will discuss abt having a range-hash partition on 10000disctintval - present partition key
Last edited by tabreaz; 09-14-2006 at 01:59 AM.
-
Hi,
As I posted early, presently we cant change the design as the management approval is not yet decided. Is there any other way to improve the performance of that query?
Data is inserted at bulk, maximum 3-4 times a day data is inserted into a single partition that also not concurently. But data can be inserted concurently into different partitions.
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
|