Which of these explain plan is better?

[badrinathn]

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.

[slimdave]

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