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'

---

"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.

Quote:

---

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

Quote:

---

Originally Posted by tabreaz

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

---

The present methodology is irrelevant, as you can't convert a table's partitioning scheme anyway -- you always have to create a new table. So you could create a new version as range-hash, for example.

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

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.

Quote:

---

Originally Posted by tabreaz

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.

---

If data is inserted 3 to 4 times a day, then bit map indexes may not be suitable. Do you have enough time to drop and recreate the bit map indexes?
Going back to the SELECT statement, you said that it returns 0.3 M rows and you want to improve performance of the query. Isn't it a good idea to tune the entire process.
What I mean is:
What does the process do after it gets 0.3 M rows?
Have you looked into that?
Did you trace the entire process?

Tamil