Hi,
I have one table defined as below, one of the column is defined as identity type
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)
);
We found the col2 jumps its value if using merge into statement. We can reproduce it as below
1. First insert one row, since there is no row with 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. Update this row by updating col3 from a to a2 as below
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. insert one new row
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
We expect the col2 is 2 for this new row, however, we get 3 here.
Could you please give us some help
Identity columns in Oracle Database are a special case of sequence defaults for columns. Which also shows this behaviour:
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
So it looks like the database is making unnecessary calls to seq.nextval.
But the real question is:
Why do you care? You can't rely on sequences to be gap-free!
See:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:530735152441