Oracle sequence increased unexpected when using in merge statement

Following sample is going to merge from tab2 to tab1:

CREATE TABLE tab1(id NUMBER, name varchar2(10), score NUMBER);

CREATE TABLE tab2(id NUMBER, name varchar2(10), score NUMBER);

CREATE SEQUENCE seq START WITH 11 INCREMENT BY 1 NOCACHE NOCYCLE;

insert into tab1 values(1, 'AAA',10);

insert into tab1 values(2, 'BBB',20);

insert into tab1 values(3, 'CCC',30);

insert into tab2 values(1, 'BBB',5);

insert into tab2 values(2, 'CCC',5);

insert into tab2 values(3, 'DDD',40);

insert into tab2 values(4, 'EEE',50);

insert into tab2 values(5, 'FFF',60);

MERGE INTO tab1 T USING tab2 F

   ON (F.name = T.name)

   WHEN MATCHED THEN

        UPDATE SET T.score = F.score + T.score

   WHEN NOT MATCHED THEN

        INSERT (T.id, T.name, T.score) VALUES (seq.nextval, F.name, F.score);

-- tab1  has 3 records

-- tab2  has 5 records

-- the merge operation wish 3 records('DDD', 'EEE', 'FFF') are added into tab1, and 2 records('BBB', 'CCC') are updated to tab1.

-- the next value of sequence seq is 11.

-- so the expected result is:

    ID NAME        SCORE

---------- ---------- ----------

     1 AAA              10

     2 BBB              25

     3 CCC              35

    11 FFF              60

    12 EEE              50

    13 DDD              40

-- however, the real result is:

    ID NAME        SCORE

---------- ---------- ----------

     1 AAA              10

     2 BBB              25

     3 CCC              35

    13 FFF              60

    14 EEE              50

    15 DDD              40

-- the problem is the id field (sequence) is not as expected; 11 is the beginning value as expected, but 13 is the real beginning value.

This is because the update branch also occupied a sequence value, see:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns002.htm#sthref809

· For each row merged by a MERGE statement. 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. If NEXTVAL is specified more than once in any of these locations, then the sequence is incremented once for each row and returns the same value for all occurrences of NEXTVAL for that row.

Solution 1: separate the insert and update into 2 merge statements.

MERGE INTO tab1 T USING (select * from tab2 where name in (select name from tab1)) F

  ON (F.name = T.name)

  WHEN MATCHED THEN

       UPDATE SET T.score = F.score + T.score;

--  WHEN NOT MATCHED THEN

--       INSERT (T.id, T.name, T.score) VALUES (seq.nextval, F.name, F.score);

MERGE INTO tab1 T USING (select * from tab2 where name not in (select name from tab1)) F

  ON (F.name = T.name)

--  WHEN MATCHED THEN

--       UPDATE SET T.score = F.score + T.score;

  WHEN NOT MATCHED THEN

       INSERT (T.id, T.name, T.score) VALUES (seq.nextval, F.name, F.score);