Oracle Live SQL - Script: UPDATE-if-exists, INSERT-if-not-exists (aka UPSERT) data with MERGE

Statement 1

create sequence customer_merge_id

Sequence created.

Statement 2

Target table to load

create table customers_dim (  
  customer_id integer  
    not null primary key,  
  full_name   varchar2(100)  
    not null,  
  birth_date  date  
    not null,  
  insert_datetime timestamp  
    default systimestamp not null,  
  update_datetime timestamp  
    default systimestamp not null,  
  merge_run_id    integer  
    default on null customer_merge_id.nextval not null  
)

Table created.

Statement 3

Source table

create table customers_stage (  
  customer_id integer  
    not null primary key,  
  full_name   varchar2(100)  
    not null,  
  birth_date  date  
    not null  
)

Table created.

Statement 4

exec dbms_random.seed ( 0 )

Statement processed.

Statement 5

Populate source table

insert into customers_stage  
with rws as (  
  select level id from dual  
  connect by level <= 100  
)  
  select id,   
         initcap (   
           dbms_random.string ( 'l', dbms_random.value ( 2, 10 ) ) || ' ' ||  
           dbms_random.string ( 'l', dbms_random.value ( 2, 10 ) )   
         ) nm,  
         date'1920-01-01' +  
           numtoyminterval ( dbms_random.value ( 2, 1200 ), 'month' ) +  
           numtodsinterval ( round ( dbms_random.value ( 1, 31 ) ), 'day' ) dt  
  from   rws

100 row(s) inserted.

Statement 6

commit

Statement processed.

Statement 7

select * from customers_stage 
fetch  first 5 rows only


CUSTOMER_ID FULL_NAME BIRTH_DATE
1 Vgf Blyyv 10-AUG-65
2 Tvmiey Tsd 06-JUN-81
3 Jy Sawz 06-JUN-22
4 Kdc Vjji 05-MAY-96
5 Idnrykc Ew 07-APR-81

5 rows selected.

CUSTOMER_ID	FULL_NAME	BIRTH_DATE
1	Vgf Blyyv	10-AUG-65
2	Tvmiey Tsd	06-JUN-81
3	Jy Sawz	06-JUN-22
4	Kdc Vjji	05-MAY-96
5	Idnrykc Ew	07-APR-81

Statement 8

select * from customers_dim

no data found

Statement 9

This does update-if-exists, insert-if-not-exists logic in one statement. The target table is currently empty, so every row from the source is an insert into the target

UPSERT rows

merge into customers_dim cd  
using customers_stage cs  
on    ( cd.customer_id = cs.customer_id )  
when not matched then   
  insert (   
    cd.customer_id, cd.full_name, cd.birth_date   
  ) values (   
    cs.customer_id, cs.full_name, cs.birth_date  
  )  
when matched then   
  update  
  set cd.full_name = cs.full_name,  
      cd.update_datetime = systimestamp

Statement processed.

Statement 10

select count (*) from customers_dim


COUNT(*)
100

COUNT(*)
100

Statement 11

select customer_id, full_name, birth_date  
from   customers_dim 
minus   
select * from customers_stage

no data found

Statement 12

update customers_stage 
set    full_name = 'New Name' 
where  rownum <= 10

10 row(s) updated.

Statement 13

All the rows in the source have been loaded, so this updates every row in the target.

UPSERT changes

merge into customers_dim cd  
using customers_stage cs  
on    ( cd.customer_id = cs.customer_id )  
when not matched then   
  insert (   
    cd.customer_id, cd.full_name, cd.birth_date   
  ) values (   
    cs.customer_id, cs.full_name, cs.birth_date  
  )  
when matched then   
  update  
  set cd.full_name = cs.full_name,  
      cd.update_datetime = systimestamp

Statement processed.

Statement 14

select customer_id, full_name, birth_date  
from   customers_dim 
where  full_name = 'New Name'


CUSTOMER_ID FULL_NAME BIRTH_DATE
1 New Name 10-AUG-65
2 New Name 06-JUN-81
3 New Name 06-JUN-22
4 New Name 05-MAY-96
5 New Name 07-APR-81
6 New Name 31-MAY-00
7 New Name 07-MAY-11
8 New Name 22-JUN-62
9 New Name 27-JUL-69
10 New Name 10-SEP-67

10 rows selected.

CUSTOMER_ID	FULL_NAME	BIRTH_DATE
1	New Name	10-AUG-65
2	New Name	06-JUN-81
3	New Name	06-JUN-22
4	New Name	05-MAY-96
5	New Name	07-APR-81
6	New Name	31-MAY-00
7	New Name	07-MAY-11
8	New Name	22-JUN-62
9	New Name	27-JUL-69
10	New Name	10-SEP-67

Statement 15

select update_datetime, count(*) 
from   customers_dim 
group  by update_datetime


UPDATE_DATETIME COUNT(*)
19-JUL-22 10.39.27.591609 AM 100

UPDATE_DATETIME	COUNT(*)
19-JUL-22 10.39.27.591609 AM	100

Statement 16

update customers_stage 
set    full_name = 'Another New Name' 
where  rownum <= 10

10 row(s) updated.

Statement 17

The previous MERGE updated every row in the target table - even if the names are the same. This filters the update, so it only changes rows with values for FULL_NAME.

UPSERT filtered UPDATE

merge into customers_dim cd  
using customers_stage cs  
on    ( cd.customer_id = cs.customer_id )  
when matched then   
  update  
  set cd.full_name = cs.full_name,  
      cd.update_datetime = systimestamp  
  where cd.full_name <> cs.full_name

Statement processed.

Statement 18

select update_datetime, count(*) 
from   customers_dim 
group  by update_datetime


UPDATE_DATETIME COUNT(*)
19-JUL-22 10.39.27.591609 AM 90
19-JUL-22 10.39.27.620554 AM 10

2 rows selected.

UPDATE_DATETIME	COUNT(*)
19-JUL-22 10.39.27.591609 AM	90
19-JUL-22 10.39.27.620554 AM	10

Statement 19

rollback

Statement processed.

Statement 20

truncate table customers_dim

Table truncated.

Statement 21

You can also apply a WHERE clause to the INSERT section of MERGE. This loads half the rows from the source into the target.

UPSERT filtered INSERT

merge into customers_dim cd  
using customers_stage cs  
on    ( cd.customer_id = cs.customer_id )  
when not matched then   
  insert (   
    cd.customer_id, cd.full_name, cd.birth_date   
  ) values (   
    cs.customer_id, cs.full_name, cs.birth_date  
  )  
  where  cs.customer_id <= 50

Statement processed.

Statement 22

select count (*) from customers_dim


COUNT(*)
50

COUNT(*)
50

Statement 23

You can use MERGE with bind variables. To do this, use DUAL as the source table and join a primary or unique key on the target table to a variable. Supply the bind variables as the inputs for the INSERT & UPDATE sections. All the PL/SQL variables in this MERGE statement are bind variables.

Merging bind variables

declare  
  cust_id   integer := -1;  
  new_name  varchar2(30) := 'New Baby';  
  birthday  date := date'2022-07-19';  
begin  
  merge into customers_dim cd  
  using dual  
  on   ( customer_id = cust_id )  
  when not matched then   
    insert (   
      cd.customer_id, cd.full_name, cd.birth_date   
    ) values (   
      cust_id, new_name, birthday  
    )  
  when matched then   
    update  
    set cd.full_name = new_name,  
        cd.update_datetime = systimestamp;  
end;

Statement processed.

Statement 24

select * from customers_dim 
where  customer_id = -1


CUSTOMER_ID FULL_NAME BIRTH_DATE INSERT_DATETIME UPDATE_DATETIME MERGE_RUN_ID
-1 New Baby 19-JUL-22 19-JUL-22 10.39.27.672965 AM 19-JUL-22 10.39.27.672965 AM 301

CUSTOMER_ID	FULL_NAME	BIRTH_DATE	INSERT_DATETIME	UPDATE_DATETIME	MERGE_RUN_ID
-1	New Baby	19-JUL-22	19-JUL-22 10.39.27.672965 AM	19-JUL-22 10.39.27.672965 AM	301

Statement 25

declare 
  cust_id   integer := -1; 
  new_name  varchar2(30) := 'Sally Squirrel'; 
  birthday  date := date'2022-07-19'; 
begin 
  merge into customers_dim cd 
  using dual 
  on   ( customer_id = cust_id ) 
  when not matched then  
    insert (  
      cd.customer_id, cd.full_name, cd.birth_date  
    ) values (  
      cust_id, new_name, birthday 
    ) 
  when matched then  
    update 
    set cd.full_name = new_name, 
        cd.update_datetime = systimestamp; 
end;

Statement processed.

Statement 26

select * from customers_dim 
where  customer_id = -1


CUSTOMER_ID FULL_NAME BIRTH_DATE INSERT_DATETIME UPDATE_DATETIME MERGE_RUN_ID
-1 Sally Squirrel 19-JUL-22 19-JUL-22 10.39.27.672965 AM 19-JUL-22 10.39.27.682902 AM 301

CUSTOMER_ID	FULL_NAME	BIRTH_DATE	INSERT_DATETIME	UPDATE_DATETIME	MERGE_RUN_ID
-1	Sally Squirrel	19-JUL-22	19-JUL-22 10.39.27.672965 AM	19-JUL-22 10.39.27.682902 AM	301

Statement 27

rollback

Statement processed.

Statement 28

exec dbms_session.sleep ( 15 )

ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SESSION' must be declared

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 29

truncate table customers_dim

Table truncated.

Statement 30

Logging table for inserts

create table inserted_customers (  
  customer_id     integer,   
  insert_datetime timestamp  
)

Table created.

Statement 31

Logging table for updates

create table updated_customers (  
  customer_id     integer,   
  update_datetime timestamp  
)

Table created.

Statement 32

After running MERGE, SQL%ROWCOUNT gives the total rows affected; not the INSERT vs UPDATE split. This trigger logs which operation was done for each CUSTOMER_ID

Track INSERTs vs UPDATEs

create or replace trigger merge_tracking_trig  
for insert or update on customers_dim  
compound trigger  
  updated_rows   dbms_sql.number_table;  
  inserted_rows  dbms_sql.number_table;  
  merge_datetime timestamp := systimestamp;  
  
  after each row is  
  begin  
    if inserting then   
      inserted_rows ( :new.customer_id ) :=   
        :new.customer_id;  
    elsif updating then  
      updated_rows ( :new.customer_id ) :=   
        :new.customer_id;  
    end if;  
  end after each row;  
    
  after statement is  
  begin  
    forall i in indices of updated_rows  
      insert into updated_customers   
      values ( updated_rows(i), merge_datetime );  
      
    forall i in indices of inserted_rows  
      insert into inserted_customers   
      values ( inserted_rows(i), merge_datetime );  
  end after statement;  
  
end merge_tracking_trig;

Trigger created.

Statement 33

This runs the MERGE twice. The first time it inserts all the rows. The second some rows are updated and some are inserted.

declare  
  procedure upsert_customers as   
  begin  
      
    merge /*+ monitor */into customers_dim cd  
    using customers_stage cs  
    on    ( cd.customer_id = cs.customer_id )  
    when not matched then   
      insert (   
        cd.customer_id, cd.full_name, cd.birth_date   
      ) values (   
        cs.customer_id, cs.full_name, cs.birth_date  
      )  
    when matched then   
      update  
      set cd.full_name = cs.full_name,  
          cd.update_datetime = systimestamp  
      where  cd.full_name <> cs.full_name;  
    /*  
    Get transation details - not available in LiveSQL   
    select xid into trans_id from v$transaction;  
    dbms_output.put_line (   
      'Merged ' || sql%rowcount ||   
      ' XID ' || rawtohex ( trans_id )  
    );  
    */  
  end;  
begin  
  upsert_customers;  
    
  commit;  
    
  /* Change source */  
  update customers_stage  
  set    full_name = 'New Name ' || rownum  
  where  customer_id <= 10;  
    
  /* Remove from target */  
  delete customers_dim  
  where  customer_id > 90;  
    
  commit;  
    
  upsert_customers;  
  
end;

Statement processed.

Statement 34

View the logs to see the add vs change split for rows. Note the triggering time for the inserts and updates are different. To ensure you know which rows were affected by the MERGE, it's best to log a run id for the statement.

INSERT vs UPDATE log

select nvl ( insert_datetime, update_datetime ) change_time,   
       count (*) ,   
       count ( insert_datetime ),   
       count ( update_datetime )  
from   inserted_customers  
full outer join updated_customers  
on     insert_datetime = update_datetime  
group  by nvl ( insert_datetime, update_datetime )  
order  by change_time


CHANGE_TIME COUNT(*) COUNT(INSERT_DATETIME) COUNT(UPDATE_DATETIME)
19-JUL-22 10.39.27.747236 AM 100 100 0
19-JUL-22 10.39.27.763447 AM 10 0 10
19-JUL-22 10.39.27.764533 AM 10 10 0

3 rows selected.

CHANGE_TIME	COUNT(*)	COUNT(INSERT_DATETIME)	COUNT(UPDATE_DATETIME)
19-JUL-22 10.39.27.747236 AM	100	100	0
19-JUL-22 10.39.27.763447 AM	10	0	10
19-JUL-22 10.39.27.764533 AM	10	10	0

Statement 35

Use this to view the types of changes to the table for recent transactions. Version queries only return committed transactions, so details of the MERGE are missing. Note change information is unavailable in Live SQL

Flashback versions query

select versions_xid, versions_operation, count(*)   
from   customers_dim  
  versions between scn   
  minvalue and maxvalue  
group  by versions_xid, rollup ( versions_operation )  
order  by versions_xid


VERSIONS_XID VERSIONS_OPERATION COUNT(*)
 -  - 90
 -  - 90

2 rows selected.

VERSIONS_XID	VERSIONS_OPERATION	COUNT(*)
-	-	90
-	-	90

Statement 36

commit

Statement processed.

Statement 37

After committing the change, both MERGE transactions should be visible (run in your own environment to see this)

Flashback versions query

select versions_xid, versions_operation, count(*)   
from   customers_dim  
  versions between scn   
  minvalue and maxvalue  
group  by versions_xid, rollup ( versions_operation )  
order  by versions_xid


VERSIONS_XID VERSIONS_OPERATION COUNT(*)
 -  - 100
 -  - 100

2 rows selected.

VERSIONS_XID	VERSIONS_OPERATION	COUNT(*)
-	-	100
-	-	100

Statement 38

The SQL Monitor also gives the INSERT/UPDATE/DELETE split for MERGE. Again this is unavailable in Live SQL. You must be licensed for Diagnostics and Tuning to use this in your database.

SQL Monitor

select m.last_refresh_time,   
       s1.name, otherstat_1_value insert_count,   
       s2.name, otherstat_2_value update_count,   
       s3.name, otherstat_3_value delete_count  
from   v$sql_plan_monitor m  
left join v$sql_monitor_statname s1  
on     m.otherstat_1_id = s1.id  
and    s1.name like 'MERGE%'  
left join   v$sql_monitor_statname s2  
on     m.otherstat_2_id = s2.id  
and    s2.name like 'MERGE%'  
left join   v$sql_monitor_statname s3  
on     m.otherstat_3_id = s3.id  
and    s3.name like 'MERGE%'  
where  nvl ( s1.name, s2.name ) is not null  
order  by last_refresh_time desc

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 39

commit

Statement processed.

Statement 40

alter table customers_stage 
  drop primary key

Table altered.

Statement 41

Add a duplicate row into the source table

insert into customers_stage   
  values ( 1, 'Duplicate Customer', trunc ( sysdate ) )

1 row(s) inserted.

Statement 42

select * from customers_stage  
where  customer_id = 1


CUSTOMER_ID FULL_NAME BIRTH_DATE
1 New Name 1 10-AUG-65
1 Duplicate Customer 19-JUL-22

2 rows selected.

CUSTOMER_ID	FULL_NAME	BIRTH_DATE
1	New Name 1	10-AUG-65
1	Duplicate Customer	19-JUL-22

Statement 43

MERGE raises an exception when it updates a row in the target twice.

Merging duplicate rows

merge into customers_dim cd  
using customers_stage cs  
on    ( cd.customer_id = cs.customer_id )  
when matched then   
  update  
  set cd.full_name = cs.full_name,  
      cd.update_datetime = systimestamp

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 44

Although there are two rows in the source for CUSTOMER_ID 1, this only updates the target if the name is different. So although there is a M:1 relationship source -> target, the merge succeeds. Here the WHERE clause hides the 2:1 relationship; this could lead to unexpected behaviour.

merge into customers_dim cd  
using customers_stage cs  
on    ( cd.customer_id = cs.customer_id )  
when matched then   
  update  
  set cd.full_name = cs.full_name,  
      cd.update_datetime = systimestamp  
  where cd.full_name <> cs.full_name

Statement processed.

Statement 45

select * from customers_dim 
where  customer_id = 1


CUSTOMER_ID FULL_NAME BIRTH_DATE INSERT_DATETIME UPDATE_DATETIME MERGE_RUN_ID
1 Duplicate Customer 10-AUG-65 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.864080 AM 303

CUSTOMER_ID	FULL_NAME	BIRTH_DATE	INSERT_DATETIME	UPDATE_DATETIME	MERGE_RUN_ID
1	Duplicate Customer	10-AUG-65	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.864080 AM	303

Statement 46

The source table can be a query! This filters the source table to only return one row for each CUSTOMER_ID. This enables the MERGE to work.

De-duplicate the source rows

merge into customers_dim cd  
using (   
  with rws as (  
    select cs.*,   
      row_number() over (   
        partition by customer_id   
        order by birth_date desc   
      ) rn  
    from   customers_stage cs  
  )  
    select * from rws  
    where  rn = 1  
) cs  
on    ( cd.customer_id = cs.customer_id )  
when matched then   
  update  
  set cd.full_name = cs.full_name,  
      cd.update_datetime = systimestamp

Statement processed.

Statement 47

select * from customers_dim 
where  customer_id = 1


CUSTOMER_ID FULL_NAME BIRTH_DATE INSERT_DATETIME UPDATE_DATETIME MERGE_RUN_ID
1 Duplicate Customer 10-AUG-65 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.878594 AM 303

CUSTOMER_ID	FULL_NAME	BIRTH_DATE	INSERT_DATETIME	UPDATE_DATETIME	MERGE_RUN_ID
1	Duplicate Customer	10-AUG-65	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.878594 AM	303

Statement 48

delete customers_stage 
where  customer_id < 11

11 row(s) deleted.

Statement 49

FULL OUTER JOIN from source to target

create or replace view customer_stage_dim as  
  select customer_id,  
         nvl ( cs.full_name, cd.full_name ) full_name,  
         nvl ( cs.birth_date, cd.birth_date ) birth_date,  
         case   
           when cs.full_name is null then 'Y'   
           else 'N'  
         end to_delete  
  from   customers_dim cd  
  full  join customers_stage cs  
  using  ( customer_id )

View created.

Statement 50

The FULL OUTER JOIN view has a flag to indicate rows in the target missing from the source. You can use this to remove rows in MERGE.

select * from customer_stage_dim  
order  by to_delete desc, customer_id  
fetch  first 20 rows only


CUSTOMER_ID FULL_NAME BIRTH_DATE TO_DELETE
1 Duplicate Customer 10-AUG-65 Y
2 New Name 2 06-JUN-81 Y
3 New Name 3 06-JUN-22 Y
4 New Name 4 05-MAY-96 Y
5 New Name 5 07-APR-81 Y
6 New Name 6 31-MAY-00 Y
7 New Name 7 07-MAY-11 Y
8 New Name 8 22-JUN-62 Y
9 New Name 9 27-JUL-69 Y
10 New Name 10 10-SEP-67 Y
11 Iztd Ydvuhdwf 08-OCT-52 N
12 Wpcbmjvwj Gpi 25-OCT-27 N
13 Buqp Yjvaitju 09-FEB-31 N
14 Yzt Vcesdz 06-NOV-94 N
15 Xb Cumahorwy 15-MAY-03 N
16 Lbyh Zee 02-MAR-37 N
17 Fujsvwkrc Fkb 29-OCT-85 N
18 Wvevhddahz Hfwzbxefw 31-JUL-34 N
19 Zbbvi Zxe 07-JUL-16 N
20 Libymthaas Tzgkg 21-MAR-41 N

20 rows selected.

CUSTOMER_ID	FULL_NAME	BIRTH_DATE	TO_DELETE
1	Duplicate Customer	10-AUG-65	Y
2	New Name 2	06-JUN-81	Y
3	New Name 3	06-JUN-22	Y
4	New Name 4	05-MAY-96	Y
5	New Name 5	07-APR-81	Y
6	New Name 6	31-MAY-00	Y
7	New Name 7	07-MAY-11	Y
8	New Name 8	22-JUN-62	Y
9	New Name 9	27-JUL-69	Y
10	New Name 10	10-SEP-67	Y
11	Iztd Ydvuhdwf	08-OCT-52	N
12	Wpcbmjvwj Gpi	25-OCT-27	N
13	Buqp Yjvaitju	09-FEB-31	N
14	Yzt Vcesdz	06-NOV-94	N
15	Xb Cumahorwy	15-MAY-03	N
16	Lbyh Zee	02-MAR-37	N
17	Fujsvwkrc Fkb	29-OCT-85	N
18	Wvevhddahz Hfwzbxefw	31-JUL-34	N
19	Zbbvi Zxe	07-JUL-16	N
20	Libymthaas Tzgkg	21-MAR-41	N

Statement 51

This uses the view to try and delete missing row from the target. In an attempt to reduce the work it does, it only updates rows not marked for deletion. But the DELETE only fires on changed rows! So this removes nothing from the target.

MERGE DELETE

merge into customers_dim cd  
using customer_stage_dim cs  
on    ( cd.customer_id = cs.customer_id )  
when matched then   
  update  
  set cd.full_name = cs.full_name,  
      cd.update_datetime = systimestamp  
  where  to_delete = 'N'  
  delete where to_delete = 'Y'

Statement processed.

Statement 52

select * from customers_dim 
where  customer_id < 11


CUSTOMER_ID FULL_NAME BIRTH_DATE INSERT_DATETIME UPDATE_DATETIME MERGE_RUN_ID
1 Duplicate Customer 10-AUG-65 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.878594 AM 303
2 New Name 2 06-JUN-81 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.878594 AM 304
3 New Name 3 06-JUN-22 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.878594 AM 305
4 New Name 4 05-MAY-96 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.878594 AM 306
5 New Name 5 07-APR-81 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.878594 AM 307
6 New Name 6 31-MAY-00 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.878594 AM 308
7 New Name 7 07-MAY-11 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.878594 AM 309
8 New Name 8 22-JUN-62 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.878594 AM 310
9 New Name 9 27-JUL-69 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.878594 AM 311
10 New Name 10 10-SEP-67 19-JUL-22 10.39.27.745419 AM 19-JUL-22 10.39.27.878594 AM 312

10 rows selected.

CUSTOMER_ID	FULL_NAME	BIRTH_DATE	INSERT_DATETIME	UPDATE_DATETIME	MERGE_RUN_ID
1	Duplicate Customer	10-AUG-65	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.878594 AM	303
2	New Name 2	06-JUN-81	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.878594 AM	304
3	New Name 3	06-JUN-22	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.878594 AM	305
4	New Name 4	05-MAY-96	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.878594 AM	306
5	New Name 5	07-APR-81	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.878594 AM	307
6	New Name 6	31-MAY-00	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.878594 AM	308
7	New Name 7	07-MAY-11	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.878594 AM	309
8	New Name 8	22-JUN-62	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.878594 AM	310
9	New Name 9	27-JUL-69	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.878594 AM	311
10	New Name 10	10-SEP-67	19-JUL-22 10.39.27.745419 AM	19-JUL-22 10.39.27.878594 AM	312

Statement 53

To remove rows from the target missing from the source, you must also update them.

MERGE DELETE

merge /*+ monitor */into customers_dim cd  
using customer_stage_dim cs  
on    ( cd.customer_id = cs.customer_id )  
when matched then   
  update  
  set cd.full_name = cs.full_name,  
      cd.update_datetime = systimestamp  
  delete where to_delete = 'Y'

Statement processed.

Statement 54

select * from customers_dim 
where  customer_id < 11

no data found

Statement 55

The SQL Monitor report shows the INSERT/UPDATE/DELETE split for the statement.

select m.last_refresh_time,   
       s1.name, otherstat_1_value insert_count,   
       s2.name, otherstat_2_value update_count,   
       s3.name, otherstat_3_value delete_count  
from   v$sql_plan_monitor m  
left join v$sql_monitor_statname s1  
on     m.otherstat_1_id = s1.id  
and    s1.name like 'MERGE%'  
left join   v$sql_monitor_statname s2  
on     m.otherstat_2_id = s2.id  
and    s2.name like 'MERGE%'  
left join   v$sql_monitor_statname s3  
on     m.otherstat_3_id = s3.id  
and    s3.name like 'MERGE%'  
where  nvl ( s1.name, s2.name ) is not null  
order  by last_refresh_time desc

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 56

alter session set statistics_level = all

Statement processed.

Statement 57

truncate table customers_stage

Table truncated.

Statement 58

truncate table customers_dim

Table truncated.

Statement 59

Add primary key back to source table

alter table customers_stage 
  add constraint stage_pk 
  primary key ( customer_id )

Table altered.

Statement 60

insert into customers_stage  
with rws as (  
  select level id from dual  
  connect by level <= 10000  
)  
  select id,   
         initcap (   
           dbms_random.string ( 'l', dbms_random.value ( 2, 10 ) ) || ' ' ||  
           dbms_random.string ( 'l', dbms_random.value ( 2, 10 ) )   
         ) nm,  
         date'1920-01-01' +  
           numtoyminterval ( dbms_random.value ( 2, 1200 ), 'month' ) +  
           numtodsinterval ( round ( dbms_random.value ( 1, 31 ) ), 'day' ) dt  
  from   rws

10000 row(s) inserted.

Statement 61

insert into customers_dim ( customer_id, full_name, birth_date )  
  select customer_id, full_name, birth_date from customers_stage

10000 row(s) inserted.

Statement 62

commit

Statement processed.

Statement 63

exec dbms_stats.gather_table_stats ( null, 'customers_stage' )

Statement processed.

Statement 64

exec dbms_stats.gather_table_stats ( null, 'customers_dim' )

Statement processed.

Statement 65

merge into customers_dim cd   
using customers_stage cs   
on    ( cd.customer_id = cs.customer_id )   
when matched then    
  update   
  set cd.full_name = cs.full_name,   
      cd.update_datetime = systimestamp

Statement processed.

Statement 66

Merge performance

merge into customers_dim cd   
using customers_stage cs   
on    ( cd.customer_id = cs.customer_id )   
when matched then    
  update   
  set cd.full_name = cs.full_name,   
      cd.update_datetime = systimestamp

Statement processed.

Statement 67

MERGE does a hash join of the source and target tables, reading each once.

select *  
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, null, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  c5qrau9sf1vvs, child number 0
-------------------------------------
merge into customers_dim cd    using customers_stage cs    on    ( 
cd.customer_id = cs.customer_id )    when matched then       update     
 set cd.full_name = cs.full_name,          cd.update_datetime = 
systimestamp
 
Plan hash value: 4152672054
 
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |                 |      1 |        |      0 |00:00:00.08 |   10447 |       |       |          |
|   1 |  MERGE               | CUSTOMERS_DIM   |      1 |        |      0 |00:00:00.08 |   10447 |       |       |          |
|   2 |   VIEW               |                 |      1 |        |  10000 |00:00:00.01 |     136 |       |       |          |
|*  3 |    HASH JOIN         |                 |      1 |  10000 |  10000 |00:00:00.01 |     136 |  1708K|  1708K| 2119K (0)|
|   4 |     TABLE ACCESS FULL| CUSTOMERS_STAGE |      1 |  10000 |  10000 |00:00:00.01 |      45 |       |       |          |
|   5 |     TABLE ACCESS FULL| CUSTOMERS_DIM   |      1 |  10000 |  10000 |00:00:00.01 |      91 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CD"."CUSTOMER_ID"="CS"."CUSTOMER_ID")
 

25 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID c5qrau9sf1vvs, child number 0
-------------------------------------
merge into customers_dim cd using customers_stage cs on (
cd.customer_id = cs.customer_id ) when matched then update
set cd.full_name = cs.full_name, cd.update_datetime =
systimestamp

Plan hash value: 4152672054

-----------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| OMem \| 1Mem \| Used-Mem \|
-----------------------------------------------------------------------------------------------------------------------------
\| 0 \| MERGE STATEMENT \| \| 1 \| \| 0 \|00:00:00.08 \| 10447 \| \| \| \|
\| 1 \| MERGE \| CUSTOMERS_DIM \| 1 \| \| 0 \|00:00:00.08 \| 10447 \| \| \| \|
\| 2 \| VIEW \| \| 1 \| \| 10000 \|00:00:00.01 \| 136 \| \| \| \|
\|* 3 \| HASH JOIN \| \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 136 \| 1708K\| 1708K\| 2119K (0)\|
\| 4 \| TABLE ACCESS FULL\| CUSTOMERS_STAGE \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 45 \| \| \| \|
\| 5 \| TABLE ACCESS FULL\| CUSTOMERS_DIM \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 91 \| \| \| \|
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("CD"."CUSTOMER_ID"="CS"."CUSTOMER_ID")

Statement 68

Updatable join view performance

update (   
  select cs.full_name as full_name_stage,    
         cd.full_name as full_name_dim,   
         cd.update_datetime   
  from   customers_stage cs   
  join   customers_dim cd   
  on     cs.customer_id = cd.customer_id   
) cd   
set    full_name_dim = full_name_stage,   
       update_datetime = systimestamp

10000 row(s) updated.

Statement 69

Updating the join of the tables also hash joins them. This essentially the same plan as MERGE and does the same amount of work.

select *  
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, null, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  f055tqpj77vnm, child number 0
-------------------------------------
update (      select cs.full_name as full_name_stage,              
cd.full_name as full_name_dim,             cd.update_datetime      from 
  customers_stage cs      join   customers_dim cd      on     
cs.customer_id = cd.customer_id    ) cd    set    full_name_dim = 
full_name_stage,           update_datetime = systimestamp
 
Plan hash value: 3717330510
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |                 |      1 |        |      0 |00:00:00.08 |   10447 |       |       |          |
|   1 |  UPDATE             | CUSTOMERS_DIM   |      1 |        |      0 |00:00:00.08 |   10447 |       |       |          |
|*  2 |   HASH JOIN         |                 |      1 |  10000 |  10000 |00:00:00.01 |     136 |  1722K|  1722K| 1966K (0)|
|   3 |    TABLE ACCESS FULL| CUSTOMERS_STAGE |      1 |  10000 |  10000 |00:00:00.01 |      45 |       |       |          |
|   4 |    TABLE ACCESS FULL| CUSTOMERS_DIM   |      1 |  10000 |  10000 |00:00:00.01 |      91 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CS"."CUSTOMER_ID"="CD"."CUSTOMER_ID")
 

25 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID f055tqpj77vnm, child number 0
-------------------------------------
update ( select cs.full_name as full_name_stage,
cd.full_name as full_name_dim, cd.update_datetime from
customers_stage cs join customers_dim cd on
cs.customer_id = cd.customer_id ) cd set full_name_dim =
full_name_stage, update_datetime = systimestamp

Plan hash value: 3717330510

----------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| OMem \| 1Mem \| Used-Mem \|
----------------------------------------------------------------------------------------------------------------------------
\| 0 \| UPDATE STATEMENT \| \| 1 \| \| 0 \|00:00:00.08 \| 10447 \| \| \| \|
\| 1 \| UPDATE \| CUSTOMERS_DIM \| 1 \| \| 0 \|00:00:00.08 \| 10447 \| \| \| \|
\|* 2 \| HASH JOIN \| \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 136 \| 1722K\| 1722K\| 1966K (0)\|
\| 3 \| TABLE ACCESS FULL\| CUSTOMERS_STAGE \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 45 \| \| \| \|
\| 4 \| TABLE ACCESS FULL\| CUSTOMERS_DIM \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 91 \| \| \| \|
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CS"."CUSTOMER_ID"="CD"."CUSTOMER_ID")

Statement 70

There are many restrictions on updatable join views, so you may need to write a correlated update like this instead.

Correlated update performance

update customers_dim cd     
set    ( full_name, update_datetime ) = (     
  select cs.full_name, systimestamp     
  from   customers_stage cs     
  where  cs.customer_id = cd.customer_id     
)     
where  exists (     
  select null from customers_stage ce     
  where  ce.customer_id = cd.customer_id     
)

10000 row(s) updated.

Statement 71

A correlated update reads the source table twice. This can be much slower than an updatable join view or merge.

select *    
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, null, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  44uzhq17r52ff, child number 0
-------------------------------------
update customers_dim cd      set    ( full_name, update_datetime ) = (  
      select cs.full_name, systimestamp        from   customers_stage 
cs        where  cs.customer_id = cd.customer_id      )      where  
exists (        select null from customers_stage ce        where  
ce.customer_id = cd.customer_id      )
 
Plan hash value: 3378486070
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                 |      1 |        | 60031 (100)|      0 |00:00:00.12 |   41036 |
|   1 |  UPDATE                      | CUSTOMERS_DIM   |      1 |        |            |      0 |00:00:00.12 |   41036 |
|   2 |   NESTED LOOPS               |                 |      1 |  10000 |    31   (4)|  10000 |00:00:00.01 |     457 |
|   3 |    TABLE ACCESS FULL         | CUSTOMERS_DIM   |      1 |  10000 |    30   (0)|  10000 |00:00:00.01 |      91 |
|*  4 |    INDEX UNIQUE SCAN         | STAGE_PK        |  10000 |      1 |     0   (0)|  10000 |00:00:00.01 |     366 |
|   5 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS_STAGE |  10000 |      1 |     2   (0)|  10000 |00:00:00.02 |   20311 |
|*  6 |    INDEX UNIQUE SCAN         | STAGE_PK        |  10000 |      1 |     1   (0)|  10000 |00:00:00.01 |   10311 |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CE"."CUSTOMER_ID"="CD"."CUSTOMER_ID")
   6 - access("CS"."CUSTOMER_ID"=:B1)
 

28 rows selected.

UPDATE-if-exists, INSERT-if-not-exists (aka UPSERT) data with MERGE

Additional Information