Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Orcale Update Statment

    I am new to oracle, and it is proving to be a pain. I am trying to flag some data. The select I have is very simple and have a very cheep explain plan, but when I put it in an Update statment the explain plan goes through the roof. I am also having issues finding the tools to evaluate the SQL for suggestions.

    I am running on a Sun Solaris box with Oracle 10g

    My code is as follows

    Code:
    update MARSTOP.FREIGHTELEMENTS_B 
    set delete_flag = (Select 1
                      from MARSTOP.FREIGHTELEMENTS_B F inner join MARSTOP.BILLOFLADINGS_B B on f.blno = b.blno
                       where b.SHCODE = '100100720'
                         and b.CNCODE in ('12700123850','127SEV01013')
                         and b.BBCODE = '100100720'
                         and f.custno = '100100720')
    WHERE EXISTS (Select 1
                      from MARSTOP.FREIGHTELEMENTS_B F inner join MARSTOP.BILLOFLADINGS_B B on f.blno = b.blno
                       where b.SHCODE = '100100720'
                         and b.CNCODE in ('12700123850','127SEV01013')
                         and b.BBCODE = '100100720'
                         and f.custno = '100100720');
    If there is a beter way to write this in Oracle or if you can help me find the performance advisors I would be greatly apreciative.
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    The Answer

    I hate keeping track of all the stupid tweaks between the major databases......

    Code:
    update FREIGHTELEMENTS_B f
    set f.delete_flag = 1
    where f.blno in
    (
    select b.blno
    from BILLOFLADINGS_B b
    where b.SHCODE = '100100720'
    and b.CNCODE in ('12700123850','127SEV01013')
    and b.BBCODE = '100100720'
    )
    and f.custno = '100100720';
    This takes the estimated cost from 20980 to 8.....
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Lowest cost?

    Would this produce a lower cost?:
    Code:
    UPDATE marstop.freightelements_b f
       SET delete_flag = 1
     WHERE f.custno = '100100720'
       AND EXISTS (
              SELECT 1
                FROM billofladings_b b
               WHERE b.shcode = '100100720'
                 AND b.blno = f.blno
                 AND b.cncode IN ('12700123850', '127SEV01013')
                 AND b.bbcode = '100100720');
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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