Oracle 실행 계획의 Sort 오퍼레이션 완전 정리

실행 계획을 보다 보면 SORT AGGREGATE, SORT ORDER BY, SORT GROUP BY, SORT UNIQUE와 같은 Sort 오퍼레이션을 자주 볼 수 있다.

모두 이름에 Sort가 들어가지만 역할은 서로 다르다. 어떤 오퍼레이션은 실제 정렬을 수행하고, 어떤 오퍼레이션은 이름과 달리 정렬을 수행하지 않는다.

이번 글에서는 각 Sort 오퍼레이션이 언제 발생하는지와 내부 동작을 그림과 함께 정리해보자.

---

1. SORT AGGREGATE

전체 로우를 대상으로 하나의 집계 결과를 만들 때 발생한다.

이름에 Sort가 포함되어 있지만 실제 정렬은 수행하지 않는다.

SELECT SUM(sal),
       MAX(sal),
       MIN(sal)
FROM emp;

동작 흐름

EMP
 │
 ▼
모든 Row 읽기
 │
 ▼
SUM / MAX / MIN 계산
 │
 ▼
결과 1건 반환

---

2. SORT ORDER BY

ORDER BY로 결과를 정렬할 때 발생한다.

SELECT *
FROM emp
ORDER BY sal DESC;

동작 흐름

테이블 읽기
    │
    ▼

5000
3000
4000
1000
2000

    │
    ▼

SORT ORDER BY

    │
    ▼

5000
4000
3000
2000
1000

---

3. SORT GROUP BY

소팅 알고리즘을 이용해 같은 그룹을 모은 뒤 집계를 수행한다.

SELECT deptno,
       job,
       SUM(sal)
FROM emp
GROUP BY deptno, job
ORDER BY deptno, job;

동작 흐름

테이블 읽기
      │
      ▼

10 CLERK
20 MANAGER
10 CLERK
30 SALESMAN
20 MANAGER

      │
      ▼

(deptno, job) 기준 정렬

10 CLERK
10 CLERK
20 MANAGER
20 MANAGER
30 SALESMAN

      │
      ▼

연속된 같은 그룹 집계

10 CLERK    SUM
20 MANAGER  SUM
30 SALESMAN SUM

HASH GROUP BY와 비교

10gR2부터는 ORDER BY가 없다면 HASH GROUP BY가 선택되는 경우가 많다.

SORT GROUP BY가 먼저 정렬한 뒤 그룹을 찾는다면, HASH GROUP BY는 Hash Bucket을 이용해 그룹을 바로 찾아간다.

입력 데이터

10 1000
20 2000
10 1500
30 3000
20 1800

        │
        ▼

   Hash Function

        │
        ▼

+----------------------+
| Bucket #1            |
| dept=10              |
| SUM=2500             |
+----------------------+

+----------------------+
| Bucket #2            |
| dept=20              |
| SUM=3800             |
+----------------------+

+----------------------+
| Bucket #3            |
| dept=30              |
| SUM=3000             |
+----------------------+

새로운 Row인 (20, 500)을 읽으면 다음과 같이 처리된다.

(20, 500)
     │
     ▼

Hash(20)

     │
     ▼

Bucket #2 발견

     │
     ▼

SUM = 3800

     │
     ▼

SUM = 4300

즉, 같은 그룹을 찾기 위해 정렬하지 않고 Hash Bucket을 찾아 집계값을 계속 갱신하는 방식이다.

---

4. SORT UNIQUE

중복 제거가 필요한 경우 발생한다.

대표적인 경우는 다음과 같다.

• Unnesting된 서브쿼리의 중복 제거
• UNION / MINUS / INTERSECT
• DISTINCT

PK, UNIQUE 제약 또는 UNIQUE Index로 중복이 이미 보장된다면 SORT UNIQUE는 생략될 수 있다.

SELECT DISTINCT deptno
FROM emp
ORDER BY deptno;

SORT UNIQUE 내부 동작

                 테이블 읽기
                      │
                      ▼

      30 10 20 10 40 20 30

                      │
                      ▼

      PGA Sort Area에 데이터 적재

                      │
                      ▼

              오름차순 정렬

      10 10 20 20 30 30 40

                      │
                      ▼

      이전 Row와 비교하며 중복 제거

      10  ✓
      10  ✗
      20  ✓
      20  ✗
      30  ✓
      30  ✗
      40  ✓

                      │
                      ▼

          최종 결과

      10 20 30 40

HASH UNIQUE

10gR2부터 ORDER BY가 없는 DISTINCT는 HASH UNIQUE가 선택되는 경우가 많다.

                 테이블 읽기
                      │
                      ▼

      30 10 20 10 40 20 30

                      │
                      ▼

          Hash Function 계산

                      │
                      ▼

+-------------------------+
| Bucket1 : 20           |
| Bucket2 : 10           |
| Bucket3 : 30           |
| Bucket4 : 40           |
+-------------------------+

      ▲
      │
이미 존재하면 버림
없으면 Bucket에 저장

      │
      ▼

      최종 결과

      20 10 30 40

Hash Unique는 정렬 과정이 없기 때문에 결과 순서가 중요하지 않고, DISTINCT 대상 컬럼 수가 적으며 메모리가 충분한 경우 유리하다.

---

5. SORT JOIN

Sort Merge Join을 수행할 때 발생한다.

SELECT /*+ ordered use_merge(e) */ *
FROM dept d,
     emp e
WHERE d.deptno = e.deptno;

동작 흐름

DEPT 정렬
      │
      ├────────────┐
      │            │
EMP 정렬           │
      │            │
      ▼            ▼

    Merge Join

---

6. WINDOW SORT

분석 함수를 수행할 때 발생한다.

SELECT empno,
       AVG(sal) OVER(PARTITION BY deptno)
FROM emp;

동작 흐름

테이블 읽기
      │
      ▼

PARTITION 생성

      │
      ▼

PARTITION 내부 정렬

      │
      ▼

Window Function 계산

---

정리

오퍼레이션	실제 정렬	역할
SORT AGGREGATE	❌	전체 집계
SORT ORDER BY	✅	결과 정렬
SORT GROUP BY	✅	정렬 후 그룹 집계
HASH GROUP BY	❌	Hash Bucket으로 그룹 집계
SORT UNIQUE	✅	정렬 후 중복 제거
HASH UNIQUE	❌	Hash Table로 중복 제거
SORT JOIN	✅	Sort Merge Join
WINDOW SORT	대부분 ✅	분석 함수 처리