SCD2一次合并违反Oracle 12中的主键

Question

过了一段时间，我发现imho是一个相当快的单次合并语句，用于历史记录缓慢变化的维度类型2。

它可以在没有唯一约束的表上完美地工作。

CREATE TABLE TESTS 
  (
     T_Key_1                        NUMBER(38,0)    DEFAULT -1 NOT NULL ENABLE /*  */
    ,T_Key_2                        NUMBER(38,0)    DEFAULT -1 NOT NULL ENABLE /*  */
    ,Text_Value                     VARCHAR2(100)    /*  */
    ,Number_Value                   NUMBER(38,0)    DEFAULT -1 NOT NULL ENABLE /*  */
    ,Amount                         NUMBER           /*  */

    ,CONSTRAINT T_PK PRIMARY KEY (T_Key_1, T_Key_2)  /* Primární klíč */    
  )
;

历史记录表：

CREATE TABLE TEST_HISTORY 
  (
     T_Key_1                        NUMBER(38,0)    DEFAULT -1 NOT NULL
    ,T_Key_2                        NUMBER(38,0)    DEFAULT -1 NOT NULL
    ,Text_Value                     VARCHAR2(100)    
    ,Number_Value                   NUMBER(38,0)    DEFAULT -1 NOT NULL
    ,Amount                         NUMBER          
    ,TH_Valid_From_Date             DATE            DEFAULT to_date('1000-01-01','yyyy-mm-dd') NOT NULL /* SCD2 - Start of validity of record. */
    ,TH_Valid_To_Date               DATE            DEFAULT to_date('3000-01-01','yyyy-mm-dd') NOT NULL /* SCD2 - End of validity of record. */

    ,CONSTRAINT TH_PK PRIMARY KEY (T_Key_1, T_Key_2, TH_Valid_To_Date) using index local 
  )
/** Physical Options **************************************************************************************************/
partition by range (TH_Valid_To_Date) interval (NUMTOYMINTERVAL (1, 'MONTH'))
(partition P_10000000 values less than (TO_DATE ('01-01-1000', 'DD-MM-YYYY')))
ENABLE ROW MOVEMENT 
;

合并：

MERGE INTO (SELECT * FROM TEST_HISTORY WHERE TH_Valid_To_Date = to_date('3000-01-01','yyyy-mm-dd'))             Hst /*change only current records which are identified by TH_Valid_To_Date = to_date('3000-01-01','yyyy-mm-dd') */
   USING (
      SELECT * FROM (
         SELECT NVL(Src.T_Key_1, Dst.T_Key_1)                                AS T_Key_1
            ,NVL(Src.T_Key_2, Dst.T_Key_2)                                   AS T_Key_2
            ,Src.Text_Value
            ,Src.Number_Value
            ,Src.Amount
            ,CASE WHEN Src.T_Key_1 is null                                   THEN 'D' /*delete*/
                  WHEN Dst.T_Key_1 is null                                   THEN 'I' /*insert*/
                  WHEN (Src.Text_Value=Dst.Text_Value OR (Src.Text_Value is null AND Dst.Text_Value is null))
                       AND (Src.Number_Value=Dst.Number_Value OR (Src.Number_Value is null AND Dst.Number_Value is null))
                       AND (Src.Amount=Dst.Amount OR (Src.Amount is null AND Dst.Amount is null))
                                                                             THEN 'X' /*no change*/
                                                                             ELSE 'U' /*update*/ END  AS Operation  
         FROM TESTS                                         Src
         FULL JOIN (SELECT * FROM TEST_HISTORY WHERE TH_Valid_To_Date = to_date('3000-01-01','yyyy-mm-dd'))     Dst
            ON (Src.T_Key_1 = Dst.T_Key_1 AND Src.T_Key_2 = Dst.T_Key_2)
      )
      INNER JOIN (SELECT LEVEL AS duplication FROM DUAL CONNECT BY LEVEL BETWEEN 1 AND 2) ON (duplication=1 OR Operation='U') /*need to duplicate update records so that they can go to both matched and not matched parts*/
      WHERE Operation<>'X'
   )                                                                          Act
   ON (Act.T_Key_1 = Hst.T_Key_1 AND Act.T_Key_2 = Hst.T_Key_2 AND Act.duplication=1 AND Act.operation<>'I')

WHEN MATCHED THEN UPDATE
   SET
      TH_Valid_To_Date                              = p_Load_Date - 1,
   WHERE Hst.TH_Valid_To_Date                       = to_date('3000-01-01','yyyy-mm-dd')

WHEN NOT MATCHED THEN INSERT /*+ append */
   (
       T_Key_1                       
      ,T_Key_2                       
      ,Text_Value                    
      ,Number_Value                  
      ,Amount                        
      ,TH_Valid_From_Date             /*Auditní sloupec*/
      ,TH_Valid_To_Date               /*Auditní sloupec*/
   ) VALUES (
       Act.T_Key_1                       
      ,Act.T_Key_2                       
      ,Act.Text_Value                    
      ,Act.Number_Value                  
      ,Act.Amount                        
      ,p_Load_Date                    /*Auditní sloupec*/
      ,to_date('3000-01-01','yyyy-mm-dd') /*Auditní sloupec*/
   )
;

Answer 1

我自己也遇到了同样的问题：不能同时插入和更新记录。Oracle在首次执行联接时确定是插入还是更新记录。下面是一个简化的测试用例来说明这一点：

-- This is the table into which we will be merging data

CREATE TABLE merge_table
(
    mt_col1   INTEGER NOT NULL
  , mt_col2   INTEGER
);

-- Make mt_col1 unique
ALTER TABLE merge_table ADD (
  UNIQUE (mt_col1)
  USING INDEX);

-- This is the table from which we will be drawing the data

CREATE TABLE datasource_table
(
    ds_col1   INTEGER
  , ds_col2   INTEGER
);

-- Load up the data source with 10 rows (1-10)

INSERT INTO datasource_table (
           ds_col1, ds_col2
            )
    SELECT ROWNUM r, ROWNUM r
      FROM all_objects
     WHERE ROWNUM < 11;

-- Create a duplicate record

INSERT INTO datasource_table (
           ds_col1, ds_col2
            )
     VALUES (1, 1);

-- Create a record to be updated

INSERT INTO merge_table (
           mt_col1, mt_col2
            )
     VALUES (2, 2);

COMMIT;

-- This merge will fail with a unique constraint violation because
-- an mt_col1 value of 1 does not exist. The datasource table contains
-- two entries for 1, so the merge will try to insert two mt_col1=1 records,
-- violating the unique constraint.

MERGE INTO merge_table dt
     USING (SELECT ds_col1, ds_col2
              FROM datasource_table) a
        ON (a.ds_col1 = dt.mt_col1)
WHEN MATCHED
THEN
    UPDATE SET mt_col2   = a.ds_col2 + 10
WHEN NOT MATCHED
THEN
    INSERT     (
               mt_col1, mt_col2
               )
        VALUES (ds_col1, ds_col2);

-- ORA-00001: unique constraint (SYS_C0013990) violated

-- Delete one of the duplicate records and the merge succeeds

DELETE FROM datasource_table
      WHERE ds_col1 = 1
        AND ROWNUM < 2;

-- Merge is now successful

MERGE INTO merge_table dt
     USING (SELECT ds_col1, ds_col2
              FROM datasource_table) a
        ON (a.ds_col1 = dt.mt_col1)
WHEN MATCHED
THEN
    UPDATE SET mt_col2   = a.ds_col2 + 10
WHEN NOT MATCHED
THEN
    INSERT     (
               mt_col1, mt_col2
               )
        VALUES (ds_col1, ds_col2);

-- 10 rows updated