Top-N 처리 원리: ROWNUM·FETCH FIRST·STOPKEY

Top-N
= 전체 후보에 업무 정렬 기준을 적용
+ 정렬 기준의 앞쪽 N건을 선택

최종 결과 Row = N건

하지만 실제 작업량은
- 후보를 몇 행 읽었는가?
- 정렬 Key를 몇 행 계산했는가?
- Sort Workarea·TEMP를 얼마나 사용했는가?
- Index·Table Block을 얼마나 읽었는가?
- N건을 찾은 뒤 하위 처리를 실제로 멈췄는가?
로 결정

입력이 이미 필요한 순서
→ 앞에서 N건 확보
→ 하위 Row Source 요청 중단 가능
→ COUNT STOPKEY·WINDOW NOSORT STOPKEY 등

입력이 필요한 순서가 아님
→ 후보를 읽으면서 상위 N개 유지
→ SORT ORDER BY STOPKEY
→ 입력 전체를 읽을 수도 있음

전체 정렬 후 외부에서 제한
→ 불필요한 Full Sort가 발생할 수 있음

1. 전체 후보에서 주문금액 기준 순서를 결정
2. 그 순서의 앞쪽 10건 선택

SELECT order_id,
       order_amount
FROM   orders
WHERE  ROWNUM <= 10
ORDER BY order_amount DESC;

Access Path에서 먼저 발견된 10건 선택
→ 선택된 10건만 금액순 정렬

임의 10건을 정렬
≠ 전체 후보의 상위 10건

첫 번째 선택 Row → ROWNUM 1
두 번째 선택 Row → ROWNUM 2
...

WHERE ROWNUM <= 10
ORDER BY ...

SELECT order_id,
       order_amount
FROM (
    SELECT order_id,
           order_amount
    FROM   orders
    ORDER BY order_amount DESC,
             order_id DESC
)
WHERE ROWNUM <= 10;

안쪽 Query Block
→ 전체 후보의 결정적 순서 정의

바깥 Query Block
→ 정렬 결과의 앞쪽 10건 제한

WHERE ROWNUM > 1

SELECT order_id,
       order_amount
FROM   orders
ORDER BY order_amount DESC,
         order_id DESC
FETCH FIRST 10 ROWS ONLY;

ORDER BY로 순서를 정의
→ 앞쪽 10행 반환

ORDER BY order_amount DESC,
         order_id DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

FETCH FIRST 10 PERCENT ROWS ONLY;

FETCH FIRST 10 ROWS ONLY

ORDER BY score DESC
FETCH FIRST 3 ROWS WITH TIES;

정렬 점수: 100, 95, 90, 90, 90, 80

FETCH FIRST 3 ROWS WITH TIES
→ 100, 95, 90, 90, 90
→ 5행 반환

SELECT student_id,
       score
FROM   exam_result
ORDER BY score DESC
FETCH FIRST 10 ROWS ONLY;

ORDER BY score DESC,
         student_id ASC
FETCH FIRST 10 ROWS ONLY;

동점 중 어떤 Row가 포함되는가?
→ Unique Tie-Breaker가 결정

ORDER BY score DESC
FETCH FIRST 3 ROWS WITH TIES;

ORDER BY score DESC,
         student_id ASC
FETCH FIRST 3 ROWS WITH TIES;

COUNT STOPKEY
  TABLE ACCESS BY INDEX ROWID ORDERS
    INDEX RANGE SCAN DESCENDING ORDERS_AMT_IX

Index에서 가장 큰 Key부터 순서대로 읽음
→ Table Row 조회
→ 조건을 만족한 결과 N건 확보
→ 하위 Row 요청 중단

SORT ORDER BY STOPKEY
  TABLE ACCESS FULL ORDERS

모든 후보를 Sort Workarea·TEMP Run에 정렬
→ 전체 정렬 결과 생성

후보를 읽으면서 현재 상위 N개 중심으로 유지
→ 전체 Row를 모두 Memory에 보관하지 않을 수 있음

SORT ORDER BY STOPKEY A-Rows = 10
TABLE ACCESS FULL A-Rows     = 50,000,000

출력은 10행
하지만 상위값 확정을 위해
하위 후보 5천만 행을 읽음

SELECT order_id,
       customer_id,
       order_date
FROM   orders
WHERE  customer_id = :customer_id
ORDER BY order_date DESC,
         order_id DESC
FETCH FIRST 20 ROWS ONLY;

CREATE INDEX orders_cust_date_ix
ON orders(
    customer_id,
    order_date DESC,
    order_id DESC
);

customer_id
→ Equality로 한 고객의 Index 범위 고정

order_date DESC, order_id DESC
→ 해당 범위 안에서 요청한 순서 제공

FETCH FIRST 20
→ 앞쪽 Match 20건 확보 후 중단 가능

INDEX FULL SCAN
→ Index Key 순서
→ Sort 생략 가능성

INDEX FAST FULL SCAN
→ 정렬되지 않은 Block 순서
→ Sort 생략용 순서 제공 불가

(customer_id, order_date DESC, order_id DESC)

customer 10의 날짜순 구간
→ customer 20의 날짜순 구간

전체 날짜순
≠ 보장

SELECT order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
AND    status = 'COMPLETED'
ORDER BY order_date DESC,
         order_id DESC
FETCH FIRST 20 ROWS ONLY;

(customer_id, order_date DESC, order_id DESC)

Index Entry 읽음
→ ROWID Table Access
→ status Filter
→ 탈락 가능

(customer_id, status, order_date DESC, order_id DESC)

(customer_id, order_date DESC, order_id DESC)

customer_id, order_date, order_id

COUNT STOPKEY
  INDEX RANGE SCAN DESCENDING ORDERS_CUST_DATE_IX

SELECT o.order_id,
       i.item_id,
       o.order_date
FROM   orders o
JOIN   order_items i
  ON   i.order_id = o.order_id
ORDER BY o.order_date DESC,
         o.order_id DESC,
         i.item_id
FETCH FIRST 20 ROWS ONLY;

주문·상품 조합 Row 중 상위 20행

WITH recent_order AS (
    SELECT order_id,
           order_date
    FROM   orders
    ORDER BY order_date DESC,
             order_id DESC
    FETCH FIRST 20 ROWS ONLY
)
SELECT r.order_id,
       i.item_id,
       r.order_date
FROM   recent_order r
JOIN   order_items i
  ON   i.order_id = r.order_id;

최근 주문 20건 선택
→ 각 주문의 모든 상품 반환
→ 최종 결과 20행 초과 가능

모든 주문 Top 20 선선택
→ VIP Join·Filter

≠

VIP 주문 집합
→ Top 20

ORDER BY order_date DESC,
         order_id DESC
OFFSET 100000 ROWS
FETCH NEXT 20 ROWS ONLY;

WHERE (
       order_date < :last_order_date
    OR (
       order_date = :last_order_date
       AND order_id < :last_order_id
    )
)
ORDER BY order_date DESC,
         order_id DESC
FETCH FIRST 20 ROWS ONLY;

SELECT employee_id,
       department_id,
       salary
FROM (
    SELECT employee_id,
           department_id,
           salary,
           ROW_NUMBER() OVER (
               PARTITION BY department_id
               ORDER BY salary DESC,
                        employee_id
           ) AS rn
    FROM employees
)
WHERE rn <= 3;

전체 결과 Top 3
≠ 부서별 Top 3

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       customer_id,
       order_date
FROM   orders
WHERE  customer_id = :customer_id
ORDER BY order_date DESC,
         order_id DESC
FETCH FIRST 20 ROWS ONLY;

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        :sql_id,
        :child_no,
        'ALLSTATS LAST +MEMSTATS +PREDICATE +ALIAS +NOTE'
    )
);

Plan A
COUNT STOPKEY
Index A-Rows 20
Table Buffers 25

Plan B
SORT ORDER BY STOPKEY
Full Scan A-Rows 50,000,000
Buffers 400,000
Sort Output 20

COUNT STOPKEY
Index 후보 A-Rows 500,000
Table A-Rows 20
Buffers 600,000

1. Top-N 대상 Entity·Join Grain을 정의한다.
2. ONLY 또는 WITH TIES를 결정한다.
3. 결정적 ORDER BY와 Unique Tie-Breaker를 정한다.
4. ROWNUM Query Block 또는 FETCH FIRST 위치를 확인한다.
5. Predicate 후 후보 Row 수와 추가 Table Filter를 확인한다.
6. Index가 전체 정렬 순서를 제공하는지 검토한다.
7. COUNT·SORT STOPKEY와 하위 A-Rows를 확인한다.
8. Covering·복합 Index와 DML Trade-off를 비교한다.
9. OFFSET이 크면 Keyset Pagination을 검토한다.
10. 동일 결과·Bind·Fetch에서 First Row와 전체 Runtime을 검증한다.