Hi,

I have one more question:

Now I am told by my developers that they will use another query, the only difference between the first query and the second query is instead of using table1.col3, they plan to use another date column - table1.col7, the second query looks like:

Code:
SELECT count(1)
  FROM table1 INNER JOIN table2 ON ( table1.col1 = table2.cola 
                                     AND
                                     table1.col2 = table2.colb)
 WHERE table1.col7<=SYSDATE
   AND (
        (
         table1.col4='a' 
         AND 
         table1.col5<100
        )
        OR
        (
         table1.col4='b' 
         AND 
         (
          table1.col5=10 
          OR
          table1.col5=20
         )
        )
       ) 
   AND table1.col6<>'abc' 
   AND table1.col6<>'def' 
   AND table1.col6<>'ghi'
   AND table2.colc='xy' 
   AND table2.cold='z'
Sadly I found that the index I created on table1(col1, col2, col3, col4, col5, col6) for the first query can not be used for the second query, should I create another index on table1(col1, col2, col4, col5, col6, col7), is it a good approach? What's the best practice in this situation?

Thanks