JOIN-068

정보계의 「분기 상품 매출」 리포트가 상품 25줄을 내는 데 3초 가까이 걸린다

중급SELECT한빛유통218 / 700

상황

한빛유통 정보계에는 운영 DB의 주문과 주문상세를 매일 밤 합쳐 넣는 주문상세 마트가 있다. 구축 업체가 남긴 「리포트 작성 가이드」에는 "스타 조인: 차원부터 읽어 팩트를 결합 인덱스로 찾는다"는 항목과 함께 아래 SQL의 힌트가 템플릿으로 적혀 있다. 지난주 만든 「분기 등급별 상품 매출」(B 등급 고객의 분기 신선 상품 매출)도 이 템플릿을 따랐는데 한 번에 3초 가까이 걸린다. 같은 템플릿으로 만든 「VIP 주간 상품 매출」(A 등급 20명, 일주일, 상품 5개)은 0.01초다. 아래는 2024년 4분기로 조회한 실행계획이다.

테이블과 인덱스

DW_ORD_DET 주문상세 마트: 200,000행 · 1,250블록 · ORD_DT(자정) · CUS_ID · ITM_ID · ORD_QTY · SLE_AMT · 2023-01-01 ~ 2024-12-31을 날짜순으로 적재 · IX_DW_ORD_DET_STAR (ORD_DT, CUS_ID, ITM_ID) BLEVEL 2 · 리프 약 910블록
2024년 4분기 25,170행(리프 약 115블록, 테이블 약 157블록에 모여 있다) · 그중 B 등급 고객 2,290행 · 그중 신선 상품 571행
D_DATE 날짜: 731행 · 6블록 · DT / D_CUS 고객: 2,000행 · 30블록 · CUS_ID · CUS_GD(B 180명) / D_ITM 상품: 100행 · 5블록 · ITM_ID · ITM_NM · ITM_TP(신선 'FRESH' 25개)
통계 최신

원본 SQL

sql

SELECT /*+ LEADING(d c i f) USE_NL(f) INDEX(f IX_DW_ORD_DET_STAR) */   -- 가이드 3.2 스타 조인 템플릿
       i.ITM_ID, i.ITM_NM, SUM(f.ORD_QTY) AS qty, SUM(f.SLE_AMT) AS amt
  FROM D_DATE d, D_CUS c, D_ITM i, DW_ORD_DET f
 WHERE d.DT >= :v_from
   AND d.DT <  :v_to
   AND c.CUS_GD = :v_gd
   AND i.ITM_TP = :v_tp
   AND f.ORD_DT = d.DT
   AND f.CUS_ID = c.CUS_ID
   AND f.ITM_ID = i.ITM_ID
 GROUP BY i.ITM_ID, i.ITM_NM

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |      1 |        |     25 |00:00:02.71 |   1243K |           |
|   1 |  HASH GROUP BY                  |                    |      1 |     25 |     25 |00:00:02.71 |   1243K | 1201K (0) |
|   2 |   NESTED LOOPS                  |                    |      1 |    567 |    571 |00:00:02.70 |   1243K |           |
|   3 |    NESTED LOOPS                 |                    |      1 |    567 |    571 |00:00:02.69 |   1242K |           |
|   4 |     MERGE JOIN CARTESIAN        |                    |      1 |   414K |   414K |00:00:00.21 |      41 |           |
|   5 |      MERGE JOIN CARTESIAN       |                    |      1 |  16560 |  16560 |00:00:00.01 |      36 |           |
|*  6 |       TABLE ACCESS FULL         | D_DATE             |      1 |     92 |     92 |00:00:00.01 |       6 |           |
|   7 |       BUFFER SORT               |                    |     92 |    180 |  16560 |00:00:00.01 |      30 | 15360 (0) |
|*  8 |        TABLE ACCESS FULL        | D_CUS              |      1 |    180 |    180 |00:00:00.01 |      30 |           |
|   9 |      BUFFER SORT                |                    |  16560 |     25 |   414K |00:00:00.12 |       5 |  2048 (0) |
|* 10 |       TABLE ACCESS FULL         | D_ITM              |      1 |     25 |     25 |00:00:00.01 |       5 |           |
|* 11 |     INDEX RANGE SCAN            | IX_DW_ORD_DET_STAR |   414K |      1 |    571 |00:00:02.31 |   1242K |           |
|  12 |    TABLE ACCESS BY INDEX ROWID  | DW_ORD_DET         |    571 |      1 |    571 |00:00:00.01 |     571 |           |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(("D"."DT">=:V_FROM AND "D"."DT"<:V_TO))
   8 - filter("C"."CUS_GD"=:V_GD)
  10 - filter("I"."ITM_TP"=:V_TP)
  11 - access("F"."ORD_DT"="D"."DT" AND "F"."CUS_ID"="C"."CUS_ID" AND "F"."ITM_ID"="I"."ITM_ID")

문제

11번 줄의 Starts가 어디서 나왔는지 4번과 5번 줄로 계산하고, 그중 몇 번이 행을 찾았는지 쓰시오. 같은 템플릿이 「VIP 주간 상품 매출」에서는 빨랐던 이유도 설명하시오.
이 리포트의 SQL을 고치고, 가이드의 '스타 조인' 항목을 어떻게 고쳐 써야 할지 쓰는 조건과 함께 제안하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 템플릿 힌트(차원 카티션 → 결합 인덱스 NL)를 뗀다 : 4분기 범위를 결합 인덱스 선두(ORD_DT)로 한 번 읽고 차원과 해시 조인
-- 날짜 조건은 옵티마이저가 조건 전이로 팩트에도 적용하지만 읽는 사람이 알아보도록 팩트 쪽에도 적는다
SELECT i.ITM_ID, i.ITM_NM, SUM(f.ORD_QTY) AS qty, SUM(f.SLE_AMT) AS amt
  FROM D_DATE d, D_CUS c, D_ITM i, DW_ORD_DET f
 WHERE d.DT >= :v_from
   AND d.DT <  :v_to
   AND f.ORD_DT >= :v_from
   AND f.ORD_DT <  :v_to
   AND c.CUS_GD = :v_gd
   AND i.ITM_TP = :v_tp
   AND f.ORD_DT = d.DT
   AND f.CUS_ID = c.CUS_ID
   AND f.ITM_ID = i.ITM_ID
 GROUP BY i.ITM_ID, i.ITM_NM

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

xplan

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                    |      1 |        |     25 |00:00:00.04 |     315 |           |
|   1 |  HASH GROUP BY                            |                    |      1 |     25 |     25 |00:00:00.04 |     315 | 1201K (0) |
|*  2 |   HASH JOIN                               |                    |      1 |    567 |    571 |00:00:00.04 |     315 | 1290K (0) |
|*  3 |    TABLE ACCESS FULL                      | D_ITM              |      1 |     25 |     25 |00:00:00.01 |       5 |           |
|*  4 |    HASH JOIN                              |                    |      1 |   2265 |   2290 |00:00:00.03 |     310 | 1347K (0) |
|*  5 |     TABLE ACCESS FULL                     | D_CUS              |      1 |    180 |    180 |00:00:00.01 |      30 |           |
|*  6 |     HASH JOIN                             |                    |      1 |  25170 |  25170 |00:00:00.02 |     280 | 1386K (0) |
|*  7 |      TABLE ACCESS FULL                    | D_DATE             |      1 |     92 |     92 |00:00:00.01 |       6 |           |
|   8 |      TABLE ACCESS BY INDEX ROWID BATCHED  | DW_ORD_DET         |      1 |  25170 |  25170 |00:00:00.01 |     274 |           |
|*  9 |       INDEX RANGE SCAN                    | IX_DW_ORD_DET_STAR |      1 |  25170 |  25170 |00:00:00.01 |     117 |           |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("F"."ITM_ID"="I"."ITM_ID")
   3 - filter("I"."ITM_TP"=:V_TP)
   4 - access("F"."CUS_ID"="C"."CUS_ID")
   5 - filter("C"."CUS_GD"=:V_GD)
   6 - access("F"."ORD_DT"="D"."DT")
   7 - filter(("D"."DT">=:V_FROM AND "D"."DT"<:V_TO))
   9 - access("F"."ORD_DT">=:V_FROM AND "F"."ORD_DT"<:V_TO)

모범 답안 서술

템플릿 힌트가 조인 순서를 날짜 → 고객 → 상품 → 팩트로 고정하고 팩트는 NL로 붙이게 했다. 날짜와 고객과 상품 사이에는 조인 조건이 없으므로 셋을 먼저 조건 없이 곱한다. 5번 줄에서 날짜 92일 × B 등급 고객 180명 = 16,560조합, 4번 줄에서 × 신선 상품 25개 = 414,000조합이다. 11번 줄은 이 41만 4천 조합마다 결합 인덱스를 한 번씩 찾았고(한 번에 3블록, 1,242K블록), 그중 행이 있었던 것은 571번뿐이다. 99.9%가 빈손으로 돌아왔다. 이번에는 E-Rows도 414K로 맞았다. 옵티마이저가 잘못 추정한 것이 아니라 힌트가 카티션 곱을 시켰다. 「VIP 주간 상품 매출」은 7일 × 20명 × 5개 = 700조합이라 2천 블록 남짓으로 끝났다. 차원을 먼저 곱하는 스타 조인은 곱이 작을 때만 맞는 방법이다. 차원 조건이 조금만 넓어져도 조합 수는 곱셈으로 늘어난다. 기간이 일주일에서 분기로, 고객이 20명에서 180명으로, 상품이 5개에서 25개로 늘자 조합은 700개에서 41만 개가 됐다. 이 리포트에서는 템플릿 힌트를 뗀다. 팩트는 날짜순으로 쌓여 있고 결합 인덱스의 선두도 날짜이므로, 4분기 범위를 인덱스로 한 번 읽으면 25,170행(274블록)이다. 이 행을 날짜, 고객, 상품과 해시 조인으로 맞대면 571행이 남는다. Buffers는 1,243K → 315, 시간은 2.71 → 0.04초다. 가이드의 스타 조인 항목에는 쓰는 조건을 적는다. 차원 조건을 적용한 뒤의 행 수를 곱한 조합이 수백 개 수준일 때만 '차원 카티션 뒤 결합 인덱스 NL'을 쓰고, 그렇지 않으면 힌트 없이 두거나 날짜 범위로 팩트를 읽는 해시 조인을 쓴다. 조건 조합이 자주 바뀌는 리포트가 늘면 팩트의 차원 키마다 비트맵 인덱스를 두고 스타 변환을 검토한다.

해설

카티션이 맞는 경우와 틀린 경우: 같은 MERGE JOIN CARTESIAN이라도 곱이 700이면 맞고 41만이면 틀린다. 계획에서 카티션을 보면 그 줄의 A-Rows가 얼마인지부터 확인한다.
힌트를 템플릿으로 나눠 주면: 템플릿 힌트는 만든 사람이 시험한 조건(VIP, 일주일)에서만 검증된 판단이다. 가이드에 힌트를 싣는다면 쓰는 조건과 검증한 규모를 함께 적는다(교재 06화 힌트 유효기간 대장).
조건 전이: d.DT 범위와 f.ORD_DT = d.DT가 있으면 옵티마이저가 f.ORD_DT 범위를 스스로 만든다. 힌트를 떼기만 해도 같은 계획이 나올 가능성이 크지만, 팩트 쪽에 조건을 적어 두면 뜻이 분명하다.
더 줄인다면: 분기 리포트가 하루 수십 번이라면 등급과 상품 유형별 일 집계를 미리 만들어 두는 요약 테이블도 검토한다. 315블록도 충분히 작으므로 이 리포트에서는 필요 없다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황