JOIN-017

프리미엄셰이크 세 가지의 한 달 판매 집계가 같은 한 달치 주문상품을 세 번 읽는다

실전SELECT버거167 / 700

상황

마케팅팀이 2013년 7월 프리미엄셰이크 프로모션 성과를 보는 대시보드를 만들었다. 상품명이 프리미엄으로 시작하는 상품 3개의 일별 판매 수량을 내며, 지금은 10분마다 돈다(하루 144번). 점주 앱에도 같은 칸을 넣자는 이야기가 나와 있어서, 그렇게 되면 하루 2만 번쯤 호출된다. 개발자는 "날짜 인덱스를 태워야 빠르다"라며 힌트를 달았다. 오과장은 인덱스를 새로 만들지, SQL만 고칠지 비교한 의견서를 내라고 했다.

테이블과 인덱스

ITEM 상품: 92행 · 5블록 · ITEM_PK (ITEM_ID) · 프리미엄셰이크 3개 (ITEM_ID 71, 72, 73)
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 날짜순 적재
2013년 7월 주문상품 105,012줄이 약 2,350블록에 모여 있다 (ORD_ITEM_X01 리프 약 375블록) · 92개 상품이 고르게 팔려 프리미엄셰이크 3개의 7월 판매는 3,418줄이다
POS가 주문상품을 실시간으로 넣는다 (하루 약 3,400줄, 점심과 저녁에 몰린다) · 통계 최신

원본 SQL

sql

SELECT /*+ LEADING(t i) USE_NL(i) INDEX(i ORD_ITEM_X01) */
       i.ORD_DT, t.ITEM_NM, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ITEM t, ORD_ITEM i
 WHERE t.ITEM_NM LIKE '프리미엄%'
   AND i.ITEM_ID = t.ITEM_ID
   AND i.ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY i.ORD_DT, t.ITEM_NM
 ORDER BY i.ORD_DT, t.ITEM_NM

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |      1 |        |     93 |00:00:00.21 |    8189 |
|   1 |  SORT GROUP BY                  |              |      1 |     93 |     93 |00:00:00.21 |    8189 |
|*  2 |   FILTER                        |              |      1 |        |   3418 |00:00:00.21 |    8189 |
|   3 |    NESTED LOOPS                 |              |      1 |   3424 |   3418 |00:00:00.21 |    8189 |
|*  4 |     TABLE ACCESS FULL           | ITEM         |      1 |      3 |      3 |00:00:00.01 |       5 |
|*  5 |     TABLE ACCESS BY INDEX ROWID | ORD_ITEM     |      3 |   1141 |   3418 |00:00:00.20 |    8184 |
|*  6 |      INDEX RANGE SCAN           | ORD_ITEM_X01 |      3 |   105K |   315K |00:00:00.05 |    1134 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   4 - filter("T"."ITEM_NM" LIKE '프리미엄%')
   5 - filter("I"."ITEM_ID"="T"."ITEM_ID")
   6 - access("I"."ORD_DT">=:V_FROM AND "I"."ORD_DT"<=:V_TO)

문제

5번과 6번 줄의 Starts와 A-Rows로 이 SQL의 일량이 어디서 나오는지 설명하시오.
해시 조인으로 바꾸는 안(가), ORD_ITEM (ITEM_ID, ORD_DT) 인덱스를 만드는 안(나), ORD_ITEM (ITEM_ID, ORD_DT, ORD_ITEM_QTY) 인덱스를 만드는 안(다)을 예상 Buffers로 비교하시오.
지금(하루 144번)과 점주 앱에 들어갈 때(하루 2만 번)를 나눠 어느 안을 고를지 정하고, 그 안의 부작용까지 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 지금(마케팅 대시보드, 하루 144번): DDL 없이 해시 조인. ITEM 3행으로 build, 7월 범위는 한 번만 읽는다
SELECT /*+ LEADING(t i) USE_HASH(i) INDEX(i ORD_ITEM_X01) */
       i.ORD_DT, t.ITEM_NM, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ITEM t, ORD_ITEM i
 WHERE t.ITEM_NM LIKE '프리미엄%'
   AND i.ITEM_ID = t.ITEM_ID
   AND i.ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY i.ORD_DT, t.ITEM_NM
 ORDER BY i.ORD_DT, t.ITEM_NM;

-- 점주 앱(하루 2만 번)에 들어간다면: 집계 컬럼까지 담은 인덱스. INVISIBLE 로 만들어 POS 적재 영향을 잰 뒤 승인
CREATE INDEX ORD_ITEM_X02 ON ORD_ITEM (ITEM_ID, ORD_DT, ORD_ITEM_QTY) INVISIBLE;

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

xplan

[(가) 지금: 해시 조인]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |              |      1 |        |     93 |00:00:00.06 |    2733 |
|   1 |  SORT GROUP BY                          |              |      1 |     93 |     93 |00:00:00.06 |    2733 |
|*  2 |   FILTER                                |              |      1 |        |   3418 |00:00:00.06 |    2733 |
|*  3 |    HASH JOIN                            |              |      1 |   3424 |   3418 |00:00:00.06 |    2733 |
|*  4 |     TABLE ACCESS FULL                   | ITEM         |      1 |      3 |      3 |00:00:00.01 |       5 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD_ITEM     |      1 |   105K |   105K |00:00:00.03 |    2728 |
|*  6 |      INDEX RANGE SCAN                   | ORD_ITEM_X01 |      1 |   105K |   105K |00:00:00.01 |     378 |
------------------------------------------------------------------------------------------------------------------

[(다) 점주 앱용: ORD_ITEM_X02 (ITEM_ID, ORD_DT, ORD_ITEM_QTY)]
------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |     93 |00:00:00.01 |      26 |
|   1 |  SORT GROUP BY        |              |      1 |     93 |     93 |00:00:00.01 |      26 |
|*  2 |   FILTER              |              |      1 |        |   3418 |00:00:00.01 |      26 |
|   3 |    NESTED LOOPS       |              |      1 |   3424 |   3418 |00:00:00.01 |      26 |
|*  4 |     TABLE ACCESS FULL | ITEM         |      1 |      3 |      3 |00:00:00.01 |       5 |
|*  5 |     INDEX RANGE SCAN  | ORD_ITEM_X02 |      3 |   1141 |   3418 |00:00:00.01 |      21 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  (가) 3 - access("I"."ITEM_ID"="T"."ITEM_ID")
       6 - access("I"."ORD_DT">=:V_FROM AND "I"."ORD_DT"<=:V_TO)
  (다) 5 - access("I"."ITEM_ID"="T"."ITEM_ID" AND "I"."ORD_DT">=:V_FROM AND "I"."ORD_DT"<=:V_TO)

모범 답안 서술

원인: 6번 줄 ORD_ITEM_X01의 Starts는 3이고 A-Rows는 315K다. inner 인덱스 (ORD_DT, ORD_HMS)에 조인 키 ITEM_ID가 없어서, 상품 3개마다 7월 한 달치 105,012줄의 인덱스 구간을 처음부터 끝까지 다시 읽었다. 5번 줄은 그 315K행의 테이블 블록을 모두 읽은 뒤 filter("I"."ITEM_ID"="T"."ITEM_ID")로 3,418행만 남겼다. 한 달치 약 2,350블록을 세 번 읽은 것이 Buffers 8,189의 대부분이다. (가) 해시 조인: ITEM 3행으로 해시 테이블을 만들고 7월 범위를 한 번만 읽으며 맞춰 본다. 인덱스 378블록과 테이블 약 2,350블록으로 약 2,730블록이다. DDL이 필요 없다. (나) (ITEM_ID, ORD_DT) 인덱스: 상품마다 그 상품의 7월 줄만 찾아가므로 인덱스는 3번에 약 18블록이면 된다. 하지만 날짜순으로 쌓인 테이블에서 한 상품의 7월 판매 1,140줄은 7월 2,350블록에 흩어져 있다. 상품마다 900개 남짓한 블록을 따로 읽어야 해서 테이블 액세스가 약 2,700블록이고, 전체는 해시 조인과 거의 같다. 인덱스만 늘고 이득이 없다. (다) (ITEM_ID, ORD_DT, ORD_ITEM_QTY) 인덱스: 집계할 수량까지 인덱스에 있어 테이블을 읽지 않는다. 상품마다 리프 몇 블록이면 되어 전체 26블록이다. 선택: 지금은 (가)를 고른다. 하루 144번이면 약 39만 블록이라 부담이 없고, 운영 테이블에 손대지 않는다. 점주 앱에 들어가 하루 2만 번이 되면 (가)는 하루 약 5,466만 블록, (다)는 52만 블록이므로 그때 (다)를 만든다. 부작용: (다)는 POS가 주문상품을 INSERT할 때마다 함께 갱신된다. 날짜순으로 오른쪽 끝에만 쌓이는 ORD_ITEM_X01과 달리 ITEM_ID가 앞이라 상품 92개의 리프 블록에 흩어져 들어가므로, 점심과 저녁 피크에 갱신 부담과 블록 분할이 는다. 247만 행만큼 인덱스 크기도 는다. 그래서 INVISIBLE로 만들어 피크 시간의 INSERT 응답 시간과 redo를 잰 뒤 승인받는다. 커버링은 이 SQL 모양에 맞춘 설계라서, 대시보드에 컬럼이 하나 늘면 다시 테이블 액세스가 생긴다는 점도 의견서에 적는다.

해설

inner 인덱스의 두 조건: NL의 inner 인덱스는 조인 키로 찾아 들어갈 수 있어야 하고(ITEM_ID 선두), 거기서 읽은 행이 테이블에 모여 있거나 테이블에 갈 필요가 없어야 한다. (나)는 첫 조건만 채워서 이득이 없다(교재 04화 §1 클러스터링 팩터).
ORD_ITEM_X01을 고칠 수는 없나: (ORD_DT, ORD_HMS)를 다른 순서로 바꾸면 이 SQL은 좋아질 수 있지만, 시간대로 주문상품을 찾는 다른 SQL이 망가진다. 이미 쓰이는 인덱스의 컬럼 순서를 바꾸기 전에는 그 인덱스를 쓰는 SQL을 모두 찾는다.
범위가 넓어지면: 1년을 조회하면 (가)는 약 3만 2천 블록, (다)는 약 150블록이다. 범위가 넓을수록 커버링 인덱스의 이득이 커진다.
해시 조인을 다시 보기: 이 SQL에서 해시 조인은 'NL보다 무거운 방식'이 아니라 inner 범위를 한 번만 읽게 해 주는 방식이다. build 쪽이 3행이라 메모리도 거의 쓰지 않는다(교재 06화 §4).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황