ORACLE ROWID를 이용한 중복데이터 삭제

ORACLE ROWID를 이용한 중복데이터 삭제

---

1. 데이터 샘플

👉 총 데이터 20 ROW

👉 중복 데이터 16 ROW (삭제 대상)

👉 유니크 데이터 4 ROW

WITH DUP_TABLE AS (
    -- 중복데이터 5개
    SELECT 'DUP1_COLUMN1' AS COLUMN1, 'DUP1_COLUMN2' AS COLUMN2, 'DUP1_COLUMN3' AS COLUMN3, 'DUP1_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP1_COLUMN1' AS COLUMN1, 'DUP1_COLUMN2' AS COLUMN2, 'DUP1_COLUMN3' AS COLUMN3, 'DUP1_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP1_COLUMN1' AS COLUMN1, 'DUP1_COLUMN2' AS COLUMN2, 'DUP1_COLUMN3' AS COLUMN3, 'DUP1_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP1_COLUMN1' AS COLUMN1, 'DUP1_COLUMN2' AS COLUMN2, 'DUP1_COLUMN3' AS COLUMN3, 'DUP1_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP1_COLUMN1' AS COLUMN1, 'DUP1_COLUMN2' AS COLUMN2, 'DUP1_COLUMN3' AS COLUMN3, 'DUP1_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    -- 중복데이터 5개
    SELECT 'DUP2_COLUMN1' AS COLUMN1, 'DUP2_COLUMN2' AS COLUMN2, 'DUP2_COLUMN3' AS COLUMN3, 'DUP2_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP2_COLUMN1' AS COLUMN1, 'DUP2_COLUMN2' AS COLUMN2, 'DUP2_COLUMN3' AS COLUMN3, 'DUP2_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP2_COLUMN1' AS COLUMN1, 'DUP2_COLUMN2' AS COLUMN2, 'DUP2_COLUMN3' AS COLUMN3, 'DUP2_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP2_COLUMN1' AS COLUMN1, 'DUP2_COLUMN2' AS COLUMN2, 'DUP2_COLUMN3' AS COLUMN3, 'DUP2_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP2_COLUMN1' AS COLUMN1, 'DUP2_COLUMN2' AS COLUMN2, 'DUP2_COLUMN3' AS COLUMN3, 'DUP2_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    -- 중복데이터 5개
    SELECT 'DUP3_COLUMN1' AS COLUMN1, 'DUP3_COLUMN2' AS COLUMN2, 'DUP3_COLUMN3' AS COLUMN3, 'DUP3_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP3_COLUMN1' AS COLUMN1, 'DUP3_COLUMN2' AS COLUMN2, 'DUP3_COLUMN3' AS COLUMN3, 'DUP3_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP3_COLUMN1' AS COLUMN1, 'DUP3_COLUMN2' AS COLUMN2, 'DUP3_COLUMN3' AS COLUMN3, 'DUP3_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP3_COLUMN1' AS COLUMN1, 'DUP3_COLUMN2' AS COLUMN2, 'DUP3_COLUMN3' AS COLUMN3, 'DUP3_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP3_COLUMN1' AS COLUMN1, 'DUP3_COLUMN2' AS COLUMN2, 'DUP3_COLUMN3' AS COLUMN3, 'DUP3_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    -- 중복데이터 5개
    SELECT 'DUP4_COLUMN1' AS COLUMN1, 'DUP4_COLUMN2' AS COLUMN2, 'DUP4_COLUMN3' AS COLUMN3, 'DUP4_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP4_COLUMN1' AS COLUMN1, 'DUP4_COLUMN2' AS COLUMN2, 'DUP4_COLUMN3' AS COLUMN3, 'DUP4_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP4_COLUMN1' AS COLUMN1, 'DUP4_COLUMN2' AS COLUMN2, 'DUP4_COLUMN3' AS COLUMN3, 'DUP4_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP4_COLUMN1' AS COLUMN1, 'DUP4_COLUMN2' AS COLUMN2, 'DUP4_COLUMN3' AS COLUMN3, 'DUP4_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
    SELECT 'DUP4_COLUMN1' AS COLUMN1, 'DUP4_COLUMN2' AS COLUMN2, 'DUP4_COLUMN3' AS COLUMN3, 'DUP4_COLUMN4' AS COLUMN4 FROM DUAL
)

---

2. ORACLE ROWID를 이용한 중복데이터 조회

👉 먼저 생성된 ROWID를 기준으로 데이터를 기준으로 조회

👉 나중에 생성된 데이터를 남기고 싶다면 MAX(ROWID)

WITH DUP_TABLE AS (
    -- 중복데이터 5개
    SELECT 'DUP1_COLUMN1' AS COLUMN1... FROM DUAL
)
SELECT COUNT(1)
FROM DUP_TABLE A
WHERE ROWID > (
    SELECT MIN(ROWID)
    FROM DUP_TABLE B
    WHERE A.COLUMN1 = B.COLUMN1
    AND A.COLUMN2 = B.COLUMN2
    AND A.COLUMN3 = B.COLUMN3
    AND A.COLUMN4 = B.COLUMN4
);

3. ORACLE ROWID를 이용한 중복데이터 삭제

👉 실행 되지는 않음(WITH TABLE 은 삭제할 수 없음)

WITH DUP_TABLE AS (
    -- 중복데이터 5개
    SELECT 'DUP1_COLUMN1' AS COLUMN1... FROM DUAL
)
DELETE FROM DUP_TABLE 
WHERE ROWID > (
    SELECT MIN(ROWID)
    FROM DUP_TABLE B
    WHERE A.COLUMN1 = B.COLUMN1
    AND A.COLUMN2 = B.COLUMN2
    AND A.COLUMN3 = B.COLUMN3
    AND A.COLUMN4 = B.COLUMN4
);