JOIN-059

매장 계약 이력을 붙이는 SQL 넷에 인덱스를 달아 달라는데 이력 테이블은 6블록뿐이다

실전DDL버거209 / 700

상황

본사는 매장의 계약 형태(직영, 가맹), 점주, 수수료율이 바뀔 때마다 매장 계약 이력에 새 구간을 연다. 이 이력을 붙이는 SQL 네 개가 새로 생겼고, 개발팀은 "이력 조인이 다 느릴 테니 이력 테이블에 인덱스를 두세 개 달아 달라"는 요청서를 올렸다. 박팀장은 "주문 테이블에는 POS가 실시간으로 넣으니 인덱스는 하나까지만 허락한다. 네 SQL을 다 보고 꼭 필요한 것만 정해 달라"라고 했다. 지금 느리다고 민원이 들어온 것은 경영진 보고 기간에 하루 50번쯤 도는 Q3뿐이다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · 날짜순 적재(하루 약 1,370건이 약 31블록에 모여 있다) · SHOP_NO 200곳, 매장마다 하루 약 6.8건
SHOP_CTRT_HIS 매장 계약 이력 (이 문제에서 쓰는 파생 테이블, 선분이력): SHOP_NO · STA_DT · END_DT (VARCHAR2(8), 그날까지 포함, 지금 구간은 '99991231') · CTRT_TP · OWNER_NM · FEE_RT · CHG_RSN (신규, 가맹전환, 점주변경) · 612행 · 6블록 · SHOP_CTRT_HIS_PK (SHOP_NO, STA_DT) · 매장마다 평균 3구간
2013년에 직영에서 가맹으로 바뀐 매장은 12곳이다 · 구간은 겹치거나 끊기지 않는다 · 통계 최신

원본 SQL

sql

-- [Q1] 점주 앱 첫 화면: 내 매장의 지금 계약 (하루 2만 번)
SELECT CTRT_TP, OWNER_NM, FEE_RT
  FROM SHOP_CTRT_HIS
 WHERE SHOP_NO = :shop
   AND :today BETWEEN STA_DT AND END_DT;

-- [Q2] 월 가맹 수수료 정산: 주문 당시 계약의 수수료율로 (월 1회)
SELECT o.SHOP_NO, h.CTRT_TP, h.FEE_RT, COUNT(*) AS ord_cnt
  FROM ORD o, SHOP_CTRT_HIS h
 WHERE o.ORD_DT BETWEEN :v_from AND :v_to
   AND h.SHOP_NO = o.SHOP_NO
   AND o.ORD_DT BETWEEN h.STA_DT AND h.END_DT
 GROUP BY o.SHOP_NO, h.CTRT_TP, h.FEE_RT;

-- [Q3] 가맹 전환 효과: 전환한 매장의 전환 전 30일과 후 30일 주문 수 (보고 기간 하루 50번)
SELECT h.SHOP_NO, h.STA_DT AS CHG_DT,
       COUNT(CASE WHEN o.ORD_DT <  h.STA_DT THEN 1 END) AS before_30d,
       COUNT(CASE WHEN o.ORD_DT >= h.STA_DT THEN 1 END) AS after_30d
  FROM SHOP_CTRT_HIS h, ORD o
 WHERE h.CHG_RSN = '가맹전환'
   AND o.SHOP_NO = h.SHOP_NO
   AND o.ORD_DT >= TO_CHAR(TO_DATE(h.STA_DT, 'YYYYMMDD') - 30, 'YYYYMMDD')
   AND o.ORD_DT <  TO_CHAR(TO_DATE(h.STA_DT, 'YYYYMMDD') + 30, 'YYYYMMDD')
 GROUP BY h.SHOP_NO, h.STA_DT;

-- [Q4] 계약 이력 화면: 매장 하나의 전체 이력 (하루 수십 번)
SELECT STA_DT, END_DT, CTRT_TP, OWNER_NM, FEE_RT
  FROM SHOP_CTRT_HIS
 WHERE SHOP_NO = :shop
 ORDER BY STA_DT;

실행계획 학습용 예시

xplan

[Q1] Buffers 5 (SHOP_CTRT_HIS_PK 범위 2 + 테이블 3) · [Q4] Buffers 5
[Q2] 2013년 7월 · ORD_X01 범위(42,408건) + 이력 FULL 6 → HASH JOIN · Buffers 1,082 · 0.09초 (월 1회)

[Q3]
-----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |      1 |        |     12 |00:00:00.93 |   22428 |
|   1 |  HASH GROUP BY      |               |      1 |     12 |     12 |00:00:00.93 |   22428 |
|*  2 |   HASH JOIN         |               |      1 |   1250 |   4896 |00:00:00.92 |   22428 |
|*  3 |    TABLE ACCESS FULL| SHOP_CTRT_HIS |      1 |     12 |     12 |00:00:00.01 |       6 |
|   4 |    TABLE ACCESS FULL| ORD           |      1 |   1000K|   1000K|00:00:00.21 |   22422 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."SHOP_NO"="H"."SHOP_NO")
       filter(("O"."ORD_DT">=TO_CHAR(TO_DATE("H"."STA_DT",'YYYYMMDD')-30,'YYYYMMDD') AND
              "O"."ORD_DT"<TO_CHAR(TO_DATE("H"."STA_DT",'YYYYMMDD')+30,'YYYYMMDD')))
   3 - filter("H"."CHG_RSN"='가맹전환')

문제

네 SQL 각각에 대해 지금 일량이 어디서 나오는지 쓰고, 이력 테이블에 인덱스를 더 만들면 어느 SQL이 얼마나 나아지는지 판단하시오.
Q3를 빠르게 하려면 어느 테이블에서 출발해야 하는지 정하고, 그때 필요한 인덱스를 DDL로 작성하시오. 고친 Q3의 일량을 숫자로 쓰시오.
새 인덱스의 대가와 그 인덱스가 함께 덕을 볼 수 있는 화면을 쓰고, 만들지 않을 인덱스와 그 이유를 정리하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 주문 테이블에 하나: 매장 '=' 를 앞에, 주문일 범위를 뒤에 (시각까지 담아 매장별 최근 주문 화면도 함께 쓴다)
CREATE INDEX ORD_X02 ON ORD (SHOP_NO, ORD_DT, ORD_HMS) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- Q1 ~ Q4 와 POS 적재를 재 본 뒤 VISIBLE

-- [Q3] 전환 구간 12개에서 출발해, 구간마다 그 매장의 전후 60일만 인덱스로 센다
SELECT /*+ LEADING(h o) USE_NL(o) INDEX(o ORD_X02) */
       h.SHOP_NO, h.STA_DT AS CHG_DT,
       COUNT(CASE WHEN o.ORD_DT <  h.STA_DT THEN 1 END) AS before_30d,
       COUNT(CASE WHEN o.ORD_DT >= h.STA_DT THEN 1 END) AS after_30d
  FROM SHOP_CTRT_HIS h, ORD o
 WHERE h.CHG_RSN = '가맹전환'
   AND o.SHOP_NO = h.SHOP_NO
   AND o.ORD_DT >= TO_CHAR(TO_DATE(h.STA_DT, 'YYYYMMDD') - 30, 'YYYYMMDD')
   AND o.ORD_DT <  TO_CHAR(TO_DATE(h.STA_DT, 'YYYYMMDD') + 30, 'YYYYMMDD')
 GROUP BY h.SHOP_NO, h.STA_DT;

-- 만들지 않는 것: SHOP_CTRT_HIS 의 (SHOP_NO, END_DT) · (CTRT_TP) 등 이력 쪽 인덱스 (612행 · 6블록)
-- Q1 · Q2 · Q4 는 그대로 둔다

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

xplan

[Q3]
-----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |      1 |        |     12 |00:00:00.01 |      70 |
|   1 |  HASH GROUP BY      |               |      1 |     12 |     12 |00:00:00.01 |      70 |
|   2 |   NESTED LOOPS      |               |      1 |   1250 |   4896 |00:00:00.01 |      70 |
|*  3 |    TABLE ACCESS FULL| SHOP_CTRT_HIS |      1 |     12 |     12 |00:00:00.01 |       6 |
|*  4 |    INDEX RANGE SCAN | ORD_X02       |     12 |    104 |   4896 |00:00:00.01 |      64 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("H"."CHG_RSN"='가맹전환')
   4 - access("O"."SHOP_NO"="H"."SHOP_NO" AND "O"."ORD_DT">=TO_CHAR(TO_DATE("H"."STA_DT",'YYYYMMDD')-30,'YYYYMMDD')
              AND "O"."ORD_DT"<TO_CHAR(TO_DATE("H"."STA_DT",'YYYYMMDD')+30,'YYYYMMDD'))

[Q1] [Q4] Buffers 5 그대로 · [Q2] Buffers 1,082 그대로 (ORD_X01 범위 + HASH JOIN)

모범 답안 서술

네 SQL의 일량: Q1과 Q4는 SHOP_CTRT_HIS_PK로 매장 하나의 구간 3개쯤을 읽는다(5블록). 이력 테이블이 612행, 6블록이라 (SHOP_NO, END_DT) 같은 인덱스를 더해도 Q1은 5블록이 4블록이 될 뿐이고 Q4는 그대로다. Q2는 한 달 주문 42,408건을 ORD_X01로 읽고 작은 이력(612행)과 해시 조인한다(1,082블록, 월 1회). 매장마다 구간이 3개뿐이라 해시 조인 안의 비교도 적다. 요청서대로 이력 쪽에 인덱스를 두세 개 달면 얻는 것은 거의 없고, 이력을 고칠 때마다 인덱스도 고쳐야 한다. 이력 쪽에는 만들지 않는다. Q3가 느린 까닭: Q3에서 가장 많이 거르는 조건은 CHG_RSN = '가맹전환'이고 남는 것은 12구간이다. 구간 하나가 정하는 것은 '어느 매장의 어느 60일'이므로, 12구간에서 출발해 주문 쪽에서 그 범위만 바로 짚으면 된다. 그런데 ORD에는 매장을 선두로 한 인덱스가 없다. ORD_X01 (ORD_DT, ORD_HMS)로는 매장 하나의 60일을 찾으려고 60일 치 전 매장 주문(약 8만 건)을 훑어야 하므로, 옵티마이저는 주문 100만 건을 FULL로 읽어(22,422블록) 해시 조인했다. 남은 것은 4,896건이다. 고침: ORD에 (SHOP_NO, ORD_DT, ORD_HMS) 인덱스를 하나 만든다. 이력이 바깥쪽이 되면 구간의 매장과 전후 날짜가 SHOP_NO = :1 AND ORD_DT >= :2 AND ORD_DT < :3이라는 access 조건이 되어, 구간마다 그 매장의 60일 치 약 408항목(2 ~ 3리프 블록)만 읽는다. 세는 데 필요한 칸이 인덱스에 다 있어서 테이블은 읽지 않는다. Buffers는 22,428 → 70, 시간은 0.93초 → 0.01초로 준다(하루 50번이면 약 112만 → 3,500블록). 대가와 덤: POS가 주문을 넣을 때마다(하루 약 1,370건) 인덱스 하나를 더 고친다. 매장 번호가 선두라 새 항목이 200곳으로 흩어져 들어가므로 오른쪽 끝 블록 하나에 몰리지는 않지만, 리프 분할은 늘어난다. 그래서 INVISIBLE로 만들어 Q1 ~ Q4의 계획과 POS 적재 시간을 재 본 뒤 공개한다. 이 인덱스는 점주 앱의 「오늘 주문」이나 매장별 최근 주문처럼 매장 하나의 주문을 찾는 화면도 함께 쓸 수 있어서, 박팀장이 허락한 한 자리에 가장 값어치가 있다. 만들지 않는 것: 이력 쪽의 (SHOP_NO, END_DT), (CTRT_TP), (CHG_RSN) 인덱스다. 612행짜리 테이블에서는 FULL 한 번(6블록)이 어느 인덱스보다 싸거나 비슷하다. Q2를 위한 주문 쪽 인덱스도 만들지 않는다. 한 달 범위는 이미 ORD_X01로 모여 있고 월 1회다.

해설

이력 조인이 느리면 이력에 인덱스, 가 아니다: 선분이력 조인에서 비용은 대개 사실(주문) 쪽에 있다. 이력이 작으면 이력 쪽 인덱스 순서는 거의 상관이 없고, 이력이 출발점이 될 때 사실 쪽에 '키 = , 날짜 범위' 인덱스가 있느냐가 갈림길이다.
다른 정답: 매장 · 일자별 주문 수 요약 테이블(200곳 × 730일 = 14만 6천 행)을 밤마다 채우면 Q3는 요약만 읽어 몇 블록이 되고 ORD에 인덱스를 더하지 않아도 된다. 대신 요약이 하루 늦고 적재 배치를 관리해야 한다. 보고 기간에만 쓰는 화면이면 이쪽도 합리적이다.
Q1을 굳이 고친다면: 지금 구간은 END_DT = '99991231'이라 등치로 쓸 수 있다. 그래도 매장마다 구간이 3개라 차이는 한 블록이다. 이력이 수백 구간으로 늘어날 때 다시 본다.
날짜 칸이 문자(VARCHAR2(8))라 30일 전후를 구할 때 TO_DATE로 바꿨다가 TO_CHAR로 되돌렸다. 가공은 이력 쪽(바깥, 12행)에만 하고 주문 쪽 ORD_DT는 그대로 두었기 때문에 인덱스 access가 된다 (교재 03화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황