How to handle auto increment sequence on dup_val_on_index oracle sql

Question

I have an oracle table which has

1. An auto increment primary key which uses a sequence.
2. Unique key
3. Non unique field/s

create table FOO (
    ai_id number primary key,
    name varchar(20),
    bar varchar(20)
    CONSTRAINT foo_uk_name UNIQUE (name)
);
create sequence  FOO_seq;

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  select FOO_seq.nextval into :new.ai_id from dual;
end;

I have separate stored procedure which upserts the table

create PROCEDURE UPSERT_FOO(
  name_input IN VARCHAR2,
  bar_input IN VARCHAR2
begin
  begin
    insert into FOO ( name, bar )
    values ( name_input, bar_input )
  exception
    when dup_val_on_index then
      update FOO 
      set  bar = bar_input
      where name = name_input
  end;
end;

This works perfectly fine but the only issue is, sequence "FOO_seq" always increases regardless of whether it is an update or insert(As FOO_seq increments in "FOO_trg" before it inserts).

Is there a way to increment the sequence, only when there is an insert, without hurting the performance?

Answer 1

Oracle has a built-in merge statement to do an 'upsert':

create PROCEDURE UPSERT_FOO(
  name_input IN VARCHAR2,
  bar_input IN VARCHAR2
) as
begin
  merge into foo
  using (
    select name_input as name, bar_input as bar from dual
  ) src
  on (foo.name = src.name)
  when matched then
    update set foo.bar = src.bar
  when not matched then
    insert (name, bar)
    values (src.name, src.bar);
end;
/

The insert only happens (and thus the trigger only fires, incrementing the sequence) if there is no match.

That doesn't have to be done through a procedure now, of course; you could just issue a merge directly, plugging in the name/bar values you would currently have to pass to the procedure.

Incidentally, your trigger could be simplified slightly to do an assignment:

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  :new.ai_id := FOO_seq.nextval;
end;
/

db<>fiddles using your original code and using the code above. Notice the ID for 'b' in the final query; 5 in the first one, but only 2 in the second one.

Gaps in sequences shouldn't matter, of course; they are guaranteed to increment and be unique (if they don't cycle), not to be gapless. Or to necessarily be issued in strict order if you have a cache and are using RAC. Still, your approach would potentially waste a lot of values for no reason, and it doesn't need to be that complicated.