Index_combine question
735308Jul 13 2010 — edited Jul 13 2010Hi,
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.