Merge statement issue in oracle

Question

I came from Microsoft SQL environment.I have two tables tak_ne and tak_beb and my requirement was to insert values from tak_beb to tak_ne if value is not present,if it is present just update.So i made a merge statement as shown below.But the problem now i am facing is veryday 50000 count is getting increment for sequence number.Oracle is stable database, and i don't know why they made it like that.So i create a Function and prevented incrementing sequence number.My question is ,is it a right approach by creating function.Following is what i did

merge into tak_ne a using tak_beb b ON (a.NAME=b.NAME)
When matched then
   Update
   Set a.AC_NO = b.AC_NO
       a.LOCATION = b.LOCATION
       a.MODEL = b.MODEL
When not matched then
insert
(
sl_no,
AC_NO,
LOCATION
MODEL
)
Values
(
s_slno_nextval
b.AC_NO
b.LOCATION
b.MODEL
)

and then i created a function

CREATE OR REPLACE FUNCTION s_slno_nextval
      RETURN NUMBER
    AS
      v_nextval NUMBER;
    BEGIN
      SELECT s_emp.nextval
      INTO   v_nextval
      FROM   dual;
      RETURN v_nextval;
  END;

score 3 · Answer 1 · edited Jan 03 '16 at 17:45

Oracle uses this approach to generate unique id for each row inserted by a statement. Your TAK_BEB table has probably 50000 rows, so the sequence is incremented 50000 times.

To hide increment into a function does not help. Function is called AND EXECUTED for every row, it increments sequence for 50000 times again. And it adds overhead with 50000 selects from dual table.

If you really need to use ONE value from sequence for ALL rows inserted by statement, use package variable:

create package single_id_pkg is
  id  Number;
  function get_id return number;
end;
/

create or replace package body single_id_pkg is
  function get_id return number is
  begin
    return id;
  end;
end;
/

Now use for example before statement trigger on table to set the variable:

create trigger tak_ne_BSI_trg
before insert
on tak_ne
begin
  select s_emp.nextval
    into single_id_pkg.id
    from dual;
end;

Insert trigger has one disadvantage - with MERGE clause it fires even if the statement does only updates rows (see https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:25733900083512). If it is a problem, you have to initialize the variable in other way.

Next modify your statement to use a package variable:

merge into tak_ne a
  using tak_beb b
  on (a.NAME=b.NAME)
when matched then
  update
    set a.AC_NO = b.AC_NO
        a.LOCATION = b.LOCATION
        a.MODEL = b.MODEL
when not matched then
  insert (sl_no,
          AC_NO,
          LOCATION,
          MODEL)
  values (single_id_pkg.get_id
          b.AC_NO,
          b.LOCATION,
          b.MODEL)

i will do an R &D about what you suggested.Mean while what ever i did in my question was helped to stop sequence incremented every time until i make an insert.But i will try in your way — peter, Jan 02 '16 at 08:17
This will use the same ID for each new row, I'm not sure if that's what the OP wants. I know he wants to avoid generating a sequence for every UPDATED row, but should every INSERTED row get the same ID or a different one? — Jon Heller, Jan 03 '16 at 17:49
Right, in that case would be better soution provided by @Mikhailov - set id from sequence directly in trigger - check if id is not null before selecting from sequence, because MERGE statement fires both update and insert trigger for each row — Petr Pribyl, Jan 04 '16 at 04:43

Mikhailov Valentin · Answer 2 · 2016-01-02T10:37:29.627

In Oracle standard way to use autoincrement field is by using sequences. And of course it will increment sequence number each time you want to use it. But you can omit calling sequence_name.nextval, hiding it in trigger it is considered the standard approach also.

CREATE OR REPLACE EDITIONABLE TRIGGER TAK_NE_ID_TR"
BEFORE INSERT ON tak_ne
FOR EACH ROW
BEGIN
IF :old.sl_no IS NULL THEN
  :new.sl_no := s_emp.nextval;
END IF;
END;

If you want to add same id for a batch of your inserts you can use global temporary table for saving it. For example, like this:

create global temporary table tak_ne_id ("id" number) on commit delete rows

create or replace trigger tak_ne_BSI_trg
before insert
on tak_ne
begin
 insert into tak_ne_id("id")
 values(s_emp.nextval);
end

create or replace TRIGGER TAK_NE_ID_TR
BEFORE INSERT ON tak_ne
FOR EACH ROW
BEGIN
if :old.sl_no is null then
  SELECT "id"
  INTO   :new.sl_no
  FROM   tak_ne_id;
end if;
END;

Then you can use you merge as before, and without calling nextval:

 merge into tak_ne a using tak_beb b ON (a.NAME=b.NAME)
    When matched then
       update
       set a.AC_NO = b.AC_NO,
           a.LOCATION = b.LOCATION,
           a.MODEL = b.MODEL
    When not matched then
    insert
    (
    AC_NO,
    LOCATION,
    MODEL
    )
    Values
    (
    b.AC_NO,
    b.LOCATION,
    b.MODEL
    );

Merge statement issue in oracle

2 Answers2