Sequences with MERGE statements

[jovery]

Hi All,

Bit of an odd one here and I'm hoping someone has a solution.

I have a table which has a sequence based PK and has a merge statment run against it (a simplified version is shown below).

MERGE INTO databases_users dk
USING (SELECT username, created from dba_users) du
ON (dk.username = du.username)
WHEN MATCHED THEN UPDATE SET dk.created = du.created
WHEN NOT MATCHED THEN INSERT (dk.user_id, dk.username, dk.created)
VALUES (databases_users_s.nextval, du.username, du.created)';

The problem is that even though the sequence is only part of the insert it is still incremented as part of the update statement, which means that the sequence is advance by 2700 each time the merge is run even though no new records are added.

I have looked on Metalink and can find some simialr problems but no solution (apart from creating a trigger to insert the sequence value). Has anyone else hit this issue and found a way round it. I'd like to keep this a part of one statement if possible.

DB Ver is 9.2.0.5 on Sun Solaris

Thanks in advance

[padders]

It appears in your case that using a PL/SQL function to provide the next value of the sequence would not increment the sequence unnecessarily.

Of course there is absolutely no guarantee of when or how many times the PL/SQL function will be called in the future.

[jovery]

It appears in your case that using a PL/SQL function to provide the next value of the sequence would not increment the sequence unnecessarily.

Of course there is absolutely no guarantee of when or how many times the PL/SQL function will be called in the future.

Cheers Padders,

Already tried that, the value does not increment as steeply but does still go up by 200.

[padders]

Well isn't that better? ;-)

I tried a quick test in 9.2.0.6 and the sequence was not touched. Of course there is the question of how many times it is touched for each row that is inserted.

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production

SQL> CREATE SEQUENCE s_emp;

Sequence created.

SQL> CREATE OR REPLACE FUNCTION s_emp_nextval 
  2    RETURN NUMBER
  3  AS
  4    v_nextval NUMBER;
  5  BEGIN
  6    SELECT s_emp.nextval
  7    INTO   v_nextval
  8    FROM   dual;
  9    RETURN v_nextval;
 10  END;
 11  /

Function created.

SQL> MERGE INTO emp t USING (
  2    SELECT empno, ename
  3    FROM   emp) s
  4  ON (t.empno = s.empno)
  5  WHEN MATCHED THEN 
  6    UPDATE 
  7    SET t.ename = s.ename
  8  WHEN NOT MATCHED THEN 
  9    INSERT (empno, ename) 
 10    VALUES (s_emp_nextval, 'SMITH');

14 rows merged.

SQL> SELECT s_emp.NEXTVAL
  2  FROM   dual;

   NEXTVAL
----------
         1

SQL>

[gamyers]

The 200 increment may have been the sequence aging out and going up by the cache value.

Have you considered setting the sequence in a BEFORE INSERT trigger ?

[jovery]

Well isn't that better? ;-)

I tried a quick test in 9.2.0.6 and the sequence was not touched. Of course there is the question of how many times it is touched for each row that is inserted.

Odd when I try that same test it works fine, the only difference between the code I posted and the live code is that the data is gather over a database link in the live example. Here is a sample run using the funtion method:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> SELECT databases_users_s.nextval
   2   FROM dual;

   NEXTVAL
----------
      3306

SQL> exec dbspy.scan('C','A');

PL/SQL procedure successfully completed.

SQL> SELECT  databases_users_s.nextval
  2  FROM dual;

   NEXTVAL
----------
      3363

So in this case the sequence advanced by 56 (taken off 1 for my second select), the procedure scans 28 databases which means the sequence is advanced by 2 for each individual execution. Only 27 databases are contatced via links which means that in the packaged example the sequence increases in the same way unlike the sample code. Again no rows are added so the sequence should not advance.

Although this is better than the original code it still means that over the day there will be over 1500 sequence advances for no reason as this package is run on a fairly regular basis (part of a monitoring tool)

Have you considered setting the sequence in a BEFORE INSERT trigger ?

Yes I had considered using a trigger, which is something I may have to resort to but I'd like to know why this occurs.

Thanks for the help so far, any more suggestions what could be causing this weird result?

[jovery]

...bit of additional info, just tried using the trigger method and the same occurs. Unless someone can shed any light on this I'll have to recode this merge with tradional update/insert statements :-(

Might raise a TAR and see what Oracle come back with.