권순용의 DB 이야기
SQL 작성시 ROWID 이용 0 0 3,702

by axiom ROWID 중복제거 [2015.10.18]


이번 시간에는 SQL을 작성할 때 ROWID를 이용하는 방법에 대해 알아본다. 많은 애플리케이션을 분석하더라도 ROWID를 활용해 SQL을 작성한 경우는 보기 힘들다.

SQL을 작성할 때 ROWID를 이용하면 보다 효과적으로 SQL을 작성할 수 있는데, 지금부터 몇 가지 SQL 예제를 통해 ROWID를 이용한 SQL 작성에 대해 살펴보자.

첫 번째로 하나의 테이블에서 중복된 데이터를 찾는 예제를 살펴보자. 이처럼 테이블상의 중복된 데이터를 제거하는 데에도 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절로 그룹핑한다.

  • CUBRID 설치 초기 화면

그룹핑된 데이터는 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 활용 방법에 대해 살펴보겠다.

- 강좌 URL : http://www.gurubee.net/lecture/2932

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입