JOIN-039

전 매장 오늘 현황판이 1분마다 오늘 주문 범위를 200번 다시 읽는다

중급SELECT버거189 / 700

상황

본사 상황실의 「전 매장 오늘 현황」은 매장 200곳의 오늘 주문 건수와 마지막 주문 시각을 1분마다 새로 띄운다(영업시간 14시간 동안 하루 840번). 주문이 없는 매장도 0으로 보여야 해서 아우터 조인으로 썼다. 개발자는 "NL 조인이니 오늘 주문 1,400건을 하나씩 읽으면서 매장을 붙일 줄 알았다"라고 한다. 아래는 2013-06-14 밤 9시에 뜬 실행계획이다.

테이블과 인덱스

SHOP 매장: 200행 · 5블록 · PK (SHOP_NO)
ORD 주문: 1,000,000행 · 22,422블록 · 하루 약 1,370건
인덱스 ORD_X01 (ORD_DT, ORD_HMS): 날짜순 적재, CF 22,223 · 하루치는 인덱스 리프 약 6블록, 테이블 약 31블록
SHOP_NO로 시작하는 인덱스는 없다 · 2013-06-14 주문 1,368건 · 그날 주문이 없는 매장 3곳(리모델링) · 통계 최신

원본 SQL

sql

SELECT /*+ USE_NL(o) */           -- 주문은 NL 로 붙인다 (오늘 주문부터 읽게)
       s.SHOP_NO, s.SHOP_NM,
       COUNT(o.ORD_NO) AS ord_cnt,
       MAX(o.ORD_HMS)  AS last_hms
  FROM SHOP s, ORD o
 WHERE o.SHOP_NO(+) = s.SHOP_NO
   AND o.ORD_DT(+)  = :v_dt
 GROUP BY s.SHOP_NO, s.SHOP_NM
 ORDER BY s.SHOP_NO

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |      1 |        |    200 |00:00:00.19 |    7609 |
|   1 |  SORT GROUP BY                        |         |      1 |    200 |    200 |00:00:00.19 |    7609 |
|   2 |   NESTED LOOPS OUTER                  |         |      1 |   1370 |   1371 |00:00:00.18 |    7609 |
|   3 |    TABLE ACCESS FULL                  | SHOP    |      1 |    200 |    200 |00:00:00.01 |       7 |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |    200 |      7 |   1368 |00:00:00.17 |    7602 |
|*  5 |     INDEX RANGE SCAN                  | ORD_X01 |    200 |   1370 |    274K|00:00:00.04 |    1200 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("O"."SHOP_NO"(+)="S"."SHOP_NO")
   5 - access("O"."ORD_DT"(+)=:V_DT)

문제

개발자의 기대와 달리 어느 테이블이 드라이빙이 됐는지 계획에서 찾고, 아우터 조인을 NL로 처리할 때 순서가 왜 그렇게 정해지는지 설명하시오.
7,609블록이 어디서 나오는지 4번 줄과 5번 줄의 Starts와 A-Rows로 설명하시오.
개선 SQL을 작성하고, ORD (SHOP_NO, ORD_DT) 인덱스를 만들어 NL을 유지하는 안과 숫자로 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매장 200 행을 build 로 두고 오늘 주문 범위는 한 번만 읽는다 (HASH JOIN OUTER)
SELECT /*+ LEADING(s o) USE_HASH(o) */
       s.SHOP_NO, s.SHOP_NM,
       COUNT(o.ORD_NO) AS ord_cnt,
       MAX(o.ORD_HMS)  AS last_hms
  FROM SHOP s, ORD o
 WHERE o.SHOP_NO(+) = s.SHOP_NO
   AND o.ORD_DT(+)  = :v_dt
 GROUP BY s.SHOP_NO, s.SHOP_NM
 ORDER BY s.SHOP_NO
-- 같은 뜻 : 오늘 주문을 매장별로 먼저 묶은 인라인 뷰(197 행)를 SHOP 에 아우터 조인한다

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

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |      1 |        |    200 |00:00:00.01 |      45 |
|   1 |  SORT GROUP BY                        |         |      1 |    200 |    200 |00:00:00.01 |      45 |
|*  2 |   HASH JOIN OUTER                     |         |      1 |   1370 |   1371 |00:00:00.01 |      45 |
|   3 |    TABLE ACCESS FULL                  | SHOP    |      1 |    200 |    200 |00:00:00.01 |       7 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |   1370 |   1368 |00:00:00.01 |      38 |
|*  5 |     INDEX RANGE SCAN                  | ORD_X01 |      1 |   1370 |   1368 |00:00:00.01 |       6 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."SHOP_NO"(+)="S"."SHOP_NO")
   5 - access("O"."ORD_DT"(+)=:V_DT)

모범 답안 서술

계획 3번 줄 SHOP이 바깥(드라이빙)이고, 4번과 5번 줄의 ORD가 안쪽(inner)이다. 아우터 조인을 NL로 처리하면 보존할 쪽(SHOP)이 언제나 드라이빙이 된다. NL은 바깥 한 행마다 안쪽을 찾아보고 짝이 없으면 NULL로 채운 행을 내보내는 방식이라, 보존할 행이 바깥에 있어야 하기 때문이다. USE_NL(o)는 "o를 안쪽에 두고 NL로 붙여라"라는 뜻이어서 개발자의 기대와 반대로 매장부터 읽었다. LEADING(o)를 더해도 NL로는 주문부터 읽는 순서를 만들 수 없다. 그래서 매장 200곳마다 5번 줄에서 ORD_X01로 오늘 주문 범위 전체를 다시 읽었다. Starts 200, A-Rows 274K(200 × 1,368)이고, 4번 줄에서 테이블을 읽은 뒤 SHOP_NO로 걸러 1,368건만 남겼다. 한 번에 인덱스 6블록과 테이블 약 32블록이므로 200번이면 7,602블록이다. 해시 조인으로 바꾸면 매장 200행을 build로 올리고 오늘 주문 범위는 한 번만 읽는다(HASH JOIN OUTER). Buffers는 7,609 → 45블록, 하루 840번이면 약 639만 → 3만 8천 블록이다. 오늘 주문을 매장별로 먼저 묶은 뷰(197행)를 매장에 아우터 조인해도 같은 45블록이다. ORD (SHOP_NO, ORD_DT) 인덱스로 NL을 유지하면 매장마다 인덱스 3블록 안팎과 그 매장 주문 7건 정도를 하나씩 찾아가 약 600 + 1,368 ≈ 2,000블록이다. 해시 조인보다 40배 넘게 비싸고, 하루 1,370건씩 들어오는 주문마다 인덱스를 하나 더 고쳐야 한다. 전 매장을 한꺼번에 보는 현황판에는 해시 조인이 맞다. 그 인덱스는 매장 하나만 보는 점주 앱 화면이 생기면 그때 따로 판단한다.

해설

해시 조인이면 순서를 바꿀 수 있다: 해시 아우터 조인은 보존할 쪽을 build로 쓰는 HASH JOIN OUTER와, inner 쪽을 build로 쓰는 HASH JOIN RIGHT OUTER(SWAP_JOIN_INPUTS(o)) 둘 다 된다. 여기서는 두 쪽이 모두 작아 어느 쪽이든 45블록이다. 순서가 고정되는 것은 NL 아우터 조인뿐이다 (JOIN-021).
USE_NL의 인자: USE_NL(o)는 o를 inner로 NL 조인하라는 뜻이지 o부터 읽으라는 뜻이 아니다. 드라이빙은 LEADING이 정하고, 아우터 NL에서는 그마저 보존할 쪽으로 고정된다 (교재 06화 §7).
inner 인덱스가 조인 키를 못 받을 때: ORD_X01은 바깥에서 받은 SHOP_NO로 범위를 좁히지 못해서 매번 하루치를 다 훑었다. NL inner 인덱스는 바깥에서 받는 조인 키로 시작해야 한다 (JOIN-007, JOIN-011과 같은 원리).
새로 고침 주기도 본다: 1분마다 200줄을 다시 만드는 화면이라 요약 테이블을 주문 적재 때 함께 고치는 안도 있다. 그만큼 POS 적재가 무거워지므로, 45블록이면 SQL만 고치는 쪽이 낫다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황