소트 튜닝 - 정렬 생략과 Top-N 최적화

소트 튜닝 - 정렬 생략과 Top-N 최적화

이 장에서 배우는 것

온라인 서점의 운영 화면에는 정렬이 자주 등장한다. 최근 주문을 위에 놓고, 판매액이 큰 상품을 먼저 보여 주고, 같은 시각에 접수된 주문의 순서를 고정한다. 결과가 스무 줄뿐이어도 그 스무 줄을 고르기 위해 수만 줄을 읽고 비교할 수 있다. 소트 튜닝의 출발점은 결과 건수와 처리 건수를 구분하는 데 있다.

앞 장에서 쿼리 변환에 따라 조건이 적용되는 위치가 달라지는 모습을 살펴보았다. 여기서는 최근 주문 화면 하나를 대상으로 정렬에 들어가는 행 수와 읽기를 멈추는 위치를 추적한다. 기본서에서 익힌 인덱스 구조와 실행계획 읽기를 전제로, 정렬을 없앨 수 있는 조건과 정렬을 유지해야 하는 조건을 나누어 판단한다.

• 인덱스의 열 순서와 정렬 방향을 이용해 ORDER BY를 위한 별도 정렬을 생략한다.
• 상위 N건 조회(Top-N)에서 STOPKEY가 제한하는 대상과 실제 입력 처리량을 구분한다.
• GROUP BY의 집계 방식과 최종 결과의 출력 순서를 구분한다.
• 메모리 정렬과 디스크 정렬을 실행 통계로 확인하고 논리 읽기와 함께 평가한다.

문제 상황

서점 운영자는 판매 채널별로 최근 주문 스무 건을 확인한다. 주문은 계속 쌓이는데 화면에는 주문번호와 결제금액만 필요하다. 개발 당시에는 해당 채널의 주문을 모두 읽어 최신순으로 정렬해도 응답이 빨랐다. 데이터가 늘자 화면을 새로 고칠 때마다 지연이 나타났고, 여러 운영자가 동시에 조회하면 응답시간의 변동도 커졌다.

기존 테이블에는 주문번호 기본 키만 있다. 검색 조건은 판매 채널 번호이며 정렬 기준은 주문 접수 시각 내림차순, 주문번호 내림차순이다. 접수 시각이 같을 때도 표시 순서를 일정하게 유지하려고 주문번호를 보조 기준으로 사용한다. 접수 시각은 NULL을 허용하지 않는다.

실습에서는 주문 십만 건을 만들고 열 개 채널에 균등하게 나눈다. 채널 하나에 해당하는 주문은 만 건이다. 화면이 요구하는 스무 건과 정렬의 입력이 되는 만 건 사이의 차이가 이번 사례의 핵심이다. 테이블에는 화면에 표시하지 않는 메모 열도 두어 테이블 전체 읽기의 부담을 드러낸다.

실습 데이터는 원리를 재현하기 위한 작은 모형이다. 운영 데이터에서 채널별 주문 수, 행 길이, 저장 상태가 달라지면 읽기 수치도 달라진다. 뒤의 비교 수치는 설명용 가정값이며 실제 측정 결과로 제시하지 않는다. 완성 코드는 실행한 데이터베이스의 실제 실행계획과 논리 읽기를 로그에 남기도록 구성한다.

인덱스 순서가 정렬을 대신하는 조건

정렬을 생략하려면 선택한 접근 경로가 필요한 순서로 행을 공급해야 한다. 이번 화면에는 판매 채널, 접수 시각 내림차순, 주문번호 내림차순으로 구성한 인덱스가 맞는다. 채널 번호를 등호로 고정하면 해당 구간 내부에는 나머지 두 열의 순서가 그대로 남는다. 구간의 앞에서 읽기 시작해 스무 건을 얻으면 뒤쪽 주문을 읽을 필요가 없다.

CREATE INDEX st7_orders_recent_ix
    ON st7_orders (store_id, ordered_at DESC, order_id DESC);

인덱스에 정렬 열이 들어 있다는 사실만으로 충분하지는 않다. 선두 열의 조건, 여러 열의 정렬 방향, NULL 배치, 표현식이 함께 맞아야 한다. 이번 실습은 채널 하나만 조회하고 정렬 열을 모두 NOT NULL로 정의해 이 조건을 단순하게 만든다. 여러 채널을 IN 조건으로 조회하면 채널별 인덱스 구간의 순서가 전체 주문의 최신순과 같지 않으므로 별도 정렬이 필요할 수 있다.

오름차순으로 구성한 복합 인덱스도 역방향으로 읽으면 모든 정렬 열의 방향을 함께 뒤집을 수 있다. 그러나 접수 시각은 내림차순이고 주문번호는 오름차순인 혼합 정렬이라면 두 열의 방향을 함께 뒤집는 것만으로 해결되지 않는다. 실제 ORDER BY와 인덱스가 제공하는 순서를 열별로 비교해야 한다.

실습 인덱스에는 결제금액이 없다. 따라서 스무 건의 금액을 가져오기 위한 테이블 접근은 남는다. 이번 변경의 목적은 정렬 후보 만 건을 끝까지 읽는 경로를, 필요한 순서로 읽다가 스무 건에서 멈추는 경로로 바꾸는 것이다. 최종 실행계획에서 정렬 연산이 사라졌는지와 테이블 접근 건수가 줄었는지를 함께 확인한다.

STOPKEY가 줄이는 것과 줄이지 못하는 것

Oracle의 ROWNUM은 같은 쿼리 블록에서 ORDER BY보다 앞서 적용된다. 따라서 최신 스무 건을 구하려면 내부 쿼리에서 순서를 정의하고 외부 쿼리에서 ROWNUM으로 건수를 제한해야 한다. 이 형태에서 옵티마이저는 정렬과 건수 제한을 결합하거나, 순서가 맞는 인덱스에 건수 제한을 적용할 수 있다.

SELECT order_id, amount
FROM (
    SELECT order_id, amount
    FROM st7_orders
    WHERE store_id = 1
    ORDER BY ordered_at DESC, order_id DESC
)
WHERE ROWNUM <= 20;

SORT ORDER BY STOPKEY는 상위 스무 건을 얻기 위한 정렬이다. 전체 결과를 완전히 정렬해 보관하는 방식보다 작업 메모리를 줄일 여지가 있다. 그러나 입력이 정렬되어 있지 않으면 마지막 입력 행이 가장 최근 주문일 수도 있다. 이 경우 조건에 맞는 만 건을 모두 살펴야 한다. STOPKEY라는 이름이 보인다는 이유만으로 테이블 읽기가 스무 건에 그쳤다고 해석하면 안 된다.

반면 최신순 인덱스에서는 처음 받은 스무 건이 원하는 결과다. 위쪽의 COUNT STOPKEY가 필요한 건수를 받으면 아래쪽 접근 경로에 더 이상 행을 요구하지 않는다. 이 연산의 COUNT는 SQL의 COUNT(*) 집계를 뜻하지 않는다. 행 수 제한을 수행하는 실행계획 연산이다.

Oracle 19c의 FETCH FIRST 20 ROWS ONLY로 같은 요구를 표현할 수도 있다. 이때는 변환과 접근 경로에 따라 WINDOW SORT PUSHED RANK 또는 WINDOW NOSORT STOPKEY 같은 연산이 나타날 수 있다. 구문에 익숙한 연산 이름이 보이는지를 확인하는 데서 멈추지 않고, 정렬 유무와 하위 연산의 실제 반환 행 수를 확인해야 한다.

추가 필터가 테이블에만 있으면 이야기가 달라진다. 최신순으로 읽은 주문 중 화면 조건에 맞지 않는 행을 버려야 한다면 스무 건을 반환하기 위해 인덱스와 테이블에서 더 많은 행을 읽을 수 있다. 정렬 생략과 조기 종료는 관련이 있지만 같은 보장은 아니다. 이번 실습에는 이러한 추가 필터를 두지 않는다.

집계 정렬과 작업 메모리를 구분한다

GROUP BY는 그룹별 값을 계산하는 요구이며 결과의 표시 순서를 정하는 요구가 아니다. Oracle은 입력 특성과 비용에 따라 HASH GROUP BY나 SORT GROUP BY 등을 선택한다. 인덱스가 그룹 순서로 입력을 공급하면 SORT GROUP BY NOSORT가 나타날 수도 있다. 연산 이름에 SORT가 포함되어 있어도 NOSORT 여부를 함께 읽어야 한다.

판매 채널별 주문 수를 채널 번호순으로 표시하려면 ORDER BY store_id를 명시한다. SORT GROUP BY가 필요한 순서까지 제공하면 별도 정렬을 추가하지 않을 수 있다. HASH GROUP BY를 선택하면 집계 결과를 정렬하는 연산이 추가될 수 있다. GROUP BY의 출력이 우연히 정렬되어 보인다는 이유로 ORDER BY를 삭제하지 않는다.

SELECT store_id, COUNT(*) AS order_count
FROM st7_orders
GROUP BY store_id
ORDER BY store_id;

집계 뒤의 상위 N건은 원본 주문의 상위 N건과 다르다. 채널별 매출 상위 세 곳을 구하려면 각 채널의 매출 합계를 알아야 한다. 원본 주문을 세 건만 읽고 멈추면 집계 대상 자체가 달라진다. 일반적인 실행에서는 원본 주문을 집계한 뒤 열 개 채널의 합계를 정렬하고 세 그룹을 선택한다. 최종 정렬이 작아도 원본 읽기는 클 수 있다.

정렬과 해시 집계는 작업 영역(work area)을 사용한다. 전용 서버 구성에서는 이 작업 영역이 보통 서버 프로세스의 PGA에 놓인다. 처리량에 비해 할당된 메모리가 충분하면 임시 영역을 이용한 병합 과정 없이 끝난다. 부족하면 중간 데이터를 TEMP에 기록하고 다시 읽는다. 한 차례의 외부 처리로 끝나는 경우와 여러 차례 반복하는 경우를 나누어 볼 수 있다.

자동 메모리 관리 아래에서는 같은 SQL도 동시 실행 수와 다른 작업의 메모리 수요에 따라 할당량이 달라질 수 있다. PGA_AGGREGATE_TARGET은 개별 정렬에 전부 주어지는 메모리 크기가 아니다. 이번 사례의 스무 건 제한 정렬은 메모리에서 끝날 가능성이 높으며, 십만 건 데이터만으로 디스크 정렬을 재현한다고 단정하지 않는다.

DBMS_XPLAN의 메모리 통계에서는 OMem, 1Mem, Used-Mem과 나타나는 경우의 Used-Tmp를 살펴본다. 정확한 사용 이력은 V$SQL_WORKAREA의 LAST_EXECUTION, LAST_MEMORY_USED, LAST_TEMPSEG_SIZE 등으로 보완한다. Used-Tmp가 표시되지 않았다는 사실 하나만으로 모든 실행에서 TEMP를 쓰지 않는다고 결론 내리지 않는다.

논리 읽기는 버퍼에서 블록을 읽은 횟수이며 정렬 비교 횟수나 TEMP 사용량을 직접 나타내지 않는다. 메모리를 늘려 디스크 정렬이 사라져도 테이블에서 읽는 블록 수는 그대로일 수 있다. 반대로 정렬 순서에 맞는 인덱스로 조기 종료하면 입력 읽기와 정렬 작업을 함께 줄일 수 있다. 두 개선을 같은 효과로 기록하지 않는다.

Oracle 19c와 MySQL 8에서 같은 요구를 확인하는 방법

항목	Oracle 19c	MySQL 8	판단 기준
상위 건수 제한	ROWNUM 또는 FETCH FIRST	LIMIT	제한 아래의 입력 처리량을 확인한다.
정렬 생략	순서에 맞는 인덱스 접근과 정렬 연산 부재를 확인한다.	인덱스 사용과 filesort 유무를 확인한다.	검색 조건과 정렬 방향을 함께 비교한다.
집계 결과 순서	GROUP BY만으로 보장하지 않는다.	GROUP BY만으로 보장하지 않는다.	표시 순서는 ORDER BY로 지정한다.
실제 실행 확인	DBMS_XPLAN의 A-Rows, Buffers와 작업 영역 통계	8.0.18 이상에서 EXPLAIN ANALYZE의 실제 행 수와 시간	MySQL 출력에는 Oracle Buffers와 같은 지표가 직접 제공되지 않는다.
디스크 정렬	작업 영역과 TEMP 사용 통계를 확인한다.	filesort는 메모리에서도 수행될 수 있다.	정렬 연산의 존재와 디스크 사용을 구분한다.

사실 확인에는 Oracle의 ROWNUM 설명, DBMS_XPLAN 참조, V$SQL_WORKAREA 참조, MySQL의 ORDER BY 최적화 설명을 이용할 수 있다. 아래 데이터와 코드는 이 사례를 위해 새로 구성한 것이다.

완성 코드

이 프로그램은 SQL*Plus에서 실행하는 Oracle SQL·PL/SQL 스크립트다. macOS 또는 Linux에 SQL*Plus 클라이언트를 준비하고 Oracle 19c 데이터베이스에 접속한다. macOS에서는 접속 대상 데이터베이스를 별도 서버에 두어도 된다. 서버의 운영체제와 클라이언트의 운영체제를 같게 맞출 필요는 없다.

실습 계정에는 테이블·인덱스 생성 권한과 테이블스페이스 할당량이 필요하다. 자기 테이블의 통계를 수집할 수 있어야 하며 DBMS_XPLAN.DISPLAY_CURSOR가 이용하는 V$SQL, V$SQL_PLAN, V$SESSION, V$SQL_PLAN_STATISTICS_ALL의 조회 권한도 필요하다. 이미 ST7_ORDERS가 존재하면 생성 단계에서 중단하므로 새 실습 스키마에서 실행한다.

파일 전체를 sort_lab.sql로 저장한다. 저장 프로시저나 외부 라이브러리를 만들지 않으며 익명 PL/SQL 블록으로 통계만 수집한다. SQL 또는 운영체제 오류가 발생하면 성공 문구를 출력하기 전에 종료한다. 이 원고에서는 실제 Oracle 인스턴스에서 컴파일·실행 검증을 수행하지 않았으므로 환경별 실행 성공을 측정 사실로 주장하지 않는다.

sort_lab.sql

WHENEVER OSERROR EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK

SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 220
SET TRIMSPOOL ON
SET TAB OFF
SET SERVEROUTPUT OFF
SET AUTOTRACE OFF
SET TIMING OFF
SET TERMOUT OFF

CREATE TABLE st7_orders (
    order_id   NUMBER(10)    NOT NULL,
    store_id   NUMBER(2)     NOT NULL,
    ordered_at DATE          NOT NULL,
    amount     NUMBER(10)    NOT NULL,
    memo       VARCHAR2(200) NOT NULL,
    CONSTRAINT st7_orders_pk PRIMARY KEY (order_id)
);

INSERT INTO st7_orders (
    order_id, store_id, ordered_at, amount, memo
)
SELECT
    LEVEL,
    MOD(LEVEL, 10) + 1,
    DATE '2025-01-01' + LEVEL / 86400,
    10000 + MOD(LEVEL, 100) * 100,
    RPAD('x', 200, 'x')
FROM dual
CONNECT BY LEVEL <= 100000;

COMMIT;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => USER,
        tabname          => 'ST7_ORDERS',
        estimate_percent => 100,
        method_opt       => 'FOR ALL COLUMNS SIZE 1',
        cascade          => TRUE
    );
END;
/

COLUMN order_id FORMAT 999999
COLUMN amount FORMAT 999999
COLUMN plan_table_output FORMAT A220

SPOOL sort_before.log REPLACE

SELECT /*+ GATHER_PLAN_STATISTICS NO_PARALLEL */
       /* st7_before */ order_id, amount
FROM (
    SELECT /*+ FULL(o) NO_PARALLEL(o) */
           o.order_id, o.ordered_at, o.amount
    FROM st7_orders o
    WHERE o.store_id = 1
    ORDER BY o.ordered_at DESC, o.order_id DESC
)
WHERE ROWNUM <= 20;

SELECT plan_table_output
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL, NULL,
        'ALLSTATS LAST +MEMSTATS -PROJECTION -ALIAS'
    )
);

SPOOL OFF

CREATE INDEX st7_orders_recent_ix
    ON st7_orders (store_id, ordered_at DESC, order_id DESC);

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => USER,
        tabname          => 'ST7_ORDERS',
        estimate_percent => 100,
        method_opt       => 'FOR ALL COLUMNS SIZE 1',
        cascade          => TRUE
    );
END;
/

SPOOL sort_after.log REPLACE

SELECT /*+ GATHER_PLAN_STATISTICS NO_PARALLEL */
       /* st7_after */ order_id, amount
FROM (
    SELECT /*+ INDEX(o st7_orders_recent_ix)
               NO_BATCH_TABLE_ACCESS_BY_ROWID(o)
               NO_PARALLEL(o) */
           o.order_id, o.ordered_at, o.amount
    FROM st7_orders o
    WHERE o.store_id = 1
    ORDER BY o.ordered_at DESC, o.order_id DESC
)
WHERE ROWNUM <= 20;

SELECT plan_table_output
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL, NULL,
        'ALLSTATS LAST +MEMSTATS -PROJECTION -ALIAS'
    )
);

SPOOL OFF
SET TERMOUT ON

PROMPT BEFORE_LOG=sort_before.log
PROMPT AFTER_LOG=sort_after.log
PROMPT DONE

EXIT SUCCESS

줄별 해설

첫 두 줄은 실패를 숨기지 않기 위한 실행 제어다. DDL은 암묵적으로 커밋되므로 종료 시 ROLLBACK이 앞서 생성한 테이블까지 제거하지는 않는다. 이어지는 SET 명령은 화면 출력을 일정하게 하고, 실행계획을 로그로 모으는 동안 터미널 출력을 감춘다. TERMOUT OFF는 @로 실행하는 스크립트의 화면 출력을 제어하며 SPOOL 파일 기록은 계속된다.

CREATE TABLE은 정렬 조건을 재현할 다섯 열을 정의한다. 정렬 열에 NOT NULL을 지정했으므로 NULL을 앞이나 뒤에 놓는 규칙이 실험에 개입하지 않는다. 기본 키 인덱스는 채널과 접수 시각 순서를 제공하지 않는다. 화면에서 사용하지 않는 memo 열은 각 행의 크기를 늘리지만 정렬 결과에 반환되지는 않는다.

INSERT의 LEVEL은 1부터 100000까지 증가한다. MOD(LEVEL, 10) + 1은 채널을 1부터 10까지 반복한다. 채널 1의 주문번호는 10의 배수다. 접수 시각도 주문번호와 함께 증가하므로 채널 1의 최신 주문번호는 100000이며 다음은 99990이다. 날짜 리터럴을 사용하므로 세션의 날짜 입력 형식에 의존하지 않는다.

첫 통계 수집은 데이터 적재 후의 행 수와 열 분포를 옵티마이저에 알린다. SIZE 1은 이 실험에서 히스토그램을 만들지 않도록 지정한다. 실무에서 언제나 히스토그램을 배제하라는 뜻은 아니다. 여기서는 분포보다 접근 순서의 차이를 분리해 관찰한다.

변경 전 SELECT의 FULL 힌트는 비교할 전체 스캔 경로를 지정한다. 외부의 GATHER_PLAN_STATISTICS는 이 실행의 행 원천 통계를 수집한다. 내부 ORDER BY는 결과의 기준을 정하고 외부 ROWNUM은 스무 건을 제한한다. SQL*Plus가 결과를 모두 가져온 직후 DISPLAY_CURSOR를 실행해 방금 수행한 문장의 실제 통계를 출력한다.

두 번째 인덱스를 만든 뒤 통계를 다시 수집한다. 변경 후의 INDEX 힌트는 새 인덱스 경로를 비교 대상으로 지정한다. NO_BATCH_TABLE_ACCESS_BY_ROWID는 이 실험에서 테이블 접근 형태를 단순하게 유지하려는 지정이다. 힌트는 학습용 비교 경로를 고정하기 위한 것이므로 운영 반영 시에는 힌트를 제거한 실행계획도 확인해야 한다.

마지막 PROMPT 세 줄만 터미널에 출력된다. 두 로그에는 각각 스무 건의 조회 결과와 실행계획이 들어 있다. 실제 행 수와 읽기를 얻으려면 EXPLAIN PLAN만 실행해서는 부족하다. 또한 조회 직후 다른 SQL을 실행하고 DISPLAY_CURSOR에 NULL을 넘기면 의도와 다른 최근 문장을 볼 수 있으므로 이 스크립트의 호출 순서를 유지한다.

실행 결과

접속 문자열은 비밀번호를 명령행에 직접 적지 않도록 이미 설정된 Oracle Wallet 별칭 등을 사용한다. 다음은 BOOK19C라는 별칭이 준비된 환경의 실행 명령이다.

sqlplus -s /@BOOK19C @sort_lab.sql

별도의 로그인 스크립트가 출력이나 설정을 추가하지 않고 정상 실행되었다면 터미널에는 다음 세 줄이 출력된다. 실행계획의 환경별 차이는 로그에 남으므로 이 고정 출력에는 섞이지 않는다.

BEFORE_LOG=sort_before.log
AFTER_LOG=sort_after.log
DONE

두 로그의 조회 결과는 같아야 한다. 아래는 여백을 제외한 값의 확인 기준이다. 첫 주문번호가 100000이고 이후 10씩 감소하며, 스무 번째 주문번호는 99810이다. 금액은 데이터 생성식에 따라 열 줄 단위로 반복된다.

order_id  amount
100000    10000
99990     19000
99980     18000
99970     17000
99960     16000
99950     15000
99940     14000
99930     13000
99920     12000
99910     11000
99900     10000
99890     19000
99880     18000
99870     17000
99860     16000
99850     15000
99840     14000
99830     13000
99820     12000
99810     11000

다음은 비교할 실행계획의 대표 형태를 간추린 것이다. 원문 로그의 출력 형식이나 실제 측정값을 재현한 것이 아니다. A-Rows는 해당 연산이 위쪽으로 반환한 실제 행 수다. 전체 스캔의 10000은 채널 조건을 통과한 행 수이며, 테이블에서 검사한 전체 행 수가 10000이라는 뜻은 아니다.

변경 전 대표 형태
Operation                         A-Rows
SELECT STATEMENT                      20
  COUNT STOPKEY                       20
    VIEW                              20
      SORT ORDER BY STOPKEY           20
        TABLE ACCESS FULL          10000

변경 후 대표 형태
Operation                         A-Rows
SELECT STATEMENT                      20
  COUNT STOPKEY                       20
    VIEW                              20
      TABLE ACCESS BY INDEX ROWID     20
        INDEX RANGE SCAN              20

변경 전에는 정렬이 스무 건만 반환해도 그 아래에서 만 건이 올라온다. 변경 후에는 정렬 연산 없이 인덱스와 테이블 접근이 필요한 스무 건을 공급한다. 실제 계획에 정렬이 남았거나 인덱스 입력이 예상보다 크다면 힌트 적용 여부, 조건, 열 방향을 확인한다. 필요하면 DISPLAY_CURSOR의 형식에 HINT_REPORT를 추가해 힌트 사용 상태를 살펴본다.

정렬 경로 변경을 평가하는 예시 수치이며 실제 측정값은 각 로그에서 읽는다

비교 항목	변경 전	변경 후	해석
최종 반환 행 수	20	20	화면의 요구는 같다.
정렬에 공급하는 행 수	10000	별도 정렬 없음	정렬 입력 자체를 없앤다.
문장 전체 Buffers 가정값	3204	44	가정값 기준 약 98.6% 감소다.
TEMP 사용	측정 필요	해당 정렬 작업 없음	정렬이 있었다고 디스크 사용을 단정하지 않는다.

Buffers는 문장 전체를 대표하는 최상위 통계로 비교하고, 부모와 자식 행의 값을 모두 합산하지 않는다. 상위 연산에 하위 작업이 포함된 누적 통계가 나타날 수 있기 때문이다. 실제 감소율은 각 로그에서 얻은 값으로 다시 계산한다. 블록 크기, 데이터 저장 상태, 클라이언트의 가져오기 방식에 따라 가정값과 다른 수치가 나오는 것이 자연스럽다.

응답시간은 캐시 상태의 영향도 받는다. 인덱스 생성과 통계 수집 직후의 실행은 캐시가 데워진 상태일 수 있으므로 이 한 번의 실행으로 시간 개선율까지 확정하지 않는다. 운영 판단에는 같은 바인드 값과 같은 동시 실행 조건에서 반복 측정한 시간, 논리 읽기, TEMP 사용을 함께 기록한다. 이번 사례에서 먼저 확인할 증거는 정렬 제거와 하위 접근량 감소다.

실무에서 자주 틀리는 것

ROWNUM과 ORDER BY를 같은 블록에 둔다

다음 문장은 먼저 선택된 스무 건을 정렬한다. 최신 스무 건을 고른다는 요구와 다르다.

SELECT order_id, amount
FROM st7_orders
WHERE store_id = 1
  AND ROWNUM <= 20
ORDER BY ordered_at DESC, order_id DESC;

정렬 기준을 내부에 두고 건수 제한을 외부에 두면 선택 범위가 명확해진다.

SELECT order_id, amount
FROM (
    SELECT order_id, amount
    FROM st7_orders
    WHERE store_id = 1
    ORDER BY ordered_at DESC, order_id DESC
)
WHERE ROWNUM <= 20;

GROUP BY의 출력 순서를 믿는다

다음 문장은 채널별 주문 수를 계산하지만 채널 번호순 표시를 보장하지 않는다. 실행계획이 바뀌면 관찰되는 순서도 달라질 수 있다.

SELECT store_id, COUNT(*) AS order_count
FROM st7_orders
GROUP BY store_id;

화면에서 필요한 순서를 SQL에 명시한다. 집계 과정에서 얻은 순서를 재사용할지는 옵티마이저가 판단한다.

SELECT store_id, COUNT(*) AS order_count
FROM st7_orders
GROUP BY store_id
ORDER BY store_id;

집계할 원본을 먼저 잘라 버린다

채널별 총매출 상위 세 곳을 구하면서 원본 주문을 세 건으로 제한하면 일부 주문의 합계만 계산한다. 정렬 비용을 줄인 것이 아니라 질문을 바꾼 셈이다.

SELECT store_id, SUM(amount) AS total_amount
FROM st7_orders
WHERE ROWNUM <= 3
GROUP BY store_id
ORDER BY total_amount DESC;

전체 집계를 마친 결과를 정렬하고 세 그룹을 선택한다. 동률이면 채널 번호로 순서를 결정한다.

SELECT store_id, total_amount
FROM (
    SELECT store_id, SUM(amount) AS total_amount
    FROM st7_orders
    GROUP BY store_id
    ORDER BY total_amount DESC, store_id
)
WHERE ROWNUM <= 3;

같은 시각의 주문 순서를 남겨 둔다

접수 시각만으로 정렬하면 같은 시각의 주문이 제한 경계에 걸릴 때 어떤 주문이 포함될지 일정하지 않을 수 있다. 다음 문장은 최신 시각을 기준으로는 맞지만 스무 건의 구성까지 고정하지는 않는다.

SELECT order_id, amount
FROM st7_orders
WHERE store_id = 1
ORDER BY ordered_at DESC
FETCH FIRST 20 ROWS ONLY;

고유한 주문번호를 마지막 정렬 기준에 추가한다. 실습에서는 초마다 한 건을 생성하지만 운영에서는 같은 시각에 여러 주문이 들어오는 상황을 고려해야 한다.

SELECT order_id, amount
FROM st7_orders
WHERE store_id = 1
ORDER BY ordered_at DESC, order_id DESC
FETCH FIRST 20 ROWS ONLY;

한눈에 보기

실행계획의 정렬 관련 신호와 다음 확인 항목

관찰한 신호	의미	다음 확인
SORT ORDER BY	별도 정렬로 출력 순서를 만든다.	정렬 열과 인덱스 순서가 맞는지 본다.
SORT ORDER BY STOPKEY	상위 건수 제한을 반영한 정렬이다.	자식의 A-Rows가 얼마나 큰지 본다.
정렬 없는 접근과 STOPKEY	필요한 순서로 읽다가 멈출 수 있다.	추가 필터 때문에 읽기가 늘지 않는지 본다.
HASH GROUP BY	해시 방식으로 그룹을 집계한다.	출력 순서가 필요하면 ORDER BY를 둔다.
SORT GROUP BY NOSORT	입력 순서를 집계에 이용한다.	출력 순서 요구는 SQL에 따로 명시한다.
작업 영역의 TEMP 사용	중간 작업이 메모리 밖으로 나갔다.	입력량과 동시 실행 수부터 확인한다.

최근 주문 화면은 반환 건수가 작고 정렬 기준이 일정하므로 순서에 맞는 인덱스의 효과를 얻기 쉽다. 반면 집계 순위는 합계를 계산하기 전까지 상위 그룹을 알 수 없어 원본 처리량이 남는다. 다음 장에서는 필요한 건수만 가져오는 화면에서도 앞부분을 건너뛰는 방식에 따라 읽기가 다시 커지는 문제를 살펴본다.

연습 문제

1. 변경 전 계획에서 SORT ORDER BY STOPKEY의 A-Rows가 20이고 TABLE ACCESS FULL의 A-Rows가 10000이다. 테이블을 스무 행만 읽었다고 볼 수 없는 이유를 설명하라.
2. 새 인덱스를 그대로 두고 WHERE store_id = 1을 WHERE store_id IN (1, 2)로 바꾸었다. 전체 결과의 최신순 정렬이 계속 생략된다고 보장할 수 있는지 설명하라.
3. 채널별 총매출 상위 세 곳을 조회하는 SQL에서 마지막 정렬이 메모리에서 끝났다. 원본 테이블의 논리 읽기도 작다고 결론 내릴 수 있는지 설명하라.
4. 반복 측정에서 문장 전체 Buffers가 4200에서 84로 줄었고 두 실행 모두 TEMP를 사용하지 않았다. 논리 읽기 감소율을 계산하고, 이 개선을 디스크 정렬 제거라고 부를 수 있는지 설명하라.

정답과 해설

1. 정렬 연산은 위쪽으로 스무 건을 반환했지만 아래쪽에서 조건을 통과한 만 건을 받았다. 순서 없는 입력에서는 마지막 행까지 확인해야 상위 스무 건을 결정할 수 있다. 또한 전체 스캔의 A-Rows는 필터 통과 행 수이므로 검사한 전체 행 수와도 구분해야 한다.
2. 보장할 수 없다. 인덱스에는 채널 1의 최신순 구간과 채널 2의 최신순 구간이 따로 존재한다. 각 구간이 정렬되어 있어도 두 구간을 단순히 이어 붙인 결과는 전체 최신순이 아니다. 실제 계획에서 별도 정렬이나 순서를 결합하는 처리가 있는지 확인해야 한다.
3. 결론 내릴 수 없다. 열 개 채널의 집계 결과를 정렬하는 작업은 작아도, 그 합계를 만들기 위해 원본 주문을 대량으로 읽을 수 있다. 원본 접근의 Buffers와 집계 입력 행 수를 별도로 확인해야 한다.
4. 감소율은 (4200 - 84) / 4200 × 100으로 98%다. 두 실행 모두 TEMP를 쓰지 않았으므로 디스크 정렬 제거라는 설명은 맞지 않는다. 계획에서 정렬 제거와 조기 종료가 확인된다면 필요한 순서의 접근 경로를 통해 입력 읽기를 줄인 효과라고 설명한다.