Oracle 대용량 데이터 페이징 처리 방식 비교 ( ROW_NUMBER VS OFFSET-FETCH VS KEYSET(SEEK) )
Oracle 환경에서 1,000만 건 이상의 대용량 데이터를 다루면서 페이징을 구현해야 할 경우, 적절한 페이징 방식의 선택은 성능과 안정성에 직접적인 영향을 미친다. 이번에 받은 작업은 대용량 엑셀 다운로드 기능을 구현해야하는데 기존 프로젝트에서 쓰는 페이징 기법은 뒤로 갈 수록 느리다는 단점이 존재하였다. 직접 3가지 유형을 써보고 제일 빠르게 개선된 방식을 기록해두겠다. 대표적인 페이징 방식은 다음 세 가지가 있으며, 각각의 작동 방식, 장단점, 인덱스 및 정렬 순서 유지 여부, 대용량 처리 시 문제점을 함께 비교하여 정리한다.
1. ROWNUM / ROW_NUMBER 기반 페이징
Oracle 8i 이상에서 사용 가능한 방식으로, 전체 데이터를 정렬한 후 ROWNUM 또는 ROW_NUMBER()를 부여하고 원하는 범위를 추출하는 방식이다.
예제
SELECT *
FROM (
SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.created_at) AS rn
FROM orders t
WHERE t.status = 'ACTIVE'
)
WHERE rn BETWEEN 10001 AND 11000;장점
- 정렬 순서 보장
- 임의 페이지 접근 가능
- 복잡한 조건과 조인에서도 유연하게 적용 가능
단점
- 전체 정렬 후 번호를 부여하므로 정렬 비용 발생
- 페이지 번호가 클수록 TEMP 사용 및 처리 시간 증가
1,000만 건 테스트 시 문제점
- 전체 정렬 후 잘라내기 때문에 후반부 페이지(예: 900만 건 이후)는 처리 시간이 수 초~수십 초 소요될 수 있음
2. OFFSET-FETCH 기반 페이징 (Oracle 12c 이상)
ANSI SQL 표준 구문을 사용하는 방식으로, Oracle 12c 이상에서 지원된다.
예제
SELECT *
FROM orders
WHERE status = 'ACTIVE'
ORDER BY created_at
OFFSET 1000000 ROWS FETCH NEXT 1000 ROWS ONLY;장점
- SQL 문법이 간결하고 직관적
- 프론트엔드/API 개발자에게 친숙한 방식
단점
- OFFSET 수만큼 정렬 후 데이터를 건너뛰기 때문에 성능 저하 발생
- 페이지 번호가 클수록 처리 시간이 급격히 증가
1,000만 건 테스트 시 문제점
- OFFSET이 커질수록 정렬 비용이 폭발적으로 증가하며, TEMP 공간 사용률과 CPU 부하도 함께 증가함
3. KEYSET 페이징 (Seek 방식)
이전 페이지의 마지막 정렬 키 값을 조건으로 사용하여 다음 페이지를 조회하는 방식이다. 불필요한 데이터를 건너뛰지 않고 바로 필요한 레코드를 조회할 수 있다. 1,000건의 마지막 행의 데이터를 다음 1,000건 조회할 때 파라미터로 던져주어야한다.
예제
SELECT *
FROM orders
WHERE
created_at > :lastCreatedAt
OR (created_at = :lastCreatedAt AND order_id > :lastOrderId)
ORDER BY created_at, order_id
FETCH FIRST 1000 ROWS ONLY;인덱스 및 정렬 순서의 중요성
- 정렬 기준 컬럼에 복합 인덱스가 반드시 존재해야 한다.
- 예: CREATE INDEX idx_orders_created_at_id ON orders (created_at, order_id);
- 정렬 기준이 고유하지 않으면 중복 데이터 발생 가능 → PK 또는 ROWID 추가 필요
장점
- 인덱스 기반 탐색으로 정렬 비용이 없음
- 페이지 번호가 커져도 일정한 성능 유지
- TEMP 사용 없음
단점
- 임의 페이지 접근 불가 (엑셀 작업이기 때문에 임의 페이지는 의미없었음)
- 정렬 키 값을 클라이언트에서 유지해야 함 (자바단에서 마지막 행을 계속 던져줘야함)
1,000만 건 테스트 시 성능
- 인덱스가 잘 구성되어 있다면 모든 페이지에서 응답 속도가 일정하게 유지됨 (밀리초~1초 수준)
- 100만건이든 1000만건이든 해당 인덱스를 바로 찾아 다음 1,000건을 들고오는 방식이라 굉장히 빠름
방식별 비교 요약
| 항목 | ROWNUM/ROW_NUMBER | OFFSET-FETCH | KEYSET |
| Oracle 지원 버전 | 8i 이상 | 12c 이상 | 8i 이상 |
| 정렬 순서 보장 | O | O | O |
| 임의 페이지 접근 | O | O | X |
| 페이지 커질 때 성능 | 저하됨 | 급격히 저하 | 일정함 |
| TEMP 사용 가능성 | 높음 | 매우 높음 | 없음 |
| 구현 난이도 | 낮음 | 낮음 | 중간 |
| 인덱스 의존도 | 낮음 | 중간 | 높음 |
결론
Oracle에서 대용량 페이징 처리를 수행할 때, OLTP 환경 즉, 단순한 구조와 가독성을 원한다면 ROWNUM 또는 OFFSET 방식이 유용할 수 있다.
그러나 정렬 기준 인덱스가 잘 구성되어 있고, 대용량엑셀 다운로드 같은 환경에 적합한 OLAP환경에선 KEYSET 방식이 성능과 확장성 측면에서 가장 뛰어난 선택이다.
정렬 대상 컬럼에 인덱스를 구성하고, 정렬 기준이 고유하도록 보장하는 설계가 KEYSET 방식의 성능을 극대화하는 핵심 요소이다.
현재 진행하고있는 사내 프로젝트에서 이렇게 많은 대용량 데이터를 다운받는 작업은 없었는데 KEYSET 방식을 통해 처음 1,000만건 이상 다운로드를 DB가 죽지 않고 빠르게 잘 받을 수 있도록 구현하였다.
1,000 건의 마지막 행 데이터를 가져와 파라미터로 던지는 작업은 JAVA 서비스 단에서 간단하게 구현할 수 있으니 이 페이징 기법 꼭 추천한다.