JOIN-018

새로 만든 주문 화면 네 개 가운데 세 개가 주문 테이블을 통째로 읽는다

실전DDL한빛유통168 / 700

상황

한빛유통이 상담과 CRM 화면을 개편하면서 아래 네 SQL을 새로 만들었다. 개발 DB에는 PK만 있어서 네 화면 중 세 화면이 T_ORD를 FULL로 읽는다. 박팀장은 "화면마다 하나씩 만들지 말고, 같이 쓸 수 있게 꼭 필요한 만큼만 설계해 달라"라고 했다. 호출 빈도는 업무팀이 추정했다. 아래 실행계획은 Q1이 고객 CUS_000390, Q2가 2024-06-14와 상품 ITM00042, Q3이 2024-06-13, Q4가 상품 하나로 잰 것이다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ)만 있다 · 고객마다 50건(2년) · 하루 137건이 들어온다 · 하루치 주문은 테이블 전체에 흩어져 있다
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문당 1 ~ 3줄 · 상품마다 2,000줄
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · A 등급 20명 / M_ITM: 100행 · 5블록 · PK_M_ITM (ITM_ID)
T_ITM_EVL 상품평가: 5,000행 · 48블록 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO) · 상품마다 50건 · 평가 번호 순서와 평가일 순서는 다르다
통계 최신

원본 SQL

sql

-- [Q1] 상담: 고객 한 명의 최근 6개월 주문 상세 (하루 5,000번)
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, d.ORD_DET_NO, i.ITM_NM, d.ORD_QTY, d.UNT_PRC
  FROM T_ORD o, T_ORD_DET d, M_ITM i
 WHERE o.CUS_ID = :v_cus
   AND o.ORD_DT >= :v_from
   AND d.ORD_SEQ = o.ORD_SEQ
   AND i.ITM_ID  = d.ITM_ID
 ORDER BY o.ORD_DT DESC, d.ORD_DET_NO;

-- [Q2] 품질: 특정 상품이 들어간 어제 주문과 그 고객 (하루 50번)
SELECT c.CUS_ID, c.CUS_NM, o.ORD_SEQ, o.ORD_DT, d.ORD_QTY
  FROM T_ORD o, T_ORD_DET d, M_CUS c
 WHERE o.ORD_DT >= :v_dt AND o.ORD_DT < :v_dt + 1
   AND d.ORD_SEQ = o.ORD_SEQ
   AND d.ITM_ID  = :v_itm
   AND c.CUS_ID  = o.CUS_ID;

-- [Q3] CRM: VIP(A 등급) 고객의 오늘 주문 (10분마다, 하루 144번)
SELECT 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 >= :v_dt AND o.ORD_DT < :v_dt + 1;

-- [Q4] 상품 상세: 최근 평가 20건과 평가자 등급 (하루 3만 번)
SELECT e.EVL_DT, e.EVL_PT, c.CUS_NM, c.CUS_GD
  FROM T_ITM_EVL e, M_CUS c
 WHERE e.ITM_ID = :v_itm
   AND c.CUS_ID = e.CUS_ID
 ORDER BY e.EVL_DT DESC
 FETCH FIRST 20 ROWS ONLY;

실행계획 학습용 예시 · 행 수는 실데이터

xplan

[Q1 · 지금]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |      1 |        |     27 |00:00:00.01 |     964 |
|   1 |  SORT ORDER BY                   |              |      1 |     26 |     27 |00:00:00.01 |     964 |
|   2 |   NESTED LOOPS                   |              |      1 |     26 |     27 |00:00:00.01 |     964 |
|   3 |    NESTED LOOPS                  |              |      1 |     26 |     27 |00:00:00.01 |     937 |
|   4 |     NESTED LOOPS                 |              |      1 |     26 |     27 |00:00:00.01 |     935 |
|*  5 |      TABLE ACCESS FULL           | T_ORD        |      1 |     13 |     13 |00:00:00.01 |     896 |
|   6 |      TABLE ACCESS BY INDEX ROWID | T_ORD_DET    |     13 |      2 |     27 |00:00:00.01 |      39 |
|*  7 |       INDEX RANGE SCAN           | PK_T_ORD_DET |     13 |      2 |     27 |00:00:00.01 |      26 |
|*  8 |     INDEX UNIQUE SCAN            | PK_M_ITM     |     27 |      1 |     27 |00:00:00.01 |       2 |
|   9 |    TABLE ACCESS BY INDEX ROWID   | M_ITM        |     27 |      1 |     27 |00:00:00.01 |      27 |
-----------------------------------------------------------------------------------------------------------
   5 - filter(("O"."CUS_ID"=:V_CUS AND "O"."ORD_DT">=:V_FROM))

[Q2 · 지금]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |      1 |        |     13 |00:00:00.01 |    1335 |
|   1 |  NESTED LOOPS                   |              |      1 |      3 |     13 |00:00:00.01 |    1335 |
|   2 |   NESTED LOOPS                  |              |      1 |      3 |     13 |00:00:00.01 |    1322 |
|   3 |    NESTED LOOPS                 |              |      1 |      3 |     13 |00:00:00.01 |    1307 |
|*  4 |     TABLE ACCESS FULL           | T_ORD        |      1 |    137 |    137 |00:00:00.01 |     896 |
|*  5 |     TABLE ACCESS BY INDEX ROWID | T_ORD_DET    |    137 |      1 |     13 |00:00:00.01 |     411 |
|*  6 |      INDEX RANGE SCAN           | PK_T_ORD_DET |    137 |      2 |    274 |00:00:00.01 |     274 |
|*  7 |    INDEX UNIQUE SCAN            | PK_M_CUS     |     13 |      1 |     13 |00:00:00.01 |      15 |
|   8 |   TABLE ACCESS BY INDEX ROWID   | M_CUS        |     13 |      1 |     13 |00:00:00.01 |      13 |
----------------------------------------------------------------------------------------------------------
   4 - filter(("O"."ORD_DT">=:V_DT AND "O"."ORD_DT"<:V_DT+1))
   5 - filter("D"."ITM_ID"=:V_ITM)

[Q3 · 지금]
---------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |     13 |00:00:00.01 |     926 |
|*  1 |  HASH JOIN          |       |      1 |      2 |     13 |00:00:00.01 |     926 |
|*  2 |   TABLE ACCESS FULL | M_CUS |      1 |     20 |     20 |00:00:00.01 |      30 |
|*  3 |   TABLE ACCESS FULL | T_ORD |      1 |    137 |    137 |00:00:00.01 |     896 |
---------------------------------------------------------------------------------------
   1 - access("O"."CUS_ID"="C"."CUS_ID")
   2 - filter("C"."CUS_GD"='A')
   3 - filter(("O"."ORD_DT">=:V_DT AND "O"."ORD_DT"<:V_DT+1))

[Q4 · 지금]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |      1 |        |     20 |00:00:00.01 |     105 |
|*  1 |  VIEW                                    |              |      1 |     20 |     20 |00:00:00.01 |     105 |
|*  2 |   WINDOW SORT PUSHED RANK                |              |      1 |     50 |     20 |00:00:00.01 |     105 |
|   3 |    NESTED LOOPS                          |              |      1 |     50 |     50 |00:00:00.01 |     105 |
|   4 |     NESTED LOOPS                         |              |      1 |     50 |     50 |00:00:00.01 |      55 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED | T_ITM_EVL    |      1 |     50 |     50 |00:00:00.01 |      52 |
|*  6 |       INDEX RANGE SCAN                   | PK_T_ITM_EVL |      1 |     50 |     50 |00:00:00.01 |       2 |
|*  7 |      INDEX UNIQUE SCAN                   | PK_M_CUS     |     50 |      1 |     50 |00:00:00.01 |       3 |
|   8 |     TABLE ACCESS BY INDEX ROWID          | M_CUS        |     50 |      1 |     50 |00:00:00.01 |      50 |
-------------------------------------------------------------------------------------------------------------------
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("E"."EVL_DT") DESC )<=20)
   6 - access("E"."ITM_ID"=:V_ITM)

문제

네 SQL마다 드라이빙 테이블과 inner 테이블의 조인 키를 정하고, 지금(PK만 있는 상태)의 계획에서 일량이 어디서 나오는지 줄 번호와 Buffers로 설명하시오.
네 SQL을 함께 고려한 최소 인덱스 세트를 DDL로 작성하시오. 인덱스마다 컬럼 순서의 근거와 그 인덱스를 쓰는 SQL을 적고, 만들지 않기로 한 인덱스와 그 이유도 쓰시오.
인덱스 하나(T_ORD (ORD_DT, CUS_ID))로 Q1 ~ Q3을 모두 처리하는 안과 비교해, 하루 일량과 적재 비용으로 판단을 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① Q1(드라이빙)과 Q3(NL inner)이 같이 쓴다: 고객 '=' 를 앞에, 주문일 범위를 뒤에
CREATE INDEX IX_T_ORD_CUS_DT ON T_ORD (CUS_ID, ORD_DT) INVISIBLE;
-- ② Q2의 드라이빙: 하루치 주문 137건에서 출발한다 (상품 쪽에서 출발하면 2,000줄)
CREATE INDEX IX_T_ORD_DT ON T_ORD (ORD_DT) INVISIBLE;
-- ③ Q4: 상품별 평가를 평가일 역순으로 읽다가 20건에서 멈춘다
CREATE INDEX IX_T_ITM_EVL_DT ON T_ITM_EVL (ITM_ID, EVL_DT) INVISIBLE;
-- 네 SQL의 계획을 세션에서 확인한 뒤 공개한다
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;
-- 만들지 않는 것: T_ORD_DET (ITM_ID), M_CUS (CUS_GD)

개선 후 실행계획 학습용 예시 · 행 수는 실데이터

xplan

[Q1 · 개선 후]
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |      1 |        |     27 |00:00:00.01 |      84 |
|   1 |  SORT ORDER BY                           |                 |      1 |     26 |     27 |00:00:00.01 |      84 |
|   2 |   NESTED LOOPS                           |                 |      1 |     26 |     27 |00:00:00.01 |      84 |
|   3 |    NESTED LOOPS                          |                 |      1 |     26 |     27 |00:00:00.01 |      57 |
|   4 |     NESTED LOOPS                         |                 |      1 |     26 |     27 |00:00:00.01 |      55 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD           |      1 |     13 |     13 |00:00:00.01 |      16 |
|*  6 |       INDEX RANGE SCAN                   | IX_T_ORD_CUS_DT |      1 |     13 |     13 |00:00:00.01 |       3 |
|   7 |      TABLE ACCESS BY INDEX ROWID         | T_ORD_DET       |     13 |      2 |     27 |00:00:00.01 |      39 |
|*  8 |       INDEX RANGE SCAN                   | PK_T_ORD_DET    |     13 |      2 |     27 |00:00:00.01 |      26 |
|*  9 |     INDEX UNIQUE SCAN                    | PK_M_ITM        |     27 |      1 |     27 |00:00:00.01 |       2 |
|  10 |    TABLE ACCESS BY INDEX ROWID           | M_ITM           |     27 |      1 |     27 |00:00:00.01 |      27 |
----------------------------------------------------------------------------------------------------------------------
   6 - access("O"."CUS_ID"=:V_CUS AND "O"."ORD_DT">=:V_FROM)

[Q3 · 개선 후]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |     13 |00:00:00.01 |      83 |
|   1 |  NESTED LOOPS                 |                 |      1 |      2 |     13 |00:00:00.01 |      83 |
|   2 |   NESTED LOOPS                |                 |      1 |      2 |     13 |00:00:00.01 |      70 |
|*  3 |    TABLE ACCESS FULL          | M_CUS           |      1 |     20 |     20 |00:00:00.01 |      30 |
|*  4 |    INDEX RANGE SCAN           | IX_T_ORD_CUS_DT |     20 |      1 |     13 |00:00:00.01 |      40 |
|   5 |   TABLE ACCESS BY INDEX ROWID | T_ORD           |     13 |      1 |     13 |00:00:00.01 |      13 |
-----------------------------------------------------------------------------------------------------------
   3 - filter("C"."CUS_GD"='A')
   4 - access("O"."CUS_ID"="C"."CUS_ID" AND "O"."ORD_DT">=:V_DT AND "O"."ORD_DT"<:V_DT+1)

[Q2 · Q4 개선 후 요약]
  Q2  IX_T_ORD_DT 2 + T_ORD 137 + PK_T_ORD_DET 274 + T_ORD_DET 137 + M_CUS 28 = 578
  Q4  IX_T_ITM_EVL_DT (INDEX RANGE SCAN DESCENDING) 2 + T_ITM_EVL 20 + M_CUS 22 = 44  (WINDOW NOSORT STOPKEY)

모범 답안 서술

지금의 일량: Q1은 5번 줄 T_ORD FULL 896블록이 964블록의 93%다. 고객 한 명의 최근 주문 13건을 찾으려고 주문 10만 건을 다 읽었다. Q2도 4번 줄 FULL 896블록이 1,335블록의 대부분이고, Q3은 VIP 20명과 주문 테이블 FULL 896블록을 해시 조인했다(926블록). Q4는 PK로 상품의 평가 50건을 모두 읽고 고객을 50번 찾아간 뒤 정렬해서 20건을 남겼다(105블록). 드라이빙과 조인 키: Q1은 고객 조건으로 T_ORD에서 출발하고, T_ORD_DET는 ORD_SEQ, M_ITM은 ITM_ID로 PK를 찾아간다. Q2는 '어제 하루'(137건)와 '상품 하나'(2년 2,000줄) 중 하루 쪽이 훨씬 적게 남기므로 T_ORD에서 출발해 ORD_SEQ로 주문상세를 찾고 상품은 거기서 거른다. Q3은 VIP 20명(M_CUS)에서 출발하고, inner인 T_ORD를 CUS_ID로 찾아간다. Q4는 T_ITM_EVL에서 출발해 CUS_ID로 M_CUS를 찾는다. 인덱스 세트: ① T_ORD (CUS_ID, ORD_DT)는 Q1과 Q3이 같이 쓴다. Q1에서는 드라이빙 조건, Q3에서는 NL inner의 조인 키다. 둘 다 고객은 '=', 주문일은 범위이므로 CUS_ID를 앞에 둔다. ② T_ORD (ORD_DT)는 Q2가 하루치 주문을 찾는 드라이빙 인덱스다. ③ T_ITM_EVL (ITM_ID, EVL_DT)는 Q4가 평가일 역순으로 읽다가 20건에서 멈추게 한다(부분범위 처리). 만들지 않는 것: T_ORD_DET (ITM_ID) 인덱스는 만들지 않는다. Q2를 상품 쪽에서 출발시키면 2,000줄을 읽고 주문을 2,000번 찾아가 날짜로 걸러야 해서 하루 쪽(137건)에서 출발하는 것보다 비싸다. M_CUS (CUS_GD)도 만들지 않는다. 30블록짜리 테이블이라 FULL로 읽는 편이 낫다. 숫자: Q1 964 → 84, Q2 1,335 → 578, Q3 926 → 83, Q4 105 → 44다. 호출 빈도를 곱하면 하루 약 817만 → 178만 블록이다(Q1 482만 → 42만, Q4 315만 → 132만). 하나로 묶는 안: T_ORD (ORD_DT, CUS_ID) 하나로도 Q2와 Q3은 처리된다. 하지만 Q1은 ORD_DT가 앞이라 고객 한 명을 찾으려고 6개월치 약 2만 5천 건의 인덱스 구간(리프 약 70블록)을 훑어야 한다. 한 번에 약 70블록이 늘고 하루 5,000번이면 35만 블록이 는다. T_ORD는 하루 137건만 들어오므로 인덱스가 둘이어도 적재 비용은 작다. 그래서 두 개로 나눈다.

해설

화면별로 하나씩 만들면: Q1용, Q2용, Q2의 상품용 T_ORD_DET (ITM_ID), Q3용(Q1과 같은 것), Q4용으로 인덱스가 늘어난다. 인덱스는 SQL 묶음을 보고 같이 쓸 수 있는 것부터 정한다(교재 04화 §6).
Q3은 NL inner 설계다: 바깥(VIP 20명)에서 받는 값이 CUS_ID이므로 inner 인덱스는 CUS_ID로 찾아 들어갈 수 있어야 한다. Q3만 보면 (ORD_DT, CUS_ID)도 두 조건이 모두 좁아서 쓸 수 있지만, Q1을 함께 보면 CUS_ID가 앞이어야 한다.
Q4는 판단의 문제다: 상품당 50건이라 PK로 읽고 정렬해도 105블록이다. 하루 3만 번이라 평가일 인덱스로 하루 약 183만 블록을 줄일 수 있고, 상품평가는 들어오는 양이 적어 인덱스 비용이 작다. 호출이 하루 수백 번이라면 PK로 두는 것도 정답이다.
적재 비용은 테이블마다 다르다: T_ORD는 하루 137건이라 인덱스 둘의 유지 비용이 작다. 하루 수만 건이 들어오는 테이블이었다면 판단이 달라진다(교재 14화, 18화 T2). INVISIBLE로 만들어 네 SQL의 계획을 확인한 뒤 공개한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수는 실데이터 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황