Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Smile Unanswered: Oracle performance issue

    sql1 runs 10 times slowe rhtna sql2 while giving same results,can someone explain why??

    sql1:
    select c.*
    FROM
    CHARGE C,
    ADJUSTMENT A,
    INVOICE_ITEM I
    where
    C.BAN = 400077190
    AND I.BAN = C.BAN
    AND I.ENT_SEQ_NO = C.INV_SEQ_NO
    AND (I.ESSENTIAL_TYPE = 'A'
    OR 'A' IN ('A','O' ))
    AND ((I.INV_STATUS = 'O'
    AND
    I.INV_TYPE IN ('DA','9 '))
    OR
    and (I.DSPT_ADJ_AMT > 0
    AND
    C.DSPT_TOT_AMT > 0)
    AND C.BAN = A.BAN(+)
    AND C.ENT_SEQ_NO = A.CHARGE_SEQ_NO(+)

    sql2:

    SELECT
    C.*
    FROM
    CHARGE C,
    ADJUSTMENT A,
    INVOICE_ITEM I
    WHERE
    I.BAN = 400077901
    AND (I.ESSENTIAL_TYPE = 'A' OR 'A'IN ('A','O' ))
    AND I.INV_STATUS = 'O'
    AND I.INV_TYPE IN ('DA','9 ')
    AND I.BAN = C.BAN
    AND I.ENT_SEQ_NO = C.INV_SEQ_NO
    AND C.BAN = A.BAN(+)
    AND C.ENT_SEQ_NO = A.CHARGE_SEQ_NO(+)
    UNION
    SELECT C.*
    FROM
    CHARGE C,
    ADJUSTMENT A,
    INVOICE_ITEM I
    WHERE
    I.BAN = 400077901
    AND (I.ESSENTIAL_TYPE = 'A' OR 'A' IN ('A','O'))
    AND I.DSPT_ADJ_AMT > 0
    AND C.DSPT_TOT_AMT > 0
    AND I.BAN = C.BAN
    AND I.ENT_SEQ_NO = C.INV_SEQ_NO
    AND C.BAN = A.BAN(+)
    AND C.ENT_SEQ_NO = A.CHARGE_SEQ_NO(+)

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    post explain plans for both.

    run autotrace and TKPROF tracefile.
    THese will both tell you why.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suspect that it can be made even faster,
    because of the poor coding used.
    The FROM clause should only include tables
    which contribute columns to the SELECT clause.
    Remove ADJUSTMENT A & INVOICE_ITEM I
    because they contribute no data to the resultset.

  4. #4
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by anacedent
    I suspect that it can be made even faster,
    because of the poor coding used.
    The FROM clause should only include tables
    which contribute columns to the SELECT clause.
    Remove ADJUSTMENT A & INVOICE_ITEM I
    because they contribute no data to the resultset.
    removing those two tables might change the resultset, since they are joined to the main table.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You'll get the same resultset if the SQL is similar to the following:
    SELECT C.*
    FROM CHARGE C,
    WHERE EXISTS (SELECT '1'
    FROM ADJUSTMENT A,
    WHERE C.BAN = A.BAN(+)
    AND C.ENT_SEQ_NO = A.CHARGE_SEQ_NO(+)
    )
    AND EXISTS (SELECT '1'
    FROM INVOICE_ITEM I
    WHERE I.BAN = 400077901
    AND (I.ESSENTIAL_TYPE = 'A' OR 'A'IN ('A','O' ))
    AND I.INV_STATUS = 'O'
    AND I.INV_TYPE IN ('DA','9 ')
    AND I.BAN = C.BAN
    AND I.ENT_SEQ_NO = C.INV_SEQ_NO
    )

  6. #6
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by anacedent
    You'll get the same resultset if the SQL is similar to the following:
    SELECT C.*
    FROM CHARGE C,
    WHERE EXISTS (SELECT '1'
    FROM ADJUSTMENT A,
    WHERE C.BAN = A.BAN(+)
    AND C.ENT_SEQ_NO = A.CHARGE_SEQ_NO(+)
    )
    AND EXISTS (SELECT '1'
    FROM INVOICE_ITEM I
    WHERE I.BAN = 400077901
    AND (I.ESSENTIAL_TYPE = 'A' OR 'A'IN ('A','O' ))
    AND I.INV_STATUS = 'O'
    AND I.INV_TYPE IN ('DA','9 ')
    AND I.BAN = C.BAN
    AND I.ENT_SEQ_NO = C.INV_SEQ_NO
    )
    But I think in general, EXISTS is slows than joins, right?

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Whoa whoa whoa!

    no way!

    Anacedent is correct. EXISTS in many situations (note the 'many') is a great deal faster and more efficient.

    WHY?
    because once it finds one condition in the table it moves on instead of scanning the whole table. This can be a huge difference when scanning large tables.

    In the end, don't take OUR word for it.
    Run a trace on both queries and post the result.
    You will see my padawan duckling!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The proof is in the EXPLAIN PLANS; which only YOU can create.
    Look at the situation this way...
    By using sub-queries you've made it easier for the optimizer to make good choices.

  9. #9
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by The_Duck
    Whoa whoa whoa!

    no way!

    Anacedent is correct. EXISTS in many situations (note the 'many') is a great deal faster and more efficient.

    WHY?
    because once it finds one condition in the table it moves on instead of scanning the whole table. This can be a huge difference when scanning large tables.
    EXITS is like a faster version of nest loops.
    But in others case when hash joins are used, EXISTS becomes slower.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    hey, whatever.
    EXPLAIN and TRACE tell the stories not me.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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