(Oracle) ignore_row_on_dupkey_index 동작이 11g와 19c에서 다른 문제 (bug)

상황

• tbl 테이블 PK는 tbl_no이고, tbl_tmp에는 unique constraint 같은 것이 안걸려 있는 상황.
• 요구사항 1) tbl 테이블에 이미 존재하는 tbl_no row를 insert하는 경우, 에러 없이 수행되어야 함
• 요구사항 2) tbl_tmp 테이블에 tbl_no가 동일한 row가 2개 이상 존재하는 경우, 에러 없이 1개만 insert 되어야 함

11g, 19c 버전에 따라 동작이 다른 ignore_row_on_dupkey_index 힌트

• 실행 예제
  • 11g http://sqlfiddle.com/#!4/33936/2/1
  • 19.8.0.0.0 https://livesql.oracle.com/apex/livesql/s/m1u7dlcxh71of7r8x02tlvj2t (여기서는 문제가 없다.)
  • 19.0.0.0.0 버전에서 문제 발생하는 것으로 보임.
• 요구사항 1) tbl 테이블에 이미 존재하는 tbl_no row를 insert하는 경우, 에러 없이 수행되어야 함
  • 11g : O
  • 19c : O
• 요구사항 2) tbl_tmp 테이블에 tbl_no가 동일한 row가 2개 이상 존재하는 경우, 에러 없이 1개만 insert 되어야 함
  • 11g : O
  • 19c : X(PK violation)

해결책 : MERGE INTO를 사용하면 어떨까?

MERGE INTO tbl e
USING tbl_tmp t
ON (e.tbl_NO = t.tbl_NO)
WHEN NOT MATCHED THEN
    INSERT (e.tbl_no) 
    VALUES (t.tbl_no);
---
[2022-02-07 11:54:10] [23000][1] ORA-00001: 무결성 제약 조건(tbl_PK)에 위배됩니다

• 요구사항 1) tbl 테이블에 이미 존재하는 tbl_no row를 insert하는 경우, 에러 없이 수행되어야 함
  • 11g : O
  • 19c : O
• 요구사항 2) tbl_tmp 테이블에 tbl_no가 동일한 row가 2개 이상 존재하는 경우, 에러 없이 1개만 insert 되어야 함
  • 11g : X
  • 19c : X(PK violation)
  • MERGE INTO에 UPDATE 문 추가해도X

요구사항 1, 2를 모두 만족하는 해결책은?

• 근본적으로 tbl_tmp 테이블에 중복 로그가 생기지 않도록 비즈니스 로직을 고친다
• tbl_tmp 가져올 때, 서브쿼리로 DISTINCT 해서 가져온다.

tbl_tmp 중복제거

SELECT *
FROM tbl_tmp
WHERE ROWID IN (SELECT MAX(ROWID) FROM tbl_tmp GROUP BY tbl_no);

tbl_tmp 중복제거 - MERGE INTO 사용하는 방법

MERGE INTO tbl e
USING (SELECT * FROM tbl_tmp WHERE ROWID IN (SELECT MAX(ROWID) FROM tbl_TMP GROUP BY tbl_NO)) t
...

tbl_tmp 중복제거 - hint 사용하는 방법

INSERT /*+ ignore_row_on_dupkey_index(tbl(tbl_no)) */
INTO tbl
(
   ...
)
SELECT
    ...
FROM tbl_tmp
WHERE ROWID IN (SELECT MAX(ROWID) FROM tbl_tmp GROUP BY tbl_no);