SUM 집계를 SUM OVER 분석함수로 변환

게시 2026/04/30

By 반지현

읽는 시간 11분

메인쿼리와 inline view 가 같은 테이블을 같은 구간으로 두 번 ACCESS 하는 패턴은 분석함수 SUM(SUM(...)) OVER (PARTITION BY ...) 로 1회 액세스로 통합할 수 있습니다.
옵티마이저가 자동으로 해 주는 변환이 아니라 SQL 을 다시 써야 하는 패턴입니다.

핵심 정리

같은 데이터를 두 번 ACCESS 하는 패턴: 메인쿼리와 inline view 가 같은 구간 (ORDER_DATE BETWEEN ...) 을 읽어도 옵티마이저는 두 액세스를 하나로 합치지 못합니다. inline view 의 GROUP BY 집계 단위 (YYYYMM × employee) 가 메인의 집계 단위 (YYYYMMDD × employee) 와 다르기 때문에 각각 별도 result set 을 만들어 HASH JOIN 으로 합치게 됩니다.
분석함수의 본질: SUM(SUM(x)) OVER (PARTITION BY ...) 는 GROUP BY 가 만든 1차 집계 위에 PARTITION 단위 2차 누적을 한 번에 수행합니다. 같은 row 에 일별 합계와 월합계를 동시에 만들 수 있어 self-join 형 inline view 자체가 사라집니다.
성능 보증: ORDERS 액세스가 1 회로 줄어 Buffers 가 정확히 절반 (39,238 → 19,619) 으로 떨어지고 HASH JOIN 단계도 사라집니다. 단 이 변환은 옵티마이저 자동이 아니므로 패턴을 알고 의식적으로 적용해야 합니다.

시나리오

ORDERS 대용량 테이블에서 1 년치 (2012-01-01 ~ 2012-12-31) 주문을 일별·사원별로 집계하면서, 같은 row 안에 해당 월의 사원별 월합계도 같이 출력 하는 쿼리입니다.

항목	값
데이터 범위	2007-01-01 ~ 2012-12-31
총 건수	3,000,000 건
총 BLOCK 수	19,791
인덱스	`IX_ORDERS_N1 (ORDER_DATE)`
조회 구간	2012-01-01 ~ 2012-12-31 (1 년치, 약 500K rows)
출력 단위	일별 (`YYYYMMDD`) × 사원별 합계 + 같은 row 에 해당 월합계

1) 원본 — Inline view 로 월합계 산출

inline view (별칭 B) 가 YYYYMM × EMPLOYEE_ID 단위로 월합계를 만들고, 메인쿼리 (A) 가 일별 합계를 만든 뒤 두 결과를 YYYYMM + EMPLOYEE_ID 키로 join 하여 같은 row 에 월합계 컬럼을 붙입니다.

SELECT TO_CHAR(A.ORDER_DATE, 'YYYYMMDD') ORDER_DATE
     , A.EMPLOYEE_ID
     , SUM(A.ORDER_TOTAL)         ORDER_TOTAL
     , MAX(B.ORDER_TOTAL)         MM_ORDER_TOTAL
  FROM ORDERS A,
       (SELECT TO_CHAR(ORDER_DATE, 'YYYYMM') ORDER_MM
             , EMPLOYEE_ID
             , SUM(ORDER_TOTAL) ORDER_TOTAL
          FROM ORDERS
         WHERE ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
           AND ORDER_DATE <  TO_DATE('20130101', 'YYYYMMDD')
         GROUP BY TO_CHAR(ORDER_DATE, 'YYYYMM')
                , EMPLOYEE_ID) B
 WHERE TO_CHAR(A.ORDER_DATE, 'YYYYMM') = B.ORDER_MM
   AND A.EMPLOYEE_ID                   = B.EMPLOYEE_ID
   AND A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
   AND A.ORDER_DATE <  TO_DATE('20130101', 'YYYYMMDD')
 GROUP BY TO_CHAR(ORDER_DATE, 'YYYYMMDD')
        , A.EMPLOYEE_ID;

inline view 안의 WHERE ORDER_DATE >= ... AND ORDER_DATE < ... 구간이 메인쿼리의 동일 구간과 정확히 일치하지만, 옵티마이저는 두 액세스를 하나로 머지하지 못합니다.

------------------------------------------------------------------------------------------------
| Id | Operation              | Name      | Starts | A-Time      | A-Rows | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |           |      1 | 00:00:00.76 |   206K |   39238 |          |
|  1 |  HASH GROUP BY         |           |      1 | 00:00:00.76 |   206K |   39238 |   81M (0)|
|* 2 |   HASH JOIN            |           |      1 | 00:00:00.47 |   506K |   39238 |   24M (0)|
|  3 |    VIEW                | VW_NSO_1  |      1 | 00:00:00.16 |   7704 |   19619 |          |
|  4 |     HASH GROUP BY      |           |      1 | 00:00:00.16 |   7704 |   19619 | 1814K (0)|
|* 5 |      TABLE ACCESS FULL | ORDERS    |      1 | 00:00:00.08 |   500K |   19619 |          |  ← Inline view 의 ORDERS 풀 스캔
|* 6 |    TABLE ACCESS FULL   | ORDERS    |      1 | 00:00:00.10 |   500K |   19619 |          |  ← 메인쿼리의 ORDERS 풀 스캔
------------------------------------------------------------------------------------------------

Id 5 와 Id 6 가 같은 ORDERS 테이블 같은 구간을 두 번 풀 스캔 한 결과입니다. Buffers 합계 39,238 = 19,619 × 2 가 정확히 두 액세스의 합. 그 위에 Id 2 HASH JOIN 까지 추가 메모리 24 M 를 소비합니다.

2) 수정 — SUM OVER 분석함수로 통합

inline view 를 제거하고 메인쿼리의 SELECT 절에 SUM(SUM(A.ORDER_TOTAL)) OVER (PARTITION BY ... ) 를 추가합니다. 외측 SUM 이 분석함수, 내측 SUM 이 GROUP BY 의 일반 집계 역할을 합니다.

SELECT TO_CHAR(A.ORDER_DATE, 'YYYYMMDD') ORDER_DATE
     , A.EMPLOYEE_ID
     , SUM(A.ORDER_TOTAL)                                              ORDER_TOTAL
     , SUM(SUM(A.ORDER_TOTAL)) OVER (PARTITION BY TO_CHAR(ORDER_DATE, 'YYYYMM')
                                                , EMPLOYEE_ID)         MM_ORDER_TOTAL
  FROM ORDERS A
 WHERE A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
   AND A.ORDER_DATE <  TO_DATE('20130101', 'YYYYMMDD')
 GROUP BY TO_CHAR(ORDER_DATE, 'YYYYMMDD')
        , A.EMPLOYEE_ID
        , TO_CHAR(ORDER_DATE, 'YYYYMM');

GROUP BY 절에 TO_CHAR(ORDER_DATE, 'YYYYMM') 가 추가된 점이 중요합니다. PARTITION BY 절에서 참조하는 컬럼이 GROUP BY 에 존재해야 하므로 같이 그룹핑해 둡니다 (일별 그룹과 동일한 단위라 row 수에는 영향 없음).

------------------------------------------------------------------------------
| Id | Operation             | Name   | Starts | A-Rows | Buffers | Used-Mem |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |        |      1 |   206K |   19619 |          |
|  1 |  WINDOW BUFFER        |        |      1 |   206K |   19619 |   11M (0)|
|  2 |   SORT GROUP BY       |        |      1 |   206K |   19619 |   18M (0)|
|* 3 |    TABLE ACCESS FULL  | ORDERS |      1 |   500K |   19619 |          |
------------------------------------------------------------------------------

Id 3 의 ORDERS 풀 스캔이 단 1 회 (Buffers 19,619). HASH JOIN 자체가 사라졌고, Id 1 WINDOW BUFFER 가 GROUP BY 결과 위에 PARTITION 단위 누적을 처리합니다.

분석

왜 Inline view 방식은 ORDERS 를 두 번 읽는가

옵티마이저가 inline view 와 메인쿼리의 WHERE 구간이 동일한 것을 인지하더라도 두 액세스를 하나로 머지하지 못하는 이유는 두 쿼리 블록의 집계 단위가 서로 다르기 때문입니다.

inline view (B): YYYYMM × EMPLOYEE_ID 단위로 1 회 집계
메인쿼리 (A): YYYYMMDD × EMPLOYEE_ID 단위로 1 회 집계

두 집계 결과는 row 수도 다르고 (월합계 7,704 vs 일별 합계 506K) 키도 다르기 때문에 옵티마이저는 각각 별도 result set 을 만들어 YYYYMM + EMPLOYEE_ID 키로 HASH JOIN 할 수밖에 없습니다. 그래서 같은 ORDERS 를 두 번 풀 스캔하는 비용이 발생합니다.

`SUM(SUM(...)) OVER` 의 이중 집계 메커니즘

분석함수 표기 SUM(SUM(A.ORDER_TOTAL)) OVER (PARTITION BY ...) 의 핵심은 두 SUM 의 역할이 다르다는 점입니다.

내측 SUM(A.ORDER_TOTAL): GROUP BY 절이 만든 일별 (YYYYMMDD × EMPLOYEE_ID) 단위의 일반 집계
외측 SUM(...) + OVER (PARTITION BY YYYYMM, EMPLOYEE_ID): 일별 합계 row 들을 월합계 단위로 partition 하여 누적하는 분석함수

실행 순서로 보면 다음 흐름입니다.

Id 3  TABLE ACCESS FULL ORDERS    → 500K rows (1년치 raw)
Id 2  SORT GROUP BY                → 206K rows (일별 × employee 합계 = 내측 SUM)
Id 1  WINDOW BUFFER                → 206K rows (월×employee partition 으로 누적 = 외측 SUM)
Id 0  SELECT STATEMENT             → 같은 row 에 일별 합계 + 월합계 동시 출력

self-join 도 inline view 도 없이 한 번의 풀 스캔과 한 번의 그룹핑, 한 번의 윈도우 누적으로 끝납니다.

비교 요약

항목	원본 (Inline view)	수정 (분석함수)	개선
ORDERS 액세스	풀 스캔 × 2	풀 스캔 × 1	-50%
Buffers	39,238	19,619	-50%
단계 수	6 (FULL × 2 + HASH JOIN + HGB × 2 + VIEW)	3 (FULL + SORT GB + WINDOW BUFFER)	-50%
피크 메모리	81 M (HGB) + 24 M (HJ)	18 M (SGB) + 11 M (WB)	-65%
HASH JOIN	필요	제거	✅

같은 데이터를 다른 단위로 두 번 집계해야 하는 경우, inline view + 조인보다 분석함수의 이중 집계가 거의 항상 더 가볍습니다.
단, 옵티마이저는 이 변환을 자동으로 해 주지 않으니 작성자가 패턴을 알고 의식적으로 적용해야 합니다.

정리

메인쿼리와 같은 구간을 inline view 로 다시 ACCESS 하는 패턴은 진단 시그널: 실행계획에서 같은 테이블이 두 번 등장하고 그 위에 HASH JOIN 이 보이면 분석함수로 변환할 가능성이 큽니다.
SUM(SUM(x)) OVER (PARTITION BY ...) 는 GROUP BY 결과 위에 추가 집계 컬럼을 동시에 생성 합니다. self-join 없이 한 row 에 일별 합계와 월합계를 같이 만들 수 있습니다.
분석함수 변환은 옵티마이저 자동이 아니라 SQL 재작성입니다. UNNEST / NO_UNNEST 같은 힌트로 유도되지 않으므로 패턴을 알고 있어야 적용할 수 있는 변환입니다.

SQL Tuning, 케이스 스터디