Merge statements and sequences

Here’s something I stumbled upon when I was researching a performance problem involving the oracle merge command.

fdh@GUNNAR> create table t_dest
  2  ( n1   number(10,0),
  3    n2   number(10,0),
  4    v1   varchar2(1)
  5  )
  6  /

Table created.

fdh@GUNNAR> create table t_source
  2  ( n1 number(10,0)
  3  )
  4  /

Table created.

fdh@GUNNAR> create sequence s_seq
  2  start with 1
  3  increment by 1
  4  nomaxvalue
  5  nocache
  6  nocycle
  7  noorder
  8  /

Sequence created.

fdh@GUNNAR> insert into t_dest
2 select rownum, s_seq.nextval, 'O'
3 from all_objects
4 where rownum <= 1000; 

1000 rows created. 

fdh@GUNNAR> insert into t_source
2 select rownum
3 from all_objects
4 where rownum <= 500; 

500 rows created. 

fdh@GUNNAR> commit; 

Commit complete.

fdh@GUNNAR> select last_number from user_sequences where sequence_name = 'S_SEQ';

LAST_NUMBER
-----------
       1001

fdh@GUNNAR> set timing on
fdh@GUNNAR> exec dbms_monitor.session_trace_enable(waits => TRUE, binds => TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
fdh@GUNNAR> merge into t_dest
  2  using t_source
  3  on ( t_dest.n1 = t_source.n1)
  4  when matched then update
  5  set v1 = 'U'
  6  when not matched then insert (n1, n2, v1)
  7  values (0, s_seq.nextval, 'N');

500 rows merged.

Elapsed: 00:00:03.68
fdh@GUNNAR> commit;

Commit complete.

Elapsed: 00:00:00.06
fdh@GUNNAR> exec dbms_monitor.session_trace_disable;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
fdh@GUNNAR> select v1, count(*)
  2  from t_dest
  3  group by v1
  4  order by v1;

V   COUNT(*)
- ----------
O        500
U        500

Elapsed: 00:00:00.09

As you can see, the merge statement has updated 500 records and inserted no new records.
But check out the sequence:

fdh@GUNNAR> select last_number from user_sequences where sequence_name = 'S_SEQ';

LAST_NUMBER
-----------
       1501

Elapsed: 00:00:00.07

Apparently the sequence has increased with 500 (number of records from t_source), even with the “when not matched” clause never evaluating to true.
The trace output confirms this as well.

merge into t_dest
using t_source
on ( t_dest.n1 = t_source.n1)
when matched then update
set v1 = 'U'
when not matched then insert (n1, n2, v1)
values (0, s_seq.nextval, 'N')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          6          0           0
Execute      1      0.59       0.56          0          9       1014         500
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.60       0.58          0         15       1014         500

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  MERGE  T_DEST (cr=509 pr=0 pw=0 time=3478125 us)
    500   VIEW  (cr=509 pr=0 pw=0 time=3510161 us)
    500    SEQUENCE  S_SEQ (cr=509 pr=0 pw=0 time=3508659 us)
    500     HASH JOIN OUTER (cr=9 pr=0 pw=0 time=6366 us)
    500      TABLE ACCESS FULL T_SOURCE (cr=3 pr=0 pw=0 time=1042 us)
   1000      TABLE ACCESS FULL T_DEST (cr=6 pr=0 pw=0 time=1049 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file sync                                   2        0.01          0.01
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.01          0.01
********************************************************************************

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
  cache=:7,highwater=:8,audit$=:9,flags=:10
where
 obj#=:1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      500      0.00       0.00          0          0          0           0
Execute    500      2.96       2.90          0        500       1013         500
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1000      2.96       2.91          0        500       1013         500

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  UPDATE  SEQ$ (cr=1 pr=0 pw=0 time=167 us)
      1   INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=16 us)(object id 102)

********************************************************************************

Clearly something to remember when you are using a merge command with a sequence in the “when not matched” clause and a source table containing a couple of million rows. Certainly when you expect most records to be matched.
In such a case it could be better to move the sequence to a row trigger on the target table.