Oracle DB ROWNUM과 ROWID의 완벽한 이해: 차이점, 사용법, 성능 최적화 전략

안녕하세요.

이번 포스팅은 Oracle 데이터베이스에서 자주 사용되지만 종종 혼동되는 두 개념인 ROWNUM과 ROWID에 대해 상세히 알아보겠습니다. 이 두 기능은 비슷해 보이지만 완전히 다른 목적을 가지고 있으며, 각각의 특성을 이해하면 쿼리 성능을 크게 개선할 수 있습니다.

#1. ROWNUM과 ROWID 개요

ROWNUM이란?

ROWNUM은 Oracle이 쿼리 결과 집합의 각 행에 할당하는 가상(pseudocolumn) 일련번호입니다. 쿼리가 처리되는 과정에서 결과 행이 반환될 때마다 증가하는 숫자를 부여합니다.

ROWID란?

ROWID는 Oracle 데이터베이스 내의 행(row)의 물리적 주소를 가리키는 고유 식별자입니다. 테이블의 모든 행은 생성 시점에 자동으로 할당되는 ROWID를 갖게 됩니다.

#2. ROWNUM 상세 분석

ROWNUM의 특성

동적 할당: ROWNUM은 쿼리 실행 시점에 동적으로 할당됩니다.
순차적 특성: 1부터 순차적으로 증가하며, 건너뛰는 숫자가 없습니다.
재할당 불가: 한 번 할당된 ROWNUM은 해당 쿼리 내에서 변경되지 않습니다.
쿼리 종속성: 같은 테이블이라도 다른 쿼리에서는 다른 ROWNUM 값을 가질 수 있습니다.

ROWNUM 처리 순서

ROWNUM은 다음 순서로 처리됩니다:

FROM 절 처리
WHERE 절 처리
ROWNUM 할당
SELECT 절 처리
ORDER BY 절 처리

이 순서는 ROWNUM을 사용할 때 매우 중요한 특성입니다. 특히 ORDER BY와 함께 사용할 때 주의해야 합니다.

ROWNUM 사용 구문

-- 기본 ROWNUM 사용법
SELECT ROWNUM, employee_id, first_name
FROM employees
WHERE ROWNUM <= 5;

-- 서브쿼리를 이용한 정렬 후 ROWNUM 할당
SELECT ROWNUM, employee_id, first_name
FROM (
    SELECT employee_id, first_name
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

#3. ROWID 상세 분석

ROWID의 구조

Oracle ROWID는 18자리 문자열로 표현되며, 다음 정보를 인코딩합니다:

데이터 객체 번호(Data Object Number): 객체가 속한 테이블스페이스 내의 고유 번호
데이터 파일 번호(Data File Number): 데이터 파일의 번호
데이터 블록 번호(Data Block Number): 데이터 파일 내 블록 번호
데이터 행 번호(Data Row Number): 블록 내 행 번호

ROWID의 특성

고유성: 모든 행은 데이터베이스 내에서 고유한 ROWID를 갖습니다.
불변성: 행이 존재하는 한 ROWID는 변경되지 않습니다(특수한 경우 제외).
물리적 위치: 데이터베이스 내 행의 실제 물리적 위치를 나타냅니다.
자동 생성: 행 삽입 시 Oracle이 자동으로 할당합니다.

ROWID 사용 구문

-- 테이블에서 ROWID 조회
SELECT ROWID, employee_id, first_name
FROM employees
WHERE department_id = 10;

-- ROWID를 사용한 행 조회 (가장 빠른 방법)
SELECT employee_id, first_name, last_name
FROM employees
WHERE ROWID = 'AAAE5zAAEAAAADLAAA';

-- ROWID와 형식 변환 함수
SELECT DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID) AS file_number,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS block_number,
DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS row_number
FROM employees
WHERE employee_id = 100;

#4. ROWNUM과 ROWID의 핵심 차이점

ROWNUM과 ROWID는 근본적으로 다른 개념입니다. 주요 차이점은 다음과 같습니다:

No	항목	ROWNUM	ROWID
1	정의	쿼리 결과 집합의 가상 일련번호	행의 물리적 주소를 나타내는 고유 식별자
2	할당 시점	쿼리 실행 시 동적 할당	행 생성 시 자동 할당
3	유지 기간	쿼리 결과 내에서만 유효	행이 존재하는 동안 유지
4	변경 가능성	쿼리에 따라 변경됨	행이 이동하지 않는 한 변경되지 않음
5	용도	페이징, 상위/하위 N개 행 추출	특정 행의 직접 접근, 성능 최적화
6	유일성	쿼리 내에서 유일 (1부터 순차적)	데이터베이스 전체에서 유일
7	접근 방식	논리적 접근	물리적 접근

#5. ROWNUM 활용 사례

페이징 처리 구현

-- 11-20번째 행 가져오기 (Oracle 12c 이전)
SELECT *
FROM (
    SELECT a.*, ROWNUM AS rnum
    FROM (
        SELECT employee_id, first_name, last_name
        FROM employees
        ORDER BY employee_id
    ) a
    WHERE ROWNUM <= 20
)
WHERE rnum >= 11;

-- Oracle 12c 이상에서는 ROW_NUMBER() 또는 OFFSET-FETCH 사용 권장
SELECT employee_id, first_name, last_name
FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

상위 N개 레코드 추출

-- 급여 기준 상위 5명 직원
SELECT employee_id, first_name, last_name, salary
FROM (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

샘플 데이터 추출

-- 랜덤 샘플 10개 추출
SELECT employee_id, first_name, last_name
FROM (
    SELECT employee_id, first_name, last_name
    FROM employees
    ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM <= 10;

#6. ROWID 활용 사례

초고속 단일 행 조회

-- ROWID를 사용한 가장 빠른 단일 행 접근
SELECT *
FROM employees
WHERE ROWID = 'AAAE5zAAEAAAADLAAA';

중복 행 식별 및 제거

-- 중복 행 중 하나만 남기고 삭제
DELETE FROM duplicate_data
WHERE ROWID NOT IN (
    SELECT MIN(ROWID)
    FROM duplicate_data
    GROUP BY col1, col2, col3
);

테이블 조인 최적화

-- ROWID를 활용한 조인 최적화
SELECT e.employee_id, e.first_name, e.salary, j.job_title
FROM employees e, jobs j
WHERE j.ROWID = (
    SELECT ROWID
    FROM jobs
    WHERE job_id = e.job_id
)
AND e.department_id = 10;

데이터 이동 추적

-- 행 이동을 모니터링하는 트리거
CREATE OR REPLACE TRIGGER track_row_movement
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO row_movement_log(
        employee_id,
        old_rowid,
        new_rowid,
        change_date
    )
    VALUES(
        :NEW.employee_id,
        :OLD.ROWID,
        :NEW.ROWID,
        SYSDATE
    );
END;

#7. 성능 관점에서의 비교

ROWNUM 성능 특성

동적 계산: 매 쿼리마다 새로 계산되므로 약간의 오버헤드 발생
인덱스 사용: ROWNUM <= n 조건은 일반적으로 인덱스와 함께 효율적으로 동작
대용량 데이터 처리: 대량의 데이터에서 상위/하위 N개 추출 시 효율적
정렬 비용: ORDER BY와 함께 사용 시 정렬 비용 발생

ROWID 성능 특성

최고 속도: Oracle에서 단일 행에 접근하는 가장 빠른 방법
인덱스보다 빠름: 기본 키나 고유 인덱스보다 빠른 접근 제공
직접 주소 지정: 데이터의 물리적 위치를 직접 가리키므로 검색 과정 생략
물리적 I/O 감소: 테이블 접근 경로를 최소화하여 I/O 감소

성능 비교 실험

일반적인 시나리오에서 ROWID vs 인덱스 vs ROWNUM 성능 비교:

No	접근 방식	1백만 행 테이블 접근 시간 (ms)	상대적 성능
1	ROWID	1	가장 빠름 (1x)
2	기본 키	2-3	2-3배 느림
3	인덱스	3-5	3-5배 느림
4	ROWNUM <= 1	2-10	2-10배 느림 (쿼리 복잡도에 따라 다름)

#8. 일반적인 실수와 오해

ROWNUM 관련 일반적인 실수

ORDER BY와 ROWNUM 동시 사용 오류

-- 잘못된 사용: ORDER BY는 ROWNUM 할당 후 적용됨
SELECT ROWNUM, employee_id, salary
FROM employees
WHERE ROWNUM <= 5
ORDER BY salary DESC;  -- 급여 상위 5명이 아닌 임의의 5명이 정렬됨

-- 올바른 사용: 서브쿼리로 먼저 정렬 후 ROWNUM 적용
SELECT ROWNUM, employee_id, salary
FROM (
    SELECT employee_id, salary
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

ROWNUM 범위 조건 오류

-- 잘못된 사용: ROWNUM > 1 조건은 어떤 행도 반환하지 않음
SELECT employee_id, first_name
FROM employees
WHERE ROWNUM > 1 AND ROWNUM <= 5;  -- 결과 없음

-- 올바른 사용: 서브쿼리로 ROWNUM 먼저 할당 후 필터링
SELECT *
FROM (
    SELECT ROWNUM AS rnum, employee_id, first_name
    FROM employees
)
WHERE rnum > 1 AND rnum <= 5;

ROWID 관련 일반적인 오해

ROWID 영구성 오해

ROWID는 다음 작업 후에 변경될 수 있습니다:
- 테이블 재구성(REORGANIZE)
- 테이블 이동
- 일부 파티션 작업
- 특정 유형의 UPDATE 작업

ROWID 저장 시도

-- 좋지 않은 방식: ROWID를 저장하는 것은 위험할 수 있음
CREATE TABLE saved_rowids (
    employee_id NUMBER,
    emp_rowid ROWID  -- 테이블 재구성 후 무효화될 수 있음
);

-- 더 나은 방식: 비즈니스 키 사용
CREATE TABLE references (
    employee_id NUMBER PRIMARY KEY,
    reference_data VARCHAR2(100)
);

#9. Oracle 버전별 변화와 특징

Oracle 9i

ROWNUM 기본 기능 제공
ROWID는 18자리 확장 형식(Extended ROWID) 사용

Oracle 10g

ROWNUM 처리 알고리즘 개선
ROWID 접근 최적화

Oracle 11g

결과 캐싱 메커니즘 향상으로 ROWNUM 쿼리 성능 개선
ROWID를 통한 접근 최적화 기능 향상

Oracle 12c

ROW_LIMITING 절 도입: OFFSET-FETCH 구문 추가
Top-N 쿼리 최적화 기능 강화

-- Oracle 12c 이상의 향상된 페이징 구문
SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Oracle 19c/21c

분석 함수와 ROWNUM의 병렬 처리 기능 향상
메모리 최적화를 통한 ROWID 접근 성능 개선

#10. 자주 묻는 질문(FAQ)

Q1: ROWNUM과 ROW_NUMBER() 분석 함수의 차이점은 무엇인가요?

A: ROWNUM은 pseudocolumn으로, 쿼리 처리 중에 동적으로 할당되며 ORDER BY 전에 적용됩니다. ROW_NUMBER()는 분석 함수로, 정렬된 결과에 번호를 매기며 PARTITION BY를 통해 그룹별 번호 부여가 가능합니다.

-- ROW_NUMBER() 예제
SELECT
    employee_id,
    first_name,
    department_id,
    salary,
    ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

Q2: ROWID는 항상 불변인가요?

A: 아니요, ROWID는 다음과 같은 상황에서 변경될 수 있습니다:

테이블 재구성(REORGANIZE)
행 이동(특정 유형의 UPDATE 작업 시)
테이블 이동
파티션 작업

Q3: ROWNUM = 1과 ROWNUM <= 1의 성능 차이가 있나요?

A: 실행 계획 상으로는 동일하지만, Oracle 옵티마이저가 일부 상황에서 ROWNUM = 1을 더 효율적으로 처리할 수 있습니다. 특히 첫 번째 행을 찾자마자 처리를 중단하는 최적화가 적용될 수 있습니다.

Q4: ROWNUM은 인덱스를 사용하나요?

A: ROWNUM 자체는 인덱스를 사용하지 않지만, WHERE 절의 다른 조건이 인덱스를 사용한다면 ROWNUM 조건과 함께 인덱스 검색이 효율적으로 수행될 수 있습니다.

Q5: 대용량 테이블에서 페이징 처리 시 ROWNUM과 ROWID 중 어떤 것이 효율적인가요?

A: 대용량 테이블의 페이징 처리에는 다음 방법이 권장됩니다:

Oracle 12c 이상: OFFSET-FETCH 구문 사용
이전 버전: 인덱스를 활용한 범위 스캔 + ROWNUM
ROWID는 특정 행에 직접 접근할 때 유용하지만, 페이징에는 적합하지 않습니다.

결론

ROWNUM과 ROWID는 Oracle 데이터베이스에서 서로 다른 목적으로 사용되는 중요한 기능입니다.

ROWNUM은 쿼리 결과에 동적으로 할당되는 가상 일련번호로, 상위/하위 N개 레코드 추출, 페이징 처리 등에 유용합니다. 그러나 ORDER BY와 함께 사용할 때는 적절한 서브쿼리 구조가 필요합니다.

ROWID는 행의 물리적 주소를 나타내는 고유 식별자로, Oracle에서 가장 빠른 접근 방법을 제공합니다. 특정 행에 빠르게 접근해야 할 때, 중복 행 제거, 테이블 조인 최적화 등에 효과적입니다.

두 기능의 특성과 차이점을 제대로 이해하고 적절한 상황에 활용한다면, Oracle 데이터베이스의 성능을 크게 향상시킬 수 있습니다. 특히 대용량 데이터를 다루는 엔터프라이즈 환경에서는 이러한 기본 개념의 깊은 이해가 성능 최적화의 핵심 요소가 됩니다.

긴 글 읽어주셔서 감사합니다.

끝.

저작자표시 비영리 변경금지

'■Development■ > 《Database》' 카테고리의 다른 글

[DB] Oracle SQL 완벽 정리 (0)	2022.08.31
[DB] Oracle Listener 완벽 가이드 (0)	2020.04.08
[DB] DB Sequence 생성과 삭제 완벽 가이드 (0)	2020.04.08
[DB] ORACLE DB 사용자 계정 패스워드 없애는 방법 (0)	2020.04.08
[DB] ORACLE DB SYSTEM, SYS 암호 변경 (0)	2020.04.08

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

[DB] ROWNUM과 ROWID의 완벽한 이해