JOIN-029

금액 구간별 수수료 집계의 건수를 다 더하면 그해 주문 수보다 271건 많다

중급SELECT한빛유통179 / 700

상황

정산팀의 「금액 구간별 PG 수수료」는 2024년 주문을 월마다 PG사 수수료 구간표에 맞춰 구간별 건수, 금액, 수수료를 낸다. 재무팀이 구간별 건수를 더해 보니 333,516건으로, 그해 주문 333,245건보다 271건 많아 반려했다. 개발자는 해시 조인이 빠르다며 USE_HASH를 달았는데 실행계획에는 MERGE JOIN이 나온다. 박팀장은 "숫자부터 맞추고, 조인도 제대로 정리해 달라"라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 인덱스 없음 · 2024년 주문 333,245건 · ORD_AMT는 10,000 ~ 500,000원 사이 100원 단위 (서로 다른 값 4,901개, 값마다 2024년에 약 68건)
T_FEE_BAND PG 수수료 구간표 (이 문제에서 쓰는 파생 테이블): 5행 · 4블록 · BAND_CD · LO_AMT · HI_AMT · FEE_RT
구간표의 값: 0 ~ 50,000 (3.5%) · 50,000 ~ 100,000 (3.0%) · 100,000 ~ 200,000 (2.5%) · 200,000 ~ 300,000 (2.2%) · 300,000 ~ 999,999,999 (2.0%). 앞 구간의 HI_AMT와 다음 구간의 LO_AMT가 같다 (PG사 계약서: "하한 이상, 상한 미만")
통계 최신

원본 SQL

sql

SELECT /*+ USE_HASH(o b) */
       TO_CHAR(o.ORD_DT, 'YYYYMM') AS ym, b.BAND_CD,
       COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt,
       SUM(o.ORD_AMT * b.FEE_RT) AS fee_amt
  FROM T_ORD_BIG o, T_FEE_BAND b
 WHERE o.ORD_DT >= DATE '2024-01-01'
   AND o.ORD_DT <  DATE '2025-01-01'
   AND o.ORD_AMT BETWEEN b.LO_AMT AND b.HI_AMT
 GROUP BY TO_CHAR(o.ORD_DT, 'YYYYMM'), b.BAND_CD

실행계획 학습용 예시 · 행 수는 실데이터 분포로 계산

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |      1 |        |     60 |00:00:00.71 |    5830 |       |       |          |
|   1 |  HASH GROUP BY        |            |      1 |     60 |     60 |00:00:00.71 |    5830 |  1270K|  1270K| 1123K (0)|
|   2 |   MERGE JOIN          |            |      1 |   8331 |    333K|00:00:00.62 |    5830 |       |       |          |
|   3 |    SORT JOIN          |            |      1 |      5 |      5 |00:00:00.01 |       4 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS FULL | T_FEE_BAND |      1 |      5 |      5 |00:00:00.01 |       4 |       |       |          |
|*  5 |    FILTER             |            |      5 |        |    333K|00:00:00.55 |    5826 |       |       |          |
|*  6 |     SORT JOIN         |            |      5 |    333K|   1087K|00:00:00.47 |    5826 |  9876K|  1101K| 8778K (0)|
|*  7 |      TABLE ACCESS FULL| T_ORD_BIG  |      1 |    333K|    333K|00:00:00.06 |    5826 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("O"."ORD_AMT"<="B"."HI_AMT")
   6 - access(INTERNAL_FUNCTION("O"."ORD_AMT")>=INTERNAL_FUNCTION("B"."LO_AMT"))
       filter(INTERNAL_FUNCTION("O"."ORD_AMT")>=INTERNAL_FUNCTION("B"."LO_AMT"))
   7 - filter(("O"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[결과 대조]  구간별 건수 합계 333,516 · 2024년 주문 333,245 · 차이 271

문제

건수가 271건 많은 이유를 구간표의 값과 조인 조건으로 설명하시오. USE_HASH가 무시된 이유도 설명하시오.
결과가 맞도록 조인 조건을 고치고, 부등호 조인에 대는 행을 줄이도록 SQL을 다시 작성하시오.
고친 SQL이 맞다는 것을 무엇과 어떻게 대조해 보일지 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- ① 구간은 "하한 이상, 상한 미만" (계약서 그대로). BETWEEN 은 양 끝을 다 넣어 경계값을 두 번 센다
-- ② 구간을 정하는 것은 ORD_AMT 값 하나다: 월 · 금액으로 먼저 묶어 부등호 조인에 대는 행을 줄인다
SELECT a.ym, b.BAND_CD,
       SUM(a.cnt) AS ord_cnt, SUM(a.amt) AS ord_amt,
       SUM(a.amt * b.FEE_RT) AS fee_amt
  FROM (SELECT TO_CHAR(ORD_DT, 'YYYYMM') AS ym, ORD_AMT,
               COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
          FROM T_ORD_BIG
         WHERE ORD_DT >= DATE '2024-01-01'
           AND ORD_DT <  DATE '2025-01-01'
         GROUP BY TO_CHAR(ORD_DT, 'YYYYMM'), ORD_AMT) a,
       T_FEE_BAND b
 WHERE a.ORD_AMT >= b.LO_AMT
   AND a.ORD_AMT <  b.HI_AMT
 GROUP BY a.ym, b.BAND_CD;

-- ③ 대조: 건수 합계 = 그해 주문 수, 금액 합계 = 원장
SELECT COUNT(*), SUM(ORD_AMT)
  FROM T_ORD_BIG
 WHERE ORD_DT >= DATE '2024-01-01' AND ORD_DT < DATE '2025-01-01';

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 분포로 계산

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |      1 |        |     60 |00:00:00.29 |    5830 |       |       |          |
|   1 |  HASH GROUP BY           |            |      1 |     60 |     60 |00:00:00.29 |    5830 |  1270K|  1270K| 1123K (0)|
|   2 |   MERGE JOIN             |            |      1 |   2931 |  58610 |00:00:00.27 |    5830 |       |       |          |
|   3 |    SORT JOIN             |            |      1 |      5 |      5 |00:00:00.01 |       4 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS FULL    | T_FEE_BAND |      1 |      5 |      5 |00:00:00.01 |       4 |       |       |          |
|*  5 |    FILTER                |            |      5 |        |  58610 |00:00:00.25 |    5826 |       |       |          |
|*  6 |     SORT JOIN            |            |      5 |  58610 |    191K|00:00:00.24 |    5826 |  2250K|   668K| 1999K (0)|
|   7 |      VIEW                |            |      1 |  58610 |  58610 |00:00:00.21 |    5826 |       |       |          |
|   8 |       HASH GROUP BY      |            |      1 |  58610 |  58610 |00:00:00.21 |    5826 |  3880K|  1612K| 4012K (0)|
|*  9 |        TABLE ACCESS FULL | T_ORD_BIG  |      1 |    333K|    333K|00:00:00.06 |    5826 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("A"."ORD_AMT"<"B"."HI_AMT")
   6 - access(INTERNAL_FUNCTION("A"."ORD_AMT")>=INTERNAL_FUNCTION("B"."LO_AMT"))
       filter(INTERNAL_FUNCTION("A"."ORD_AMT")>=INTERNAL_FUNCTION("B"."LO_AMT"))
   9 - filter(("ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[결과 대조]  구간별 건수 합계 333,245 = 2024년 주문 333,245 · 금액 합계도 원장과 같다

모범 답안 서술

구간표는 앞 구간의 상한과 다음 구간의 하한이 같은 값이다(50,000 · 100,000 · 200,000 · 300,000). 계약서의 뜻은 "하한 이상, 상한 미만"인데 SQL은 BETWEEN b.LO_AMT AND b.HI_AMT로 양 끝을 모두 넣었다. 그래서 금액이 경계값과 같은 주문은 두 구간에 모두 짝지어져 두 번 세어졌다. 경계값 네 개마다 2024년 주문이 약 68건이라 모두 271건이 중복됐고, 수수료도 그 주문들은 두 구간의 요율로 두 번 계산됐다. USE_HASH가 무시된 이유는 조인 조건에 등치 조건이 없기 때문이다. 해시 조인은 등치 조건에서만 쓸 수 있으므로, 옵티마이저는 부등호 조인을 처리할 수 있는 소트 머지를 골랐다. 6번 줄 SORT JOIN이 2024년 주문 33만 건을 금액 순으로 정렬하고, 구간 5개마다 하한부터 훑어 내려가며 상한으로 걸렀다(A-Rows 1,087K). 조인 조건을 a.ORD_AMT >= b.LO_AMT AND a.ORD_AMT < b.HI_AMT로 고친다. 이것으로 숫자가 맞는다. 부등호 조인은 해시로 바꿀 수 없으니, 조인에 대는 행을 줄인다. 구간을 정하는 것은 ORD_AMT 값 하나이므로, 조인 전에 월과 금액으로 먼저 묶으면 33만 건이 58,610행(12개월 × 월마다 약 4,880가지 금액)으로 준다. 같은 금액끼리는 같은 구간에 들어가므로 묶은 뒤에 붙여도 결과가 같고, 수수료도 금액 합계 × 요율로 같다. 정렬하는 행이 33만 → 5만 9천으로 줄어 Used-Mem은 8,778K → 1,999K가 되고, A-Time은 0.71 → 0.29초로 준다. 테이블은 여전히 한 번 읽으므로 Buffers는 5,830 그대로다. 맞다는 증거는 대조다. 구간별 건수 합계가 그해 주문 수 333,245건과 같고, 구간별 금액 합계가 원장의 그해 금액 합계와 같은지 확인한다. 경계값(50,000원 등)인 주문이 위 구간 하나에만 들어갔는지 몇 건을 골라 본다.

해설

경계는 반열림으로: 날짜 범위에서 < 다음 날을 쓰는 것과 같은 원리다. 구간표처럼 상한과 하한이 맞닿는 표에 BETWEEN을 쓰면 경계값이 두 번 들어간다. 구간표를 만들 때 "하한 이상, 상한 미만"을 테이블 주석에 적어 둔다 (교재 02화의 상한 규칙).
해시가 안 되는 조인에서 할 수 있는 일: 조인 방식을 바꿀 수 없으면 조인에 대는 행을 줄인다. 조인 키(금액)로 먼저 묶는 것은 결과를 바꾸지 않는다. 금액이 1만 원 단위 구간이라면 TRUNC(ORD_AMT, -4)로 펼친 구간표와 등치 조인을 하는 방법도 있다 (JOIN-028).
소트 머지가 나쁜 것은 아니다: 구간표가 수천 행이고 양쪽이 모두 크면, 부등호 조인에서는 소트 머지가 NL보다 낫다. NL로 풀면 주문 한 건마다 구간표를 찾아가야 한다.
정합성 회귀: 건수 합계, 금액 합계, 경계값 표본을 함께 본다. 금액이 맞아도 건수가 틀릴 수 있고, 그 반대도 있다 (교재 08화 §10).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수는 실데이터 분포로 계산 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 분포로 계산 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황