페이징 처리 - OFFSET 의 한계와 키셋 페이징
이 장에서 배우는 것
온라인 서점의 독자 게시판은 최신 글부터 보여 준다. 첫 화면은 빠르지만 오래된 글을 찾으려고 페이지를 넘기면 응답 시간이 길어진다. 정렬에 맞는 인덱스가 있어도 이런 현상이 생긴다. 정렬을 생략하는 것과 앞선 행을 건너뛰는 것은 서로 다른 작업이기 때문이다.
앞 장에서 정렬을 줄이고 필요한 행만 일찍 반환하는 방법을 살펴보았다. 여기서는 그 위에 페이지 경계라는 조건을 추가한다. 페이지 번호로 위치를 계산하는 방식과 마지막으로 본 정렬 키에서 다시 탐색하는 방식을 같은 데이터로 비교한다. 실행계획의 모양뿐 아니라 실제로 읽은 행과 논리 읽기를 확인하는 것이 핵심이다.
- 오프셋(OFFSET)이 커질수록 인덱스 접근량이 증가하는 이유를 설명한다.
- Oracle의 ROWNUM과 행 제한 절로 동일한 페이지를 조회한다.
- 중복 없는 정렬 순서와 복합 경계를 사용해 키셋 페이징(keyset pagination)을 구현한다.
- 실제 실행 통계에서 반환 행 수와 처리 행 수를 구분하고 튜닝 효과를 판단한다.
- 게시판의 페이지 번호, 다음 버튼, 데이터 변경 정책에 맞는 방식을 선택한다.
문제 상황
서점 운영팀은 독자 게시판에서 오래된 배송 문의를 확인한다. 게시판에는 공개 글 20만 건이 있고 화면에는 한 번에 20건을 표시한다. 정렬은 작성 시각 내림차순이다. 처음에는 첫 페이지의 응답 시간만 검사했으므로 문제가 드러나지 않았다. 이후 운영자가 검색 기간을 넓히고 뒤쪽 페이지를 조회하면서 지연을 발견했다.
기존 SQL은 페이지 번호에서 건너뛸 행 수를 계산한다. 페이지 크기가 20이고 페이지 번호가 5,001이라면 앞의 100,000건을 건너뛴다. 사용자가 받는 결과는 여전히 20건이지만 데이터베이스는 그 앞부분을 처리해야 한다. 결과 건수가 작다는 사실만으로 작업량이 작다고 판단할 수 없다.
사례에서는 게시판 번호와 공개 상태를 선두로 하고 작성 시각과 글 번호를 내림차순으로 둔 인덱스를 사용한다. 글 번호는 기본 키이며 작성 시각은 NULL을 허용하지 않는다. 같은 시각에 여러 글이 등록될 수 있으므로 글 번호를 마지막 정렬 기준으로 추가한다. 제목은 인덱스에 포함하지 않는다. 따라서 목록을 구성하려면 테이블에서 제목을 가져오는 접근도 필요하다.
실험은 조회 방식 자체의 차이를 드러내기 위해 데이터 변경이 없는 상태에서 수행한다. 첫 페이지, 깊은 위치의 기존 조회, 같은 위치의 개선 조회를 차례로 실행한다. 깊은 위치는 앞의 100,002건을 건너뛴 지점이다. 일반적인 페이지 번호에서 조금 벗어난 경계를 일부러 선택한다. 작성 시각이 같은 글 사이에 경계가 놓여야 복합 조건의 정확성까지 확인할 수 있기 때문이다.
개선 대상은 운영 화면의 다음 글 탐색이다. 특정 페이지 번호로 바로 이동하는 기능까지 같은 비용으로 제공하는 것이 목표는 아니다. 운영팀은 오래된 글을 연속해서 살펴보는 경우가 많으므로 다음 버튼에 키셋 방식을 적용하고, 임의 위치로 이동하는 기능은 별도의 요구사항으로 남긴다.
뒤 페이지가 느려지는 이유
오프셋 방식은 결과의 앞부분을 읽은 뒤 버린다. 정렬에 맞는 인덱스를 사용하면 별도 정렬을 생략할 수 있지만 인덱스가 결과의 100,003번째 행을 번호만으로 찾아 주지는 않는다. 선행 컬럼의 조건과 정렬 순서에 따라 인덱스를 읽으며 위치를 세어야 한다. 페이지 크기를 K, 건너뛸 행 수를 M이라고 하면 대체로 M＋K개의 후보를 처리하는 구조가 된다.
이 수식은 논리 읽기의 정확한 계산식은 아니다. 하나의 인덱스 블록에는 여러 엔트리가 있고, 테이블 접근 시점과 행 배치에 따라서도 읽기 수가 달라진다. 제목을 가져온 뒤 행 제한을 적용하는 실행계획이라면 버릴 행에 대해서도 테이블 접근이 발생할 수 있다. 반대로 테이블 접근을 늦춘 계획에서는 그 비용 일부가 줄어든다. 어느 경우든 앞선 인덱스 엔트리를 건너뛰는 작업 자체는 남는다.
Oracle 19c에서는 OFFSET과 FETCH NEXT로 의도를 직접 표현할 수 있다. OFFSET을 생략하고 FETCH FIRST만 사용하면 첫 페이지 조회가 된다. FIRST와 NEXT는 이 구문에서 같은 역할을 한다. 다음 SQL의 두 번째 조회는 앞의 100,002건을 처리한 다음 최대 20건을 반환한다.
SELECT post_id, created_at, title
FROM paging_posts
WHERE board_id = 10
AND status = 'P'
ORDER BY created_at DESC, post_id DESC
FETCH FIRST 20 ROWS ONLY;
SELECT post_id, created_at, title
FROM paging_posts
WHERE board_id = 10
AND status = 'P'
ORDER BY created_at DESC, post_id DESC
OFFSET 100002 ROWS FETCH NEXT 20 ROWS ONLY;ROWNUM을 사용하는 경우에는 정렬, 상한 제한, 하한 제거를 서로 다른 쿼리 블록에 배치한다. 가장 안쪽에서 순서를 정하고 중간에서 100,022건까지만 통과시킨다. 마지막에는 앞의 100,002건을 제거한다. 이 구조 역시 앞부분을 처리하는 비용을 없애지는 않는다.
SELECT post_id, created_at, title
FROM (
SELECT s.post_id, s.created_at, s.title, ROWNUM AS rn
FROM (
SELECT post_id, created_at, title
FROM paging_posts
WHERE board_id = 10
AND status = 'P'
ORDER BY created_at DESC, post_id DESC
) s
WHERE ROWNUM <= 100022
)
WHERE rn > 100002
ORDER BY rn;실행계획에서는 ROWNUM의 상한이 COUNT STOPKEY로 나타나거나 행 제한 절이 WINDOW NOSORT STOPKEY로 나타날 수 있다. 정렬이 필요하면 WINDOW SORT PUSHED RANK와 같은 형태도 가능하다. 연산자 이름은 SQL 형태와 옵티마이저 선택에 따라 달라진다. STOPKEY가 보인다는 이유만으로 20건만 읽었다고 판단해서는 안 된다. 중단 지점이 20인지 100,022인지 확인해야 한다.
논리 읽기는 필요한 블록을 버퍼 캐시에서 접근한 횟수에 해당한다. 캐시가 따뜻해져 물리 읽기와 응답 시간이 줄어들더라도 반복적으로 접근하는 블록의 양은 남을 수 있다. 따라서 이번 비교에서는 응답 시간만 보지 않고 실제 실행계획의 Buffers와 A-Rows를 함께 읽는다. Buffers는 서로 다른 블록의 개수가 아니며 같은 블록에 대한 반복 접근도 포함할 수 있다.
마지막 정렬 키를 다음 조회의 경계로 사용한다
키셋 방식은 마지막으로 반환한 행의 정렬 키를 다음 요청에 전달한다. 내림차순 목록에서 마지막 행의 작성 시각이 T, 글 번호가 I라면 다음 행은 작성 시각이 T보다 작거나, 작성 시각이 T와 같으면서 글 번호가 I보다 작아야 한다. 페이지 번호가 아니라 값으로 범위를 지정하므로 인덱스에서 다음 구간을 찾을 수 있다.
WHERE board_id = :board_id
AND status = 'P'
AND (
created_at < :last_created_at
OR (
created_at = :last_created_at
AND post_id < :last_post_id
)
)
ORDER BY created_at DESC, post_id DESC
FETCH FIRST 20 ROWS ONLY이 조건은 결과의 의미를 설명하기에 간결하다. 다만 논리적으로 올바른 조건과 효율적인 접근 경계는 구분해야 한다. 실행계획에 따라 위 OR 조건 전체가 필터로 처리되면 게시판의 최신 글부터 다시 읽으며 경계 밖의 행을 버릴 수 있다. 인덱스를 사용한다는 사실만으로 탐색 시작점까지 개선되었다고 판단할 수 없다.
완성 코드에서는 이 가능성을 관찰하기 쉽게 두 범위로 나눈다. 첫 범위는 작성 시각이 같고 글 번호가 작은 행이다. 두 번째 범위는 작성 시각이 더 과거인 행이다. 두 범위는 겹치지 않으므로 UNION ALL로 합친다. 각각에서 최대 20건을 구한 다음 전체 순서에 맞춰 최종 20건을 선택한다. 모든 범위에 게시판 번호와 공개 상태 조건을 동일하게 적용한다.
각 범위에서 20건씩만 가져와도 최종 결과는 정확하다. 어떤 범위에서 21번째 이후인 행은 그 범위 안에만 자신보다 앞선 행이 이미 20개 존재한다. 따라서 전체 상위 20건에 들어갈 수 없다. 최종 정렬의 입력은 최대 40건이다. 여기서는 작은 정렬을 허용하면서 두 개의 명시적인 인덱스 범위를 얻는다.
실험 데이터는 같은 작성 시각을 네 글이 공유한다. 마지막으로 본 글이 99,999번이면 같은 시각에서 뒤따르는 글은 99,998번과 99,997번이다. 그 다음에는 더 과거인 99,996번부터 이어진다. 두 범위를 합친 최종 결과는 99,998번부터 99,979번까지다. 시각 조건만 사용하면 앞의 두 글이 빠지므로 이 경계는 정확성을 검사하기에도 적합하다.
운영 코드에서는 마지막 행에서 받은 값을 그대로 바인드한다. 화면에 표시한 초 단위 문자열을 다시 해석하거나 작성 시각을 별도로 재조회하지 않는다. TIMESTAMP를 쓰는 테이블이라면 소수 초도 보존해야 한다. 경계 값 외에 게시판, 공개 상태, 검색 조건, 정렬 방향도 같은 요청 맥락으로 묶는다. 검색 조건이 바뀌면 기존 경계를 폐기하고 첫 페이지부터 조회한다.
첫 페이지에는 경계 조건이 없다. 이후에는 이전 응답의 마지막 행을 경계로 전달한다. 마지막 페이지 여부를 응답 한 번으로 판단하려면 필요한 수보다 한 건 더 조회할 수 있다. 20건을 보여 주는 화면이라면 각 범위와 최종 제한을 21건으로 바꾸고, 응답에는 앞의 20건만 담는다. 다음 경계는 숨겨 둔 21번째 행이 아니라 실제로 보여 준 20번째 행에서 만든다.
일관성과 데이터베이스별 차이
정렬 키는 결과를 하나의 순서로 결정해야 한다. 작성 시각만 정렬하면 같은 시각의 글 사이에 순서가 정해지지 않는다. 기본 키를 마지막 기준에 추가하면 같은 데이터 상태에서 순서를 재현할 수 있다. 작성 시각이나 상태가 NULL일 수 있다면 NULL의 위치와 경계 표현도 별도로 정의해야 한다. 이 사례는 해당 컬럼을 NOT NULL로 제한해 그 문제를 제거한다.
Oracle의 기본 읽기 일관성은 하나의 SQL 문장에 적용된다. 서로 다른 페이지 요청이 동일한 데이터 스냅샷을 읽는다는 뜻은 아니다. 최신 글이 추가되면 오프셋 방식에서는 뒤 페이지의 위치가 밀려 같은 글을 다시 볼 수 있다. 키셋 방식은 이미 전달받은 값보다 뒤에 있는 범위를 조회하므로 앞쪽 삽입에 따른 위치 이동의 영향을 줄인다.
그러나 키셋 방식도 모든 변경을 해결하지는 않는다. 이미 본 글의 작성 시각이 과거로 수정되면 다음 페이지에 다시 나타날 수 있다. 아직 보지 않은 글이 삭제되면 그 글은 이후 결과에 없다. 게시판은 보통 최신 상태를 보여 주는 정책을 택하지만, 감사용 목록처럼 처음부터 끝까지 동일한 집합이 필요하면 별도 설계가 필요하다. Oracle에서는 보존 가능한 실행 환경을 전제로 동일한 SCN의 플래시백 조회를 검토할 수 있으며, 긴 조회 동안 필요한 과거 버전이 유지되는지도 확인해야 한다.
| 항목 | Oracle 19c | MySQL 8 |
|---|---|---|
| 첫 페이지 | FETCH FIRST 20 ROWS ONLY | LIMIT 20 |
| 앞부분 건너뛰기 | OFFSET 100002 ROWS FETCH NEXT 20 ROWS ONLY | LIMIT 20 OFFSET 100002 |
| 기존 방식 | 중첩 뷰와 ROWNUM으로 상한과 하한 분리 | ROWNUM을 같은 용도로 제공하지 않음 |
| 복합 경계 | 명시적 비교 조건이나 분리한 범위 사용 | 행 생성자 비교도 표현 가능하나 범위 접근 여부 확인 필요 |
| 실제 실행 정보 | DBMS_XPLAN에서 A-Rows와 Buffers 확인 | 8.0.18 이상에서 EXPLAIN ANALYZE로 실제 행 수와 시간 확인 |
| 논리 읽기 비교 | 실제 커서 통계의 Buffers 사용 | EXPLAIN ANALYZE에 Oracle과 같은 Buffers 열이 없음 |
MySQL에서도 LIMIT 앞의 행은 처리해야 하므로 오프셋의 기본 비용 문제는 같다. 두 범위를 합치는 구현을 옮길 때에는 각 분기의 ORDER BY와 LIMIT가 해당 분기 안에서 적용되도록 쿼리 블록을 유지한다. 문법을 바꾼 뒤에는 실행계획으로 다시 확인한다. Oracle에서 얻은 논리 읽기 수치를 MySQL의 처리 행 수나 핸들러 호출 수와 같은 지표로 간주해서는 안 된다.
완성 코드
다음은 SQL*Plus에서 실행하는 하나의 완전한 실험 스크립트다. macOS 또는 Linux의 클라이언트에서 Oracle 19c 데이터베이스에 접속해 실행한다. 데이터베이스 서버는 클라이언트와 다른 호스트에 있어도 된다. 독립적인 실습 스키마에 저장하며 PAGING_POSTS와 PAGING_IX라는 객체가 아직 없어야 한다. 기존 객체를 자동으로 삭제하지 않는다.
스키마에는 테이블 생성 권한, 테이블스페이스 할당량, DBMS_STATS와 DBMS_XPLAN 실행 권한이 필요하다. 실행 통계를 찾기 위해 V$SQL 조회 권한도 필요하다. DBMS_XPLAN.DISPLAY_CURSOR가 사용하는 V$SQL_PLAN, V$SESSION, V$SQL_PLAN_STATISTICS_ALL 조회 권한도 준비한다. 권한 오류는 DBA가 실습 계정에 필요한 범위로 부여해 해결한다.
스크립트의 인자는 N 또는 Y다. N이면 결과 검증 문장만 출력하고 Y이면 각 조회 직후 실제 실행계획도 출력한다. 통계 수집 힌트는 두 경우 모두 적용한다. 실행계획 출력까지 비교할 때에는 처음부터 Y로 실행한다. 같은 스키마에서 다시 실행하려면 실습 객체를 사용자가 정리해야 한다.
코드는 외부 라이브러리 없이 SQL과 익명 PL/SQL 블록으로 구성했다. 익명 블록은 실행 시 컴파일된다. 이 원고에서는 실제 Oracle 인스턴스에서 컴파일하거나 실행하지 않았으므로 실행 통과를 주장하지 않는다. 아래의 고정 출력은 생성 규칙에서 계산한 예상값이며, 성능 수치는 뒤에서 설명용 예시로 구분한다.
paging_case.sql
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 200
SET TRIMSPOOL ON
SET SERVEROUTPUT ON SIZE UNLIMITED
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
DEFINE show_plan = '&1'
CREATE TABLE paging_posts (
post_id NUMBER(10) NOT NULL,
board_id NUMBER(6) NOT NULL,
status CHAR(1) NOT NULL,
created_at DATE NOT NULL,
title VARCHAR2(80) NOT NULL,
CONSTRAINT paging_posts_pk PRIMARY KEY (post_id)
);
INSERT INTO paging_posts
(post_id, board_id, status, created_at, title)
SELECT LEVEL,
10,
'P',
DATE '2025-01-01' + TRUNC((LEVEL - 1) / 4) / 86400,
'Post ' || TO_CHAR(LEVEL, 'FM000000')
FROM dual
CONNECT BY LEVEL <= 200000;
COMMIT;
CREATE INDEX paging_ix
ON paging_posts (
board_id,
status,
created_at DESC,
post_id DESC
);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'PAGING_POSTS',
estimate_percent => 100,
method_opt => 'FOR ALL COLUMNS SIZE 1',
cascade => TRUE
);
END;
/
DECLARE
l_show_plan CONSTANT VARCHAR2(1) := UPPER('&show_plan');
PROCEDURE run_case(
p_name IN VARCHAR2,
p_sql IN VARCHAR2,
p_first IN NUMBER
) IS
l_cursor SYS_REFCURSOR;
l_id paging_posts.post_id%TYPE;
l_date paging_posts.created_at%TYPE;
l_title paging_posts.title%TYPE;
l_count PLS_INTEGER := 0;
l_first NUMBER;
l_last NUMBER;
l_sql_id VARCHAR2(13);
l_child NUMBER;
BEGIN
OPEN l_cursor FOR p_sql;
LOOP
FETCH l_cursor INTO l_id, l_date, l_title;
EXIT WHEN l_cursor%NOTFOUND;
l_count := l_count + 1;
IF l_id != p_first - l_count + 1 THEN
RAISE_APPLICATION_ERROR(-20001, 'Unexpected order');
END IF;
IF l_count = 1 THEN
l_first := l_id;
END IF;
l_last := l_id;
END LOOP;
CLOSE l_cursor;
IF l_count != 20 THEN
RAISE_APPLICATION_ERROR(-20002, 'Unexpected row count');
END IF;
DBMS_OUTPUT.PUT_LINE(
p_name
|| ' rows=' || TO_CHAR(l_count, 'FM9999990')
|| ' first=' || TO_CHAR(l_first, 'FM9999990')
|| ' last=' || TO_CHAR(l_last, 'FM9999990')
);
IF l_show_plan = 'Y' THEN
SELECT sql_id, child_number
INTO l_sql_id, l_child
FROM v$sql
WHERE sql_text = p_sql
AND parsing_schema_name = USER
ORDER BY last_active_time DESC, child_number DESC
FETCH FIRST 1 ROW ONLY;
FOR r IN (
SELECT plan_table_output
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
l_sql_id,
l_child,
'ALLSTATS LAST +PREDICATE'
)
)
) LOOP
DBMS_OUTPUT.PUT_LINE(r.plan_table_output);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF l_cursor%ISOPEN THEN
CLOSE l_cursor;
END IF;
RAISE;
END run_case;
BEGIN
IF l_show_plan NOT IN ('N', 'Y') OR l_show_plan IS NULL THEN
RAISE_APPLICATION_ERROR(-20003, 'Use N or Y');
END IF;
run_case(
'FIRST',
q'[SELECT /*+ gather_plan_statistics index(b paging_ix) */
/* paging_case_first */
b.post_id, b.created_at, b.title
FROM paging_posts b
WHERE b.board_id = 10
AND b.status = 'P'
ORDER BY b.created_at DESC, b.post_id DESC
FETCH FIRST 20 ROWS ONLY]',
200000
);
run_case(
'OFFSET',
q'[SELECT /*+ gather_plan_statistics index(b paging_ix) */
/* paging_case_offset */
b.post_id, b.created_at, b.title
FROM paging_posts b
WHERE b.board_id = 10
AND b.status = 'P'
ORDER BY b.created_at DESC, b.post_id DESC
OFFSET 100002 ROWS FETCH NEXT 20 ROWS ONLY]',
99998
);
run_case(
'SEEK',
q'[SELECT /*+ gather_plan_statistics */
/* paging_case_seek */
u.post_id, u.created_at, u.title
FROM (
SELECT a.post_id, a.created_at, a.title
FROM (
SELECT /*+ index(b paging_ix) */
b.post_id, b.created_at, b.title
FROM paging_posts b
WHERE b.board_id = 10
AND b.status = 'P'
AND b.created_at =
DATE '2025-01-01' + 24999 / 86400
AND b.post_id < 99999
ORDER BY b.created_at DESC, b.post_id DESC
FETCH FIRST 20 ROWS ONLY
) a
UNION ALL
SELECT c.post_id, c.created_at, c.title
FROM (
SELECT /*+ index(b paging_ix) */
b.post_id, b.created_at, b.title
FROM paging_posts b
WHERE b.board_id = 10
AND b.status = 'P'
AND b.created_at <
DATE '2025-01-01' + 24999 / 86400
ORDER BY b.created_at DESC, b.post_id DESC
FETCH FIRST 20 ROWS ONLY
) c
) u
ORDER BY u.created_at DESC, u.post_id DESC
FETCH FIRST 20 ROWS ONLY]',
99998
);
END;
/
EXIT SUCCESS줄별 해설
SET FEEDBACK OFF부터 시작하는 출력 설정은 테이블 생성과 입력 건수 같은 부가 메시지를 숨긴다. N으로 실행했을 때에는 DBMS_OUTPUT으로 작성한 세 줄만 남는다. WHENEVER SQLERROR는 SQL 또는 PL/SQL 오류가 발생하면 실패 상태로 종료하도록 한다. 다만 DDL은 자체 커밋을 수반하므로 중간 실패 때 만들어진 객체까지 되돌린다는 의미는 아니다.
DEFINE show_plan은 명령행 인자를 SQL*Plus 치환 변수로 받는다. 이 스크립트에는 N이나 Y만 전달한다. 애플리케이션의 사용자 입력을 치환 변수로 연결하는 예제가 아니다. 실제 서비스 SQL에는 바인드 변수를 사용한다.
CREATE TABLE의 기본 키는 글 번호 중복을 막는다. created_at과 post_id가 모두 NOT NULL이므로 경계 비교에서 알 수 없는 값이 생기지 않는다. 제목을 별도 컬럼으로 두고 조회에 포함한 것은 목록 조회가 인덱스 키만 반환하는 특별한 경우로 축소되지 않게 하기 위해서다.
데이터 생성식의 TRUNC((LEVEL - 1) / 4)는 네 행마다 1씩 증가한다. 이를 86,400으로 나누어 DATE에 더하므로 네 글이 같은 초를 공유한다. 가장 큰 글 번호가 가장 최신이며, 같은 시각에서는 큰 글 번호가 앞선다. 이 규칙 덕분에 모든 결과 행을 예상할 수 있다.
CREATE INDEX에서는 등호 조건인 게시판과 상태를 앞에 둔다. 그 뒤의 두 컬럼은 조회 정렬과 같은 내림차순이다. 같은 시각 분기는 세 컬럼의 등호 조건 뒤에 글 번호 범위를 지정한다. 과거 시각 분기는 앞의 두 컬럼을 고정한 뒤 작성 시각 범위를 지정한다. 두 분기 모두 인덱스 시작 경계로 사용할 수 있는 형태다.
GATHER_TABLE_STATS는 입력이 끝난 데이터의 테이블과 인덱스 통계를 수집한다. 이 작은 실험에서는 전체 표본과 히스토그램 없는 설정을 사용해 데이터 분포에 따른 변수를 줄인다. 운영 데이터에 같은 통계 설정을 일괄 적용하라는 의미는 아니다.
run_case는 전달된 조회를 열고 마지막까지 인출한다. 화면에 앞의 몇 건만 표시하고 중단하면 실제 실행 통계도 그만큼만 수집될 수 있다. 여기서는 결과를 끝까지 소비하고 커서를 닫은 뒤 실행계획을 확인한다. 행 수만 검사하지 않고 예상 번호가 하나씩 감소하는지 확인하므로 중복이나 누락도 발견한다.
gather_plan_statistics는 각 조회의 실제 실행 통계를 수집한다. index(b paging_ix)는 실험에서 인덱스를 통한 비교를 유도한다. 운영 적용의 결론을 힌트 자체로 대신하지 않으며, 최종적으로는 실제 계획에서 선택된 경로를 확인해야 한다. 힌트는 잘못된 객체 이름이나 적용 불가능한 조건 때문에 사용되지 않을 수도 있다.
v$sql에서는 방금 실행한 SQL 문자열과 파싱 스키마를 이용해 커서를 찾는다. 실험 SQL은 문자열 컬럼의 길이 제한 안에 들어간다. 격리된 실습 계정에서 한 번씩 실행한다는 전제이며, 공유 SQL을 여러 세션이 동시에 실행하는 운영 진단에서는 자식 커서와 실행 대상을 더 정확하게 식별해야 한다.
ALLSTATS LAST는 찾은 자식 커서의 마지막 실행 통계를 출력한다. +PREDICATE는 조건이 접근 경계인지 읽은 뒤 적용하는 필터인지 확인하는 데 사용한다. 내림차순 인덱스에서는 조건이 내부 표현으로 출력될 수 있다. 표시 문자열만 찾지 말고 각 분기의 범위와 실제 처리량을 함께 해석한다.
실행 결과
SQL*Plus가 설치된 터미널에서 스크립트를 실행한다. 다음 연결 식별자의 호스트와 서비스 이름은 실습 환경에 맞게 바꾼다. 비밀번호를 명령행에 넣지 않으면 SQL*Plus가 별도로 입력받는다. 로그인 안내와 비밀번호 프롬프트를 제외한 N 실행의 예상 출력은 다음과 같다.
sqlplus -s paging_lab@//localhost:1521/FREEPDB1 @paging_case.sql NFIRST rows=20 first=200000 last=199981
OFFSET rows=20 first=99998 last=99979
SEEK rows=20 first=99998 last=99979실제 실행계획까지 확인하려면 객체가 없는 실습 스키마에서 Y로 실행한다. 출력은 FIRST 검증 문장과 그 계획, OFFSET 검증 문장과 그 계획, SEEK 검증 문장과 그 계획 순서다. SQL 식별자, 계획 해시, 시간, 블록 읽기 수는 실행 환경에서 결정되므로 고정된 예상 출력으로 제시하지 않는다.
sqlplus -s paging_lab@//localhost:1521/FREEPDB1 @paging_case.sql Y아래는 계획을 읽는 방법을 설명하기 위한 축약 예시다. 코드 실행에서 수집한 측정값이 아니다. 실제 환경에서는 중간 VIEW가 추가되거나 행 제한 연산자의 이름과 위치가 달라질 수 있다. 숫자가 다르다는 이유로 실패로 판단하지 말고 깊이에 따라 처리량이 증가하는지 확인한다.
OFFSET: 설명용 계획 예시
Operation A-Rows Buffers
SELECT STATEMENT 20 102418
VIEW 20 102418
WINDOW NOSORT STOPKEY 100022 102418
TABLE ACCESS BY INDEX ROWID 100022 102418
INDEX RANGE SCAN PAGING_IX 100022 716
SEEK: 설명용 계획 예시
Operation A-Rows Buffers
SELECT STATEMENT 20 34
VIEW 20 34
SORT ORDER BY STOPKEY 20 34
UNION-ALL 22 34
VIEW 2 7
WINDOW NOSORT STOPKEY 2 7
TABLE ACCESS BY INDEX ROWID 2 7
INDEX RANGE SCAN PAGING_IX 2 4
VIEW 20 27
WINDOW NOSORT STOPKEY 20 27
TABLE ACCESS BY INDEX ROWID 20 27
INDEX RANGE SCAN PAGING_IX 20 4기존 조회의 최상위 반환량은 20건이지만 그 아래에서는 100,022건을 처리한다. 개선 조회는 같은 시각의 두 건과 더 과거인 20건을 읽는다. 합친 22건 가운데 20건을 반환하므로 두 SQL의 결과는 같다. 최상위 A-Rows만 비교했다면 이 차이를 발견하지 못했을 것이다.
| 조회 | 반환 행 | 인덱스 반환 행 | 문장 Buffers |
|---|---|---|---|
| 첫 페이지 | 20 | 20 | 27 |
| 깊은 오프셋 | 20 | 100,022 | 102,418 |
| 같은 경계의 키셋 | 20 | 2＋20 | 34 |
표의 읽기 수는 위 축약 계획과 연결한 설명용 값이며 실측 성능 보장이 아니다. 실험 결과표를 작성할 때에는 자신의 출력으로 교체한다. 행 소스 통계의 상위 Buffers에는 하위 작업이 포함될 수 있으므로 표의 문장 전체 값에 각 연산자 값을 다시 더하지 않는다. 두 인덱스 분기의 A-Rows를 합하는 것과 부모·자식의 Buffers를 중복 합산하는 것은 다른 일이다.
측정에서는 동일한 데이터와 경계를 사용하고 결과를 끝까지 읽는다. 통계 수집 설정도 맞춘다. 한 번의 경과 시간보다 여러 깊이에서의 논리 읽기 증가 추세를 확인하는 편이 이 문제의 원인을 판단하기 쉽다. 실제 데이터에서는 게시판별 글 수, 공개 상태의 비율, 제목 행의 배치가 다르므로 대표 조건을 추가해서 검증한다.
개선 조회의 Buffers가 여전히 깊이에 비례해 증가한다면 조건이 접근 경계로 쓰였는지부터 확인한다. 인덱스를 탄다는 표현으로 진단을 끝내지 않는다. 선택도가 낮은 추가 필터가 있으면 20건을 찾기 위해 많은 후보를 읽을 수도 있다. 키셋은 이미 지나온 구간을 다시 세는 비용을 줄이는 방식이며 모든 필터의 비용까지 없애지는 않는다.
실무에서 자주 틀리는 것
정렬과 ROWNUM을 같은 블록에 둔다
다음 SQL은 먼저 선택된 20건을 정렬할 수 있다. 최신 20건을 보장하는 형태가 아니다.
SELECT post_id, created_at
FROM paging_posts
WHERE board_id = 10
AND status = 'P'
AND ROWNUM <= 20
ORDER BY created_at DESC, post_id DESC;정렬한 결과 바깥에서 상한을 적용한다. 아래 SQL은 결과를 사용하는 최상위 블록에도 정렬을 명시한다.
SELECT post_id, created_at
FROM (
SELECT post_id, created_at
FROM paging_posts
WHERE board_id = 10
AND status = 'P'
ORDER BY created_at DESC, post_id DESC
)
WHERE ROWNUM <= 20
ORDER BY created_at DESC, post_id DESC;작성 시각만 경계로 전달한다
같은 시각에 여러 글이 있을 때 다음 조건은 아직 보여 주지 않은 글까지 제외한다. 비교를 작거나 같음으로 바꾸면 이미 본 글이 다시 나오는 문제가 생긴다.
AND created_at < :last_created_at정렬의 마지막 키까지 함께 전달해 엄격한 다음 범위를 만든다. 아래 조건의 결과 의미는 올바르지만 실제 범위 접근은 실행계획으로 확인한다. 완성 코드처럼 두 범위로 나눌 수도 있다.
AND (
created_at < :last_created_at
OR (
created_at = :last_created_at
AND post_id < :last_post_id
)
)경계 행을 다시 조회해서 시각을 구한다
응답에 글 번호만 저장하고 다음 요청에서 작성 시각을 재조회하면 삭제나 수정에 영향을 받는다. 경계 행이 삭제되면 스칼라 서브쿼리는 NULL을 반환하고 다음 비교는 참이 되지 않는다.
AND created_at < (
SELECT created_at
FROM paging_posts
WHERE post_id = :last_post_id
)이전 응답에 작성 시각과 글 번호를 함께 담고 그 값을 사용한다. 경계 행이 삭제되어도 값 자체는 다음 범위를 지정할 수 있다. 외부에서 받은 경계의 자료형과 검색 맥락은 애플리케이션에서 검증한다.
AND (
created_at = :last_created_at AND post_id < :last_post_id
OR created_at < :last_created_at
)키셋에 다시 페이지 오프셋을 붙인다
경계가 이미 직전 페이지의 마지막 행인데 다시 20건을 건너뛰면 한 페이지를 누락한다. 두 방식의 위치 계산을 중복 적용한 결과다.
WHERE board_id = :board_id
AND status = 'P'
AND (
created_at < :last_created_at
OR (created_at = :last_created_at AND post_id < :last_post_id)
)
ORDER BY created_at DESC, post_id DESC
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY경계 뒤에서 필요한 수만 가져온다. 이전 페이지로 이동해야 한다면 방문한 경계를 보관하거나 비교 방향과 정렬을 뒤집은 조회를 별도로 설계한다.
WHERE board_id = :board_id
AND status = 'P'
AND (
created_at < :last_created_at
OR (created_at = :last_created_at AND post_id < :last_post_id)
)
ORDER BY created_at DESC, post_id DESC
FETCH FIRST 20 ROWS ONLY한눈에 보기
| 판단 항목 | 오프셋 | 키셋 |
|---|---|---|
| 다음 위치의 표현 | 건너뛸 행 수 | 마지막 행의 정렬 키 |
| 뒤쪽 조회 비용 | 대체로 앞선 행 수와 함께 증가 | 효율적인 범위 접근이면 깊이의 영향 감소 |
| 페이지 번호 직접 이동 | 바로 표현 가능 | 해당 위치의 경계가 별도로 필요 |
| 연속 탐색 | 반복해서 앞부분을 처리 | 이전 경계에서 이어서 조회 |
| 정렬 조건 | 고유한 순서 필요 | 고유한 순서와 모든 경계 키 필요 |
| 검증 지표 | 하위 A-Rows와 Buffers 증가량 | 접근 조건과 분기별 A-Rows, Buffers |
| 변경 중 일관성 | 위치 이동으로 중복·누락 가능 | 앞쪽 삽입 영향 감소, 정렬 키 수정은 별도 고려 |
게시판에 적용한 핵심 변경은 다음 요청의 계약이다. 페이지 번호만 받던 요청이 마지막 행의 작성 시각과 글 번호를 받도록 바뀐다. 데이터베이스에서는 그 값을 인덱스 범위로 사용하고, 화면에서는 응답의 마지막 행을 다음 경계로 보관한다. 총 건수 조회는 이 변경으로 빨라지지 않으므로 매 요청에서 꼭 필요한지도 별도로 판단한다.
페이지 크기도 서버에서 제한한다. 경계가 효율적으로 잡혀도 사용자가 매우 큰 반환량을 요청하면 테이블 접근, 전송, 화면 렌더링 비용이 커진다. 기본 20건과 허용 상한을 정하고, 필요한 경우 검색 기간이나 검색어로 탐색 범위를 줄이는 기능을 함께 제공한다.
연습 문제
- 한 페이지에 30건을 반환하는 오프셋 조회가 앞의 90,000건을 건너뛴다. 정렬 순서대로 인덱스를 읽고 충분한 결과가 있다고 할 때 후보 처리량을 추정하라. 이 수를 논리 읽기 수와 같다고 말할 수 없는 이유도 설명하라.
- 동일한 작성 시각의 글 번호가 88, 87, 86이고 다음 시각의 글 번호가 85, 84다. 내림차순 목록에서 직전 페이지의 마지막 글이 87이면 다음 페이지의 처음 세 글과 필요한 경계 조건을 작성하라.
- 완성 코드의 두 분기 제한을 각각 10건으로 낮추고 최종 제한은 20건으로 유지해도 되는지 판단하라. 반례와 함께 설명하라.
- 개선 SQL에서 인덱스 범위 스캔이 보이지만 깊은 경계의 Buffers가 계속 증가한다. 실행계획에서 확인할 두 가지 항목과 애플리케이션에서 확인할 한 가지 항목을 제시하라.
정답과 해설
- 대략 90,030개의 후보를 처리한다. 인덱스 블록 하나에 여러 엔트리가 들어가므로 후보 수와 블록 접근 수는 일치하지 않는다. 테이블 접근 여부와 접근 시점, 행 배치, 필터에서 제거되는 후보도 논리 읽기에 영향을 준다. 실제 수치는 실행 통계로 확인해야 한다.
- 다음 세 글은 86, 85, 84다. 직전 작성 시각을 T라고 하면
created_at < :T OR (created_at = :T AND post_id < 87)을 사용하고 작성 시각과 글 번호를 모두 내림차순으로 정렬한다. 글 번호 조건 없이 시각만 비교하면 86이 누락된다. - 일반적으로 안 된다. 같은 시각에서 남은 글이 0건이고 과거 시각의 글이 충분해도 두 번째 분기에서 10건만 가져오므로 최종 결과는 10건에 그친다. 완성 데이터의 경계에서도 같은 시각 두 건과 과거 시각 열 건을 합쳐 12건만 얻는다. 각 분기는 자신만으로 최종 20건을 채워야 할 가능성이 있으므로 적어도 20건을 허용해야 한다.
- 첫째, 경계가 인덱스 접근 조건인지 필터인지 확인한다. 둘째, 인덱스와 테이블 행 소스의 실제 반환량 및 추가 필터를 살펴보고 적은 결과를 찾기 위해 많은 후보를 읽는지 확인한다. 애플리케이션에서는 이전 응답의 전체 정렬 키를 정확한 자료형으로 전달하는지 확인한다. 경계 조건에 함수를 적용하거나 정밀도를 잃으면 의도한 결과와 접근 경로가 달라질 수 있다.