1.If col1 has repeated values, then create an index on (col1, col2) with compress 1, and put this index in buffer_pool keep (or recycle), to reduce I/O calls.

2.Also try to run the query in parallel mode, since all the rows are selected in the outer query.

3.Create 2 bit map indexes on col1 and col2 separately , and use it in the subquery with index_combine hint.

Tamil