Однопроходное объединение SCD2 нарушает первичный ключ в Oracle 12
Через некоторое время я понял, что imho довольно быстрое однопроходное слияние для исторического описания медленно меняющегося типа измерения 2.
Он отлично работает на таблицах без уникальных ограничений.
В основном это работает и для таблиц с уникальным ограничением. Но иногда (обычно, когда изменение в истории больше) я получаю ошибку ORA-00001, уникальное нарушение ограничения. Конечно, я знаю, что есть два проходных метода, которые работают, но они медленнее.
Мое единственное предположение, что Oracle иногда делает INSERT перед UPDATE, что на мгновение дублирует TH_Valid_To_Date.
Есть идеи, как этого избежать (сохраняя первичный ключ)?
Исходная таблица:
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*/
)
;
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