매장 이름과 주소 두 칸을 붙이는 데 매장 테이블을 1만 4천 번 찾는다

기초SELECT버거243 / 700

상황

본사 영업관리팀은 매달 1일에 지난달 주문 전체를 엑셀로 내려받는다(주문번호, 일시, 매장명, 매장 주소). 2013년 12월 치 42,408건을 내려받을 때 읽은 블록의 대부분이 매장 테이블이다. 개발자는 "매장은 200곳뿐이라 스칼라 서브쿼리 캐시가 다 기억한다. 칸이 두 개라 서브쿼리도 두 개 썼다"라고 했다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · 날짜순으로 쌓여 있다 · 2013년 12월 42,408건 · SHOP_NO 200종류가 고르게 섞여 있다(매장마다 약 212건)
ORD_X01 (ORD_DT, ORD_HMS): CF 22,223(좋음) · 한 달 치 리프 약 140블록
SHOP: 200행 · 5블록 · SHOP_PK (SHOP_NO) · SHOP_NM · ADDR
통계 최신

원본 SQL

sql

SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS, o.SHOP_NO,
       (SELECT s.SHOP_NM FROM SHOP s WHERE s.SHOP_NO = o.SHOP_NO) AS shop_nm,
       (SELECT s.ADDR    FROM SHOP s WHERE s.SHOP_NO = o.SHOP_NO) AS shop_addr
  FROM ORD o
 WHERE o.ORD_DT BETWEEN '20131201' AND '20131231'
 ORDER BY o.ORD_DT, o.ORD_HMS

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |  42408 |00:00:00.21 |   30570 |
|   1 |  TABLE ACCESS BY INDEX ROWID | SHOP    |   7374 |      1 |   7374 |00:00:00.03 |   14748 |
|*  2 |   INDEX UNIQUE SCAN          | SHOP_PK |   7374 |      1 |   7374 |00:00:00.01 |    7374 |
|   3 |  TABLE ACCESS BY INDEX ROWID | SHOP    |   7374 |      1 |   7374 |00:00:00.03 |   14748 |
|*  4 |   INDEX UNIQUE SCAN          | SHOP_PK |   7374 |      1 |   7374 |00:00:00.01 |    7374 |
|   5 |  TABLE ACCESS BY INDEX ROWID | ORD     |      1 |  42408 |  42408 |00:00:00.21 |   30570 |
|*  6 |   INDEX RANGE SCAN           | ORD_X01 |      1 |  42408 |  42408 |00:00:00.01 |     142 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."SHOP_NO"=:B1)
   4 - access("S"."SHOP_NO"=:B1)
   6 - access("O"."ORD_DT">='20131201' AND "O"."ORD_DT"<='20131231')

문제

1번과 3번 줄의 Starts가 각각 7,374인 이유를 설명하시오. 매장이 200곳인데 200이 아닌 까닭과 같은 수가 두 번 찍힌 까닭을 함께 쓰시오.
매장 테이블을 한 번만 읽도록 SQL을 고치시오. NL 조인으로 바꾸면 어떻게 되는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매장은 한 번만 : 5 블록짜리 SHOP 을 해시 조인의 빌드로 (FK 가 없으니 외부 조인)
SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS, o.SHOP_NO, s.SHOP_NM, s.ADDR AS shop_addr
  FROM ORD o, SHOP s
 WHERE o.ORD_DT BETWEEN '20131201' AND '20131231'
   AND s.SHOP_NO(+) = o.SHOP_NO
 ORDER BY o.ORD_DT, o.ORD_HMS;
-- 힌트로 굳힌다면 /*+ LEADING(o) USE_HASH(s) SWAP_JOIN_INPUTS(s) */

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

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |  42408 |00:00:00.08 |    1079 |          |
|   1 |  SORT ORDER BY                |         |      1 |  42408 |  42408 |00:00:00.08 |    1079 | 3651K (0)|
|*  2 |   HASH JOIN RIGHT OUTER       |         |      1 |  42408 |  42408 |00:00:00.05 |    1079 | 1463K (0)|
|   3 |    TABLE ACCESS FULL          | SHOP    |      1 |    200 |    200 |00:00:00.01 |       5 |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| ORD     |      1 |  42408 |  42408 |00:00:00.02 |    1074 |          |
|*  5 |     INDEX RANGE SCAN          | ORD_X01 |      1 |  42408 |  42408 |00:00:00.01 |     142 |          |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."SHOP_NO"(+)="O"."SHOP_NO")
   5 - access("O"."ORD_DT">='20131201' AND "O"."ORD_DT"<='20131231')

모범 답안 서술

1번과 3번 줄은 매장명과 매장 주소를 가져오는 스칼라 서브쿼리 두 개다. 스칼라 서브쿼리는 저마다 따로 실행되고 캐시도 따로 쓰므로, 같은 매장 행을 두 번 찾는다. 그래서 같은 Starts 7,374가 두 번 찍혔다. 7,374가 200이 아닌 것은 캐시의 칸이 정해져 있기 때문이다. 입력(:B1 = 매장번호) 200종류 가운데 자리를 얻은 166곳은 한 번씩만 찾았고, 자리를 못 얻은 34곳은 나올 때마다(매장마다 약 212번) 다시 찾았다(166 + 34 × 212 = 7,374). 두 서브쿼리가 같은 입력을 같은 순서로 받으니 자리를 못 얻는 매장도 같다. 매장 테이블에서만 29,496블록을 읽었고, 주문을 읽는 데는 1,074블록이 들었다. SHOP은 200행, 5블록이다. 해시 조인의 빌드로 한 번 올리고 주문 42,408건을 대어 보면 매장 읽기가 29,496 → 5블록이 된다. FK가 없으므로 스칼라 서브쿼리처럼 마스터에 없는 매장번호의 주문도 남기도록 외부 조인으로 쓴다. Buffers는 30,570 → 1,079다. 해시 조인은 순서를 지켜 주지 않으므로 SORT ORDER BY가 붙지만 42,408행은 메모리 안에서 끝난다. NL 조인으로 바꾸면 캐시도 없이 주문마다 SHOP_PK를 찾아 42,408 × 2 = 약 85,000블록이 되어 지금보다 나빠진다. 스칼라 서브쿼리는 NL 조인에 캐시를 더한 것이라, 캐시가 반쯤이라도 듣는 동안에는 NL 조인보다 낫다.

해설

두 칸을 하나로 묶는 다른 답: (SELECT s.SHOP_NM || '|' || s.ADDR FROM SHOP s WHERE s.SHOP_NO = o.SHOP_NO)처럼 이어 붙여 한 번에 가져오고 바깥에서 SUBSTR과 INSTR로 나누면 매장 읽기가 절반(14,748블록)이 된다. 내보내는 행이 적은 화면(페이징)에 맞는 방법이고, 한 달 치 전체에는 해시 조인이 낫다.
캐시가 다 담는다는 착각: 칸 수가 정해져 있어서 입력이 수백 종류만 돼도 자리를 못 얻는 값이 생긴다(교재 07화: 입력 256종류에서 55개가 자리를 못 얻었다).
정렬(SORT ORDER BY)이 싫어 NL 조인으로 고정하면 정렬은 사라지지만 매장 읽기가 약 85,000블록으로 는다. 정렬은 메모리 안에서 끝나므로(Used-Mem 약 3.6MB) 해시 조인 쪽이 싸다.
스칼라 서브쿼리를 그대로 두는 편이 나은 경우는 입력 종류가 캐시에 다 들어갈 만큼 적을 때다(교재 06화의 지역 8곳). 그래도 캐시를 들여다보는 CPU는 행 수만큼 든다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황