Any performance advantage?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Any performance advantage?

  1. #1
    Join Date
    Apr 2002
    Posts
    17

    Question Any performance advantage?

    The purpose of this query is to find out a location that holds $itmcls and $itmcod, if such locations are found, I want to pick the one with the minimum DIST value.

    (This is in SQR code by the way, but I want to know about performance-wise if it's better than another method)


    select
    MIN(L1.LOCATN) &ret_locatn
    from LOCATN L1
    WHERE
    L1.ITMCLS = $itmcls AND
    L1.ITMCOD = $itmcod AND
    L1.ARECOD = {AP$_ARECOD_PW_SQL} AND
    L1.ZONTRV =
    (select
    MIN(L2.DIST)
    from LOCATN L2
    where
    L2.ITMCLS = L1.ITMCLS AND
    L2.ITMCOD = L1.ITMCLS AND
    L2.ARECOD = {AP$_ARECOD_PW_SQL})
    end-select


    I was wondering if I could get any performance advantage by comparing L2 with L1's ITMCLS and ITMCOD. I was comparing
    L2.ITMCLS = $itmcls AND
    L2.ITMCOD = $itmcod
    previously.

    Thanks.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    maybe you would, and maybe you wouldn't. the optimizer may be smart enough to work out that L2.ITMCLS = $itmcls and L2.ITMCOD = $itmcod on it's own.

    Personally, I wouldn't take the chance. I would go for the second option myself. You could try benchmarking the two also.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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