JOIN-051

같은 템플릿으로 만든 등급 이력 조회 두 개 가운데 VIP 기간 실적만 5초 가까이 걸린다

중급SELECT한빛유통201 / 700

상황

CRM팀의 「VIP 기간 실적」은 2024년에 A 등급이던 동안의 주문만 골라 고객별 건수와 금액을 낸다. 연말 감사 선물 대상을 확정하는 기간이라 하루 수십 번 돌리는데, 한 번에 4.6초가 걸린다. SQL은 사내 템플릿대로 "주문을 먼저 읽고, 등급 이력은 PK로 붙인다"(LEADING(o h) USE_NL(h))는 힌트를 달았다. 같은 템플릿으로 만든 「일자별 주문 목록」(날짜 하나를 고르면 그날 주문 913건에 주문 당시 등급을 붙인다)은 0.01초라서, 개발자는 템플릿 탓이 아니라고 한다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT는 전부 자정 · 2024년 주문 333,245건 · 고객마다 하루 평균 0.46건
인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT, ORD_AMT): 작년에 「지역별 고객 월 매출」 화면 때문에 만들었다 · 높이 3 · 리프 약 3,400블록
M_CUS_GD_HIS 고객 등급 이력 (이 문제에서 쓰는 파생 테이블, 선분이력): CUS_ID · ST_DT · ED_DT (그날까지 포함, 지금 구간은 DATE '9999-12-31') · CUS_GD · 2022년부터 분기마다 재산정하고 등급이 바뀐 고객만 새 구간을 연다 · 6,140행 · 40블록 · PK_M_CUS_GD_HIS (CUS_ID, ST_DT) (BLEVEL 1)
A 등급 구간은 61개이고, 그중 2024년에 걸친 구간은 44개(고객 31명)다 · 통계 최신

원본 SQL

sql

SELECT /*+ LEADING(o h) USE_NL(h) */
       o.CUS_ID, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
  FROM T_ORD_BIG o, M_CUS_GD_HIS h
 WHERE o.ORD_DT >= DATE '2024-01-01'
   AND o.ORD_DT <  DATE '2025-01-01'
   AND h.CUS_ID = o.CUS_ID
   AND o.ORD_DT BETWEEN h.ST_DT AND h.ED_DT
   AND h.CUS_GD = 'A'
 GROUP BY o.CUS_ID

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |     31 |00:00:04.62 |    1339K|
|   1 |  HASH GROUP BY                |                 |      1 |     31 |     31 |00:00:04.62 |    1339K|
|   2 |   NESTED LOOPS                |                 |      1 |   1667 |   2006 |00:00:04.59 |    1339K|
|   3 |    NESTED LOOPS               |                 |      1 |    833K|    832K|00:00:01.71 |     672K|
|*  4 |     TABLE ACCESS FULL         | T_ORD_BIG       |      1 |    333K|    333K|00:00:00.06 |    5826 |
|*  5 |     INDEX RANGE SCAN          | PK_M_CUS_GD_HIS |    333K|      3 |    832K|00:00:01.32 |     666K|
|*  6 |    TABLE ACCESS BY INDEX ROWID| M_CUS_GD_HIS    |    832K|      1 |   2006 |00:00:02.61 |     667K|
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - 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')))
   5 - access("H"."CUS_ID"="O"."CUS_ID" AND "H"."ST_DT"<="O"."ORD_DT")
   6 - filter(("H"."CUS_GD"='A' AND "H"."ED_DT">="O"."ORD_DT"))

문제

같은 템플릿인데 「일자별 주문 목록」은 빠르고 이 SQL은 느린 까닭을 Starts와 5번, 6번 줄의 Predicate Information으로 설명하시오.
어느 테이블에서 출발해야 하는지 정하고 SQL을 고치시오. 출발점을 바꾸면 BETWEEN 조건이 어느 인덱스의 access 조건이 되는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 많이 거르는 조건(A 등급)이 이력에 있다: 2024년에 걸친 A 구간 44개에서 출발해,
-- 구간마다 그 고객의 그 기간 주문만 (CUS_ID, ORD_DT, ORD_AMT) 인덱스로 찾는다
SELECT /*+ LEADING(h o) USE_NL(o) INDEX(o IX_ORD_BIG_CUS_DT) */
       o.CUS_ID, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
  FROM M_CUS_GD_HIS h, T_ORD_BIG o
 WHERE h.CUS_GD = 'A'
   AND h.ED_DT >= DATE '2024-01-01'          -- 이력 쪽에도 기간 조건 (2024년에 걸친 구간만)
   AND h.ST_DT <  DATE '2025-01-01'
   AND o.CUS_ID = h.CUS_ID
   AND o.ORD_DT BETWEEN h.ST_DT AND h.ED_DT
   AND o.ORD_DT >= DATE '2024-01-01'
   AND o.ORD_DT <  DATE '2025-01-01'
 GROUP BY o.CUS_ID

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

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |      1 |        |     31 |00:00:00.01 |     218 |
|   1 |  HASH GROUP BY      |                   |      1 |     31 |     31 |00:00:00.01 |     218 |
|   2 |   NESTED LOOPS      |                   |      1 |   2280 |   2006 |00:00:00.01 |     218 |
|*  3 |    TABLE ACCESS FULL| M_CUS_GD_HIS      |      1 |     51 |     44 |00:00:00.01 |      40 |
|*  4 |    INDEX RANGE SCAN | IX_ORD_BIG_CUS_DT |     44 |     45 |   2006 |00:00:00.01 |     178 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("H"."CUS_GD"='A' AND "H"."ED_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "H"."ST_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   4 - access("O"."CUS_ID"="H"."CUS_ID" AND "O"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "O"."ORD_DT">="H"."ST_DT" AND "O"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "O"."ORD_DT"<="H"."ED_DT")

모범 답안 서술

템플릿은 주문을 먼저 읽는다. 「일자별 주문 목록」은 주문 쪽 조건(날짜 하나)이 가장 많이 걸러서 913건만 이력을 찾아가므로 빠르다. 이 SQL은 반대다. 주문 쪽 조건(2024년)은 33만 건을 남기고, 많이 거르는 조건(A 등급)은 이력에 있다. 그래서 4번 줄의 2024년 주문 333,245건마다 5번 줄이 이력 인덱스를 찾아갔고(Starts 333K), 6번 줄이 테이블에 832K번 가서야 CUS_GD = 'A'로 걸렀다. 남은 것은 2,006건(0.6%)이고, Buffers 1,339K의 대부분이 이 왕복이다. 5번 줄의 access는 "H"."ST_DT"<="O"."ORD_DT" 하나뿐이다. 이력이 안쪽이면 주문일은 한 값이고 이력 쪽에는 시작일과 종료일 두 칸이 있으므로, 인덱스 (CUS_ID, ST_DT)로는 '시작일이 주문일 이전인 구간 전부'를 읽고 종료일과 등급은 테이블에서 걸러야 한다. 출발점을 A 등급 구간으로 바꾼다. 2024년에 걸친 A 구간은 44개뿐이고, 이력이 바깥쪽이 되면 구간 하나의 시작일과 종료일이 주문 쪽 ORD_DT의 아래 끝과 위 끝이 된다. 4번 줄처럼 CUS_ID = :1 AND ORD_DT >= 시작일 AND ORD_DT <= 종료일 전부가 IX_ORD_BIG_CUS_DT의 access가 되어, 구간마다 그 고객의 그 기간 주문만 읽는다. 인덱스에 ORD_AMT까지 있으므로 테이블은 읽지 않는다. 구간 44번 × 약 4블록에 이력 40블록을 더해 Buffers는 1,339K → 218로 준다. 이력 쪽에도 ED_DT >= 2024-01-01 AND ST_DT < 2025-01-01을 줘서 2024년에 걸친 구간만 출발시킨다. 옵티마이저는 주문의 기간 조건으로 이 조건을 만들어 주지 않는다.

해설

조인 순서는 템플릿이 아니라 조건이 정한다: 같은 두 테이블이라도 가장 많이 거르는 조건이 어느 쪽에 있느냐에 따라 출발점이 바뀐다. 날짜 하나의 주문 목록은 주문에서, A 등급 기간 실적은 이력에서 출발한다 (교재 06화 §3).
BETWEEN은 어느 방향으로도 조인 조건이 된다: 주문 쪽이 바깥이면 이력 인덱스는 (CUS_ID, ED_DT, ST_DT)처럼 종료일을 앞에 둬야 최근 주문에 유리하다. 이력 쪽이 바깥이면 주문 인덱스 (CUS_ID, ORD_DT) 하나로 구간의 양 끝이 다 access가 된다.
다른 정답: 힌트를 떼면 옵티마이저가 해시 조인(주문 FULL 5,826블록 + 이력)을 고를 수 있다. 33만 번 왕복보다는 훨씬 낫지만 218블록보다 27배 무겁다. 하루 수십 번 도는 화면이면 NL과 인덱스 쪽을 고정할 만하고, 고정했다면 '2024년 A 구간 44개'라는 규모를 힌트 유효기간 대장에 적는다.
A 등급 구간이 수천 개로 늘면 구간마다 찾아가는 비용이 주문 FULL보다 커진다. 그때는 해시 조인으로 돌아간다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황