JOIN-003

VIP 스무 명의 반년 치 주문 260줄에 2만 6천 블록을 읽는다

중급SELECT한빛유통153 / 700

상황

CRM팀의 「VIP 고객 하반기 주문」은 상담 전에 A 등급 고객들의 기간 주문을 한 번에 뽑는 화면이다. 기간만 바꿔 가며 하루 수백 번 호출된다. 소스의 힌트 옆에는 -- 주문을 먼저 읽고 고객은 PK 로 붙인다 (마스터는 나중에)라는 주석이 있다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · IX_T_ORD_CUS_DT (CUS_ID, ORD_DT) (작년에 고객 상담 화면용으로 만든 인덱스) · ORD_DT 시분초 있음 · 2023-01-01 ~ 2024-12-31에 고르게 분포 · 고객마다 50건
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD A 1% (20명) · B 9% · C 90%
통계 최신 · 히스토그램 없음 (FOR ALL COLUMNS SIZE 1) · 아래 실행계획은 2024-07-01 ~ 2024-12-31 (주문 25,062건)

원본 SQL

sql

SELECT /*+ LEADING(o c) USE_NL(c) */
       c.CUS_ID, c.CUS_NM, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT
  FROM T_ORD o, M_CUS c
 WHERE o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
   AND c.CUS_ID = o.CUS_ID
   AND c.CUS_GD = 'A'
 ORDER BY c.CUS_ID, o.ORD_DT

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |    260 |00:00:00.05 |   26484 |
|   1 |  SORT ORDER BY                 |          |      1 |   8383 |    260 |00:00:00.05 |   26484 |
|*  2 |   FILTER                       |          |      1 |        |    260 |00:00:00.05 |   26484 |
|   3 |    NESTED LOOPS                |          |      1 |   8383 |    260 |00:00:00.05 |   26484 |
|   4 |     NESTED LOOPS               |          |      1 |  25150 |  25062 |00:00:00.02 |    1422 |
|*  5 |      TABLE ACCESS FULL         | T_ORD    |      1 |  25150 |  25062 |00:00:00.01 |     896 |
|*  6 |      INDEX UNIQUE SCAN         | PK_M_CUS |  25062 |      1 |  25062 |00:00:00.01 |     526 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| M_CUS    |  25062 |      1 |    260 |00:00:00.02 |   25062 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   5 - filter(("O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))
   6 - access("C"."CUS_ID"="O"."CUS_ID")
   7 - filter("C"."CUS_GD"='A')

문제

6번과 7번 줄의 Starts를 7번 줄의 A-Rows와 비교해, 이 조인이 무엇을 낭비하는지 설명하시오.
두 조건(기간, 등급)의 선택도로 어느 쪽을 먼저 읽어야 하는지 정하고 개선 SQL을 작성하시오. 힌트 없이 옵티마이저가 그 순서를 고르게 하려면 무엇이 필요한지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 등급 A(20 명)를 먼저 읽고, 고객마다 (CUS_ID, ORD_DT) 인덱스로 그 기간 주문만 찾아간다
SELECT /*+ LEADING(c o) USE_NL(o) INDEX(o IX_T_ORD_CUS_DT) */
       c.CUS_ID, c.CUS_NM, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT
  FROM M_CUS c, T_ORD o
 WHERE c.CUS_GD = 'A'
   AND o.CUS_ID = c.CUS_ID
   AND o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 ORDER BY c.CUS_ID, o.ORD_DT;

-- 힌트 없이 옵티마이저가 이 순서를 고르게 하려면 — CUS_GD 의 치우침(1 % · 9 % · 90 %)을 알려 준다
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'M_CUS', METHOD_OPT => 'FOR COLUMNS CUS_GD SIZE 3');

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

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |    260 |00:00:00.01 |     331 |
|   1 |  SORT ORDER BY                 |                 |      1 |    260 |    260 |00:00:00.01 |     331 |
|*  2 |   FILTER                       |                 |      1 |        |    260 |00:00:00.01 |     331 |
|   3 |    NESTED LOOPS                |                 |      1 |    260 |    260 |00:00:00.01 |     331 |
|   4 |     NESTED LOOPS               |                 |      1 |    260 |    260 |00:00:00.01 |      71 |
|*  5 |      TABLE ACCESS FULL         | M_CUS           |      1 |     20 |     20 |00:00:00.01 |      30 |
|*  6 |      INDEX RANGE SCAN          | IX_T_ORD_CUS_DT |     20 |     13 |    260 |00:00:00.01 |      41 |
|   7 |     TABLE ACCESS BY INDEX ROWID| T_ORD           |    260 |     13 |    260 |00:00:00.01 |     260 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   5 - filter("C"."CUS_GD"='A')
   6 - access("O"."CUS_ID"="C"."CUS_ID" AND "O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND
              "O"."ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

모범 답안 서술

6번과 7번 줄의 Starts가 25,062다. outer(5번 줄, 하반기 주문)의 A-Rows만큼 고객을 PK로 찾아갔다는 뜻이다. 그런데 7번 줄의 A-Rows는 260이다. 찾아간 고객의 99%를 filter("C"."CUS_GD"='A')로 버린 것이다. 25,062번 왕복하면서 M_CUS 테이블 액세스에서만 25,062블록을 읽었다. 두 조건의 선택도를 비교해 보자. 기간 조건(2024년 하반기)은 주문의 25%(25,062건)를 남기고, 등급 조건은 고객의 1%(20명)만 남긴다. NL의 왕복 횟수는 드라이빙 집합의 크기로 정해지므로, 더 많이 거르는 쪽인 등급 A 고객 20명을 먼저 읽는다. T_ORD는 (CUS_ID, ORD_DT) 인덱스로 고객마다 그 기간의 주문만 찾아간다. 20번 왕복해 260행을 얻고, Buffers는 26,484 → 331로 준다. '마스터는 나중에'는 규칙이 아니다. 어느 쪽을 먼저 읽을지는 테이블 크기가 아니라 조건을 적용한 뒤 남는 행 수로 정한다. 힌트만 떼면 옵티마이저는 CUS_GD 히스토그램이 없어서 A 등급을 2,000 / 3 = 667명으로 본다 (원본 1번 줄 E-Rows 8,383 = 25,150 / 3). 그러면 M_CUS를 build로 삼은 해시 조인(30 + 896 ≈ 926블록)을 고른다. 26,484블록보다는 낫지만 최선은 아니다. 힌트 없이 이 순서를 고르게 하려면 CUS_GD의 분포(히스토그램)를 모아 E-Rows가 20이 되게 한다.

해설

다른 정답: 히스토그램을 모으고 힌트를 떼는 답도 정답이다. 옵티마이저가 E-Rows 20을 보고 같은 계획(331블록)을 고른다. 힌트로 고정했다면 '오늘의 분포'를 보고 내린 판단이므로 대장에 적어 둔다 (교재 06화 §8).
조건이 바뀌면: A 등급이 200명이 되거나 기간이 하루로 줄면 어느 조건이 더 많이 거르는지가 뒤집힌다. 순서는 외워 두는 것이 아니라 그때의 행 수로 다시 정한다.
IX_T_ORD_CUS_DT가 없다면 M_CUS를 먼저 읽어 해시 조인(M_CUS를 build로, T_ORD는 FULL)을 한다. 약 926블록이다. 이때도 '작은 쪽이 먼저(build)'라는 원칙은 같다.
7번 줄의 테이블 액세스 260블록은 고객 한 명의 주문이 여러 블록에 흩어져 있어서 생긴다(클러스터링 팩터). ORD_AMT까지 담은 인덱스라면 테이블을 읽지 않아도 되지만, 인덱스를 키운 만큼 적재 비용이 든다 (교재 04화, 14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황