부분범위 처리 성립 조건: Index Order·STOPKEY·Blocking Operation

핵심 요약

부분범위 처리는 Index가 존재하거나 실행계획에 STOPKEY가 표시된다는 사실만으로 성립하지 않는다. 업무가 요구하는 앞쪽 행이 명확하고, 실행계획이 그 순서로 행을 일찍 생산하며, Client가 필요한 행까지만 Fetch하고 Cursor를 닫아야 한다.

SQL 의미
→ 어떤 행이 앞쪽인지 결정적인 순서와 Row Limit으로 정의

실행계획
→ 필요한 순서로 행을 생산하고 조기 중단 가능한 위치에 STOPKEY 적용

Client
→ 필요한 행만 소비하고 ResultSet·Statement·Cursor를 종료

특히 ORDER BY를 B-Tree Index 순서로 만족하려면 다음 조건을 함께 확인한다.

복합 Index의 선두 Prefix
+ ASC·DESC 방향
+ NULL 정렬 의미
+ Access Predicate와 Filter Predicate
+ Table ROWID 접근량
+ 실제 Fetch 범위

Sort 생략
≠ 항상 더 낮은 전체 비용

STOPKEY 출력 20행
≠ 하위 Row Source도 20행만 처리

Index Order Plan은 초기 응답에 유리할 수 있지만, 많은 ROWID Random Access와 Filter 탈락이 발생하면 전체 처리에서는 Full Scan + Top-N Sort보다 비쌀 수 있다.

학습 목표

• 부분범위 처리의 SQL·Plan·Client 조건을 설명한다.
• 결정적인 ORDER BY와 고유 Tie-Breaker의 필요성을 설명한다.
• B-Tree Index Range Scan과 Index Fast Full Scan의 정렬 특성을 구분한다.
• 복합 Index의 사전식 정렬과 등치 Prefix의 관계를 판단한다.
• 정방향·역방향 Scan과 혼합 ASC·DESC 정렬을 구분한다.
• COUNT STOPKEY, WINDOW NOSORT STOPKEY, SORT ORDER BY STOPKEY를 해석한다.
• Blocking Operation이 초기 응답을 지연하는 이유를 설명한다.
• FIRST_ROWS(n)과 Row Limiting Clause의 역할과 한계를 구분한다.
• Index Order와 Table Random Access의 Trade-off를 Runtime 통계로 검증한다.
• 일부 Fetch와 전체 Fetch를 분리해 Index 설계의 효과를 평가한다.

1. 부분범위 처리의 세 가지 성립 조건

1.1 SQL 조건

SQL은 다음 질문에 답해야 한다.

어떤 행이 앞쪽인가?
동점 행의 순서는 무엇인가?
최대 몇 행이 필요한가?

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

order_date만으로는 같은 시각의 여러 주문 순서가 결정되지 않을 수 있다. order_id처럼 고유한 Tie-Breaker를 추가해야 Page 반복 조회와 Top-N 결과를 안정적으로 재현할 수 있다. ORDER BY가 없으면 같은 Query를 반복 실행해도 행 순서는 보장되지 않는다.

1.2 실행계획 조건

• 필요한 순서로 행을 생산할 Access Path
• Selective Access Predicate
• 조기 중단 가능한 STOPKEY
• 첫 출력 전 대규모 Sort·Aggregate·Build 최소화
• 앞쪽 N행까지 적은 Index·Table Block 방문

1.3 Client 조건

• 업무가 필요한 행까지만 Fetch
• 전체 ResultSet을 List로 Materialize한 뒤 Slice하지 않음
• ResultSet·Statement·Cursor를 즉시 Close
• Page Size와 Driver Fetch Size를 함께 검토

세 계층 중 하나만 맞아도 충분하다는 규칙은 없다.

2. B-Tree Index가 제공하는 순서

Oracle의 Index Range Scan은 Key 순서에 따른 Ordered Scan이다. 기본 B-Tree Index는 오름차순으로 저장되며, 실행계획의 INDEX RANGE SCAN DESCENDING처럼 반대 방향으로 읽을 수도 있다.

INDEX RANGE SCAN
→ Key 순서에 따라 Leaf Block을 진행

INDEX RANGE SCAN DESCENDING
→ 반대 방향으로 Leaf Block을 진행

반면 INDEX FAST FULL SCAN은 Index를 Table처럼 Multiblock I/O로 읽는 방식이며 정렬 순서를 제공하지 않는다.

INDEX FULL SCAN
→ Index Key 순서를 이용할 수 있음

INDEX FAST FULL SCAN
→ Block을 디스크 배치 순서 중심으로 읽음
→ ORDER BY 순서를 보장하지 않음

따라서 Plan에 Index라는 단어가 보인다는 이유만으로 Sort가 생략된다고 판단하면 안 된다. Oracle 공식 문서도 정렬 결과가 필요하면 반드시 ORDER BY를 사용하고, Optimizer가 Index로 그 순서를 만족할 수 있을 때 Sort를 생략한다고 설명한다.

3. 복합 Index의 사전식 정렬

다음 Index를 가정한다.

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

Index Entry는 다음 순서로 정렬된다.

customer_id
→ 같은 customer_id 안에서 status
→ 같은 customer_id·status 안에서 order_date DESC
→ 같은 Prefix 안에서 order_id DESC

3.1 선두 Prefix가 하나의 값으로 고정되는 경우

WHERE customer_id = :customer_id
AND   status      = 'READY'
ORDER BY order_date DESC, order_id DESC

선두 두 Column이 등치 조건으로 하나의 Prefix 구간에 고정되므로, 그 뒤의 order_date DESC, order_id DESC 순서를 그대로 이용할 수 있는 후보가 된다.

3.2 선두 Prefix가 여러 값으로 열리는 경우

WHERE customer_id IN (100, 200)
AND   status = 'READY'
ORDER BY order_date DESC, order_id DESC

각 고객 구간 내부에서는 날짜순이지만 두 구간을 단순 연결한 결과가 전체 날짜순이라는 보장은 없다.

customer 100 구간의 날짜순
+
customer 200 구간의 날짜순
→ 전체 날짜순을 만들기 위한 Merge·Sort가 필요할 수 있음

실행계획에 INLIST ITERATOR가 나타나면 값별 Index Scan이 반복될 수 있다. 각 반복의 Local Order와 전체 Result Set의 Global Order를 구분해야 한다.

3.3 선두 Column이 범위로 열리는 경우

WHERE customer_id >= 100
ORDER BY status, order_date DESC

여러 customer_id Prefix를 지나가므로 status, order_date만의 전역 정렬과 일치하지 않을 수 있다. 등치 Prefix 뒤의 Column 순서가 의미를 갖는 범위를 정확히 판단해야 한다.

4. 정렬 방향과 NULL 순서

4.1 전체 정방향·역방향

Index (A ASC, B ASC)

ORDER BY A ASC,  B ASC
→ 정방향 Scan 후보

ORDER BY A DESC, B DESC
→ 전체 역방향 Scan 후보

전체 방향이 함께 뒤집히면 하나의 역방향 Scan으로 만족할 수 있다.

4.2 혼합 방향

ORDER BY A ASC,
         B DESC

INDEX(A ASC, B ASC)의 단순 정방향·역방향 Scan으로는 이 혼합 순서를 그대로 만들기 어렵다. 해당 정렬이 핵심 업무이면 다음과 같은 혼합 방향 Index를 검토한다.

CREATE INDEX t_ix
ON t (A ASC, B DESC);

단, Index를 추가하기 전에 Filter 선택도, 반환 Column, DML 부하, 기존 Index 중복을 함께 평가한다.

4.3 NULLS FIRST·LAST

Oracle의 기본 NULL 정렬은 다음과 같다.

ASC  → NULLS LAST 기본
DESC → NULLS FIRST 기본

SQL이 명시한 NULL 순서와 Index가 제공 가능한 순서가 맞아야 Sort 생략 후보가 된다. 다음을 검토한다.

• 정렬 Column의 NOT NULL 가능 여부
• 업무가 요구하는 NULLS FIRST·LAST 명시
• 필요 시 표현식과 Function-Based Index
• 실제 실행계획에서 Sort 생략 여부

업무 결과를 Index에 맞추기 위해 NULL 의미를 바꾸면 안 된다. 먼저 정확한 업무 순서를 정의하고 Index가 이를 지원하도록 설계한다.

5. STOPKEY Operation을 구분한다

Operation 이름은 SQL 표현과 Optimizer 변환에 따라 달라질 수 있으므로 이름만 암기하지 말고 입력 순서·STOPKEY 위치·하위 A-Rows를 함께 본다.

5.1 COUNT STOPKEY

이미 필요한 순서로 들어오는 Row를 N건에서 중단하는 구조에 나타날 수 있다.

COUNT STOPKEY
  TABLE ACCESS BY INDEX ROWID
    INDEX RANGE SCAN

전통적인 ROWNUM <= :n Top-N 형태 등에서 관찰될 수 있다.

5.2 WINDOW NOSORT STOPKEY

Analytic·Row Limiting 변환에서 입력이 이미 필요한 순서로 들어와 별도 Window Sort를 피할 때 나타날 수 있다.

WINDOW NOSORT STOPKEY
  TABLE ACCESS BY INDEX ROWID
    INDEX RANGE SCAN DESCENDING

NOSORT는 입력 순서가 준비됐다는 뜻이지 하위 Access가 항상 적다는 뜻은 아니다.

5.3 SORT ORDER BY STOPKEY

입력이 정렬돼 있지 않지만 상위 N개만 필요할 때 Top-N 후보를 유지하는 Sort다.

SORT ORDER BY STOPKEY
  TABLE ACCESS FULL

전체 Sort보다 Workarea 요구를 줄일 수 있지만, 상위 N개를 확정하려면 하위 후보를 많이 읽고 비교할 수 있다.

SORT ORDER BY STOPKEY A-Rows = 20
TABLE ACCESS FULL A-Rows     = 800,000

최종 출력 20행과 하위 입력 800,000행은 모순이 아니다.

5.4 일반 SORT ORDER BY

SORT ORDER BY
  ...

전체 입력을 정렬해야 결과를 내는 Blocking Operation이다. Client가 첫 Page만 읽더라도 첫 행 전에 정렬 입력의 대부분 또는 전체를 처리할 수 있다.

6. Blocking Operation과 FIRST_ROWS(n)

대표적인 Blocking 또는 선행 작업 Operation은 다음과 같다.

FIRST_ROWS(n)은 첫 n행 응답에 유리한 Plan을 선택하도록 하는 Cost-Based 목표이지 결과 제한 기능이 아니다. 결과 행 수는 FETCH FIRST n ROWS ONLY나 올바른 Top-N SQL이 정의한다.

또한 Oracle은 Sort·Grouping처럼 Query Block 자체가 첫 행 전에 전체 입력을 처리해야 하는 Blocking Operation을 포함하면 FIRST_ROWS(n) Hint를 무시할 수 있다고 설명한다. Hint를 적었다는 사실만으로 적용됐다고 판단하지 말고 DBMS_XPLAN의 Hint Report를 확인한다.

FIRST_ROWS(20)
→ Optimizer 목표

FETCH FIRST 20 ROWS ONLY
→ SQL 반환 상한

Client가 20행에서 Close
→ 실제 소비·중단 동작

세 기능은 서로 다르다.

7. Index Order와 Table Random Access

다음 Query가 Index 순서로 앞쪽 ROWID를 찾더라도 반환 Column이 Index에 없다면 Table Block을 방문한다.

INDEX RANGE SCAN
→ ROWID 후보
→ TABLE ACCESS BY INDEX ROWID
→ Table Filter
→ 최종 Row

20행을 만들기 위해 20개 ROWID만 읽으면 효율적일 가능성이 높다. 그러나 Filter가 Table 단계에 남아 있으면 많은 후보가 탈락할 수 있다.

Index Operation A-Rows = 50,000
Table Operation A-Rows = 20
Buffers                 = 매우 큼

이 패턴은 Index가 정렬은 지원하지만 Filter 선택도가 부족해 많은 Table Random Access를 발생시켰다는 신호일 수 있다.

개선 후보

• Filter Column을 Index에 추가해 Table 방문 전 후보 감소
• 자주 반환하는 Column 일부를 포함해 Covering 강화
• 등치 조건과 정렬 Key의 Column 순서 재설계
• 업무별 전용 Top-N Index 검토
• 과도하게 넓은 Index의 공간·Cache·DML 비용 비교

Clustering Factor

Index Key 순서와 Table Block 배치가 가까우면 연속 ROWID 접근에서 Block 재사용 가능성이 높다. Row가 Table 전체에 흩어져 있으면 같은 Index Entry 수라도 Table I/O 비용이 커질 수 있다. Clustering Factor는 Index Order Plan의 Table Access 비용을 해석하는 중요 통계지만, 단일 값만으로 결론을 내리지 말고 실제 Buffers와 함께 본다.

8. Join과 부분범위 처리

8.1 Nested Loops

작은 Driving Row Source와 효율적인 Inner Index가 있으면 앞쪽 결과를 빠르게 만들 수 있다.

Outer Row 1
→ Inner Index Probe
→ Join Row 반환

Outer Row 2
→ Inner Index Probe
→ Join Row 반환

그러나 Outer 후보가 많거나 Inner Probe가 비싸면 Random Access가 누적돼 전체 처리량이 나빠질 수 있다.

8.2 Hash Join

Hash Join은 Probe 전에 Build Input을 읽어 Hash Table을 만든다. 첫 결과 전 선행 작업이 있지만 대량 전체 처리에서는 Nested Loops보다 효율적일 수 있다.

8.3 Sort Merge Join

입력이 Join Key 순서로 준비되지 않으면 Sort가 필요해 초기 응답이 지연될 수 있다. 비등치 Join이나 이미 정렬된 입력을 활용하는 경우에는 후보가 될 수 있다.

Join Method 이름 하나로 부분범위 여부를 결정하지 않는다.

• 첫 결과 전 처리되는 Outer·Build Row 수
• Inner Probe당 Buffers
• Sort·Hash Workarea와 TEMP
• Join 다음의 Blocking Operation
• 실제 Client Fetch 범위

을 함께 비교한다.

9. 실행계획 실측 절차

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

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL,
        NULL,
        'ALLSTATS LAST +PREDICATE +ALIAS +MEMSTATS +NOTE +HINT_REPORT'
    )
);

ALLSTATS는 I/O와 Memory Runtime 통계를 표시하고, LAST는 마지막 실행만 보여준다. MEMSTATS는 Sort·Hash Join 같은 Memory-Intensive Operation의 Memory와 Temp 사용을 확인하는 데 유용하다.

확인 순서

1. 실제 실행 Cursor와 올바른 Child Cursor인가?
2. 정렬이 생략됐는가, STOPKEY Sort인가, 전체 Sort인가?
3. STOPKEY가 어느 Operation에 적용됐는가?
4. STOPKEY 아래 A-Rows는 몇 개인가?
5. Index A-Rows와 Table A-Rows 차이는 얼마인가?
6. Starts가 예상보다 큰 반복 실행을 보이는가?
7. Buffers는 최종 N행에 비해 과도하지 않은가?
8. Used-Mem·Used-Tmp가 발생했는가?
9. Access Predicate와 Filter Predicate 위치가 적절한가?
10. Hint Report에서 Hint가 실제 사용됐는가?

Cost와 예상 Rows만으로 결론을 내리지 않는다. 같은 Bind와 유사한 Cache 조건에서 일부 Fetch와 전체 Fetch를 각각 실행하고 Runtime 통계를 비교한다.

10. 일부 Fetch와 전체 Fetch를 분리해 시험한다

같은 SQL과 Plan도 Client가 어디까지 Fetch하는지에 따라 실제 작업량이 달라질 수 있다.

시나리오 A: 첫 Page만 소비

20행 Fetch
→ ResultSet·Statement Close
→ First Page 응답과 Buffers 측정

시나리오 B: 전체 결과 소비

End of Fetch까지 반복
→ 전체 Elapsed·CPU·I/O·Network 측정

Index Order Plan
→ 첫 Page는 빠름
→ 전체 Fetch는 Random Access 누적으로 느릴 수 있음

Full Scan + Sort Plan
→ 첫 Page는 늦음
→ 전체 Fetch는 더 적은 총 I/O로 끝날 수 있음

검색 화면과 전체 Download가 같은 SQL을 사용하더라도 성능 목표가 다르면 별도 SQL·Index·Plan 전략이 필요할 수 있다.

11. 부분범위 처리용 Index 설계 순서

1. 업무가 요구하는 정확한 WHERE 조건을 확인한다.
2. 결정적인 ORDER BY와 고유 Tie-Breaker를 확정한다.
3. 등치 Predicate를 선두 Prefix 후보로 검토한다.
4. 정렬 Key의 Column 순서·ASC·DESC·NULL 의미를 맞춘다.
5. IN·범위 조건이 여러 Prefix를 만드는지 확인한다.
6. Access Predicate와 Table Filter를 분리한다.
7. N행을 만들기 위해 읽는 Index·Table A-Rows와 Buffers를 측정한다.
8. Covering 이득과 Index 폭·DML 비용을 비교한다.
9. 일부 Fetch와 전체 Fetch를 각각 시험한다.
10. INSERT·UPDATE·DELETE와 기존 Index 중복까지 회귀 검증한다.

Index 설계는 조회 SQL 하나의 Sort 제거만 목표로 하지 않는다. 전체 Workload에서 읽기와 쓰기 비용을 함께 최적화한다.

12. 혼동하기 쉬운 판단

13. 진단·적용 체크리스트

• ORDER BY가 결정적이며 고유 Tie-Breaker가 있는가?
• 반환 상한이 SQL에 표현되어 있는가?
• 복합 Index의 등치 Prefix와 정렬 Key가 맞는가?
• ASC·DESC와 NULLS FIRST·LAST가 업무 의미와 일치하는가?
• 사용된 Access Path가 Ordered Scan인지 확인했는가?
• INDEX FAST FULL SCAN을 정렬 Scan으로 오해하지 않았는가?
• STOPKEY 위치와 하위 A-Rows를 확인했는가?
• Sort·Hash·Window·Temporary Transformation의 선행 작업량을 확인했는가?
• Index와 Table Operation 사이의 후보 탈락량을 계산했는가?
• Buffers·Starts·Memory·Temp를 실제 실행에서 확인했는가?
• 일부 Fetch와 전체 Fetch를 분리해 측정했는가?
• Client가 필요한 행만 읽고 Cursor를 닫는가?
• 추가 Index의 DML·공간·기존 Index 중복을 검증했는가?