Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 19, 2007 - 2:07 pm UTC

Last updated: November 26, 2018 - 4:58 pm UTC

Version: 10.0.2.0

Viewed 50K+ times! This question is

You Asked


MERGE INTO pkt_prty_fncl_st ppst
USING tmp_pkt_prty_fstate_clnb_stgg tmp
on (tmp.fncl_ast_id = ppst.fncl_ast_id
AND tmp.prty_id = ppst.prty_id
AND tmp.pkt_pcsg_st_cd = ppst.pkt_pcsg_st_cd
AND tmp.prcs_dt_clctn_st_id = ppst.prcs_dt_clctn_st_id )
WHEN MATCHED THEN UPDATE
SET ppst.pkt_prty_cl_in_ipti_amt = tmp.pkt_prty_cl_in_ipti_amt,
pkt_prty_cl_in_pubd_dfi_amt = tmp.pkt_prty_cl_in_pubd_dfi_amt,
pkt_prty_cl_in_pubd_dt = tmp.pkt_prty_cl_in_pubd_dt,
pkt_prty_cl_in_pubd_ipt_rt = tmp.pkt_prty_cl_in_pubd_ipt_rt,
pkt_prty_cl_in_schd_prin_amt = tmp.pkt_prty_cl_in_schd_prin_amt,
pkt_prty_cl_in_upb_amt = tmp.pkt_prty_cl_in_upb_amt,
pkt_prty_cl_in_usch_prin_amt = tmp.pkt_prty_cl_in_usch_prin_amt,
pkt_prty_pr_cl_in_pubd_upb_amt= tmp.pkt_prty_pr_cl_in_pubd_upb_amt,
last_upd_dt =i_pcsg_dt ;

UPDATE pkt_prty_fncl_st ppst
SET ( pkt_prty_cl_in_ipti_amt,
pkt_prty_cl_in_pubd_dfi_amt,
pkt_prty_cl_in_pubd_dt,
pkt_prty_cl_in_pubd_ipt_rt,
pkt_prty_cl_in_schd_prin_amt,
pkt_prty_cl_in_upb_amt,
pkt_prty_cl_in_usch_prin_amt,
pkt_prty_pr_cl_in_pubd_upb_amt,
last_upd_dt) =
( SELECT tmp.pkt_prty_cl_in_ipti_amt,
tmp.pkt_prty_cl_in_pubd_dfi_amt,
tmp.pkt_prty_cl_in_pubd_dt,
tmp.pkt_prty_cl_in_pubd_ipt_rt,
tmp.pkt_prty_cl_in_schd_prin_amt,
tmp.pkt_prty_cl_in_upb_amt,
tmp.pkt_prty_cl_in_usch_prin_amt,
tmp.pkt_prty_pr_cl_in_pubd_upb_amt,
i_pcsg_dt
FROM tmp_pkt_prty_fstate_clnb_stgg tmp
WHERE tmp.fncl_ast_id = ppst.fncl_ast_id
AND tmp.prty_id = ppst.prty_id
AND tmp.pkt_pcsg_st_cd = ppst.pkt_pcsg_st_cd
AND tmp.prcs_dt_clctn_st_id = ppst.prcs_dt_clctn_st_id )
WHERE EXISTS
( SELECT NULL
FROM tmp_pkt_prty_fstate_clnb_stgg tmp
WHERE tmp.fncl_ast_id = ppst.fncl_ast_id
AND tmp.prty_id = ppst.prty_id
AND tmp.pkt_pcsg_st_cd = ppst.pkt_pcsg_st_cd
AND tmp.prcs_dt_clctn_st_id = ppst.prcs_dt_clctn_st_id );

Do you think any one is better than other or are they evaluated same in most of the scenarios.Which one would you prefer. If you dont have option to do benchmark.

Thanks
Koshal

and Tom said...

I would have gone for:

update (select ppst.columns, tmp.columns
          from pkt_prty_fncl_st ppst, tmp_pkt_prty_fstate_clnb_stgg tmp 
         where mp.fncl_ast_id = ppst.fncl_ast_id
        AND  tmp.prty_id = ppst.prty_id
        AND  tmp.pkt_pcsg_st_cd = ppst.pkt_pcsg_st_cd
        AND  tmp.prcs_dt_clctn_st_id = ppst.prcs_dt_clctn_st_id )
set ppst = tmp (column by column of course)

personally. Requires primary key/unique constraint on the join columns in TMP, but that must be true for this to succeed anyway (that the join columms MUST be unique)


else, it would be the merge - if the constraint was not in place.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Merge VS Insert + Update

Snehasish Das, May 02, 2012 - 7:59 am UTC

Hi Tom,

Recently I had a technical interview in which I was asked the disadvantage of Merge. I was a bit taken back. When I asked back what the answer was, The person said that if the Number of updates were high then its better to have a Update followed by a Insert.
I tried to test with 100K updates and 10K insert in a 1 Million record table but didnt find any such difference.

Can you share your views on this please.

Regards,
Snehasish Das
Tom Kyte
May 02, 2012 - 1:56 pm UTC

Umm, I believe you dodged a bullet there.

I would have disagreed with them and then made the point via a benchmark, analysing the results.

and then not gotten the job anyway because they would be insulted.




The correct answer would be:

If there are a lot of updates - I'd want to use a create table as select (DDL) so I can skip undo, redo, row migration, etc. I only want to use merge/insert/update/delete for small numbers of rows - I want to use DDL for big numbers of rows.

But if I had to choose between an update of a lot of rows followed by an insert - versus a merge (which does both in one pass of the data) - I'd be using merge to avoid having to read the source and target tables multiple times.

Merge When NOT Matched Then UPDATE

Parthiban Nagarajan, May 03, 2012 - 1:00 am UTC

Hi Tom

I want to have a MERGE statement like

MERGE INTO ...
USING ...
ON ...
WHEN MATCHED THEN UPDATE SET COL = 'N'
WHEN NOT MATCHED THEN UPDATE SET COL = 'Y';

Let me describe the situation now.

create table tbl
(
key_col1 varchar2(5),
key_col2 varchar2(5),
flg_col1 varchar2(1)
);

create table err
(
key_col1 varchar2(5),
row_id varchar2(20)
);

insert into tbl values('A', 'A1', 'N');
insert into tbl values('A', 'A2', 'N');
insert into tbl values('B', 'B1', 'N');
insert into tbl values('B', 'B2', 'N');

insert into err
select key_col1, rowid
from tbl
where key_col1 = 'A'
and key_col2 like '%2';

select * from tbl;
select * from err;

update tbl
set flg_col1 = 'Y'
where rowid NOT IN (select row_id from err where key_col1 = 'A')
and key_col1 = 'A';


select * from tbl;

The UPDATE sometimes goes very slow. SLOW means that it runs for ever (or, we didn't had enough time to allow it to get completed)
So, we either
(1) loop
kill the process;
submit the prayer to the god;
restart the process;
sleep (x);
exit when the process finished;
end loop;
(2)
Silently and manually run the following two UPDATEs

update tbl
set flg_col1 = 'N'
where key_col1 = 'A';
update tbl
set flg_col1 = 'Y'
where rowid IN (select row_id from err where key_col1 = 'A')
and key_col1 = 'A';

and mark the work as completed.

update (INLINE_VIEW_WITH_OUTER_JOIN) - did not work because of the existing KEY constraints (we cannot change it because of the design)
update /*+ BYPASS_UJVC */ (INLINE_VIEW_WITH_OUTER_JOIN) - did not work (why I puzzled) and anyway will not use as per your advice

So, I thought of having the MERGE like

MERGE INTO tbl
USING (select row_id from err where key_col1 = 'A') err
ON (tbl.rowid = err.row_id)
WHEN MATCHED THEN UPDATE SET COL = 'N'
WHEN NOT MATCHED THEN UPDATE SET COL = 'Y';

P.S - I am worried about storing ROWID too. But it is an existing legacy system and hence no other go.
--cleanup
drop table tbl purge;
drop table err purge;

Thanks and regards

Tom Kyte
May 03, 2012 - 8:57 am UTC

well, I don't get it - that merge would basically update every single row in the table - which would be slightly "expensive"

when the update runs forever - is it doing work or is it perhaps blocked? when it runs forever - is the plan the same as the "normal" plan? What have you done to diagnose this so far?

Assuming a unique constraint on ROW_ID, you can try:

update
( select tbl.flg_col1
    from tbl, (select * from err where key_col1='A') err
   where tbl.key_col1 = 'A'
     and tbl.rowid = err.row_id(+)
     and err.row_id is null )
  set flg_col1 = 'Y'
/

MERGE INTO sql tune

Roshan Bisnath, November 20, 2018 - 9:12 am UTC

Hi Tom,

could you please advise how I can rewrite the

UPDATE DWH_TOT_BILL_AMT s
   SET s.LAST_BILL_AMT =
          (SELECT SUM ( (c.INVOICE_AMT + c.INV_TAX_AMT) - ADJUSTMENT_AMT)
             FROM DWH_DETAIL_BILL_AMT c
            WHERE s.account_code_n = c.account_code
                  AND SUBSTR (c.invoice_date, 4, 6) =
                         SUBSTR ( (SYSDATE - 30), 4, 6))
WHERE s.LAST_BILL_DATE IS NULL;


Tuned sql:
merge into DWH_TOT_BILL_AMT s using (
SELECT SUM ( (c.INVOICE_AMT + c.INV_TAX_AMT) - ADJUSTMENT_AMT)
FROM DWH_DETAIL_BILL_AMT c) ON (s.account_code_n = c.account_code AND SUBSTR (c.invoice_date, 4, 6) = SUBSTR ( (SYSDATE - 30), 4, 6))

WHERE s.LAST_BILL_DATE IS NULL;


Second SQL:

UPDATE DWH_TOT_BILL_AMT cd

   SET cd.tot_surc =

  (SELECT SUM (trans_amt_n) / 100

   FROM  DWH_INVOICE_DETAIL b, DWH_ACCOUNT_MASTER c

   WHERE  b.account_link_code_n = c.account_link_code_n

   AND b.article_code_v = 'T_LPF00001'

   AND c.account_code_n = cd.account_code_n)

WHERE cd.LAST_BILL_DATE IS NULL;

COMMIT;


Tuned SQL:

MERGE INTO DWH_TOT_BILL_AMT  cd USING (

SELECT SUM (trans_amt_n)/100 FROM  DWH_INVOICE_DETAIL b, DWH_ACCOUNT_MASTER c

   WHERE  b.account_link_code_n = c.account_link_code_n

   AND b.article_code_v = 'T_LPF00001'

   AND c.account_code_n = cd.account_code_n




Thanks,

Roshan


Chris Saxon
November 26, 2018 - 4:58 pm UTC

You can merge into a subquery. So something along the lines of:

merge into (
  select * from dwh_tot_bill_amt s
  where  s.last_bill_date is null
) s 
using (
  select c.account_code, sum(( c.invoice_amt + c.inv_tax_amt ) - adjustment_amt )
  from   dwh_detail_bill_amt c
  where  substr (c.invoice_date, 4, 6) = substr ( (sysdate - 30), 4, 6)
  group  by c.account_code
)
on ( s.account_code_n = c.account_code)
when matched then update
  set ...


But it's hard to be sure without the table DDL (create table) & sample data.