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.