Which of these explain plan is better?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Which of these explain plan is better?

  1. #1
    Join Date
    Jan 2001
    Posts
    642

    Which of these explain plan is better?

    Hi,

    I have a update statement

    UPDATE BOOKINGS B
    SET GLOBALPARTNUM = (SELECT U.GLOBAL
    FROM UPD1 U
    WHERE U.CURRENT_FRANCODE = B.FRANCODE
    AND U.GLOBALPART = B.GLOBALPARTNUM
    AND U.CURRENT_MANFPARTNUM = B.MANFPARTNUM
    AND U.CURRENT_REGION = B.REGIONCODE
    and B.REPLACEDFLAG IS NULL )
    For which the explain plan is

    UPDATE STATEMENT Cost = 10744
    UPDATE BOOKINGS
    TABLE ACCESS FULL BOOKINGS
    INDEX RANGE SCAN UPD1_1

    WHen I change the join as

    UPDATE BOOKINGS B
    SET GLOBALPARTNUM = (SELECT U.GLOBAL
    FROM UPD1 U
    WHERE U.CURRENT_FRANCODE = B.FRANCODE
    AND U.GLOBALPART = B.GLOBALPARTNUM
    AND U.CURRENT_MANFPARTNUM = B.MANFPARTNUM
    AND U.CURRENT_REGION = B.REGIONCODE)
    where B.REPLACEDFLAG IS NULL

    Query Plan
    ----------------------------------------------------------------------UPDATE STATEMENT Cost = 10744
    UPDATE BOOKINGS
    TABLE ACCESS FULL BOOKINGS
    FILTER
    INDEX RANGE SCAN UPD1_1



    Here the cost of both statements is the same, while uses a filter other does not. WHich one of these would perform a better way. The Bookings table has 5Million rec and upd1 will have 4000 records.

    Please advice.
    There is always a better way to do the things.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    They are different updates entirely.

    The first one will update all rows of bookings, the second will update only those rows where replacedflag is null
    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