Bitmap Indexes

[slimdave]

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.

[tabreaz]

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

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

[tamilselvan]

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