Oracle Update and merge update statement performance

Question

I have run the below DML statements in Oracle 12C, one by straight update and the other using merge. When I run update statement on a table which is 50k rows data it took 9 mins to update. When I use merge update it took me under 40sec to update. Below are my queries. Could anyone help me if I have written the logic of merge statement correctly.

UPDATE RJR_M16_DM_PRD.DLVR_BRAND_INTEGRATED_PRE

SET

(PRICE_TIER_SORT,PRICE_TIER_GROUP_SORT,SIGNER_GROUP_SORT,SIZE_CODE_SORT,SIZE_GROUP_SORT,STRENGTH_SORT,
SUPER_BRAND_FAMILY_SORT,SUPER_MANUF_BF_SORT,TAR_LEVEL_SORT,
TIP_COLOR_SORT,SUPER_MANUF_BF_BRAND,SUPER_MANUF_BF_BRAND_DESC,SUPER_MANUF_BF_BRAND_SORT, ECIG_PUFF_EQUIV, ECIG_PUFF_EQUIV_DESC) =

(SELECT PRICE_TIER_SORT,PRICE_TIER_GROUP_SORT,SIGNER_GROUP_SORT,SIZE_CODE_SORT,SIZE_GROUP_SORT,STRENGTH_SORT,
SUPER_BRAND_FAMILY_SORT,SUPER_MANUF_BF_SORT,
TAR_LEVEL_SORT,TIP_COLOR_SORT,SUPER_MANUF_BF_BRAND,SUPER_MANUF_BF_BRAND_DESC,SUPER_MANUF_BF_BRAND_SORT, ECIG_PUFF_EQUIV, ECIG_PUFF_EQUIV_DESC

FROM (SELECT * FROM RJR_M16_DM_PRD.DLVR_BRAND_INTEGRATED_PRE WHERE SOURCE_1 = '1') a

WHERE a.CLIENT_BRAND_OID = DLVR_BRAND_INTEGRATED_PRE.CLIENT_BRAND_OID)

Where SOURCE_2 = '1' AND CLIENT_BRAND_OID IN (SELECT CLIENT_BRAND_OID

FROM RJR_M16_DM_PRD.DLVR_BRAND_INTEGRATED_PRE WHERE SOURCE_1 = '1');  

 

 

MERGE INTO DLVR_BRAND_INTEGRATED_PRE C

USING (SELECT CLIENT_BRAND_OID,PRICE_TIER_SORT,PRICE_TIER_GROUP_SORT,SIGNER_GROUP_SORT,SIZE_CODE_SORT,SIZE_GROUP_SORT,STRENGTH_SORT,
SUPER_BRAND_FAMILY_SORT,SUPER_MANUF_BF_SORT,
TAR_LEVEL_SORT,TIP_COLOR_SORT,SUPER_MANUF_BF_BRAND,SUPER_MANUF_BF_BRAND_DESC,SUPER_MANUF_BF_BRAND_SORT, ECIG_PUFF_EQUIV, ECIG_PUFF_EQUIV_DESC

FROM (SELECT * FROM DLVR_BRAND_INTEGRATED_PRE WHERE SOURCE_1 = '1') ) B

   ON (C.CLIENT_BRAND_OID = B.CLIENT_BRAND_OID)

   WHEN MATCHED

       THEN

       UPDATE SET C.PRICE_TIER_SORT=B.PRICE_TIER_SORT,C.PRICE_TIER_GROUP_SORT=B.PRICE_TIER_GROUP_SORT,

       C.SIGNER_GROUP_SORT=B.SIGNER_GROUP_SORT,C.SIZE_CODE_SORT=B.SIZE_CODE_SORT,

       C.SIZE_GROUP_SORT=B.SIZE_GROUP_SORT,C.STRENGTH_SORT=B.STRENGTH_SORT,C.SUPER_BRAND_FAMILY_SORT=B.SUPER_BRAND_FAMILY_SORT,

       C.SUPER_MANUF_BF_SORT=B.SUPER_MANUF_BF_SORT,C.TAR_LEVEL_SORT=B.TAR_LEVEL_SORT,C.TIP_COLOR_SORT=B.TIP_COLOR_SORT,

       C.SUPER_MANUF_BF_BRAND=B.SUPER_MANUF_BF_BRAND,C.SUPER_MANUF_BF_BRAND_DESC=B.SUPER_MANUF_BF_BRAND_DESC,

       C.SUPER_MANUF_BF_BRAND_SORT=B.SUPER_MANUF_BF_BRAND_SORT,C.ECIG_PUFF_EQUIV=B.ECIG_PUFF_EQUIV,C.ECIG_PUFF_EQUIV_DESC=B.ECIG_PUFF_EQUIV_DESC

       Where SOURCE_2 = '1' AND CLIENT_BRAND_OID IN (SELECT CLIENT_BRAND_OID

                                                FROM DLVR_BRAND_INTEGRATED_PRE WHERE SOURCE_1 = '1');

Thanks in advance

Execution plan for update statement:

Execution plan for merge update statement:

Oracle Update and merge update statement performance

0 Answers