How to avoid wasting sequences in differential merge pattern

DATA ARCHITECTURE

by Ludek Bob Jankovsky, 25-Feb-2009 (ETL PATTERNS)

In Oracle 10g, 11g getting values from sequences during MERGE behaves a bit strange. Despite you define getting value of sequence directly in a WHEN NOT MATCHED clause, sequence is acquired and wasted even if record is matched. According to docomented behaviour sequence will be acguired for every source record. It is a bad message because of offer usage of mentioned construction in DIFF_MERGE pattern.
Lets see what to do with it.

See: Differential merge pattern

The best proven solution is to create a function returning number of sequence. This function should be then used in the SQL merge statement (insert part only). The solution slows the execution in comparison to direct usage of sequence, but it prevents waste of sequence numbers and makes keys smaller.

We will show the effect of sequence waste on following example:
Lets create two tables:

The SRC_TABLE representing source of simple 1:1 transformation
The TRG_TABLE representing target of the transformation

CREATE TABLE SRC_TABLE (
  SRCT_ID    VARCHAR2(100 CHAR)
,SRCT_NAME  VARCHAR2(500 CHAR)
,SRCT_DESC  VARCHAR2(2000 BYTE)
)
;
INSERT INTO SRC_TABLE
SELECT TO_CHAR(LEVEL), 'Name of '||TO_CHAR(LEVEL), 'Description of '||TO_CHAR(LEVEL)
FROM DUAL CONNECT BY LEVEL <= 10
;
CREATE TABLE TRG_TABLE (
  TRGT_KEY   INTEGER
,TRGT_ID    VARCHAR2(100 CHAR)
,TRGT_NAME  VARCHAR2(500 CHAR)
,TRGT_DESC  VARCHAR2(2000 BYTE)
)
;

[Download]

We create the SEQ_TRG_TABLE sequence to support surrogate key generation

CREATE SEQUENCE SEQ_TRG_TABLE MINVALUE 0;

We create standard Differential Merge transformation using SEQ_TRG_TABLE.NEXTVAL in the NOT MATCHED INSERT clause:

MERGE INTO A1.TRG_TABLE TRG$
USING ( SELECT
   SRCT_ID AS TRGT_ID
  ,SRCT_NAME AS TRGT_NAME
  ,SRCT_DESC AS TRGT_DESC
FROM SRC_TABLE) SRC$
ON(
  TRG$.TRGT_ID=SRC$.TRGT_ID
)
WHEN MATCHED THEN UPDATE SET
   TRG$.TRGT_NAME=SRC$.TRGT_NAME
  ,TRG$.TRGT_DESC=SRC$.TRGT_DESC
WHERE --update when
      (TRG$.TRGT_NAME is null and SRC$.TRGT_NAME is not null)
   or (TRG$.TRGT_NAME is not null and SRC$.TRGT_NAME is null)
   or TRG$.TRGT_NAME!=SRC$.TRGT_NAME
   or (TRG$.TRGT_DESC is null and SRC$.TRGT_DESC is not null)
   or (TRG$.TRGT_DESC is not null and SRC$.TRGT_DESC is null)
   or TRG$.TRGT_DESC!=SRC$.TRGT_DESC
WHEN NOT MATCHED THEN INSERT(
   TRG$.TRGT_KEY
  ,TRG$.TRGT_ID
  ,TRG$.TRGT_NAME
  ,TRG$.TRGT_DESC
)VALUES(
   SEQ_TRG_TABLE.NEXTVAL
  ,SRC$.TRGT_ID
  ,SRC$.TRGT_NAME
  ,SRC$.TRGT_DESC
);

[Download]

Now we run the transformation to fill target table with the source data.

10 rows merged. SELECT * FROM TRG_TABLE; TRGT_KEY TRGT_ID TRGT_NAME TRGT_DESC ---------------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 1 Name of 1 Description of 1 2 2 Name of 2 Description of 2 3 3 Name of 3 Description of 3 4 4 Name of 4 Description of 4 5 5 Name of 5 Description of 5 6 6 Name of 6 Description of 6 7 7 Name of 7 Description of 7 8 8 Name of 8 Description of 8 9 9 Name of 9 Description of 9 10 10 Name of 10 Description of 10 10 rows selected

Now we run the transformation three times without changing source data.

0 rows merged. 0 rows merged. 0 rows merged.

We add another record to the source data to see the impact of idle runs.

INSERT INTO SRC_TABLE VALUES (11,'Name of 11', 'Description of 11');

We repeat the transformation and check the result.

1 rows inserted. 1 rows merged. SELECT * FROM TRG_TABLE; TRGT_KEY TRGT_ID TRGT_NAME TRGT_DESC ---------------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 1 Name of 1 Description of 1 2 2 Name of 2 Description of 2 3 3 Name of 3 Description of 3 4 4 Name of 4 Description of 4 5 5 Name of 5 Description of 5 6 6 Name of 6 Description of 6 7 7 Name of 7 Description of 7 8 8 Name of 8 Description of 8 9 9 Name of 9 Description of 9 10 10 Name of 10 Description of 10 51 11 Name of 11 Description of 11 11 rows selected

Now you can see, the sequence in TRGT_KEY has been wasted, there is new sequence used 51 instead of expected 11.

Now we present following solution not wasting the sequence:
We create function FSEQ_TRG_TABLE returning value from the sequence:

CREATE OR REPLACE FUNCTION FSEQ_TRG_TABLE RETURN INTEGER IS
BEGIN
RETURN SEQ_TRG_TABLE.NEXTVAL;
END FSEQ_TRG_TABLE;
/

[Download]

We change the Differential Merge transformation using the function in the NOT MATCHED INSERT clause instead of direct usage of the sequence.

MERGE INTO A1.TRG_TABLE TRG$
USING ( SELECT
   SRCT_ID AS TRGT_ID
  ,SRCT_NAME AS TRGT_NAME
  ,SRCT_DESC AS TRGT_DESC
FROM SRC_TABLE) SRC$
ON(
  TRG$.TRGT_ID=SRC$.TRGT_ID
)
WHEN MATCHED THEN UPDATE SET
   TRG$.TRGT_NAME=SRC$.TRGT_NAME
  ,TRG$.TRGT_DESC=SRC$.TRGT_DESC
WHERE --update when
      (TRG$.TRGT_NAME is null and SRC$.TRGT_NAME is not null)
   or (TRG$.TRGT_NAME is not null and SRC$.TRGT_NAME is null)
   or TRG$.TRGT_NAME!=SRC$.TRGT_NAME
   or (TRG$.TRGT_DESC is null and SRC$.TRGT_DESC is not null)
   or (TRG$.TRGT_DESC is not null and SRC$.TRGT_DESC is null)
   or TRG$.TRGT_DESC!=SRC$.TRGT_DESC
WHEN NOT MATCHED THEN INSERT(
   TRG$.TRGT_KEY
  ,TRG$.TRGT_ID
  ,TRG$.TRGT_NAME
  ,TRG$.TRGT_DESC
)VALUES(
   FSEQ_TRG_TABLE
  ,SRC$.TRGT_ID
  ,SRC$.TRGT_NAME
  ,SRC$.TRGT_DESC
);

[Download]

Now we will repeat all the steps and we will seek another result:

10 rows merged. 0 rows merged. 0 rows merged. 0 rows merged. 1 rows inserted. 1 rows merged. TRGT_KEY TRGT_ID TRGT_NAME TRGT_DESC ---------------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 1 Name of 1 Description of 1 2 2 Name of 2 Description of 2 3 3 Name of 3 Description of 3 4 4 Name of 4 Description of 4 5 5 Name of 5 Description of 5 6 6 Name of 6 Description of 6 7 7 Name of 7 Description of 7 8 8 Name of 8 Description of 8 9 9 Name of 9 Description of 9 10 10 Name of 10 Description of 10 11 11 Name of 11 Description of 11 11 rows selected

Timing comparison of inserting one million of record one and the other way:

SEQUENCE: 1 000 000 rows merged. Elapsed: 00:00:25.481 FUNCTION: 1 000 000 rows merged. Elapsed: 00:01:17.341

Taking sequence number through function is three times longer than using sequence directly.
Now we will do another test .... with 1000000 updates and 1000000 inserts.

SEQUENCE: 1 000 000 rows merged. Elapsed: 00:01:03.574 FUNCTION: 1 000 000 rows merged. Elapsed: 00:02:13.108

Ludek Bob Jankovsky