JOIN-056

정가를 당시 가격 이력으로 바꾸라는 감사 지적을 반영했더니 하반기 정가 점검이 정산 인덱스를 5만 번 찾아간다

중급SELECT한빛유통206 / 700

상황

정산팀의 「하반기 정가 점검」은 정산이 끝난 주문의 상세를 상품별로 묶어 정가 매출과 실제 판매 금액을 견준다. 지금까지는 상품 마스터의 현재 단가를 정가로 썼는데, 감사에서 "주문 당시 가격을 써야 한다"는 지적을 받아 개발자가 가격 이력 조인을 하나 더했다. 그 뒤로 0.12초이던 점검이 1.4초가 됐다. Buffers는 2,400에서 15만으로 늘었는데, 조인 하나를 더한 것 말고는 바꾼 것이 없다. 힌트는 원래 없었다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · 2024년 하반기 주문 25,062건 / T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 하반기 상세 50,124줄
T_ORD_STLM 정산: 100,000행 · 640블록 · 주문 한 건에 정산 한 건 · 인덱스 IX_STLM_ORD (ORD_SEQ)(정산 대사 화면용, BLEVEL 1) · 정산 마감일(:v_close) 전에 정산된 하반기 주문 24,310건
M_ITM_PRC_HIS 상품 가격 이력 (신시스템에 있는 테이블, 이 문제에서는 2023 ~ 2024년 개정분까지 쌓인 모양): ITM_ID · FR_YMD · TO_YMD (VARCHAR2(8), 그날까지 포함) · UNT_PRC · 상품 100개 × 6구간 = 600행 · 5블록 · PK_M_ITM_PRC_HIS (ITM_ID, TO_YMD) · 구간은 겹치거나 끊기지 않는다
통계 최신 · 이력을 더하기 전 계획은 네 테이블 모두 해시 조인이었다(Buffers 약 2,400)

원본 SQL

sql

SELECT d.ITM_ID,
       COUNT(*)                   AS det_cnt,
       SUM(d.ORD_QTY * h.UNT_PRC) AS list_amt,
       SUM(d.ORD_QTY * d.UNT_PRC) AS sale_amt
  FROM T_ORD o, T_ORD_DET d, M_ITM_PRC_HIS h, T_ORD_STLM s
 WHERE o.ORD_DT >= DATE '2024-07-01'
   AND o.ORD_DT <  DATE '2025-01-01'
   AND d.ORD_SEQ = o.ORD_SEQ
   AND h.ITM_ID  = d.ITM_ID
   AND TO_CHAR(o.ORD_DT, 'YYYYMMDD') BETWEEN h.FR_YMD AND h.TO_YMD
   AND s.ORD_SEQ = o.ORD_SEQ
   AND s.STLM_DT < :v_close
 GROUP BY d.ITM_ID

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |      1 |        |    100 |00:00:01.43 |     152K|
|   1 |  HASH GROUP BY                 |               |      1 |    100 |    100 |00:00:01.43 |     152K|
|   2 |   NESTED LOOPS                 |               |      1 |    731 |  48620 |00:00:01.36 |     152K|
|   3 |    NESTED LOOPS                |               |      1 |    752 |  50124 |00:00:00.29 |     102K|
|*  4 |     HASH JOIN                  |               |      1 |    752 |  50124 |00:00:00.11 |    1797 |
|   5 |      TABLE ACCESS FULL         | M_ITM_PRC_HIS |      1 |    600 |    600 |00:00:00.01 |       5 |
|*  6 |      HASH JOIN                 |               |      1 |  50300 |  50124 |00:00:00.08 |    1792 |
|*  7 |       TABLE ACCESS FULL        | T_ORD         |      1 |  25150 |  25062 |00:00:00.01 |     896 |
|   8 |       TABLE ACCESS FULL        | T_ORD_DET     |      1 |    200K|    200K|00:00:00.02 |     896 |
|*  9 |     INDEX RANGE SCAN           | IX_STLM_ORD   |  50124 |      1 |  50124 |00:00:00.15 |     100K|
|* 10 |    TABLE ACCESS BY INDEX ROWID | T_ORD_STLM    |  50124 |      1 |  48620 |00:00:01.02 |   50124 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("H"."ITM_ID"="D"."ITM_ID")
       filter(("H"."FR_YMD"<=TO_CHAR(INTERNAL_FUNCTION("O"."ORD_DT"),'YYYYMMDD') AND
              "H"."TO_YMD">=TO_CHAR(INTERNAL_FUNCTION("O"."ORD_DT"),'YYYYMMDD')))
   6 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   7 - filter(("O"."ORD_DT">=TO_DATE(' 2024-07-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')))
   9 - access("S"."ORD_SEQ"="O"."ORD_SEQ")
  10 - filter("S"."STLM_DT"<:V_CLOSE)

문제

가격 이력 조인 하나를 더했을 뿐인데 정산 쪽 조인이 NL로 바뀐 까닭을 4번 줄의 E-Rows와 A-Rows로 설명하시오. 옵티마이저가 그 숫자를 어떻게 얻었는지도 쓰시오.
이력 조인을 어디에 두어야 하는지 정하고, 그 순서와 조인 방식을 고정하는 SQL을 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 정산까지 등치 조인으로 먼저 붙이고(해시), 추정이 틀리는 가격 이력 조인은 맨 마지막에 둔다
-- 마지막 조인은 이력 600행을 build 로 (SWAP_JOIN_INPUTS)
SELECT /*+ LEADING(o s d h) USE_HASH(s d h) SWAP_JOIN_INPUTS(h) */
       d.ITM_ID,
       COUNT(*)                   AS det_cnt,
       SUM(d.ORD_QTY * h.UNT_PRC) AS list_amt,
       SUM(d.ORD_QTY * d.UNT_PRC) AS sale_amt
  FROM T_ORD o, T_ORD_DET d, M_ITM_PRC_HIS h, T_ORD_STLM s
 WHERE o.ORD_DT >= DATE '2024-07-01'
   AND o.ORD_DT <  DATE '2025-01-01'
   AND d.ORD_SEQ = o.ORD_SEQ
   AND h.ITM_ID  = d.ITM_ID
   AND TO_CHAR(o.ORD_DT, 'YYYYMMDD') BETWEEN h.FR_YMD AND h.TO_YMD
   AND s.ORD_SEQ = o.ORD_SEQ
   AND s.STLM_DT < :v_close
 GROUP BY d.ITM_ID

개선 후 실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |      1 |        |    100 |00:00:00.16 |    2437 |
|   1 |  HASH GROUP BY                 |               |      1 |    100 |    100 |00:00:00.16 |    2437 |
|*  2 |   HASH JOIN                    |               |      1 |    731 |  48620 |00:00:00.13 |    2437 |
|   3 |    TABLE ACCESS FULL           | M_ITM_PRC_HIS |      1 |    600 |    600 |00:00:00.01 |       5 |
|*  4 |    HASH JOIN                   |               |      1 |  48900 |  48620 |00:00:00.10 |    2432 |
|*  5 |     HASH JOIN                  |               |      1 |  24400 |  24310 |00:00:00.04 |    1536 |
|*  6 |      TABLE ACCESS FULL         | T_ORD         |      1 |  25150 |  25062 |00:00:00.01 |     896 |
|*  7 |      TABLE ACCESS FULL         | T_ORD_STLM    |      1 |  97200 |  97248 |00:00:00.01 |     640 |
|   8 |     TABLE ACCESS FULL          | T_ORD_DET     |      1 |    200K|    200K|00:00:00.02 |     896 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("H"."ITM_ID"="D"."ITM_ID")
       filter(("H"."FR_YMD"<=TO_CHAR(INTERNAL_FUNCTION("O"."ORD_DT"),'YYYYMMDD') AND
              "H"."TO_YMD">=TO_CHAR(INTERNAL_FUNCTION("O"."ORD_DT"),'YYYYMMDD')))
   4 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   5 - access("S"."ORD_SEQ"="O"."ORD_SEQ")
   6 - filter(("O"."ORD_DT">=TO_DATE(' 2024-07-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')))
   7 - filter("S"."STLM_DT"<:V_CLOSE)

모범 답안 서술

4번 줄 HASH JOIN(가격 이력 조인)의 E-Rows는 752인데 A-Rows는 50,124다. 옵티마이저는 이 조인이 상세 5만 줄을 752줄로 거른다고 믿었고, 그 숫자로 다음 조인을 정했다. 정산 인덱스를 752번 찾아가는 NL은 정산 테이블 640블록을 FULL로 읽는 해시보다 싸 보였다. 실제로는 9번과 10번 줄이 50,124번씩 돌아 Buffers가 150K 늘었다. 조인 하나를 더했을 뿐인데 뒤 조인의 방식이 바뀐 까닭이다. 752는 이렇게 나왔다. 상품 번호 등치만 보면 상세 한 줄에 가격 이력이 600 / 100 = 6행씩 맞아 50,300 × 6 = 301,800행이다. 여기에 TO_CHAR(o.ORD_DT, ...) >= FR_YMD와 <= TO_YMD라는 컬럼끼리의 부등호 두 개에 기본 선택도 5%씩을 곱해 0.25%만 남긴다(약 752행). 그러나 구간이 겹치지도 끊기지도 않으므로 상세 한 줄에는 구간이 정확히 하나 맞는다. 선분이력 BETWEEN 조인은 1:1이라 행을 거르지 않는데, 옵티마이저는 99.75%를 거른다고 계산한 것이다. 통계를 다시 모아도 이 계산은 바뀌지 않는다. 추정을 바로잡기 어려우면 그 오차가 뒤로 번지지 않게 순서를 정한다. 등치 조인(주문 → 정산 → 상세)을 먼저 해시로 붙이고, 추정이 틀리는 가격 이력 조인을 맨 마지막에 둔다. 마지막 조인 뒤에는 묶기만 남아서 E-Rows가 틀려도 고를 것이 없다. LEADING(o s d h) USE_HASH(s d h)로 순서와 방식을 고정하고, 마지막 조인은 이력 600행을 build로 올린다(SWAP_JOIN_INPUTS(h)). Buffers는 152K → 2,437, 시간은 1.43초 → 0.16초로 준다. 이력을 더하기 전의 계획(약 2,400)과 거의 같다.

해설

조인을 하나 더하면 계획 전체가 바뀔 수 있다: 옵티마이저는 조인마다 앞 단계의 E-Rows로 다음 단계를 정한다. 추정이 크게 틀리는 조인이 앞에 오면 그 뒤의 순서와 방식이 모두 틀린 숫자 위에서 정해진다. 새 조인을 넣은 SQL은 전후 계획을 꼭 비교한다 (교재 10화 §1).
다른 정답: 정산에만 USE_HASH(s)를 줘도 이번 일량은 거의 같다. 다만 이력 조인이 앞에 있는 한, 나중에 다른 테이블을 붙일 때 같은 일이 되풀이된다. CARDINALITY나 OPT_ESTIMATE처럼 E-Rows 자체를 고치는 힌트는 문서화가 약하고 이유가 SQL에 남지 않아 힌트 대장에 적어 둬야 한다.
추정을 맞추는 길: 이력 조인을 등치로 바꿀 수 있으면 추정도 맞는다. 예를 들어 주문일마다 그날 유효한 가격을 펼친 일별 가격표(상품 × 날짜)를 두면 (ITM_ID, 날짜) 등치 조인이 되어 E-Rows가 1:1로 나온다. 대신 표가 상품 × 날짜만큼 커진다.
정산 인덱스 IX_STLM_ORD는 정산 대사 화면처럼 주문 몇 건을 찾는 SQL에는 여전히 맞다. 인덱스가 나쁜 것이 아니라, 5만 번을 752번으로 잘못 센 것이 문제였다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황