Index Order로 Sort 생략: ORDER BY·Top-N·GROUP BY NOSORT

핵심 요약

B-Tree Index는 Key 순서로 Entry를 저장합니다. SQL이 요구하는 정렬 순서와 Index 순서가 맞으면 Oracle은 별도의 Sort 없이 Index에서 정렬된 Row를 얻을 수 있습니다.

대표적인 효과는 다음과 같습니다.

ORDER BY Sort 생략
Top-N 조기 중단
Pagination 시작점 탐색
GROUP BY NOSORT
분석 함수의 WINDOW NOSORT

Sort 생략 여부는 Index 존재만으로 판단하지 않습니다.

선두 Prefix 조건
+ Column 순서
+ ASC·DESC 방향
+ NULL 위치
+ 함수·Collation
+ 여러 Index 구간
+ 실제 Scan Operation
+ 후속 Join·Table Access
→ ORDER BY·GROUP BY·Window 충족 여부 결정

INDEX RANGE SCAN, INDEX FULL SCAN처럼 Key 순서로 Leaf를 읽는 Operation과 INDEX FAST FULL SCAN처럼 물리 Block 순서로 읽는 Operation을 구분해야 합니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급 활용 및 튜닝 → 소트 튜닝 → Index Order·Sort Elimination·Top-N·GROUP BY NOSORT·WINDOW NOSORT 범위를 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

1. 복합 Index의 사전식 정렬 원리를 이해한다.
2. WHERE 조건이 Index Order 활용에 미치는 영향을 설명한다.
3. ASC·DESC와 NULL 정렬 조건을 판단한다.
4. Top-N·Pagination에서 Index Order의 이점을 이해한다.
5. GROUP BY NOSORT, WINDOW NOSORT의 기본 조건을 설명한다.
6. INDEX FULL SCAN과 INDEX FAST FULL SCAN의 순서 특성을 구분한다.
7. B-Tree의 All-NULL Key 저장 규칙과 NULL 정렬을 구분한다.
8. INLIST·Skip Scan·Partition 다중 구간에서 전역 순서가 필요한 이유를 설명한다.
9. Sort 생략과 전체 실행비용을 분리해 검증한다.

1. B-Tree Index의 정렬 순서

다음 Index가 있습니다.

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

Index Entry는 개념적으로 다음 순서로 배치됩니다.

status가 먼저 정렬
→ 같은 status 안에서 order_date DESC
→ 같은 status·order_date 안에서 order_id DESC

예시:

CANCELLED, 2026-07-29, 500
CANCELLED, 2026-07-28, 450
COMPLETED, 2026-07-30, 700
COMPLETED, 2026-07-30, 699
COMPLETED, 2026-07-29, 650
PENDING,   2026-07-30, 800

이 구조를 사전식 정렬이라고 이해할 수 있습니다. 첫 Key가 다르면 뒤의 Key보다 첫 Key의 순서가 우선합니다.

따라서 전체 Index의 우선 정렬 Key는 status이며, 그 안에 order_date DESC의 작은 정렬 구간들이 연결됩니다.

1.1 모든 Key가 NULL인 Row

일반 B-Tree Index는 복합 Index의 모든 Key Column이 NULL인 Row를 저장하지 않습니다.

CREATE INDEX result_ix
ON exam_result(score, result_id);

result_id가 NOT NULL이면 score가 NULL이어도 Index Entry가 존재합니다.

(score=NULL, result_id=100)
→ 적어도 한 Key가 Non-NULL
→ Index Entry 존재

반대로 모든 Key가 Nullable이고 둘 다 NULL이면 일반 B-Tree Entry가 없을 수 있습니다.

(score=NULL, optional_code=NULL)
→ 모든 Key가 NULL
→ 일반 B-Tree Index에 Entry 없음

따라서 Index만 읽어 전체 Row를 정렬·집계하려는 Plan에서는 다음을 확인합니다.

• 적어도 한 Index Key가 NOT NULL인가?
• All-NULL Row가 업무 결과에 포함돼야 하는가?
• Function-Based Index로 상수·대체값을 저장할 필요가 있는가?
• Table Access 또는 별도 Sort가 필요한가?

2. 선두 Column의 등치 조건과 ORDER BY

다음 SQL은 status를 하나의 값으로 고정합니다.

SELECT order_id,
       order_date,
       amount
FROM   orders
WHERE  status = 'COMPLETED'
ORDER BY order_date DESC, order_id DESC;

Index (status, order_date DESC, order_id DESC)에서 status='COMPLETED' 범위만 읽으면 그 범위 안의 순서는 다음과 같습니다.

order_date DESC
→ order_id DESC

따라서 Oracle이 이 Index 경로를 선택하면 별도의 SORT ORDER BY를 생략할 가능성이 있습니다.

가능한 실행 형태:

TABLE ACCESS BY INDEX ROWID ORDERS
  INDEX RANGE SCAN ORDERS_IX

실행계획에 Sort Operation이 없고, Optimizer가 Index 순서가 ORDER BY를 만족한다고 판단한 형태입니다.

3. 선두 Column이 고정되지 않을 때

같은 Index에서 다음 SQL을 실행합니다.

SELECT order_id,
       status,
       order_date
FROM   orders
ORDER BY order_date DESC, order_id DESC;

Index 전체 순서는 다음과 같습니다.

status
→ order_date DESC
→ order_id DESC

요청 순서는 다음과 같습니다.

order_date DESC
→ order_id DESC

status별 구간이 먼저 나뉘므로 Index 전체를 순서대로 읽어도 모든 상태를 합친 전역 order_date DESC 순서가 되지 않습니다. 별도 Sort가 필요할 수 있습니다.

여러 값의 IN 조건

WHERE status IN ('COMPLETED', 'PENDING')
ORDER BY order_date DESC, order_id DESC

각 status 구간 안에서는 날짜순이지만 두 구간을 이어 읽는 것만으로 전역 날짜순이 되지 않습니다.

COMPLETED 구간의 날짜순
+
PENDING 구간의 날짜순
≠
두 상태를 합친 전체 날짜순

Optimizer가 여러 구간을 Merge하거나 별도 Sort를 수행해야 할 수 있습니다.

3.1 INLIST ITERATOR와 전역 순서

IN 조건은 값별로 여러 불연속 Index Range를 반복 탐색하는 INLIST ITERATOR 형태가 될 수 있습니다.

status='COMPLETED' 범위
status='PENDING' 범위
→ 각 범위 내부는 날짜순
→ 범위 사이 전역 날짜순은 별도 Merge·Sort 필요 가능

최종 ORDER BY가 선두 Key를 포함하면 상황이 달라질 수 있습니다.

WHERE status IN ('COMPLETED', 'PENDING')
ORDER BY status, order_date DESC, order_id DESC;

요청 순서가 Index의 사전식 Key 순서를 그대로 포함하므로 여러 범위를 순서대로 결합할 수 있는지 Optimizer가 검토할 수 있습니다.

3.2 Skip Scan

INDEX SKIP SCAN은 선두 Column의 값별 Logical Subindex를 반복 탐색합니다.

선두값 A의 후행 Key 순서
+
선두값 B의 후행 Key 순서
+
선두값 C의 후행 Key 순서

각 Logical Subindex 내부 순서가 맞아도 선두값을 제거한 전역 후행 Key 순서가 자동으로 만들어지는 것은 아닙니다. 따라서 ORDER BY Sort 생략 여부를 Operation 이름과 실제 Plan으로 확인합니다.

4. Range 조건 이후의 정렬

Index:

(customer_id, order_date, order_id)

SQL:

WHERE customer_id BETWEEN 100 AND 200
ORDER BY order_date, order_id

Index 전체 순서는 다음과 같습니다.

customer_id
→ order_date
→ order_id

여러 customer_id 값이 포함되면 고객별 날짜순 구간이 이어질 뿐, 전체 결과가 날짜순이 되지 않습니다.

반면 다음 SQL은 Index 순서와 자연스럽게 연결됩니다.

WHERE customer_id BETWEEN 100 AND 200
ORDER BY customer_id, order_date, order_id

ORDER BY가 Range Column인 customer_id부터 Index Key 순서를 따라가기 때문입니다.

핵심 판단은 다음과 같습니다.

Index에서 여러 Prefix 값이 포함될 때
→ 요청 ORDER BY가 그 Prefix 순서까지 포함하는가?

5. ASC·DESC 방향

Oracle은 B-Tree Index를 정방향 또는 역방향으로 읽을 수 있습니다.

Index:

CREATE INDEX orders_asc_ix
ON orders(order_date ASC, order_id ASC);

다음 두 순서는 하나의 Scan 방향으로 처리될 가능성이 있습니다.

ORDER BY order_date ASC,  order_id ASC
ORDER BY order_date DESC, order_id DESC

두 번째는 Index를 역방향으로 읽으면 전체 Key 방향이 함께 반전됩니다.

반면 혼합 방향은 다릅니다.

ORDER BY order_date DESC, order_id ASC

단순히 전체 Index Scan 방향을 뒤집는 것만으로 첫 Column은 DESC, 두 번째는 ASC를 만들 수 없습니다.

다음처럼 혼합 방향을 Index 정의에 반영할 수 있습니다.

CREATE INDEX orders_mixed_ix
ON orders(order_date DESC, order_id ASC);

업무에서 자주 사용하는 정렬 조합과 Index 유지 비용을 함께 검토합니다.

5.1 Index Scan Operation별 순서 특성

INDEX FAST FULL SCAN은 Index-only Access일 수 있지만 Key 순서를 제공하지 않으므로 ORDER BY를 대신할 수 없습니다.

5.2 Reverse Key Index

Reverse Key Index는 증가 Key의 Leaf Hot Block 경합을 분산하는 용도로 사용할 수 있지만, 논리 Key 순서를 뒤집어 저장하므로 일반 Range Scan이나 ORDER BY Sort 생략에 적합하지 않습니다.

정방향 B-Tree
→ 인접 Key가 Leaf에서 인접
→ Range·Order 활용 가능

Reverse Key
→ Key Byte를 반전해 분산
→ 인접 업무 Key의 Range·Order 활용 제한

경합 완화와 정렬·범위 조회의 Trade-off를 함께 판단합니다.

6. NULL 정렬 위치

Oracle의 기본 정렬 규칙은 다음과 같습니다.

ASC  → NULLS LAST
DESC → NULLS FIRST

SQL이 다음처럼 다른 NULL 위치를 요구하면 Index 순서를 그대로 활용하기 어려울 수 있습니다.

ORDER BY score DESC NULLS LAST

Index의 저장·Scan 순서, Column의 NULL 가능성, Predicate를 함께 확인합니다.

다음처럼 NULL을 제외할 수 있는 업무라면 조건을 명시할 수 있습니다.

WHERE score IS NOT NULL
ORDER BY score DESC

NULL을 특정 대체값으로 정렬하려고 Column을 가공하면 일반 Index Order를 직접 활용하기 어려울 수 있습니다.

ORDER BY NVL(score, -1) DESC

이 경우 Function-Based Index를 검토할 수 있습니다.

CREATE INDEX result_score_nvl_ix
ON exam_result(NVL(score, -1) DESC, result_id);

표현식과 데이터 의미, DML 비용을 함께 판단합니다.

6.1 문자 Collation과 표현식

문자 정렬은 BINARY가 아닌 Linguistic Collation을 사용할 수 있습니다. SQL이 요구하는 Collation과 일반 Index의 저장 순서가 다르면 Index Order를 직접 이용하기 어려울 수 있습니다.

ORDER BY customer_name COLLATE BINARY_CI

또는 다음처럼 표현식을 사용하면 일반 Column Index와 정렬 표현식이 다릅니다.

ORDER BY NLSSORT(customer_name, 'NLS_SORT=KOREAN_M')

빈번한 업무 정렬이면 동일 표현식의 Function-Based Index를 검토할 수 있지만 다음을 함께 확인합니다.

• Query 표현식과 Index 표현식의 일치
• Session·Column Collation
• 문자열 길이와 Index Entry 크기
• DML·공간 비용
• 동일 Collation의 최종 결과 의미

7. Top-N과 부분범위 처리

다음 SQL은 한 고객의 최근 주문 20건만 필요합니다.

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

Index:

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

처리 흐름은 다음과 같습니다.

customer_id Index 범위 탐색
→ 요청 순서대로 Entry 읽기
→ 필요한 Table Row 조회
→ 20건 확보
→ 중단

이 형태는 전체 결과를 정렬한 뒤 20건을 선택하는 방식보다 첫 Row 응답과 전체 작업량을 크게 줄일 수 있습니다.

Top-N 결과를 안정적으로 만들려면 동률을 해소하는 고유 Tie-Breaker를 포함합니다.

ORDER BY order_date DESC
→ 같은 날짜의 상대 순서 미결정

ORDER BY order_date DESC, order_id DESC
→ order_id가 Unique이면 결정적 전체 순서

Index Order가 Sort를 없애더라도 ORDER BY가 비결정적이면 실행마다 경계 Row가 달라질 수 있습니다.

가능한 Operation:

COUNT STOPKEY
WINDOW NOSORT STOPKEY
INDEX RANGE SCAN

실제 Operation 이름은 SQL 변환과 버전에 따라 달라질 수 있습니다. 핵심은 하위 Index·Table의 A-Rows가 필요한 행 수 근처에서 멈추는지입니다.

8. 추가 Filter가 있는 Top-N

다음 SQL을 생각합니다.

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

Index가 (customer_id, order_date DESC, order_id DESC)이면 순서는 제공하지만 amount는 Table에서 확인해야 합니다.

최근 Index Entry 읽기
→ Table Row 방문
→ amount Filter
→ 통과한 행 누적
→ 20건 확보할 때까지 반복

최근 주문 중 고액 주문 비율이 낮으면 20건을 반환하기 위해 수천 개의 Entry와 Table Row를 확인할 수 있습니다.

따라서 Sort가 생략됐다는 사실만으로 효율적이라고 결론 내리지 않습니다.

하위 Index A-Rows
Table Access A-Rows
최종 A-Rows
Buffers

이 차이를 확인합니다.

9. Covering Index와 Table Access

다음 Index가 반환 Column까지 포함한다고 가정합니다.

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

SQL이 Index Column만 필요하면 Table Access를 줄이거나 제거할 가능성이 있습니다.

정렬 순서 제공
+ Top-N 조기 중단
+ Table Access 제거

그러나 Covering Index는 다음 비용을 증가시킬 수 있습니다.

• Index Entry 폭
• Leaf Block 수
• Cache 사용
• INSERT·UPDATE·DELETE
• Redo·Undo
• 기존 Index와 중복

상위 N 조회의 빈도와 중요도, Table Access 절감량을 기준으로 판단합니다.

10. GROUP BY NOSORT

다음 SQL은 고객별 주문 수를 계산합니다.

SELECT customer_id,
       COUNT(*)
FROM   orders
GROUP BY customer_id;

Index (customer_id)를 순서대로 읽으면 같은 고객의 Entry가 연속해서 나타납니다.

C001, C001, C001
C002, C002
C003

Oracle은 Key 변경 지점에서 이전 Group을 확정할 수 있으므로 별도 Sort를 생략할 가능성이 있습니다.

SORT GROUP BY NOSORT
  INDEX FULL SCAN ORDERS_CUSTOMER_IX

NOSORT는 입력 순서가 Group Key에 맞아 정렬 단계가 필요 없다는 의미입니다.

NOSORT라는 이름이 ORDER BY 결과 순서를 보장한다는 뜻은 아닙니다. Group 결과를 특정 순서로 사용자에게 반환해야 하면 최종 ORDER BY를 명시합니다.

다음 비용은 여전히 남습니다.

• Index 전체 Scan
• 집계에 필요한 추가 Column
• Table ROWID Access
• Index Block 수
• Aggregate CPU
• 최종 ORDER BY

Full Scan + Hash Group By와 전체 작업량을 비교해야 합니다.

11. 복합 GROUP BY와 Prefix

Index:

(region_code, customer_grade, customer_id)

다음 Group은 Index Prefix와 일치합니다.

GROUP BY region_code, customer_grade

Index 순서에서 같은 (region_code, customer_grade)가 연속될 수 있으므로 NOSORT 후보가 됩니다.

다음 Group은 선두 Prefix를 건너뜁니다.

GROUP BY customer_grade

Index 전체에서는 region_code별로 customer_grade 구간이 반복되므로 같은 등급이 전역으로 연속되지 않습니다. 별도 Group 처리 또는 Sort·Hash가 필요할 수 있습니다.

다음처럼 선두 Column이 하나의 상수로 고정되면 상황이 달라집니다.

WHERE region_code = 'SEOUL'
GROUP BY customer_grade

region_code가 한 값으로 고정된 범위 안에서 customer_grade 순서를 활용할 수 있는지 Optimizer가 판단합니다.

12. WINDOW NOSORT

분석 함수가 요구하는 순서와 Index 순서가 맞으면 WINDOW NOSORT가 나타날 수 있습니다.

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

Index:

CREATE INDEX emp_dept_salary_ix
ON employees(department_id, salary DESC, employee_id);

Index 순서는 다음 Window 사양과 연결됩니다.

PARTITION BY department_id
ORDER BY salary DESC, employee_id

다만 Index 밖 Column을 많이 조회하면 Table Access 비용이 커질 수 있고, 전체 Employee를 Index 순서로 읽는 비용이 Full Scan + Window Sort보다 비쌀 수 있습니다.

분석 함수의 ORDER BY는 함수 계산 순서만 정의하며 최종 Query 결과 순서를 보장하지 않습니다.

SELECT employee_id,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

최종 출력도 급여순이어야 한다면 Query 마지막에 ORDER BY salary DESC, employee_id를 별도로 작성합니다.

12.1 Partitioned Index와 전역 순서

Local Index는 Table Partition별로 독립된 B-Tree입니다.

P202601 Local Index: Key 순서
P202602 Local Index: Key 순서
P202603 Local Index: Key 순서

한 Partition만 Pruning되면 해당 Local Index 순서를 활용할 수 있습니다. 여러 Partition을 동시에 읽으면 각 Partition의 정렬 Stream을 전역 순서로 합치는 Merge·Sort가 필요할 수 있습니다.

각 Partition 내부 정렬
≠
전체 Partition을 합친 전역 정렬

실행계획에서 PSTART·PSTOP, Partition Iterator와 Sort·Merge Operation을 함께 확인합니다.

13. Sort 생략과 결과 순서 보장

SQL에 ORDER BY가 없는데 Index Scan 결과가 정렬된 것처럼 보일 수 있습니다.

SELECT order_id, order_date
FROM   orders
WHERE  customer_id = :customer_id;

현재 Plan이 Index Range Scan이어도 다음 변화로 결과 순서가 달라질 수 있습니다.

• 다른 Index 선택
• Table Full Scan
• Parallel Execution
• Join Order 변경
• Partition 처리
• Query Transformation
• Batched Table Access

업무가 순서를 요구하면 SQL에 ORDER BY를 명시합니다. Optimizer는 그 ORDER BY를 Index가 만족한다고 판단할 때 물리 Sort를 생략할 수 있습니다.

논리적 순서 보장
→ SQL ORDER BY

물리적 Sort 생략
→ Optimizer가 Index Order로 ORDER BY를 충족

두 개념을 구분합니다.

14. 전체 결과 조회와 손익분기점

Index Order가 Sort를 생략하더라도 결과의 대부분을 조회하면 많은 ROWID Table Access가 발생할 수 있습니다.

Index Full Scan
→ 수백만 ROWID Table Access
→ Sort 없음

대안:

Table Full Scan
→ Multiblock I/O
→ Sort

반환 행 비율, Clustering Factor, Table·Index Block 수, Row 폭, TEMP 성능과 Memory에 따라 두 번째가 더 빠를 수 있습니다.

특히 ALL_ROWS 목표의 전체 결과 처리와 FIRST_ROWS(n)·Top-N 목표는 최적 경로가 다를 수 있습니다.

첫 20건 빠르게
→ Index Order가 큰 가치

전체 500만 건 완료
→ Full Scan + Sort가 더 유리할 수 있음

15. 실행계획과 실측 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id, order_date, amount
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(
        NULL,
        NULL,
        'ALLSTATS LAST +MEMSTATS +PREDICATE +ALIAS +NOTE'
    )
);

다음 항목을 확인합니다.

16. 혼동하기 쉬운 판단

17. 적용 판단 순서

1. ORDER BY·GROUP BY·Window의 정확한 Key를 적는다.
2. Index Key 순서와 비교한다.
3. 선두 Key가 등치로 고정되는지 확인한다.
4. Range·INLIST·Skip Scan으로 여러 Prefix 구간이 생기는지 본다.
5. 여러 Partition·Index Stream의 전역 Merge 필요 여부를 확인한다.
6. ASC·DESC·NULL·Collation 순서를 비교한다.
7. Range·Full·Fast Full·Descending 등 실제 Scan Operation을 확인한다.
8. All-NULL Key가 Index에서 누락되는지 확인한다.
9. 함수·표현식에 Function-Based Index가 필요한지 검토한다.
10. 추가 Filter와 반환 Column의 Table Access를 계산한다.
11. Top-N에서 실제 조기 중단 행 수를 확인한다.
12. Index 경로와 Full Scan + Sort의 전체 비용을 비교한다.
13. 결과 순서와 변경 전후 실측 통계를 검증한다.