Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Index_combine question

735308Jul 13 2010 — edited Jul 13 2010
Hi,
I have a query like this.
SELECT count(*)
FROM table_1 t1
WHERE col1 = :Number1
AND col2= :Number2
AND col3 = :Number3
AND col4 between :fromdate and :todate;
The table has about 3 mil rows.
I have a Unique key index with columns combined- col1, col2, col4 and columns col5 and col6 which are not used in this query but when i do an explain plan or run the query, it does not use this index and if i force to use it using index hint, it s very slow.
If i look at which index optimizer is choosing, its using a secondary btree index ind2 with columns col1 and col3.
When it runs, this is slower but not as slow as unique index.
I was trying to tune it and saw that there is another index ind3 with columns col1 and col2.

So, i thought i could use INDEX_COMBINE of ind2 and ind3 which would be 3 indexed columns col1, 2 and 3 and it worked gr8.

This is where i had some questions and thought of posting it here.

Looks like the index_combine hint converts ind2 and ind3 to bitmap indexes on the fly and when i execute just the plan, i see that the CPU cost has increased tremendously bcos of the BitMap conversion from rowids for each index.

The columns col1 , col2 and col3 are all non-unique columns by themselves and so, thought this index_combine hint should work consistently based on volume of data, scattered data across db blocks, etc but not 100% sure.
Any inputs will be greatly appreciated or anything i m missing here.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 10 2010
Added on Jul 13 2010
9 comments
1,591 views