jcon.no: Oracle blog

In my last blogpost (“Strange behavior in MERGE with sequence NEXTVAL”) I had a link to article written by Bob Jankovsky. In this article is suggest solving the issue with MERGE and nextvalue by returning the nextvalue from a function. In this blogpost I’ll do some tests and see how this behaves (regarding timings).

Note! There could be other reasons not to use this solution. I’ll not discuss or test further in this article.

In the test below I have been using version 19.3.0 of the Oracle database (ran inside a Docker container on my Intel Mac).

In the test I’ll use the following objects:

-- ------------------------------------
-- Drop if you need to recreate
-- ------------------------------------
drop sequence test_seq;
drop table test_t purge;
drop table test_src purge;
drop function get_test_seq;
drop function get_test_seq_udf;

-- ------------------------------------
-- Create needed objects
-- ------------------------------------
CREATE SEQUENCE test_seq 
  START WITH 1 INCREMENT BY 1 CACHE 1000 MAXVALUE 99999999999;

CREATE TABLE test_t (
   id               NUMBER(18) constraint test_t_pk primary key,
   other_id         NUMBER(18) constraint test_t_id_uq unique,
   text             VARCHAR2(255),
   sys_created_time timestamp,
   sys_updated_time timestamp
);

CREATE TABLE test_src (
  id                NUMBER(18) constraint test_src_pk primary key,
  other_id          NUMBER(18) constraint test_src_id_uq unique,
  text              VARCHAR2(500 CHAR) 
);

-- Generating some test data
INSERT INTO test_src 
SELECT
      ROWNUM id, rownum other_id,
      'Employee '||to_char(ROWNUM)||dbms_random.value(2, 9) * 1000 text
   FROM dual
   CONNECT BY level <= 1000000;

-- Normal function   
CREATE OR REPLACE FUNCTION get_test_seq RETURN INTEGER IS
BEGIN
  RETURN test_seq.NEXTVAL;
END get_test_seq;
/

-- Function with PRAGMA UDF
CREATE OR REPLACE FUNCTION get_test_seq_udf RETURN INTEGER IS
   PRAGMA UDF;
BEGIN
  RETURN test_seq.NEXTVAL;
END get_test_seq_udf;
/

I will run three different tests:

1. Test calling NEXTVAL directly in MERGE statement (“MERGE1-with-nextval”)
2. Test getting nextvalue from normal function (“MERGE2-with-function”)
3. Test getting nextvalue from function defined with PRAGMA UDF (“MERGE3-with-function-udf”)

I will use the following three MERGE statements:

----------------------------------------------------------
-- WITH NEXTVAL
----------------------------------------------------------
prompt MERGE1-with-nextval
MERGE /* MERGE1-with-nextval */ INTO test_t target
USING (
   SELECT *
   FROM test_src
) source ON ( target.other_id = source.other_id )
WHEN MATCHED THEN 
  UPDATE 
     SET   target.text = source.text, sys_updated_time = systimestamp
     --WHERE target.text <> source.text
WHEN NOT MATCHED THEN
  INSERT ( id, other_id, text, sys_created_time)
    VALUES( test_seq.NEXTVAL, source.other_id, source.text, systimestamp);

select * from test_t order by id;
select * from test_src order by id;

----------------------------------------------------------
-- WITH Normal Function
----------------------------------------------------------
prompt MERGE2-with-function
MERGE /* MERGE2-with-function */ INTO test_t target
USING (
   SELECT *
   FROM test_src
) source ON ( target.other_id = source.other_id )
WHEN MATCHED THEN 
  UPDATE 
     SET   target.text = source.text, sys_updated_time = systimestamp
     --WHERE target.text <> source.text
WHEN NOT MATCHED THEN
  INSERT ( id, other_id, text, sys_created_time)
    VALUES( get_test_seq, source.other_id, source.text, systimestamp);
    
----------------------------------------------------------
-- WITH Function Pragma UDF
----------------------------------------------------------
prompt MERGE3-with-function-udf
MERGE /* MERGE3-with-function-udf */ INTO test_t target
USING (
   SELECT *
   FROM test_src
) source ON ( target.other_id = source.other_id )
WHEN MATCHED THEN 
  UPDATE 
     SET   target.text = source.text, sys_updated_time = systimestamp
     --WHERE target.text <> source.text
WHEN NOT MATCHED THEN
  INSERT ( id, other_id, text, sys_created_time)
    VALUES( get_test_seq_udf, source.other_id, source.text, systimestamp);

In all tree test, I’ll do the following:

1. Recreate all the objects (running script above).
2. Run the corresponding MERGE statement (with “set timing on”) for the three different tests (see list above).
3. Query the metadata on sequence and count records in the TEST_T table (see query below).

The query ran after each test:

col sequence_name for a20

select sequence_name, cache_size, last_number , 
       (select count(*) from test_t) cnt_rows
  from user_sequences 
  where sequence_name='TEST_SEQ';

Summary of the test

The following table shows the timings and increase in sequence:

Test	Run	Timing	Ins/Upd	Last Number	Row Count
MERGE1-with-nextval	1	8.197 sec	INS	1000001	1000000
MERGE1-with-nextval	2	15.029 sec	UPD	2000001	1000000
MERGE1-with-nextval	3	13.147 sec	UPD	3000001	1000000
MERGE2-with-function	1	12.322 sec	INS	1000001	1000000
MERGE2-with-function	2	12.265 sec	UPD	1000001	1000000
MERGE2-with-function	3	14.268 sec	UPD	1000001	1000000
MERGE3-with-function-udf	1	10.324 sec	INS	1000001	1000000
MERGE3-with-function-udf	2	14.157 sec	UPD	1000001	1000000
MERGE3-with-function-udf	3	12.032 sec	UPD	1000001	1000000

As Bob suggest there is an increase in the timing when it comes to the INSERTs, but the UPDATEs seems to be actually faster when using the function. In our original case the first time the functionality is ran there will be only inserts, but after this the majority will be updates. If we uncomment the WHERE clause in the MERGE (see statements above), where we check for changes, we can in many situation also avoid the actual update.

The test shows a smal difference in the timings when using normal function and function defined with PRAGMA UDF. The PRAGMA UDF seems a little faster, and this was expected. I have ran the first run (INSERT) for the MERGE2 and MERGE3 several times, and the time difference is consistent (around 2 second less for MERGE3).

Output from test 1 (MERGE1-with-nextval):

MERGE1-with-nextval (run 1)

1,000,000 rows merged.

Elapsed: 00:00:08.197

MERGE1-with-nextval (run 2)

1,000,000 rows merged.

Elapsed: 00:00:15.029

MERGE1-with-nextval (run 3)

1,000,000 rows merged.

Elapsed: 00:00:13.147

SEQUENCE_NAME        CACHE_SIZE LAST_NUMBER   CNT_ROWS
-------------------- ---------- ----------- ----------
TEST_SEQ                   1000     3000001    1000000

Output from test 2 (MERGE2-with-function):

MERGE2-with-function (run 1)

1,000,000 rows merged.

Elapsed: 00:00:12.322

MERGE2-with-function (run 2)

1,000,000 rows merged.

Elapsed: 00:00:12.265

MERGE2-with-function (run 3)

1,000,000 rows merged.

Elapsed: 00:00:14.268

SEQUENCE_NAME        CACHE_SIZE LAST_NUMBER   CNT_ROWS
-------------------- ---------- ----------- ----------
TEST_SEQ                   1000     1000001    1000000

Output from test 3 (MERGE3-with-function-udf):

MERGE3-with-function-udf (run 1)

1,000,000 rows merged.

Elapsed: 00:00:10.324

MERGE3-with-function-udf (run 2)

1,000,000 rows merged.

Elapsed: 00:00:14.157

MERGE3-with-function-udf (run 3)

1,000,000 rows merged.

Elapsed: 00:00:12.032

SEQUENCE_NAME        CACHE_SIZE LAST_NUMBER   CNT_ROWS
-------------------- ---------- ----------- ----------
TEST_SEQ                   1000     1000001    1000000