Sequence's NEXTVAL in MERGE operator

I have used MERGE operator many times before. But recently I was surprised by fact that in case when in MERGE there is call of sequence's NEXTVAL it will be executed even the another branch of MERGE operator will be used.

I found remark about this here, section "How to Use Sequence Values"

The reference to NEXTVAL can appear in the merge_insert_clause or the merge_update_clause or both. The NEXTVALUE value is incremented for each row updated and for each row inserted, even if the sequence number is not actually used in the update or insert operation.

Let's illustrate this.

I created the following table

CREATE TABLE test_table (
  id        NUMBER(10)  primary key
, name      VARCHAR2(32) NOT NULL
, orign_id  NUMBER NOT NULL);

and sequence for surrogate primary key field ID

CREATE sequence test_table_seq
  minvalue 0
  maxvalue 9999999999
  START WITH 0
  increment BY 1;

It's a typical task: by ORIGN_ID find row in table and if it exists then change NAME, if it does not then create new one with such NAME and ORIGN_ID.

Usually I solve it using MERGE operator like this:

merge
 INTO test_table t
using dual
   ON (t.orign_id = 100)
 WHEN matched
 THEN UPDATE
         SET t.name = 'Name'
 WHEN NOT matched
 THEN INSERT
      VALUES (test_table_seq.NEXTVAL
            , 'Name'
            , 100);

I ran this operator several times

DECLARE
  v_cnt       PLS_INTEGER;
  v_currval   NUMBER(10);
BEGIN
 
  SELECT COUNT(*)
    INTO v_cnt
    FROM test_table;
  DBMS_OUTPUT.put_line('Count of rows in TEST_TABLE (should be empty): ' || TO_CHAR(v_cnt));
 
  FOR i IN 1..3 LOOP  
 
    merge
     INTO test_table t
    using dual
       ON (t.orign_id = 100)
     WHEN matched
     THEN UPDATE
             SET t.name = 'Name'
     WHEN NOT matched
     THEN INSERT
          VALUES (test_table_seq.NEXTVAL
                , 'Name'
                , 100);               
 
    SELECT COUNT(*)
      INTO v_cnt
      FROM test_table;
    DBMS_OUTPUT.put_line('Count of rows in TEST_TABLE: ' || TO_CHAR(v_cnt));  
 
    SELECT test_table_seq.CURRVAL
      INTO v_currval
      FROM dual;
    DBMS_OUTPUT.put_line('Current value of TEST_TABLE_SEQ: ' || TO_CHAR(v_currval)); 
 
  END LOOP;    
 
  ROLLBACK;
END;

The result is
Count of rows in TEST_TABLE (should be empty): 0
Count of rows in TEST_TABLE: 1
Current value of TEST_TABLE_SEQ: 1
Count of rows in TEST_TABLE: 1
Current value of TEST_TABLE_SEQ: 2
Count of rows in TEST_TABLE: 1
Current value of TEST_TABLE_SEQ: 3

As we see on first iteration one row was inserted and then this row was updated twice. But the sequence was incremented thrice - one time during insert as it should be and two times during update for nothing.

Tom Kyte said concerning this "if you burn 1,000,000,000 sequences per second, it would still be so many long years from now before you exhausted the number type...". Yes, actually it is not a critical problem, but... possible workaround is to create function like this

CREATE OR REPLACE
FUNCTION seq_nextval_on_demand (p_seq_name  IN  VARCHAR2)
  RETURN NUMBER
IS
  v_seq_val  NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual'
     INTO v_seq_val;
 
  RETURN v_seq_val;
END seq_nextval_on_demand;

A trick is function is called only when "insert" branch is really used.

In previous anonymous block I replaced "test_table_seq.nextval" by "seq_nextval_on_demand('test_table_seq')", recreated test_table and test_table_seq and ran the block again.
The result is
Count of rows in TEST_TABLE (should be empty): 0
Count of rows in TEST_TABLE: 1
Current value of TEST_TABLE_SEQ: 1
Count of rows in TEST_TABLE: 1
Current value of TEST_TABLE_SEQ: 1
Count of rows in TEST_TABLE: 1
Current value of TEST_TABLE_SEQ: 1

So the sequence was incremented just once when insert was happend.

Code snippets have been tested on Oracle Database 11g Release 11.2.0.1.0