부분 범위 처리와 Top N

#핵심 요약

부분 범위 처리는 결과를 한 번에 다 보내지 않고 클라이언트가 정한 Array Size만큼 끊어 보내는 것임
클라이언트의 이 요청을 Fetch Call이라고 함

인덱스 순서대로 읽어 정렬이 필요 없으면 Array Size만큼 찰 때마다 바로 보낼 수 있음
SORT ORDER BY가 필요하면 전부 정렬하기 전에는 한 건도 보낼 수 없음

Array Size가 작으면 Fetch Call(네트워크 왕복)이 많아지고, 너무 크면 첫 응답이 늦어짐
화면 조회는 작게, 대량 다운로드, 이관, 배치는 크게 잡음

ROWNUM 페이징은 안쪽에 ROWNUM <= :page * 10 조건이 있어야 COUNT STOPKEY가 생겨 필요한 만큼만 읽고 멈춤
바깥에서만 BETWEEN으로 자르면 결과는 같아도 전체를 읽음

정렬이 필요한 경우에도 SORT ORDER BY STOPKEY가 되면 상위 N건만 유지하며 정렬하므로 정렬 메모리가 크게 줄어듦

#개념과 동작 원리

실습 페이징 패턴의 각 부분:

설명: 뒤쪽 페이지로 갈수록 앞에서부터 읽어야 할 양(:page * 10)이 늘어나지만, 사용자는 대부분 앞쪽 페이지만 보므로 이 패턴이 유리함
실습에서는 12c부터 FETCH FIRST n ROWS ONLY를 쓸 수 있으나 교재가 Top N Stopkey가 이 방식에서 항상 기대대로 작동하지 않을 수 있다고 설명해 실습은 ROWNUM 방식으로 했다고 적었음

#필수 명령어 또는 SQL

#정렬 생략 여부와 부분 범위 처리

CREATE TABLE emp_partial_test AS
SELECT ROWNUM AS empno, e.ename, e.job,
       MOD(ROWNUM, 4500) + 500 AS sal,
       MOD(ROWNUM, 4) * 10 + 10 AS deptno
  FROM emp e, (SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10000);

CREATE UNIQUE INDEX idx_emp_partial_empno ON emp_partial_test (empno);

SELECT * FROM emp_partial_test WHERE empno > 0 ORDER BY empno;   -- SORT ORDER BY 없음: 빠른 첫 응답
SELECT * FROM emp_partial_test WHERE empno > 0 ORDER BY sal;     -- SORT ORDER BY: 14만 건 정렬 후 전송

결과 해석: 개별 실행 출력은 기재 없음

#Array Size와 Fetch Call

SET AUTOTRACE TRACEONLY STATISTICS
SET ARRAYSIZE 10

SELECT * FROM emp_partial_test WHERE empno <= 5000;   -- 결과: 501 SQL*Net roundtrips

결과 해석:

SET ARRAYSIZE 1000으로 다시 실행하면 실습 기록상 6 roundtrips였음(5번의 Fetch와 1번의 끝 확인)

#페이징: 안쪽 ROWNUM 조건

실습 환경: stock_trade 약 11만 건, 종목 KR123456에 5만 건, 인덱스 idx_stocktrade_cd_dt(stock_cd, trade_dt)

-- 튜닝 전(ANTI 패턴): 안쪽 ROWNUM 조건 없음
SELECT *
  FROM (SELECT ROWNUM no, a.*
          FROM (SELECT trade_dt, trade_cnt, trade_qty, trade_amt
                  FROM stock_trade
                 WHERE stock_cd = 'KR123456'
                 ORDER BY trade_dt) a)
 WHERE no BETWEEN 1 AND 10;

-- 튜닝 후
SELECT *
  FROM (SELECT ROWNUM no, a.*
          FROM (SELECT trade_dt, trade_cnt, trade_qty, trade_amt
                  FROM stock_trade
                 WHERE stock_cd = 'KR123456'
                 ORDER BY trade_dt) a
         WHERE ROWNUM <= 10)
 WHERE no BETWEEN 1 AND 10;

결과 해석: 인덱스가 stock_cd, trade_dt 순이라 정렬 없이 읽을 수 있고, COUNT STOPKEY가 있으면 10건째에서 인덱스 스캔과 테이블 방문을 멈춤
실습 튜닝 전 SQL은 바깥 조건을 no BETWEEN (1-1)*10 AND 1*10으로 적어 하한이 0임
결과에는 영향이 없음

#정렬이 필요한 Top N: SORT ORDER BY STOPKEY

실습 환경: 주문상품 약 51만 건, 고객 C0098765에 8420건, 인덱스는 고객번호 단일 컬럼(주문일시 없음)
3페이지(21~30번째)를 조회함

SELECT *
  FROM (SELECT ROWNUM no, a.*
          FROM (SELECT 주문일시, 상품코드, 주문수량, 주문금액
                  FROM 주문상품
                 WHERE 고객번호 = 'C0098765'
                 ORDER BY 주문일시 DESC) a
         WHERE ROWNUM <= 3 * 10)
 WHERE no >= (3 - 1) * 10 + 1;

결과 해석: 정렬 기준 컬럼이 인덱스에 없어 8420건을 모두 읽는 것은 같지만, STOPKEY가 있으면 상위 30건만 유지하며 정렬해 정렬 메모리가 크게 줄었음
실습 AUTOTRACE 통계는 sorts (memory) 1, sorts (disk) 0이었음
실습에서는 이어서 수동 정렬 영역을 512KB로 줄여도 디스크 정렬이 생기지 않는지 확인하게 했음(결과 기재 없음)

ALTER SESSION SET workarea_size_policy = manual;
ALTER SESSION SET sort_area_size = 524288;

결과 해석:

실습의 다른 예(종목거래 100만 건, 인덱스 없음)에서도 안쪽 ROWNUM이 있으면 SORT ORDER BY STOPKEY(Used-Mem 2048), 없으면 SORT ORDER BY(802K)였고, 두 경우 모두 Full Scan이라 Buffers는 약 5245로 같았음
실습에서는 정렬 생략 없이 단순 FETCH FIRST로 쓴 예제71에서 WINDOW SORT PUSHED RANK가 나오는 것도 기록했음

#분석 함수의 Top N 소트

MAX() 분석 함수로 그룹별 최종 이력을 구하면 WINDOW SORT가 대상 전체를 정렬하려 해 Sort Area가 모자라면 디스크 소트가 생길 수 있음
RANK()나 ROW_NUMBER()로 순위를 매기고 바깥에서 순위 1을 고르면 WINDOW SORT PUSHED RANK가 나오며, 전체를 정렬하지 않고 Top N만큼의 공간에서 값을 밀어내 소트 부하를 줄임

-- 튜닝 전
SELECT 장비번호, 변경일자, 변경순번, 상태코드, 메모
  FROM (SELECT 장비번호, 변경일자, 변경순번, 상태코드, 메모,
               MAX(변경순번) OVER (PARTITION BY 장비번호) AS 최종변경순번
          FROM 상태변경이력
         WHERE 변경일자 = '<YYYYMMDD>')
 WHERE 변경순번 = 최종변경순번;

-- 튜닝 뒤
SELECT 장비번호, 변경일자, 변경순번, 상태코드, 메모
  FROM (SELECT 장비번호, 변경일자, 변경순번, 상태코드, 메모,
               RANK() OVER (PARTITION BY 장비번호 ORDER BY 변경순번 DESC) AS rnum
          FROM 상태변경이력
         WHERE 변경일자 = '<YYYYMMDD>')
 WHERE rnum = 1;

결과 해석:

실습 기록(50만 건, 대상 5000건): 튜닝 전 WINDOW SORT, 튜닝 뒤 WINDOW SORT PUSHED RANK로 바뀌었고, 이 작은 예에서는 두 경우 모두 Used-Mem 802K (0)였음

Top N 소트 알고리즘은 PARTITION BY + ORDER BY 순의 결합 인덱스가 없어도(테이블 Full Scan이어도) 작동함
그 순서의 인덱스가 있으면 소트 자체를 생략할 수도 있음

RANK는 같은 순위가 여럿이면 모두 1이 되므로 한 건만 필요하면 ROW_NUMBER를 씀

#배치 I/O와 정렬 보장

12c부터 인덱스로 테이블을 읽을 때 여러 ROWID를 묶어 한꺼번에 읽는 배치 I/O(TABLE ACCESS BY INDEX ROWID BATCHED)가 작동할 수 있음
그러면 인덱스 순서와 다르게 결과가 도착해 순서가 섞일 수 있으므로, 순서가 필요하면 인덱스에 기대지 말고 반드시 ORDER BY를 씀

SELECT /*+ index(a idx_batch_demo) no_batch_table_access_by_rowid(a) */ id, deptno, empname
  FROM batch_demo a
 WHERE deptno = 20
   AND ROWNUM <= 20;

결과 해석:

실습에서는 2만 건을 무작위 순서로 넣어 블록에 흩어 놓고 (deptno, id) 인덱스로 조회했는데, 결과는 기대와 달리 정렬돼 나왔음
순서가 섞이면 ORDER BY를 쓰거나 no_batch_table_access_by_rowid 힌트로 배치 I/O를 끔

DB 업그레이드 뒤 ORDER BY를 넣어야 할 SQL이 너무 많으면 DBA가 숨은 파라미터 _optimizer_batch_table_access_by_rowid를 FALSE로 바꿔 시스템 수준에서 끄는 방법을 실습에서 보였음
실습에서는 확인 뒤 다시 TRUE로 되돌렸음

OLTP(주문, 결제, 송금처럼 짧고 잦은 트랜잭션, 빠른 응답과 ACID가 중요)와 OLAP(대용량 스캔, 집계, 긴 쿼리 허용)의 차이도 수업 자료에 함께 정리돼 있음

연습 문제: emp(job) 인덱스로 job = 'SALESMAN' ORDER BY sal을 실행하면 SORT ORDER BY가 나옴
(job, sal) 인덱스로 바꾸면 사라짐
같은 방식으로 deptno = 20 ORDER BY sal DESC의 정렬을 없애 봄(실습 답: (deptno, sal) 인덱스, INDEX RANGE SCAN DESCENDING)