매장 이름에 「입구」가 든 열한 곳만 골랐는데 1년 치 주문을 다 읽는다

기초SELECT버거38 / 700

상황

버거원 본사 운영팀의 「매장 주문 추이」 화면은 매장 이름 일부를 넣으면 그 말이 든 매장들의 일별 주문 수를 1년 치 보여 준다. 역세권 매장을 묶어 보려고 '입구', '터미널' 같은 말로 자주 찾는다. 개발자는 "매장 이름을 앞뒤 %로 찾으니 인덱스를 못 탄다. 매장명에 전문 검색 인덱스를 걸어 달라"고 요청했다. 아래는 '입구'로 2013년을 본 계획이다.

테이블과 인덱스

SHOP 매장: 200행 · 5블록 · SHOP_NO 'SH0001' · SHOP_NM(홍대입구점, 서울대입구점 …) · '입구'가 든 매장 11곳
ORD 주문: 1,000,000행 · 22,422블록(전체 스캔 22,239) · ORD_DT VARCHAR2(8) 2012-01-01 ~ 2013-12-31 · 날짜순 적재 · 매장 한 곳의 1년 주문은 약 2,500건이고 테이블 전체에 흩어져 있다
인덱스 ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS): 리프 3,774 · SHOP_NO로 시작하는 인덱스는 없다 · 통계 최신

원본 SQL

sql

SELECT s.SHOP_NM, o.ORD_DT, COUNT(*) AS ord_cnt
  FROM SHOP s, ORD o
 WHERE s.SHOP_NM LIKE '%' || :v_nm || '%'
   AND o.SHOP_NO = s.SHOP_NO
   AND o.ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY s.SHOP_NM, o.ORD_DT

실행계획 학습용 예시 · 매장 이름은 적재 데이터 그대로

xplan

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |   4015 |00:00:00.31 |   22244 |
|   1 |  HASH GROUP BY      |      |      1 |   3650 |   4015 |00:00:00.31 |   22244 |
|*  2 |   HASH JOIN         |      |      1 |  24966 |  27500 |00:00:00.30 |   22244 |
|*  3 |    TABLE ACCESS FULL| SHOP |      1 |     10 |     11 |00:00:00.01 |       5 |
|*  4 |    TABLE ACCESS FULL| ORD  |      1 |    499K|    500K|00:00:00.21 |   22239 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."SHOP_NO"="S"."SHOP_NO")
   3 - filter("S"."SHOP_NM" LIKE '%'||:V_NM||'%')
   4 - filter(("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<=:V_TO))

문제

앞뒤가 %인 LIKE가 이 SQL의 읽은 블록 가운데 얼마를 차지하는지 실행계획으로 설명하고, 전문 검색 인덱스 요청이 맞는지 판단하시오.
결과가 바뀌지 않게 인덱스를 설계해 고른 매장의 주문만 읽도록 하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매장을 고르는 앞뒤 % LIKE 는 그대로 둔다 (SHOP 은 200행, 5블록)
-- 고른 매장의 주문을 찾아가는 길을 만든다. ORD_DT 까지 담아 테이블에 가지 않는다
CREATE INDEX IX_ORD_SHOP_DT ON ORD (SHOP_NO, ORD_DT);

SELECT s.SHOP_NM, o.ORD_DT, COUNT(*) AS ord_cnt
  FROM SHOP s, ORD o
 WHERE s.SHOP_NM LIKE '%' || :v_nm || '%'
   AND o.SHOP_NO = s.SHOP_NO
   AND o.ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY s.SHOP_NM, o.ORD_DT;

개선 후 실행계획 학습용 예시 · 매장 이름은 적재 데이터 그대로

xplan

------------------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |      1 |        |   4015 |00:00:00.02 |     126 |
|   1 |  HASH GROUP BY      |                |      1 |   3650 |   4015 |00:00:00.02 |     126 |
|   2 |   NESTED LOOPS      |                |      1 |  24966 |  27500 |00:00:00.02 |     126 |
|*  3 |    TABLE ACCESS FULL| SHOP           |      1 |     10 |     11 |00:00:00.01 |       5 |
|*  4 |    INDEX RANGE SCAN | IX_ORD_SHOP_DT |     11 |   2496 |  27500 |00:00:00.01 |     121 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("S"."SHOP_NM" LIKE '%'||:V_NM||'%')
   4 - access("O"."SHOP_NO"="S"."SHOP_NO" AND "O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<=:V_TO)

모범 답안 서술

앞뒤 %인 LIKE는 3번 줄 SHOP에 걸려 있다. SHOP은 200행, 5블록이라 인덱스가 있어도 FULL이 낫고, 실제로 5블록을 읽어 매장 11곳을 골랐다. 앞뒤 % LIKE는 이 SQL에서 문제가 아니다. 읽은 블록 22,244 가운데 22,239는 4번 줄 ORD 전체 스캔이다. 2013년 1년 치는 주문의 절반(50만 건)이라 ORD_X01로 찾아가면 더 비싸므로 FULL이 되었고, 그중 고른 매장의 주문 27,500건만 해시 조인으로 남긴다. 그래서 전문 검색 인덱스는 필요 없다. 매장명을 더 빨리 찾아도 읽는 양은 5블록에서 줄 것이 없고, 인덱스 관리 부담만 는다. 필요한 것은 고른 매장의 주문을 찾아가는 길이다. ORD에 SHOP_NO로 시작하는 (SHOP_NO, ORD_DT) 인덱스를 만들면 매장 11곳마다 인덱스 범위를 찾아가는 NL 조인이 된다(4번 줄 Starts 11). ORD_DT 범위까지 access가 되고, 세는 데 필요한 컬럼이 인덱스에 다 있어 테이블에도 가지 않는다. 매장 한 곳의 1년 치는 항목 2,500개, 리프 9블록 남짓이라 Buffers는 22,244 → 126으로 준다. 대가는 100만 행짜리 인덱스 하나다. POS 주문을 넣을 때마다 이 인덱스도 고친다. 이 인덱스는 매장별 조회 화면에도 두루 쓰이므로 값을 한다.

해설

앞 %가 문제인지는 그 조건이 어느 테이블에 걸렸는지, 그 테이블이 몇 블록인지로 판단한다. 200행 마스터의 앞뒤 % LIKE는 전부 읽어도 5블록이다.
검색어가 짧아 매장 100곳을 고르면 NL이 100번 돌고 항목 25만 개를 읽는다. 그때는 해시 조인과 FULL이 다시 나을 수 있으므로 검색어에 최소 글자 수를 둔다.
이 인덱스는 NL 조인의 inner 쪽 인덱스이기도 하다. 조인 순서와 방식은 JOIN 단원에서 다룬다(교재 06화 §3 NL 조인의 총일량).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 매장 이름은 적재 데이터 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 매장 이름은 적재 데이터 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황