첫 번째로 하나의 테이블에서 중복된 데이터를 찾는 예제를 살펴보자. 이처럼 테이블상의 중복된 데이터를 제거하는 데에도 ROWID를 이용할 수 있다.

• [리스트 1] ROWID를 이용한 테이블 내 중복 데이터 제거

• DELETE 
    FROM EMP
   WHERE ROWID IN 
       ( SELECT MAX(ROWID) 
           FROM EMP
          GROUP BY ENAME, DEPTNO
         HAVING COUNT(*) > 1
       )
  

<리스트 1>의 SQL을 수행하면 어떤 결과가 도출될까? 이에 앞서 이 SQL의 서브쿼리를 함께 확인해보자.

• [리스트 2] [리스트 1]의 서브쿼리

• SELECT MAX(ROWID) 
    FROM EMP
   GROUP BY ENAME, DEPTNO
  HAVING COUNT(*) > 1
  

<리스트 2>에서 알 수 있듯 이 SQL은 EMP 테이블로부터 ENAME 컬럼과 DEPTNO 컬럼의 값이 동일한 데이터를 GROUP BY절로 그룹핑한다.

그룹핑된 데이터는 HAVING 절에 의해 원본 데이터가 1건인 데이터가 그룹핑에서 제거된다. 따라서 ENAME 컬럼과 DEPTNO 컬럼의 값이 동일한 2건 이상의 데이터가 테이블에 존재하면 해당 데이터 중 ROWID 값이 최대 값이 쪽이 결과로 추출된다.

이렇게 추출된 ROWID에 해당하는 데이터를 제거하면 해당 테이블에서 ENAME 컬럼과 DEPTNO 컬럼의 값이 동일한 데이터는 1건씩 제거되게 된다.

• [리스트 3] 모든 중복 데이터 제거하기

• DELETE 
    FROM EMP
   WHERE ROWID IN 
       ( SELECT RID 
           FROM 
              ( SELECT MAX(ROWID) RID
                     , ROW_NUMBER() 
                       OVER(PARTITION BY ENAME, DEPTNO 
                 ORDER BY ENAME, DEPTNO) RN
                  FROM EMP
              )
          WHERE RN > 2
       );
  

만약 동일한 데이터가 1건이 아니라 여러 건일 경우 <리스트 1>은 동일한 데이터 중 1건만 제거하므로 중복 데이터가 전부 제거되지 않는다. 이 경우 <리스트 3>처럼 SQL을 작성하면 중복 데이터를 모두 제거할 수 있다.

<리스트 3>은 ENAME 컬럼과 DEPT_NO 컬럼의 값이 동일한 데이터 각각에 번호를 할당한다. 할당된 번호(RN)가 2보다 크면 EMP 테이블에서 데이터를 삭제하므로 해당 테이블에는 ENAME 컬럼과 DEPT_NO 컬럼의 값이 동일한 데이터는 모두 제거된다.

물론 종복 데이터를 제거하는 데에는 굳이 ROWID를 사용하지 않아도 된다. 지금부터는 MERGE INTO에서 ROWID를 사용하는 법을 살펴본다.

• [리스트 4] MERGE INTO 예제

• -- TT1 테이블 생성
  CREATE TABLE TT1 (
    A VARCHAR2(10) ,
    B VARCHAR2(10) 
  );
  
  
  -- TT2 테이블 생성
  CREATE TABLE TT2 (
    A VARCHAR2(10) ,
    B VARCHAR2(10) 
  );
  
  
  MERGE INTO TT1
  USING (SELECT * FROM TT2) A
     ON ( TT1.A = A.A)
  WHERE MATCHED THEN
  UPDATE SET A = 'C';
  
  
  ORA-38104: ON 절에서 참조되는 열은 갱신할 수 없음: “TT1”.“A”
  

MERGE INTO 절에서 ON 절의 조인 조건절을 UPDATE 절에서 갱신하면 <리스트 1>과 같은 에러가 발생한다. 이 경우 어떻게 처리해야 할까?

MERGE INTO 절을 이용한 SQL을 작성하다보면 이처럼 조인 조건에 해당하는 컬럼을 갱신해야 하는 경우가 있는데, 이 때에도 ROWID를 통해 문제를 해결할 수 있다.

• [리스트 5] [리스트 4]에 ROWID 적용

• MERGE INTO TT1
  USING 
      (
        SELECT ROWID RD 
          FROM TT1
         WHERE EXISTS 
             ( SELECT 'X' 
                 FROM TT2 A
                WHERE TT1.A = A.A
             )
      ) A
     ON (TT1.ROWID = A.RD)
  WHERE MATCHED THEN 
  UPDATE SET A = 'C';
  
  1 rows upserted.
  
  
  SELECT * FROM TT1;
  
  A          B         
  --------- ----
  C          1         
  B          1
  
  2 rows selected.
  

<리스트 5>처럼 조인 조건 절을 ROWID로 변경하면 UPDATE 절에서 원래 조인 조건의 컬럼을 UPDATE할 수 있다. 다음 시간에는 이밖의 ROWID 활용 방법에 대해 살펴보겠다.