I have created a sequence such as:
create sequence pm_seq
start with 1 increment by 1 cache 2;
and a merge statement in a packaged procedure such as :
MERGE INTO target_table t
USING (SELECT col1,
col2
FROM source_table
) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.col1 = s.col1,
t.col2 = s.col2
WHERE t.col = s.col
WHEN NOT MATCHED THEN
INSERT (t.aa,
t.col1,
t.col2)
VALUES (pm_seq.nextval,
s.col1,
s.col2);
The very first time i execute the packaged procedure, the values inserted into the 'aa' column
are sequential (1..71 - there 71 rows in the source table).
Afterwards, i truncate the target table and re-execute the packaged procedure. The result is that
the values inserted into the 'aa' column are sequential but starting from 5120..5191.
Every time, i repeat the same steps (truncating the target table, re-runing the packaged procedure)
the values inserted into the 'aa' column are sequential but starting at a value 5000 greater than
the previous time.
Can anybody explain this...?
Thanks in advance...
Note: I use Oracle DB11g v2