Thursday, May 22, 2008

SQL Merge and Sequence gaps

I hope You my dear reader isn't one of the people cursed by requirement for gapless sequences ;)
Yesterday I just found another nail in the coffin for oracle sequence objects used as gapless sequence generators. Of course there are many causes not to do that, just like simple rollback, users changing their minds, network connection error, flushing cache etc. But I didn't know at least till yesterday, that MERGE statements generate gaps with quite big guarantee. It seems that Merge statement is generating the same number of calls for sequence next value as row count of source table or subquery.
Let's look at example:

CREATE TABLE target (pk number, data varchar2(10));
CREATE TABLE source (pk_s number, data_s varchar2(10));
INSERT INTO target VALUES (1, 'a');
INSERT INTO target VALUES (3, 'c');
INSERT INTO source VALUES (1, 'a');
INSERT INTO source VALUES (2, 'b');
INSERT INTO source VALUES (3, 'c');
INSERT INTO source VALUES (4, 'd');
COMMIT;
CREATE SEQUENCE seq START WITH 5;

So now we have 2 rows in target table, and 4 rows in source table. We'll compare them using data and data_s column. We'd insert only two rows, because another 2 already exists and even won't use WHEN MATCHED clause for update.

MERGE INTO target
USING source
ON (data = data_s)
WHEN NOT MATCHED THEN
INSERT VALUES (seq.nextval, data_s);
SQL> SELECT * FROM target;
PK DATA
---------- ----------
1 a
3 c
7 d
8 b

Although the sequence should have started from 5 new pk values are 7 and 8. Let's add another row into source and look what happens:

INSERT INTO source VALUES (5, 'e');
MERGE INTO target
USING source
ON (data = data_s)
WHEN NOT MATCHED THEN
INSERT VALUES (seq.nextval, data_s);
SQL> SELECT * FROM target;
PK DATA

---------- ----------
1 a
3 c
7 d
8 b
13 e

So now the value is 13 i.e. 8 + count of rows from source table (5). Now let's add another row and delete all previous.

INSERT INTO source VALUES (6, 'f');
DELETE source WHERE pk_s <=5;
MERGE INTO target
USING source
ON (data = data_s)
WHEN NOT MATCHED THEN
INSERT VALUES (seq.nextval, data_s);
SQL> SELECT * FROM target;
PK DATA

---------- ----------
1 a
3 c
7 d
8 b
13 e
14 f

As we can see pk is incremented only by 1.
After my experiments I found also metalink note "Merge Increments SEQUENCE.NEXTVAL for Both Insert and Update" (Note:554656.1), which also assures this as expected situation and not bug. So another reason not to worry about sequence values, just uniqueness of them ;)