Oracle 如果使用merge into语句，标识列会跳转其值

嗨，
我有一个定义如下的表，其中一个列被定义为标识类型

   create table TEST
   (
     col1 VARCHAR2(10), 
     col2 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 
     9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE,
     col3 varchar2(10)
   );

我们发现如果使用合并到语句，col2会跳取值。我们可以复制如下
1.首先插入一行，因为没有col1 = 'a' 的行

   merge into "TEST" T1 
    using ( select 'a' "COL1" from dual) T2
    on (T1."COL1" = T2."COL1" )
    when MATCHED THEN
    update  set "COL3" = 'a'
    when NOT MATCHED Then
     insert values('a', null,'a');

select * from TEST;
    col1 col2 col3
    a     1    a

2.通过将col3从a更新为a2来更新此行如下

   merge into "TEST" T1 
   using ( select 'a' "COL1" from dual) T2
   on (T1."COL1" = T2."COL1" )
   when MATCHED THEN
   update  set "COL3" = 'a2'
   when NOT MATCHED Then
   insert values('a', null,'a2');

 select * from TEST;
   col1 col2 col3
   a     1    a2

3.插入一个新行

    merge into "TEST" T1 
    using ( select 'b' "COL1" from dual) T2
    on (T1."COL1" = T2."COL1" )
    when MATCHED THEN
    update  set "COL3" = 'b'
    when NOT MATCHED Then
    insert values('b', null,'b');

 select * from TEST;
   col1 col2 col3
   a     1    a2
   b     3    b

我们预计这一行的col2是2，但是，我们在这里得到3。
你能给我们一些帮助吗

Oracle数据库中的标识列是列的序列默认值的一种特殊情况。这也显示了这种行为:

create sequence s;
create table TEST (
  col1 VARCHAR2(10), 
  col2 NUMBER default on null s.nextval,
  col3 varchar2(10)
);

insert into test values ('a', default, 'a');
commit;

select * from test;

COL1   COL2   COL3   
a           1 a    

merge into "TEST" T1 
using ( select 'a' "COL1" from dual) T2
on (T1."COL1" = T2."COL1" )
when MATCHED THEN
update set "COL3" = 'a2'
when NOT MATCHED Then
insert values('a', default,'a2');

merge into "TEST" T1 
using ( select 'a' "COL1" from dual) T2
on (T1."COL1" = T2."COL1" )
when MATCHED THEN
update set "COL3" = 'a3'
when NOT MATCHED Then
insert values('a', default,'a3');

select * from TEST;

COL1   COL2   COL3   
a           1 a3     

insert into test values ('b', default, 'b');

select * from TEST;

COL1   COL2   COL3   
a           1 a3     
b           4 b    

所以看起来数据库正在对seq.nextval进行不必要的调用。

但真正的问题是:

你为什么在乎？你不能依靠序列来实现无间隙!

请参阅:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:530735152441