Sort Operation 이해: ORDER BY·JOIN·WINDOW SORT

핵심 요약

실행계획에 표시되는 SORT는 모두 같은 목적의 정렬이 아닙니다.

SORT ORDER BY
  → SQL 마지막 ORDER BY의 최종 출력 순서 준비

SORT JOIN
  → Sort Merge Join 입력을 Join Key 순서로 준비

WINDOW SORT
  → 분석 함수의 PARTITION BY·ORDER BY 계산 순서 준비

같은 Sort라도 튜닝 기준이 다릅니다.

SORT ORDER BY
  → 최종 결과 전체 정렬이 필요한가?
  → Top-N·Index Order로 줄일 수 있는가?

SORT JOIN
  → Merge Join이 적절한가?
  → 첫 입력의 정렬을 기존 Index 순서로 생략할 수 있는가?

WINDOW SORT
  → 분석 함수 Window 사양이 서로 호환되는가?
  → 입력 Row를 분석 함수 전에 줄일 수 있는가?

Sort 비용의 핵심입니다.

Sort 작업량
≈ Sort 입력 A-Rows
 × 전달 Row 폭
 × 비교·정렬 복잡도
 + TEMP Spill 비용

원본 Table 행 수가 아니라 Sort Operation 바로 아래 Row Source가 실제로 생산한 A-Rows를 확인합니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 소트 튜닝 → ORDER BY·JOIN·WINDOW SORT 범위에서 Sort 목적, Blocking 특성, Top-N·Index Order, Analytic Function과 Workarea·TEMP 검증을 다룹니다.

학습 목표

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

• SORT ORDER BY, SORT JOIN, WINDOW SORT의 목적을 구분한다.
• 일반적인 전체 Sort가 Blocking Operation인 이유를 설명한다.
• Sort 입력 A-Rows와 Row 폭을 이용해 작업량을 판단한다.
• 분석 함수의 논리적 처리 위치와 최종 ORDER BY의 차이를 설명한다.
• 여러 Analytic Function이 Sort를 공유할 수 있는 조건을 설명한다.
• WINDOW NOSORT, WINDOW SORT PUSHED RANK, STOPKEY의 의미를 설명한다.
• Index Order가 Sort를 생략할 수 있는 조건과 Random Access Trade-off를 설명한다.
• Top-N 대상 Grain과 결정적 ORDER BY를 설명한다.
• OPTIMAL·ONE PASS·MULTI-PASS Workarea를 구분한다.
• V$SQL_WORKAREA·DBMS_XPLAN.DISPLAY_CURSOR로 Memory·TEMP를 검증한다.
• SORT AGGREGATE와 일반 Row Sort를 구분한다.
• 변경 전후 결과 순서·A-Rows·Buffers·TEMP·응답시간을 비교한다.

1. Sort Operation의 목적을 먼저 구분한다

Sort 튜닝은 Operation 이름과 목적을 연결하는 것에서 시작합니다.

주의합니다.

SORT AGGREGATE
  ≠ 여러 행을 결과 순서로 정렬하는 일반 Sort

BUFFER SORT
  ≠ ORDER BY Sort

Operation 이름만 보고 “Sort가 있으니 TEMP를 많이 쓴다”고 단정하지 않습니다.

2. Sort가 Blocking Operation인 이유

최솟값부터 전체 결과를 반환하려는 Sort를 생각합니다.

첫 번째 입력 Row 확인
→ 뒤에 더 작은 값이 있을 수 있음

전체 후보 확인
→ 최종 순서 확정
→ 첫 행 반환

일반적인 전체 Sort는 많은 입력을 받은 뒤에야 정렬된 첫 Row를 위쪽 Operation으로 보낼 수 있으므로 Blocking 성격을 가집니다.

2.1 예외·변형

• Index가 이미 필요한 순서를 제공하면 Sort 생략 가능
• Top-N Sort는 전체 Row를 모두 Memory에 보관하지 않고 상위 N개 후보 중심으로 처리 가능
• STOPKEY는 필요한 Row 수를 채우면 하위 처리 중단 가능
• WINDOW NOSORT는 입력 순서를 그대로 사용 가능
• SORT AGGREGATE는 일반 정렬과 다른 Aggregate Operation

따라서 Blocking 여부는 Operation과 SQL 요구를 함께 봅니다.

3. SORT ORDER BY

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

Index가 순서를 제공하지 못하면 다음 Plan이 나타날 수 있습니다.

SORT ORDER BY
  TABLE ACCESS FULL ORDERS

흐름입니다.

ORDERS Access
→ status Filter
→ 통과 Row를 order_date DESC, order_id DESC로 Sort
→ 정렬 결과 반환

3.1 Sort 입력은 하위 A-Rows

ORDERS 원본 100,000,000행
→ status Filter 후 200,000행
→ SORT ORDER BY 입력 200,000행

실제 Sort 입력은 Table 전체가 아니라 하위 Row Source의 출력입니다.

3.2 Row 폭과 Payload

Sort Workarea는 Sort Key뿐 아니라 상위 Operation에 전달해야 하는 Row Payload도 처리할 수 있습니다.

200,000행 × 24Byte
vs
200,000행 × 1,000Byte

같은 행 수라도 후자가 Memory·TEMP·CPU Copy 비용이 큽니다.

가능한 대안입니다.

• 불필요한 Column 제거
• 큰 LOB·긴 문자열을 Sort 뒤에 Join
• Sort 전 선집계
• Filter Pushdown
• Projection 확인

Optimizer Transformation으로 View가 Merge되면 작성한 Inline View 경계대로 Projection이 유지되지 않을 수 있으므로 실제 Plan의 Bytes·Projection을 확인합니다.

4. 최종 결과 순서와 내부 순서

SQL 결과 순서는 마지막 ORDER BY로만 보장합니다.

다음은 결과 순서를 보장하지 않습니다.

• Index Range Scan
• Index Full Scan
• Merge Join
• Window Sort
• 현재 Plan에서 우연히 관찰되는 순서
• Analytic Function의 내부 ORDER BY

예시입니다.

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

Analytic Function은 부서 안의 급여 순서로 rn을 계산하지만 Client에 반환되는 전체 Row 순서를 보장하지 않습니다.

최종 출력 순서를 명시합니다.

ORDER BY department_id,
         salary DESC,
         employee_id

employee_id 같은 Unique Tie-Breaker를 포함해야 Top-N이나 Pagination 결과가 재실행에서도 안정적입니다.

5. SORT JOIN

Sort Merge Join은 입력을 Join Key 순서로 준비한 뒤 병합합니다.

MERGE JOIN
  SORT JOIN
    Row Source 1
  SORT JOIN
    Row Source 2

Oracle 공식 Join 동작에서 첫 번째 입력이 이미 Join Key 순서를 제공하면 첫 번째 Sort를 생략할 수 있습니다. 두 번째 입력은 일반적으로 SORT JOIN으로 준비됩니다.

예시입니다.

MERGE JOIN
  TABLE ACCESS BY INDEX ROWID DEPARTMENTS
    INDEX FULL SCAN DEPT_ID_PK
  SORT JOIN
    TABLE ACCESS FULL EMPLOYEES

5.1 Sort 생략 Trade-off

Index 순서 활용
  → SORT JOIN 하나 생략

하지만
  → 많은 Single Block I/O
  → ROWID Table Access
  → 불리한 Clustering Factor

다음 Plan이 더 빠를 수 있습니다.

Full Scan
→ Multiblock Read
→ Workarea Sort

비교합니다.

• Buffers·Reads
• Sort A-Rows
• TEMP Spill
• 전체 Elapsed
• First Row·Full Fetch
• 동시 Workarea

SORT JOIN은 Join Algorithm 내부 순서일 뿐 최종 결과 순서를 보장하지 않습니다.

6. WINDOW SORT와 Analytic Function

Analytic Function은 Query Result의 Row Group인 Window를 대상으로 계산합니다.

Oracle의 논리적 처리 순서에서 Analytic Function은 일반적으로 다음 Operation 이후에 계산됩니다.

FROM·JOIN
→ WHERE
→ GROUP BY
→ HAVING
→ Analytic Function
→ 최종 ORDER BY

따라서 Analytic Function을 Filter하려면 Subquery·CTE 또는 QUALIFY 등을 이용해 계산 후 조건을 적용합니다.

예시입니다.

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

필요한 논리 순서입니다.

department_id별 Partition
→ salary DESC, employee_id 순서
→ ROW_NUMBER 계산

Plan에 다음이 나타날 수 있습니다.

WINDOW SORT
  TABLE ACCESS FULL EMPLOYEES

6.1 입력 Row 줄이기

WHERE employment_status='ACTIVE'

이 Predicate가 Analytic Function 이전 Row Source에서 적용되면 WINDOW SORT 입력도 줄어듭니다.

단, Analytic Function 계산 대상 전체 집합을 변경하면 결과가 달라질 수 있으므로 Filter 위치의 업무 의미를 확인합니다.

7. 여러 Analytic Function과 Sort 공유

다음 두 Analytic Function은 같은 Partition·Order 사양을 사용합니다.

ROW_NUMBER() OVER (
  PARTITION BY department_id
  ORDER BY salary DESC, employee_id
)

SUM(salary) OVER (
  PARTITION BY department_id
  ORDER BY salary DESC, employee_id
  ROWS UNBOUNDED PRECEDING
)

Oracle은 호환되는 순서의 Row Source를 여러 Window 계산에 활용할 수 있습니다.

Sort 공유 가능성을 높이는 조건입니다.

• 같은 PARTITION BY
• 같은 ORDER BY Column
• 같은 Ascending·Descending 방향
• 같은 NULLS FIRST·LAST 요구
• 같은 Query Block에서 계산 가능
• Frame이 같은 정렬 흐름으로 계산 가능

다음은 별도 Sort가 필요할 가능성이 큽니다.

PARTITION BY department_id ORDER BY salary DESC
PARTITION BY job_id        ORDER BY hire_date ASC

주의합니다.

Sort 공유를 위해 업무 계산 기준을 임의로 통일
→ 분석 결과가 달라질 수 있음

먼저 결과 의미를 확인합니다.

8. WINDOW NOSORT·PUSHED RANK·STOPKEY

8.1 WINDOW NOSORT

입력이 이미 Analytic Function에 필요한 순서를 제공하면 WINDOW NOSORT가 나타날 수 있습니다.

WINDOW NOSORT
  INDEX RANGE SCAN EMP_DEPT_SAL_IX

후보 Index입니다.

(department_id, salary DESC, employee_id)

조건입니다.

• Partition Key·Order Key 순서가 Index와 호환
• 선두 Column 조건과 Scan 범위가 순서를 깨지 않음
• 중간 Operation이 순서를 보존
• 실제 Plan에서 Sort Operation이 없음

8.2 WINDOW SORT PUSHED RANK

Top-N·Rank Filter를 Analytic Function과 가까운 단계에서 처리하도록 변환되면 다음 Operation이 나타날 수 있습니다.

WINDOW SORT PUSHED RANK

Operation 이름만으로 읽은 Row가 N개뿐이라고 단정하지 않습니다.

• 하위 A-Rows
• Window Sort A-Rows
• Rank Filter 결과
• Buffers·TEMP
• Query Transformation Note

를 확인합니다.

8.3 STOPKEY

다음 SQL은 필요한 Row 수를 명시합니다.

ORDER BY order_date DESC,
         order_id DESC
FETCH FIRST 20 ROWS ONLY;

Plan에 다음이 나타날 수 있습니다.

SORT ORDER BY STOPKEY
COUNT STOPKEY
WINDOW NOSORT STOPKEY

Sort·Access Path가 필요한 20개 Row를 채운 뒤 조기 중단할 수 있는지 확인합니다.

9. Top-N 대상 Grain

다음 SQL은 Join 후 최종 Row 20개를 의미합니다.

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

1:N Join이라면 주문 20건이 아니라 주문상품 조합 20행일 수 있습니다.

주문 20건을 먼저 고른 뒤 Detail을 붙이면 결과가 달라집니다.

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

주문 20건
→ 각 주문의 모든 Detail
→ 최종 Row 20 초과 가능

Sort를 앞 단계로 이동하기 전에 대상 Grain을 확인합니다.

10. Sort를 줄이는 방법

10.1 입력 행 수 줄이기

• 선택적 WHERE Predicate
• Partition Pruning
• 불필요한 Join 제거
• 의미가 같은 선집계
• View·Subquery Predicate Pushdown

10.2 Row 폭 줄이기

• 필요한 Column만 전달
• LOB·큰 문자열을 Sort 뒤에 Join
• 불필요한 Expression 제거
• Covering Index의 Projection 활용

10.3 Top-N 사용

업무가 전체 정렬이 아니라 앞쪽 N건만 필요하면 FETCH FIRST, ROWNUM, Analytic Rank Filter를 올바른 Query Block에 배치합니다.

10.4 Index Order 활용

Predicate와 정렬을 함께 지원하는 Index가 있으면 Sort 생략을 검토합니다.

Trade-off입니다.

• Index Single Block I/O
• Table by ROWID
• Clustering Factor
• Index 폭·DML 비용
• 전체 Fetch 여부

10.5 Analytic Function 사양 정리

동일한 업무 Window 사양이 표현 차이 때문에 불필요하게 분리돼 있는지 확인합니다.

11. Workarea와 TEMP

Sort는 SQL Workarea를 사용합니다.

V$SQL_WORKAREA의 주요 Column입니다.

• OPERATION_TYPE
• OPERATION_ID
• ESTIMATED_OPTIMAL_SIZE
• ESTIMATED_ONEPASS_SIZE
• LAST_MEMORY_USED
• LAST_EXECUTION
• LAST_TEMPSEG_SIZE
• MAX_TEMPSEG_SIZE
• OPTIMAL_EXECUTIONS
• ONEPASS_EXECUTIONS
• MULTIPASSES_EXECUTIONS

OPERATION_ID를 V$SQL_PLAN과 연결해 어떤 Sort Operation의 Workarea인지 식별합니다.

SELECT sql_id,
       child_number,
       operation_id,
       operation_type,
       estimated_optimal_size,
       estimated_onepass_size,
       last_memory_used,
       last_execution,
       last_tempseg_size
FROM   v$sql_workarea
WHERE  sql_id=:sql_id
AND    child_number=:child_number
ORDER BY operation_id;

11.1 TEMP가 없다고 Sort가 싸다는 뜻은 아니다

OPTIMAL Sort도 다음 비용을 가질 수 있습니다.

• 큰 CPU 비교·Copy
• 많은 Logical I/O로 입력 생성
• 긴 Blocking 시간
• 높은 동시 PGA 사용

반대로 ONE PASS Sort가 대량 Index Random Access보다 전체적으로 빠를 수도 있습니다.

12. 실행계획 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       ...
FROM   ...;

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

DBMS_XPLAN의 MEMSTATS는 Sort·Hash 등 Memory-Intensive Operation의 Memory 사용과 Disk Spill 정보를 표시할 수 있습니다.

확인 순서입니다.

1. Sort Operation 종류와 목적을 구분한다.
2. Sort 바로 아래 자식 A-Rows를 확인한다.
3. E-Rows와 A-Rows의 최초 큰 차이를 찾는다.
4. Plan Bytes·Projection으로 Row 폭을 확인한다.
5. OMem·1Mem·Used-Mem·Used-Tmp를 확인한다.
6. V$SQL_WORKAREA의 LAST_EXECUTION·TEMP를 확인한다.
7. Index Order·Top-N 대안의 Buffers·Reads를 비교한다.
8. 여러 WINDOW SORT의 사양이 실제로 다른지 확인한다.
9. 최종 ORDER BY와 결과 정합성을 확인한다.
10. 다른 Bind·동시 실행·Full Fetch 회귀를 확인한다.

상위 Operation의 A-Time·Buffers는 하위 작업을 포함할 수 있으므로 모든 Line을 단순 합산하지 않습니다.

13. Sort 변경 전후 검증 항목

자주 혼동하는 판단