테마
Skip to content
튜닝 연습조인 튜닝
JOIN-044
고객 상담 통합 화면의 아우터 조인 네 개 중 셋이 정산 테이블을 통째로 읽는다
상황
한빛유통이 상담 화면을 하나로 합치면서 아래 네 SQL을 새로 만들었다. 넷 다 "짝이 없는 쪽이 있어도 행이 사라지면 안 된다"는 요구 때문에 아우터 조인으로 썼다. 운영 DB에 있는 인덱스로는 네 SQL 중 세 개가 T_ORD_STLM을 FULL로 읽는다.
박팀장은 "인덱스는 꼭 필요한 것만 만들고, 정산 야간 적재에 주는 영향까지 적어 달라"라고 했다. 호출 빈도는 업무팀이 추정했다. 아래 실행계획은 Q1이 고객 CUS_000390, Q3이 RGN03 지역의 2024년 6월, Q4가 주문 한 건으로 잰 것이다.
테이블과 인덱스
M_CUS: 2,000행 · 30블록 ·PK_M_CUS (CUS_ID)· 지역마다 250명T_ORD: 100,000행 · 896블록 ·PK_T_ORD (ORD_SEQ)·IX_T_ORD_CUS_DT (CUS_ID, ORD_DT)· 고객마다 2년에 50건T_ORD_DET: 200,000행 · 896블록 ·PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO)/M_ITM: 100행 · 5블록 ·PK_M_ITM (ITM_ID)T_ORD_STLM정산: 100,000행 · 640블록 · 인덱스 없음 · 주문 한 건에 정산 한 건(지금 데이터에서ORD_SEQ중복 0건) · 매일 밤 TRUNCATE 뒤INSERT /*+ APPEND */로 10만 행을 다시 적재한다 · 한 달 정산 약 4,170건T_ITM_EVL: 5,000행 · 48블록 ·PK_T_ITM_EVL (ITM_ID, EVL_LST_NO)· 상품마다 50건 · 탈퇴한 고객은M_CUS에서 지워진다- 통계 최신
원본 SQL
sql
-- [Q1] 고객 카드: 고객 한 명의 최근 6개월 주문과 정산 (하루 8,000번)
SELECT c.CUS_ID, c.CUS_NM, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT, s.STLM_DT, s.STLM_AMT
FROM M_CUS c
LEFT JOIN T_ORD o ON o.CUS_ID = c.CUS_ID AND o.ORD_DT >= :v_from
LEFT JOIN T_ORD_STLM s ON s.ORD_SEQ = o.ORD_SEQ
WHERE c.CUS_ID = :v_cus
ORDER BY o.ORD_DT DESC;
-- [Q2] 상품 평가 탭: 상품 하나의 최근 평가 20건과 평가자 (하루 30,000번)
SELECT e.EVL_LST_NO, e.EVL_DT, e.EVL_PT, NVL(c.CUS_NM, '탈퇴 고객') AS cus_nm
FROM T_ITM_EVL e
LEFT JOIN M_CUS c ON c.CUS_ID = e.CUS_ID
WHERE e.ITM_ID = :v_itm
ORDER BY e.EVL_LST_NO DESC
FETCH FIRST 20 ROWS ONLY;
-- [Q3] 지역 정산 현황: 지역 고객 250명의 이번 달 정산 (월말에 하루 20번)
SELECT /*+ LEADING(c s) USE_NL(s) */
c.CUS_ID, c.CUS_NM, COUNT(s.STLM_SEQ) AS stlm_cnt, NVL(SUM(s.STLM_AMT), 0) AS stlm_amt
FROM M_CUS c
LEFT JOIN T_ORD_STLM s
ON s.CUS_ID = c.CUS_ID AND s.STLM_DT >= :v_from AND s.STLM_DT < :v_to
WHERE c.RGN_ID = :v_rgn
GROUP BY c.CUS_ID, c.CUS_NM;
-- [Q4] 주문 상세 팝업: 주문 한 건의 상품과 정산 (하루 5,000번)
SELECT o.ORD_SEQ, o.ORD_DT, d.ORD_DET_NO, i.ITM_NM, d.ORD_QTY, s.STLM_SEQ, s.STLM_AMT
FROM T_ORD o
JOIN T_ORD_DET d ON d.ORD_SEQ = o.ORD_SEQ
JOIN M_ITM i ON i.ITM_ID = d.ITM_ID
LEFT JOIN T_ORD_STLM s ON s.ORD_SEQ = o.ORD_SEQ
WHERE o.ORD_SEQ = :v_ord;실행계획 학습용 예시
xplan
[Q1 · 지금]
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 |00:00:00.01 | 659 |
| 1 | SORT ORDER BY | | 1 | 13 | 13 |00:00:00.01 | 659 |
|* 2 | HASH JOIN OUTER | | 1 | 13 | 13 |00:00:00.01 | 659 |
| 3 | NESTED LOOPS OUTER | | 1 | 13 | 13 |00:00:00.01 | 19 |
| 4 | TABLE ACCESS BY INDEX ROWID | M_CUS | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 5 | INDEX UNIQUE SCAN | PK_M_CUS | 1 | 1 | 1 |00:00:00.01 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD | 1 | 13 | 13 |00:00:00.01 | 16 |
|* 7 | INDEX RANGE SCAN | IX_T_ORD_CUS_DT | 1 | 13 | 13 |00:00:00.01 | 3 |
| 8 | TABLE ACCESS FULL | T_ORD_STLM | 1 | 100K| 100K|00:00:00.01 | 640 |
-------------------------------------------------------------------------------------------------------------------
2 - access("S"."ORD_SEQ"(+)="O"."ORD_SEQ")
5 - access("C"."CUS_ID"=:V_CUS)
7 - access("O"."CUS_ID"(+)="C"."CUS_ID" AND "O"."ORD_DT"(+)>=:V_FROM)
[Q2 · Q3 · Q4 · 지금 요약]
Q2 PK_T_ITM_EVL 역순 범위 2 + T_ITM_EVL 20 + M_CUS 20번(PK, NESTED LOOPS OUTER) 42 = 64 (WINDOW NOSORT STOPKEY)
Q3 M_CUS FULL 30 + T_ORD_STLM FULL 640 × 250번(NESTED LOOPS OUTER, Starts 250) = 160,030
Q4 T_ORD · T_ORD_DET · M_ITM 을 PK 로 11 + T_ORD_STLM FULL 640(HASH JOIN OUTER) = 651문제
- 네 SQL마다 보존할 쪽과 NULL로 채워질 쪽(inner)을 정하고, 지금의 계획에서 일량이 어디서 나오는지 줄 번호와 Buffers로 설명하시오.
- 네 SQL을 함께 고려한 인덱스를 DDL로 작성하고, 고쳐야 할 SQL은 고치시오. 만들지 않기로 한 인덱스와 그 이유도 쓰시오.
- 새 인덱스가 정산 야간 적재에 주는 영향과, UNIQUE로 만들 때의 이득과 위험을 서술하시오.