Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: Tuning SQL query

    Hi,
    wher I run this query:

    HTML Code:
    SELECT a.*, d.C1
      FROM t1 a, t2 b, t3 c, t4 d
     WHERE C2 IS NOT NULL
       AND C1 > '0005'
       AND a.C3 = b.C4
       AND NOT EXISTS (
              SELECT *
                FROM t5
               WHERE C5 = C2
                 AND C6 = DECODE (b.C7,'AB', 0,
                                  'BC', '555',
                                  a.C3
                                   ))
       AND a.C2 = c.C5
       AND a.C8 = d.C8;
    It is taking lot of time (more 1 hour), I need some suggestions how to tune it.
    I have noticed that If I remove the "NOT EXISTS" clause the query becomes very fast.
    I'd like to know How Can I write more efficient query, and speed up existing code.

    Thanks in advance!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Please post the execution plan, the table definition for the tables and the indexes defined on those tables.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by shammat View Post
    Please post the execution plan, the table definition for the tables and the indexes defined on those tables.
    below ddl and execution plan:


    HTML Code:
    CREATE TABLE t1
    (
      ID_F             NUMBER(13)                 NOT NULL,
      C2               NUMBER(13)
    );
    
    CREATE TABLE t2
    (
      ID_D           NUMBER(4)                      NOT NULL,
      C7       VARCHAR2(5 BYTE)
    );
    
    CREATE TABLE t3
    (
      C5         NUMBER(13) 
    );
    
    CREATE TABLE t4
    (
      ID_C            NUMBER                      NOT NULL,
      C1         VARCHAR2(14 BYTE)
    );
    
    CREATE TABLE t5
    (
      ID_C           NUMBER                         NOT NULL,
      C5             NUMBER                         NOT NULL,
      C6             NUMBER                         NOT NULL
    );
    
    ----------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                   |   103K|    12M|       |   451K  (1)| 01:30:17 |
    |*  1 |  FILTER                      |                   |       |       |       |            |          |
    |*  2 |   HASH JOIN                  |                   |   103K|    12M|       | 57064   (1)| 00:11:25 |
    |   3 |    TABLE ACCESS FULL         | t2                |    66 |   528 |       |     3   (0)| 00:00:01 |
    |   4 |    NESTED LOOPS              |                   |   103K|    11M|       | 57059   (1)| 00:11:25 |
    |*  5 |     HASH JOIN                |                   |   106K|    11M|  3440K| 57051   (1)| 00:11:25 |
    |*  6 |      TABLE ACCESS FULL       | t4                |   106K|  2188K|       |  2012   (2)| 00:00:25 |
    |*  7 |      TABLE ACCESS FULL       | t1                |  6343K|   568M|       | 22970   (2)| 00:04:36 |
    |*  8 |     INDEX UNIQUE SCAN        | SYS_C0020000      |     1 |     6 |       |     1   (0)| 00:00:01 |
    |*  9 |   TABLE ACCESS BY INDEX ROWID| t5                |     1 |     9 |       |     4   (0)| 00:00:01 |
    |* 10 |    INDEX RANGE SCAN          | IDX_C             |     1 |       |       |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------------

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The table definitions don't match with the query. t1 does not have a c8 or a c3 column.

Posting Permissions

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