구간별 집계 SQL의 DB 이식성 및 성능 최적화 과정

1. 문제 상황

매장에서 매월 고객의 월매출금액을 기준으로, 해당 고객이 어느 구간(Decile, 10등분)에 속하는지를 집계 해야하는 상황을 가정

예를 들어, 월 최대 매출금액이 20,000원이라면 이를 10개 구간으로 나누게 된다.

• 구간 1: 1 ~ 2,000원
• 구간 2: 2,001 ~ 4,000원
• 구간 3: 4,001 ~ 6,000원
• …
• 구간 10: 18,001 ~ 20,000원

결국 우리가 원하는 결과는 간단하다.

• 각 구간별 고객 수
• 각 구간별 고객 수가 전체에서 차지하는 비율(%)

 

2. 초기 접근 (Oracle 전용 문법)

처음에는 Oracle 전용 기능을 알게되어 이를 활용해 개발하고자 했다.

• CONNECT BY LEVEL : 10개 구간 생성
• WIDTH_BUCKET : 각 고객의 매출금액을 자동으로 구간 번호에 매핑

빠르게 결과를 얻을 수 있었지만, 단점도 명확했다.

• Oracle 종속성 → 다른 DBMS(PostgreSQL, MySQL 등)로 이식 불가
• 인덱스 활용 한계 → WIDTH_BUCKET은 함수 기반이라 RANGE SCAN 불가 (CPU 연산 증가)
• 성능 저하 → 100만 건 이상 데이터를 읽고 CPU에서 함수 계산을 하게 되었다

 

3. 개선 전 쿼리 (Oracle 종속 문법)

WITH 최대매출_CTE AS (
    SELECT MAX(월매출금액) 최대매출
      FROM 매출조건_T
     WHERE 매장코드 = :매장코드
       AND :기준년월 BETWEEN SUBSTR(시작일자,0,6) AND SUBSTR(종료일자,0,6)
),
구간기준_CTE AS (
    SELECT CASE WHEN LEVEL = 1
                 THEN 1
                 ELSE (LEVEL-1) * (최대매출/10)
            END AS 최소값,
           CASE WHEN LEVEL = 11
                 THEN NULL
                 ELSE LEVEL * (최대매출/10) - 1
            END AS 최대값
      FROM 최대매출_CTE
    CONNECT BY LEVEL <= 11
),
버킷집계_CTE AS (
    SELECT WIDTH_BUCKET(월매출금액, 1, V.최대매출, 10) AS 버킷번호,
           COUNT(*) AS 고객수
      FROM 월별매출집계_T A, 최대매출_CTE V
     WHERE A.매장코드 = :매장코드
       AND A.기준년월 = :기준년월
     GROUP BY WIDTH_BUCKET(월매출금액, 1, V.최대매출, 10)
)
SELECT B.최소값, B.최대값,
       NVL(D.고객수,0) AS 고객수
  FROM 구간기준_CTE B, 버킷집계_CTE D
 WHERE B.구간순번 = D.버킷번호(+)
 ORDER BY 최소값;

 

3-1. 개선 후 쿼리 (ANSI SQL 기반)

oracle에서는 Recursive 관련해서 코드가 수정이 필요하다. (이식성 100%는 아닌 점을 이해해주세요..)

WITH RECURSIVE
구간폭계산_CTE AS (
    SELECT CEIL(:최대매출/10.0) AS 구간폭,
           :최대매출 AS 최대매출
),
구간기준_CTE AS (
    SELECT 1 AS 구간번호, 1 AS 최소값, 구간폭 AS 최대값, 구간폭, 최대매출
      FROM 구간폭계산_CTE
    UNION ALL
    SELECT 구간번호+1,
           최대값+1,
           CASE WHEN 구간번호+1=10 THEN 최대매출 ELSE 최대값+구간폭 END,
           구간폭, 최대매출
      FROM 구간기준_CTE
     WHERE 구간번호 < 10
),
구간별집계_CTE AS (
    SELECT B.구간번호, B.최소값, B.최대값,
           COUNT(C.고객ID) AS 고객수
      FROM 구간기준_CTE B
      LEFT JOIN 월별매출집계_T C
        ON C.월매출금액 BETWEEN B.최소값 AND B.최대값
       AND C.매장코드 = :매장코드
       AND C.기준년월 = :기준년월
     GROUP BY B.구간번호, B.최소값, B.최대값
)
SELECT 구간번호, 최소값, 최대값, 고객수,
       ROUND(고객수*100.0/NULLIF(SUM(고객수) OVER(),0),2) AS 비율퍼센트
  FROM 구간별집계_CTE
 ORDER BY 구간번호;

 

4. 개선 포인트

1. ANSI SQL 문법
   WITH RECURSIVE를 사용해 Oracle 전용 문법을 제거하게 되었다.
   이로써 PostgreSQL, MySQL 등에서도 동일하게 동작할 수 있게 되었다.
2. 구간별 조인 구조
   구간(10행)을 기준으로 매출 데이터를 조인하게 만들었다.
   덕분에 각 구간마다 INDEX RANGE SCAN이 가능해졌다.
3. 성능 예측 가능성
   항상 10회 RANGE SCAN만 수행하게 되니, 데이터량이 커져도 실행 패턴이 안정적이게 되었다.'

 

5. 인덱스 설계 과정

5-1. 초기 인덱스

CREATE INDEX IDX_매출_CLUB_YM_CUST ON 월별매출집계_T (매장코드, 기준년월, 고객ID);

• 고객 단건 조회 유리
• 구간 조건에는 월매출금액이 없어 테이블 랜덤 액세스를 피할 수 없음

 

5-2. 개선 인덱스

CREATE INDEX IDX_매출_YM_CLUB_AMT_CUST ON 월별매출집계_T (기준년월, 매장코드, 월매출금액, 고객ID);

• 월매출금액을 포함해 구간 Range Scan이 가능
• 테이블 액세스 없이 인덱스만으로 처리(index-only)가 가능

다만 고객 단건 조회에는 기존 인덱스가 더 적합했기에,
두 인덱스를 이원화해 상황별로 선택하게 되는 것이 더 합리적일 수 있다.

 

6. SQL 실행계획 비교

6-1. 개선 전 (Oracle 전용 / WIDTH_BUCKET)

• COST: 약 1450
• TIME: 3.5 ~ 4초
• 특징: 인덱스로 조건 일부만 걸러내고, 나머지는 테이블 랜덤 액세스와 함수 계산

------------------------------------------------------------------------------------------------------------
| Id | Operation                          | Name                                | Rows   | Cost | Time  |
------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |                                     |     11 | 1450 | 3.8s  |
|  1 |  SORT ORDER BY                     |                                     |     11 |    3 | 0.0s  |
|  2 |   NESTED LOOPS OUTER               |                                     |     11 | 1447 | 3.8s  |
|  3 |    VIEW                            | (B: 구간기준_CTE)                    |     11 |    2 | 0.0s  |
|  4 |     CONNECT BY WITH FILTERING      |                                     |     11 |    2 | 0.0s  |
|  5 |    VIEW                            | (D: 버킷집계_CTE)                    |     10 | 1445 | 3.8s  |
|  6 |     HASH GROUP BY                  |                                     |     10 | 1445 | 3.8s  |
|  7 |      TABLE ACCESS BY INDEX ROWID   | 월별매출집계_T                       | 1,000K | 1400 | 3.6s  |
|  8 |       INDEX RANGE SCAN             | IDX_매출_CLUB_YM_CUST                | 1,000K |  320 | 0.9s  |
------------------------------------------------------------------------------------------------------------
Predicate Information:
8 - access("기준년월" = :기준년월 AND "매장코드" = :매장코드)
7 - filter(WIDTH_BUCKET("월매출금액", 1, :최대매출, 10) IS NOT NULL)

 

6-2. 개선 후 (ANSI SQL / RECURSIVE, JOIN, RANGE SCAN, INDEX_ONLY)

• COST: 약 150
• TIME: 0.4초
• 특징: 구간별 10회 INDEX RANGE SCAN만 수행, 테이블 액세스 발생X

Plan hash value: 7xxx4321

------------------------------------------------------------------------------------------------------------
| Id | Operation                          | Name                                | Rows  | Cost | Time  |
------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |                                     |    10 |  150 | 0.4s  |
|  1 |  SORT ORDER BY                     |                                     |    10 |    5 | 0.0s  |
|  2 |   VIEW                             | (D: 구간별집계_CTE)                  |    10 |  145 | 0.4s  |
|  3 |    HASH GROUP BY                   |                                     |    10 |  145 | 0.4s  |
|  4 |     NESTED LOOPS OUTER             |                                     |   1M  |  140 | 0.4s  |
|  5 |      RECURSIVE WITH PUMP           | (B: 구간기준_CTE, 10행)               |    10 |    1 | 0.0s  |
|  6 |      PARTITION RANGE SINGLE        |                                     |  100K |   14 | 0.05s |
|  7 |       INDEX RANGE SCAN             | IDX_매출_YM_CLUB_AMT_CUST            |  100K |   14 | 0.05s |
------------------------------------------------------------------------------------------------------------
Predicate Information:
7 - access("C"."기준년월"=:기준년월 AND "C"."매장코드"=:매장코드
            AND "C"."월매출금액" BETWEEN "B"."최소값" AND "B"."최대값")

6-3. 성능 비교 요약

개선 전	(매장코드, 기준년월, 고객ID)	RANGE SCAN 후 ROWID 랜덤 액세스 + Width Bucket 함수계산	cost : 1450	3.5~4s
개선 후	(기준년월, 매장코드, 월매출금액, 고객ID)	구간별 INDEX RANGE SCAN (index-only)	cost : 150	0.4s

 

7. 결론

1. Oracle 종속성 제거
   1. 전용 함수(WIDTH_BUCKET, CONNECT BY) 대신 ANSI SQL(WITH RECURSIVE)를 사용하면서 WIDTH_BUCKET에서의 불필요한 CPU 연산을 줄일 수 있었다.
   2. 각 DB에 종속적인 함수의 사용을 지양해야 하는 케이스를 알게 되었다.
2. 인덱스 최적화
   1. 범위 조건 컬럼(월매출금액)을 인덱스에 포함시킴으로써 테이블 엑세스가 필요하지 않게 되었고, 구간별 집계 쿼리에서의 효율이 증가하였다.
3. 성능 개선
   1. 3~4초 -> 0.4초, 약 10배 개선