Slow query need suggestion on rewrite or index

[tamilselvan]

If this query is executed once in a while, then I would not suggest to add 6 column index.

Drop the index.
Try this SQL:

select count(*)
from ( SELECT /*+ FULL(table1) parallel(table1 8) */ 
              rownum, col1, col2 
         from table1 
 WHERE table1.col3<=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'
   ) table1,  
   (select rownum, cola, colb 
      from table2 
     where table2.colc='xy' 
       AND table2.cold='z'
   ) table2
 where table1.col1 = table2.cola  
   AND table1.col2 = table2.colb
;

Tamil