Slow query need suggestion on rewrite or index - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Slow query need suggestion on rewrite or index

  1. #11
    Join Date
    Apr 2001
    Posts
    125
    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

  2. #12
    Join Date
    Mar 2002
    Posts
    534
    >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.

  3. #13
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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:
    Code:
    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

  4. #14
    Join Date
    Apr 2001
    Posts
    125
    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

  5. #15
    Join Date
    Apr 2001
    Posts
    125
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width