[Oracle] ROWID와 ROWNUM

ROWID 정의

 

▣ ROWID는 ORACLE에서 INDEX를 생성하기 위하여 내부적으로 사용하는 PSEUDOCOLUMN 으로 사용자가 임으로 변경하거나 삭제할 수 없다.

▣ 단지 테이블의 한 컬럼처럼 참조만 가능하며 데이타베이스에 값이 저장되지는 않는다.
▣ ROWID는 물리적인 ADDRESS를 가지고 있기 때문에 SINGLE BLOCK ACCESS로 찾고자 하는 ROW를 찾을 수 있어 물리적인 ROWID는 주어진 테이블에 가장 빠른 ACCESS 방법을 제공하게 된다.

▣ ROWID를 분석해보면
AAAArs AAD AAAAUa AAA
 ------  ---  ------  ---
    1     2      3     4
1) 6자리 : 데이터 오브젝트 번호 (data object number) - 오브젝트의 고유 번호
2) 3자리 : 상대적 파일 번호 (relative file number) - 각각의 데이터파일에 할당되는 번호
3) 6자리 : 블록 번호 (block number) - 데이터 블록의 위치를 알려주는 번호
4) 3자리 : 블록 내의 행 번호 (row number) - 오라클 블록의 헤더에 저장된 row directory slot의 위치를 알려주는 고유 번호

 

ROWID 활용법

▣ 중복제거

 

1. 테이블에서 필드1, 필드2, 필드3 을 불러오는데 필드1에 대해서만 중복을 제거하고 불러와야 할 때

select distinct 필드1, 필드2, 필드3 from 테이블1 where 필드2 = '1' order by 필드1;

위와같이 하면 필드1, 필드2, 필드3 가 모두 중복되어야 제거가 된다.

따라서 이 경우에는 rowid 를 사용해야 한다.

 

SELECT 필드1, 필드2, 필드3 FROM 테이블1 WHERE 필드2 = '1' and rowid in                      (SELECT max(rowid) FROM 테이블1 GROUP BY 필드1);

 

2. 중복된 데이터 중에서 ROWID가 큰 값을 제거
 

DELETE FROM emp a WHERE rowid > (SELECT MIN(rowid) FROM emp b                         WHERE b.empno = a.empno);

 

DELETE FROM emp a WHERE rowid > ANY (SELECT rowid FROM emp b                                WHERE b.empno = a.empno);

 

--> min 그룹함수를 사용하면 수행시 sort 작업이 일어나 수행속도가 떨어지는 반면, any 연산자를 사용하면 하나라도 클 경우 작업이 종료되므로 수행속도가 향상된다.

3. 나중에 들어온 데이터를 살릴 경우
 

DELETE FROM emp a WHERE rowid < (SELECT MAX(rowid) FROM emp b                         WHERE a.empno = b.empno);

 

 

▣ 중복행 찾기

 

SELECT * FROM emp a  WHERE rowid > ANY (SELECT rowid FROM emp b                                 WHERE b.code1=a.code1 AND b.code2=a.code2)

 

 

서브쿼리의 where 절에 기본키 컬럼을 모두 조건으로 기술하면 된다.

위 쿼리는 중복된 행 중에 한개만 찾아주는데 중복된 행을 모두 검색하려면 다음과 같이 한다. 

 

SELECT *

FROM EMP A

         ,(SELECT CODE1, CODE2, COUNT(*)

           FROM EMP B

           GROUP BY CODE1, CODE2

           HAVING COUNT(*) > 1)

WHERE A.CODE1 = B.CODE1

AND     A.CODE2 = B.CODE2

;

 

 

▣ 행을 찾아가는 가장 빠른 방법이 rowid 라는 것을 이용.
update emp set sal = 999
where rowid in (select rowid from emp where ename like 'A%');
==> 따라서 인덱스의 활용과도 연관성이 있을 거라 생각됩니다. 인덱스는 인덱스 컬럼과 rowid를 가지고 있으니까요.

▣ 유일성 부여

데이터가 non-unique 한 환경일 때, 데이터에 "유일성"을 부여할 수 있다.
예를 들면, 이전 페이지에 나왔던 데이터가 다음 페이지에 중복되어 나오는 것을 방지할 수 있다. 이순신이라는 사람이 10건 있다고 할 때, 이전페이지에 7건이 나왔다고 하면 "다음"버튼을 누르면 나머지 3명의 이순신이 나와야 되는 업무가 있을 때 like 를 쓰게 되면 10명 모두 나오게 된다. 이때 rowid 를 적절히 이용하면 이들에게도 유일성을 보장할 수 있다.
select * from emp
where ename || rowid > :ename || :before_rowid

참고로, 이 원리는 MSSQL2000에서도 그대로 반영되고 있다.
MSSQL2000의 인덱스페이지를 살펴보면, unique 일 경우에는 4byte의 "?" 컬럼이 생기지 않지만, non-unique 할 때는 4byte의 "?" 컬럼이 생겨 값들의 유일성을 보장하고 있는 것을 알 수 있다. 결국 unique 라는 옵션 하나만 잘 사용해도 엄청난 수행속도를 보장받을 수 있는 것이다.

 

ROWNUM 정의

결과집합에 대한 가상의 순번

따라서 같은 SQL이라고 하더라도, 다른 rownum 을 가질 수 있다.

 

 

ROWNUM 활용법
 

◈ 선택한 Row만큼만 보여주기

SQL> SELECT name
      FROM storm_board
      WHERE rownum <= 10
---> 이렇게 하면 데이터가 1000건이 있더라도, 1~10건만 보여주게 된다.

◈ copy_t 테이블 생성시
CREATE TABLE qq_t
AS
SELECT rownum no, to_char(rownum, '09') c_no

FROM cdr                  --> cdr 테이블은 최소한 100건 이상
WHERE rownum < 100;

 

● ROWID를 이용한 중복제거

 

SELECT * FROM 테이블명 

 WHERE ROWID IN(SELECT MAX(ROWID) FROM 테이블명 GROUP BY 중복제거할 기준되는 컬럼)

출처: http://kdarkdev.tistory.com/82 [kdarkdev]

[출처] ROWID 와 ROWNUM|작성자 humanang