Slow query need suggestion on rewrite or index

[zxmgh]

Quick update, I created a covering index on table1(col1, col2, col3, col4, col5, col6), after that, the query only takes 4 seconds. Now my question is how badly will this new index affect the DML performance, btw, do I still need the old index on table1(col1, col2)?

Thanks

[mike9]

>Now my question is how badly will this new index affect the DML >performance
Test it and you will see.
But instead of creating such a huge index it would make more sense to create a IOT. Also the old index is not needed anymore.

[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

[zxmgh]

Mike,

I am not quite familiar with IOT, I thought it is only for small static tables. Besides, after adding the index, this query still use HASH JOIN, how is the performance of HASH JOIN compared with NESTED LOOP?

Thanks

[zxmgh]

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:

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