JOIN-060

「VIP 이달 구매 상품」 83줄을 띄우는 데 고객 마스터를 8,355번 찾아간다

기초SELECT한빛유통210 / 700

상황

CRM팀 상담 화면의 「VIP 이달 구매 상품」 탭은 A 등급 고객 20명이 이번 달에 산 상품을 주문일, 고객명, 상품명, 수량, 금액으로 보여 준다. 상담원 40명이 하루 1,500번쯤 연다. 지난달 조민우가 만들었다. 처음 돌려 보니 해시 조인이 나와서 "화면 조회는 NL이 맞다"며 FROM 절에 적은 순서 그대로 LEADING과 USE_NL을 붙였다. 아래는 2024년 6월로 조회한 실행계획이다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · ORD_DT 인덱스 없음 · 한 달 주문은 테이블 전체에 흩어져 있다 · 2024년 6월 4,172건, 그중 A 등급 고객의 주문 41건
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) BLEVEL 1 · 주문당 1 ~ 3줄(평균 2줄) · 6월 주문의 상세 8,355줄, A 등급 고객 몫 83줄
M_ITM: 100행 · 5블록 · PK_M_ITM (ITM_ID)
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD A 20명(1%) · B 180명 · C 1,800명 · CUS_GD에 히스토그램 있음
통계 최신

원본 SQL

sql

SELECT /*+ LEADING(o d i c) USE_NL(d i c) */
       o.ORD_DT, c.CUS_NM, i.ITM_NM, d.ORD_QTY, d.ORD_QTY * d.UNT_PRC AS amt
  FROM T_ORD o, T_ORD_DET d, M_ITM i, M_CUS c
 WHERE o.ORD_DT >= :v_from
   AND o.ORD_DT <  :v_to
   AND d.ORD_SEQ = o.ORD_SEQ
   AND i.ITM_ID  = d.ITM_ID
   AND c.CUS_ID  = o.CUS_ID
   AND c.CUS_GD  = 'A'
 ORDER BY o.ORD_DT DESC, c.CUS_NM

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |      1 |        |     83 |00:00:00.29 |   46562 |           |
|   1 |  SORT ORDER BY                    |              |      1 |     84 |     83 |00:00:00.29 |   46562 | 13312 (0) |
|   2 |   NESTED LOOPS                    |              |      1 |     84 |     83 |00:00:00.29 |   46562 |           |
|   3 |    NESTED LOOPS                   |              |      1 |   8344 |   8355 |00:00:00.19 |   29848 |           |
|   4 |     NESTED LOOPS                  |              |      1 |   8344 |   8355 |00:00:00.11 |   13138 |           |
|*  5 |      TABLE ACCESS FULL            | T_ORD        |      1 |   4172 |   4172 |00:00:00.01 |     896 |           |
|   6 |      TABLE ACCESS BY INDEX ROWID  | T_ORD_DET    |   4172 |      2 |   8355 |00:00:00.08 |   12242 |           |
|*  7 |       INDEX RANGE SCAN            | PK_T_ORD_DET |   4172 |      2 |   8355 |00:00:00.04 |    8344 |           |
|   8 |     TABLE ACCESS BY INDEX ROWID   | M_ITM        |   8355 |      1 |   8355 |00:00:00.05 |   16710 |           |
|*  9 |      INDEX UNIQUE SCAN            | PK_M_ITM     |   8355 |      1 |   8355 |00:00:00.02 |    8355 |           |
|* 10 |    TABLE ACCESS BY INDEX ROWID    | M_CUS        |   8355 |      1 |     83 |00:00:00.08 |   16714 |           |
|* 11 |     INDEX UNIQUE SCAN             | PK_M_CUS     |   8355 |      1 |   8355 |00:00:00.03 |    8359 |           |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<:V_TO))
   7 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   9 - access("I"."ITM_ID"="D"."ITM_ID")
  10 - filter("C"."CUS_GD"='A')
  11 - access("C"."CUS_ID"="O"."CUS_ID")

문제

고객 등급 조건(CUS_GD = 'A')이 실행계획의 몇 번 줄에서 적용되는지 짚고, 46,562블록이 어디서 나오는지 Starts와 Buffers로 설명하시오.
결과는 그대로 두고 일량이 가장 적도록 조인 순서와 조인 방식을 정해 SQL을 작성하시오. 네 테이블의 순서를 정한 기준도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 걸러 내는 조인(고객 등급)을 맨 앞으로: A 등급 20명을 build 로 올리고 이달 주문을 한 번 훑어 41건만 남긴 뒤
-- 주문상세와 상품은 그 41건에만 NL 로 붙인다
SELECT /*+ LEADING(c o d i) USE_HASH(o) USE_NL(d i) */
       o.ORD_DT, c.CUS_NM, i.ITM_NM, d.ORD_QTY, d.ORD_QTY * d.UNT_PRC AS amt
  FROM T_ORD o, T_ORD_DET d, M_ITM i, M_CUS c
 WHERE o.ORD_DT >= :v_from
   AND o.ORD_DT <  :v_to
   AND d.ORD_SEQ = o.ORD_SEQ
   AND i.ITM_ID  = d.ITM_ID
   AND c.CUS_ID  = o.CUS_ID
   AND c.CUS_GD  = 'A'
 ORDER BY o.ORD_DT DESC, c.CUS_NM

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

xplan

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |      1 |        |     83 |00:00:00.02 |    1215 |           |
|   1 |  SORT ORDER BY                   |              |      1 |     84 |     83 |00:00:00.02 |    1215 | 13312 (0) |
|   2 |   NESTED LOOPS                   |              |      1 |     84 |     83 |00:00:00.02 |    1215 |           |
|   3 |    NESTED LOOPS                  |              |      1 |     84 |     83 |00:00:00.02 |    1049 |           |
|*  4 |     HASH JOIN                    |              |      1 |     42 |     41 |00:00:00.01 |     926 | 1236K (0) |
|*  5 |      TABLE ACCESS FULL           | M_CUS        |      1 |     20 |     20 |00:00:00.01 |      30 |           |
|*  6 |      TABLE ACCESS FULL           | T_ORD        |      1 |   4172 |   4172 |00:00:00.01 |     896 |           |
|   7 |     TABLE ACCESS BY INDEX ROWID  | T_ORD_DET    |     41 |      2 |     83 |00:00:00.01 |     123 |           |
|*  8 |      INDEX RANGE SCAN            | PK_T_ORD_DET |     41 |      2 |     83 |00:00:00.01 |      82 |           |
|   9 |    TABLE ACCESS BY INDEX ROWID   | M_ITM        |     83 |      1 |     83 |00:00:00.01 |     166 |           |
|* 10 |     INDEX UNIQUE SCAN            | PK_M_ITM     |     83 |      1 |     83 |00:00:00.01 |      83 |           |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C"."CUS_ID"="O"."CUS_ID")
   5 - filter("C"."CUS_GD"='A')
   6 - filter(("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<:V_TO))
   8 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
  10 - access("I"."ITM_ID"="D"."ITM_ID")

모범 답안 서술

LEADING(o d i c) USE_NL(d i c) 때문에 조인 순서가 주문 → 주문상세 → 상품 → 고객으로 고정됐다. 고객 등급 조건은 맨 마지막 10번 줄에서야 filter로 적용된다. 그 앞에서 6월 주문 4,172건이 주문상세 8,355줄로 불어났고, 이 8,355줄마다 상품(8번과 9번 줄, Starts 8,355)과 고객(10번과 11번 줄, Starts 8,355)을 찾아갔다. 10번 줄은 8,355번 찾아가서 83줄만 남겼다. 99%를 다 붙인 뒤에 버린 셈이다. Buffers는 주문상세 12,242, 상품 16,710, 고객 16,714로 합계 46,562다. 조인 순서는 테이블 크기가 아니라 조인한 뒤 남는 행 수로 정한다. 행을 가장 많이 걸러 내는 조인을 먼저 하고, 행을 늘리는 1:M 조인(주문상세)은 걸러 낸 뒤로 미룬다. 여기서는 고객 조인이 주문을 4,172건에서 41건으로 줄이므로 맨 앞에 둔다. A 등급 고객 20명(30블록)을 해시 build로 올리고 6월 주문을 한 번 훑어 41건을 남긴 다음, 주문상세(Starts 41)와 상품(Starts 83)은 NL로 붙인다. Buffers는 46,562 → 1,215이고 결과 83줄은 같다. 조인 방식도 순서와 함께 정한다. 고객을 두 번째에 두더라도 NL로 주문 4,172건마다 고객을 찾으면 약 8,300블록이 든다. 걸러 낼 대상이 30블록 안에 다 있으므로 해시 조인으로 한 번에 맞대는 편이 싸다. B 등급 화면처럼 남는 비율이 크게 다른 조건이 오면 이 판단도 다시 재야 하므로, 힌트를 남길 때는 그 전제를 힌트 대장에 함께 적는다.

해설

순서의 기준: 고객을 맨 앞(LEADING(c o ...))에 두든 주문 바로 다음(LEADING(o c ...))에 두든, 걸러 내는 조인이 1:M 조인보다 앞이면 된다. 힌트를 모두 떼도 옵티마이저는 고객 해시 조인을 먼저 고를 가능성이 크다(주문상세까지 해시로 붙이면 약 1,830블록).
filter의 위치 읽기: Predicate Information에서 CUS_GD = 'A'가 몇 번 줄의 filter인지를 보면 조건이 언제 적용되는지 알 수 있다. 조건이 늦게 적용될수록 그 앞 줄들의 Starts가 커진다(교재 06화).
고객에서 출발하는 NL: T_ORD (CUS_ID, ORD_DT) 인덱스가 있다면 A 등급 20명 → 고객별 이달 주문 → 주문상세 순서의 NL로 수백 블록까지 줄일 수 있다. 다만 인덱스를 새로 만들어야 하고, 하루 1,500번 화면 하나 때문에 만들 만한지 따져 봐야 한다.
FROM 절 순서는 조인 순서가 아니다: FROM 절에 적은 순서는 결과와 상관없다. 그 순서를 LEADING에 그대로 옮기면 조인 순서를 정하는 판단을 건너뛴 셈이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황