Skip to Main Content
  • Questions
  • The identity column jumps its value if using merge into statement

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Liang.

Asked: October 03, 2018 - 11:46 pm UTC

Last updated: June 29, 2022 - 11:02 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

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


and Chris said...

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

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Liang Cheng, October 04, 2018 - 4:55 pm UTC

I understand sometimes the sequence is not gap free. Such as you mentioned in the linked resource.
If someone calls seq.nextval and doesn't insert the value somewhere (or does insert it and later rolls back their transaction) that number is GONE.

But I still don't understand why following merge statement would increase the sequence if update condition is satisfied. In our real environment, there is only one thread to run this statement, and we can think there is no rollback case too.
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');
Connor McDonald
October 05, 2018 - 3:44 am UTC

We never guarantee NOT to skip values. There is nothing in the documentation that says "only if we rollback".


Skipping too many values

Graham, February 15, 2022 - 1:23 pm UTC

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!


I am performing a MERGE to insert records from a SOURCE table not found in the TARGET (so with just a WHEN NOT MATCHED clause).

The SOURCE and TARGET each contain around 4.3k records currently, and each run the sequence jumps by around that amount (not exactly and growing with data volume) regardless of the number of new records.

As there are usually only around 1-5 new records (very occasionally substantially more) this means my ID sequence is currently jumping by ~4.3k for every <5 records added.

While I don't care about the continuity of my ids, I find it hard not to care about jumps this size that are only going to get worse.

As such I will likely have to abandon MERGE for something less elegant to solve a problem that MERGE should be ideal for.

This is in Oracle 19c but I guess the issue is not resolved in later versions if the attitude above is anything to go by. I will be raising with support in any case.

Chris Saxon
February 15, 2022 - 4:18 pm UTC

I find it hard not to care about jumps this size that are only going to get worse.

Why? What actual problem is this causing?

That aside - unless you need to update the majority of the existing rows - changing MERGE so it processes less data is likely to make this process faster in the long run.

Real world example causing issues

David Giles, June 28, 2022 - 2:00 pm UTC

I have a dimension table which is refreshed from a staging table every 10 minutes using a merge. The table is simply an autoincrementing ID and the string value of the dimension.

The table currently has 175 rows in it, but because the sequence of the autoincrementing column is pinged on the merge, the value of the sequence has gone beyond the number(5) precision of the ID column.

One solution is to exclude existing values of the dimension in the source query for the merge.

So, do I care that the sequence can create gaps? Not at all. However, if you're not aware of this behaviour it can cause unexpected issues.
Chris Saxon
June 29, 2022 - 11:02 am UTC

Thanks for sharing.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.