집계와 중복 제거: SORT·HASH Operation 비교

핵심 요약

GROUP BY, DISTINCT, UNION은 입력 행을 Group별 결과로 축약하거나 중복 Key를 제거합니다. Oracle은 SQL 의미, 입력 순서, 예상 Group 수, Workarea Memory, 후속 정렬 요구 등을 고려해 Sort·Hash·NOSORT 방식을 선택할 수 있습니다.

대표 Operation입니다.

SORT AGGREGATE
→ GROUP BY 없는 전체 Aggregate 상태를 누적
→ 보통 결과 1행

SORT GROUP BY
→ Group Key 순서로 입력을 정리하며 Group별 집계

HASH GROUP BY
→ Group Key별 Aggregate 상태를 Hash Table에 누적

SORT GROUP BY NOSORT
→ 입력이 이미 Group Key 순서이면 별도 Sort 생략 가능

SORT UNIQUE
→ 중복 제거 Key로 정렬해 같은 Key를 하나로 축약

HASH UNIQUE
→ Hash 구조에서 이미 본 Key를 확인하며 중복 제거

가장 중요한 주의사항

Operation 이름에 SORT가 포함됐다는 이유만으로 모든 입력 행을 사용자 출력 순서로 정렬했다고 해석하면 안 됩니다. 특히 SORT AGGREGATE는 일반적인 SORT ORDER BY와 목적이 다릅니다.

1. 전체 Aggregate와 Group Aggregate

1.1 GROUP BY 없는 전체 Aggregate

SELECT COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM   orders
WHERE  order_date >= DATE '2026-01-01';

조건을 만족한 전체 입력이 하나의 Aggregate 집합입니다.

가능한 실행 구조:

SORT AGGREGATE
  TABLE ACCESS FULL ORDERS

SORT AGGREGATE는 Aggregate 상태를 누적해 전체 결과를 계산하는 Operation입니다. 이름의 SORT를 ORDER BY용 전체 정렬로 해석하지 않습니다.

입력이 0행이어도 GROUP BY가 없는 Aggregate Query는 결과 1행을 반환합니다.

Oracle Aggregate Function은 COUNT(*), GROUPING, GROUPING_ID 등을 제외하면 일반적으로 NULL을 무시합니다. COUNT(expr)는 Non-NULL 표현식만 계산합니다.

1.2 GROUP BY가 있는 Group Aggregate

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM   orders
GROUP BY customer_id;

각 customer_id Group마다 결과 행을 만듭니다.

SORT GROUP BY
HASH GROUP BY
SORT GROUP BY NOSORT

입력이 0행이면 생성할 Group이 없으므로 결과도 0행입니다.

GROUP BY 없음 + Aggregate + 빈 입력
→ Aggregate 결과 1행

GROUP BY 있음 + 빈 입력
→ Group이 없으므로 0행

2. SORT GROUP BY

Sort 방식은 Group Key가 같은 행을 연속 배치한 뒤 Key가 바뀌는 지점에서 이전 Group 결과를 확정합니다.

입력
C3, C1, C2, C1, C3

Group Key Sort
C1, C1, C2, C3, C3

Group 결과
C1 2건
C2 1건
C3 2건

주요 비용입니다.

• Sort 입력 행 수
• Group Key와 전달 Payload 폭
• 비교·정렬 CPU
• Sort Workarea Memory
• Memory 부족 시 TEMP Run 생성
• 후속 ORDER BY와 정렬 Key 호환 여부

Group 수가 적더라도 입력이 매우 많고 Row 폭이 넓으면 Sort Workarea가 커질 수 있습니다.

3. HASH GROUP BY

Hash 방식은 Group Key에 Hash 함수를 적용해 각 Group의 Aggregate 상태를 Hash Table에 저장합니다.

입력 Row
→ Group Key Hash 계산
→ Bucket에서 기존 Group 찾기
→ COUNT·SUM·MIN·MAX 상태 갱신
→ Group 결과 반환

Hash Table 크기는 단순 입력 행 수보다 다음 값에 크게 영향을 받습니다.

• 실제 Group 수
• Group Key 폭
• Group별 Aggregate 상태 크기
• Hash Collision과 Data Skew
• 병렬 실행 Server별 분포
• 사용 가능한 Workarea Memory

Memory가 충분하면 입력 전체를 Group Key 순서로 정렬하지 않고 집계할 수 있습니다. 하지만 실제 Group 수가 예상보다 많으면 Hash Partition이 TEMP로 Spill할 수 있습니다.

4. SORT와 HASH GROUP BY 비교

Optimizer는 예상 입력 행 수, Group Cardinality, Row 폭, Memory, 입력 순서와 후속 Operation을 함께 평가합니다. HASH GROUP BY가 항상 빠르다, SORT GROUP BY가 항상 느리다처럼 단정하지 않습니다.

5. GROUP BY 결과 순서는 보장되지 않는다

SELECT customer_id,
       SUM(amount)
FROM   orders
GROUP BY customer_id;

현재 실행계획이 SORT GROUP BY라면 결과가 customer_id 순서처럼 보일 수 있습니다. 그러나 SQL 의미상 순서는 보장되지 않습니다.

다음 변화로 순서가 달라질 수 있습니다.

• HASH GROUP BY 선택
• 병렬 실행
• Query Transformation
• 부분 집계와 최종 집계
• 다른 Access Path
• Oracle 버전·통계 변화

정렬 결과가 필요하면 명시합니다.

SELECT customer_id,
       SUM(amount) AS total_amount
FROM   orders
GROUP BY customer_id
ORDER BY customer_id;

GROUP BY와 ORDER BY의 Key·방향이 호환되더라도 실행계획에서 실제 Sort 횟수를 확인해야 합니다.

6. SORT GROUP BY NOSORT

입력이 이미 Group Key 순서이면 별도 정렬 없이 Group 경계를 인식할 수 있습니다.

CREATE INDEX orders_customer_ix
ON orders(customer_id);

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

가능한 실행 구조:

SORT GROUP BY NOSORT
  INDEX FULL SCAN ORDERS_CUSTOMER_IX

처리 흐름:

customer_id Index 순서 입력
→ 같은 Key가 연속
→ Key 변경 시 이전 Group 결과 확정
→ 별도 Sort 생략

NOSORT가 보인다는 사실만으로 최적이라고 판단하지 않습니다.

• Index Leaf Block 전체 Scan 비용
• 집계 Column이 Index에 없어 발생하는 ROWID Table Access
• Index Clustering과 Cache 효율
• Full Table Scan + Hash Group By 대안
• 중간 Operation이 순서를 유지하는지
• DML 때문에 추가 Index를 유지할 가치가 있는지

Sort를 생략했지만 수많은 Random Table Access가 발생하면 전체 성능은 더 나쁠 수 있습니다.

7. 중복 제거: DISTINCT·UNION·UNION ALL

7.1 DISTINCT

SELECT DISTINCT customer_id
FROM   orders;

중복 Key를 하나로 축약합니다.

가능한 Operation:

SORT UNIQUE
HASH UNIQUE

7.2 UNION

SELECT customer_id
FROM   online_orders
UNION
SELECT customer_id
FROM   offline_orders;

두 결과를 결합한 뒤 중복 Row를 제거합니다. 최종 출력 순서는 보장되지 않으며, 순서가 필요하면 전체 Set Query 뒤에 ORDER BY를 명시합니다.

7.3 UNION ALL

SELECT customer_id
FROM   online_orders
UNION ALL
SELECT customer_id
FROM   offline_orders;

중복을 보존하므로 전체 중복 제거 Workarea가 필요하지 않습니다.

다음 조건을 확인한 뒤에만 UNION을 UNION ALL로 변경합니다.

• 업무적으로 중복을 허용하는가?
• 두 분기가 상호 배타적인가?
• 중복 행이 결과 건수·금액을 왜곡하지 않는가?
• 후속 Query가 중복을 별도로 제거하지 않는가?

성능 개선만을 이유로 결과 의미를 바꾸면 안 됩니다.

8. SORT UNIQUE와 HASH UNIQUE

SORT UNIQUE

중복 제거 Key로 정렬
→ 같은 Key가 연속
→ 첫 Key만 남김

유리할 수 있는 상황:

• 입력이 이미 유사한 Key 순서
• 후속 Operation이 같은 순서를 요구
• Sort Workarea가 적절하고 Hash보다 Cost가 낮음

HASH UNIQUE

Key Hash 계산
→ Hash 구조에 이미 존재하는지 확인
→ 처음 본 Key만 저장

유리할 수 있는 상황:

• 최종 순서가 필요 없음
• Unique Key 집합이 Workarea에 적절히 들어감
• 대량 입력 정렬을 피하는 것이 유리함

두 방식 모두 목적은 중복 제거입니다. HASH UNIQUE 결과도 순서를 보장하지 않으며, SORT UNIQUE 결과 역시 SQL의 명시적 ORDER BY를 대신하지 않습니다.

입력이 PK·Unique Constraint로 이미 유일함을 Optimizer가 증명하면 별도의 Unique Operation이 제거될 수도 있습니다. 실행계획에서 실제 Operation 유무를 확인합니다.

9. NULL과 집계·중복 제거

GROUP BY와 DISTINCT

GROUP BY와 DISTINCT에서는 NULL들이 하나의 Group 또는 하나의 중복값처럼 축약됩니다.

입력:
C1
NULL
NULL
C1
C2

DISTINCT 결과:
C1
C2
NULL

이는 NULL = NULL이 TRUE라는 뜻이 아닙니다. Grouping·Duplicate Elimination의 SQL 의미입니다.

COUNT 구분

COUNT(*)                     → 모든 행 수
COUNT(customer_id)           → Non-NULL customer_id 행 수
COUNT(DISTINCT customer_id)  → 서로 다른 Non-NULL customer_id 수

SUM, MIN, MAX, AVG 등도 일반적으로 NULL 입력을 제외합니다. 모든 입력이 NULL이면 결과는 NULL입니다.

10. 불필요한 DISTINCT를 찾는다

다음 SQL은 Join으로 한 고객이 여러 주문 Row로 복제된 뒤 DISTINCT로 다시 줄입니다.

SELECT DISTINCT c.customer_id
FROM   customers c
JOIN   orders o
  ON   o.customer_id = c.customer_id
WHERE  o.order_date >= :start_date;

업무 요구가 “조건을 만족하는 주문이 존재하는 고객”이라면 EXISTS가 더 직접적일 수 있습니다.

SELECT c.customer_id
FROM   customers c
WHERE  EXISTS (
    SELECT 1
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
    AND    o.order_date >= :start_date
);

가능한 효과:

Join 결과 다중 복제 방지
→ 후속 SORT/HASH UNIQUE 입력 감소
→ Match 확인 후 Semi Join 형태로 처리 가능

다만 다음을 확인해야 합니다.

• 최종 결과 Grain이 같은가?
• 반환 Column이 왼쪽 Table에만 있는가?
• 오른쪽 Row 개수 자체가 결과 의미에 필요하지 않은가?
• Outer Join·NULL 보존 의미가 바뀌지 않는가?
• Optimizer가 이미 Semi Join으로 변환하는가?

DISTINCT를 보고 기계적으로 EXISTS로 바꾸지 않습니다.

11. 병렬 집계와 여러 Group By Operation

병렬 집계에서는 각 PX Server가 일부 입력을 먼저 집계하고, 재분배 후 최종 집계를 수행할 수 있습니다.

개념 구조:

PX Server별 부분 HASH GROUP BY
→ Group Key 기준 PX SEND HASH
→ 최종 HASH GROUP BY
→ QC로 결과 전달

실행계획에 Group By Operation이 두 번 보인다고 해서 반드시 불필요한 중복 집계는 아닙니다. 부분 집계가 Row 수를 줄여 Network 재분배량을 감소시키는지 확인합니다.

Data Skew가 크면 특정 Group Key가 한 PX Server에 몰려 Memory·TEMP·Elapsed Time 불균형이 발생할 수 있습니다.

12. Workarea와 TEMP Spill

Sort·Hash·Unique Operation은 Workarea를 사용합니다.

대표 실행 모드:

TEMP Spill이 발생하면 다음을 함께 점검합니다.

• 입력 행 수와 Row 폭
• 실제 Group·Distinct Key 수
• E-Rows와 A-Rows 차이
• 전달하지 않아도 되는 Column
• Predicate를 더 일찍 적용할 수 있는지
• Join 중복을 집계 전에 줄일 수 있는지
• PGA·Workarea 정책
• 병렬 Server 수와 Server별 Memory
• SQL을 강제로 Sort·Hash 방식으로 고정했는지

Memory를 무조건 크게 늘리는 것만이 해결책은 아닙니다. 입력 자체를 줄이고 Cardinality 추정을 개선하는 것이 먼저일 수 있습니다.

13. 실행계획과 Runtime 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       customer_id,
       SUM(amount)
FROM   orders
GROUP BY customer_id;

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

확인 항목:

ALLSTATS는 I/O와 Memory 통계를 함께 표시하는 형식이며, LAST는 마지막 실행 통계를 확인할 때 사용합니다. Runtime 통계가 수집되지 않았다면 실제 수치가 표시되지 않을 수 있습니다.

14. 혼동하기 쉬운 판단

15. 적용 판단 순서

1. 최종 결과 Grain과 Group·Distinct Key를 정의한다.
2. 전체 Aggregate·Group Aggregate·중복 제거를 구분한다.
3. 빈 입력과 NULL 결과 의미를 확인한다.
4. 입력 행 수와 실제 Group·Distinct Key 수를 추정한다.
5. Join이 불필요한 중복을 먼저 만들고 있는지 확인한다.
6. SORT·HASH·NOSORT의 전체 Access Cost를 비교한다.
7. 최종 ORDER BY 요구와 추가 Sort 여부를 확인한다.
8. Index 순서 이득과 Table Random Access를 비교한다.
9. Workarea 실행 모드와 TEMP Spill을 확인한다.
10. 병렬 부분 집계·재분배·Skew를 점검한다.
11. E-Rows·A-Rows·Buffers·Memory·TEMP를 변경 전후 비교한다.