권역을 하나 더하면 월 주문 집계 배치도 그만큼 길어진다

중급INSERT버거294 / 700

상황

본사 영업기획팀의 「권역별 월 주문 수」는 서울, 경기, 부산 세 권역의 2013년 월별 주문 수를 요약 테이블 RGN_MON_ORD에 담는다. 매장 정보가 정정될 때마다 1년 치를 지우고 다시 넣는데, 한 번에 3.4초가 걸리고 그동안 디스크 읽기가 몰린다. 다음 달부터 대구 권역을 더해 달라는 요청이 왔다. 개발자는 "UNION ALL에 갈래 하나만 더 붙이면 된다"라고 하고, DBA는 "그러면 4.5초가 된다"라고 한다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · 2013년 500,218건 · SHOP_NO · ORD_X01 (ORD_DT, ORD_HMS)가 있지만 1년 치를 읽을 때는 FULL이 낫다
SHOP 매장: 200행 · 5블록 · ADDR 주소(앞 두 글자가 시도: 서울 62곳 · 경기 41곳 · 부산 10곳 · 그 밖 87곳)
RGN_MON_ORD 권역 월 주문 (이 문제에서 쓰는 파생 테이블): RGN_CD NOT NULL · ORD_YM · ORD_CNT · 36행(3권역 × 12개월)
통계 최신 · 배치 시간대에는 ORD가 버퍼 캐시에 남아 있지 않다

원본 SQL

sql

INSERT INTO RGN_MON_ORD (RGN_CD, ORD_YM, ORD_CNT)
SELECT 'SEOUL', SUBSTR(o.ORD_DT, 1, 6), COUNT(*)
  FROM ORD o, SHOP s
 WHERE s.SHOP_NO = o.SHOP_NO
   AND s.ADDR LIKE '서울%'
   AND o.ORD_DT BETWEEN '20130101' AND '20131231'
 GROUP BY SUBSTR(o.ORD_DT, 1, 6)
UNION ALL
SELECT 'GYEONGGI', SUBSTR(o.ORD_DT, 1, 6), COUNT(*)
  FROM ORD o, SHOP s
 WHERE s.SHOP_NO = o.SHOP_NO
   AND s.ADDR LIKE '경기%'
   AND o.ORD_DT BETWEEN '20130101' AND '20131231'
 GROUP BY SUBSTR(o.ORD_DT, 1, 6)
UNION ALL
SELECT 'BUSAN', SUBSTR(o.ORD_DT, 1, 6), COUNT(*)
  FROM ORD o, SHOP s
 WHERE s.SHOP_NO = o.SHOP_NO
   AND s.ADDR LIKE '부산%'
   AND o.ORD_DT BETWEEN '20130101' AND '20131231'
 GROUP BY SUBSTR(o.ORD_DT, 1, 6)

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Starts | E-Rows | A-Rows |      A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |             |      1 |        |      0 | 00:00:03.41 |   67293 | 67266 |
|   1 |  LOAD TABLE CONVENTIONAL  | RGN_MON_ORD |      1 |        |      0 | 00:00:03.41 |   67293 | 67266 |
|   2 |   UNION-ALL               |             |      1 |        |     36 | 00:00:03.40 |   67281 | 67266 |
|   3 |    HASH GROUP BY          |             |      1 |     12 |     12 | 00:00:01.14 |   22427 | 22422 |
|*  4 |     HASH JOIN             |             |      1 |   150K |   155K | 00:00:01.10 |   22427 | 22422 |
|*  5 |      TABLE ACCESS FULL    | SHOP        |      1 |     60 |     62 | 00:00:00.01 |       5 |     0 |
|*  6 |      TABLE ACCESS FULL    | ORD         |      1 |   500K |   500K | 00:00:00.97 |   22422 | 22422 |
|   7 |    HASH GROUP BY          |             |      1 |     12 |     12 | 00:00:01.13 |   22427 | 22422 |
|*  8 |     HASH JOIN             |             |      1 |   100K |   102K | 00:00:01.09 |   22427 | 22422 |
|*  9 |      TABLE ACCESS FULL    | SHOP        |      1 |     40 |     41 | 00:00:00.01 |       5 |     0 |
|* 10 |      TABLE ACCESS FULL    | ORD         |      1 |   500K |   500K | 00:00:00.96 |   22422 | 22422 |
|  11 |    HASH GROUP BY          |             |      1 |     12 |     12 | 00:00:01.13 |   22427 | 22422 |
|* 12 |     HASH JOIN             |             |      1 |  25009 |  25011 | 00:00:01.09 |   22427 | 22422 |
|* 13 |      TABLE ACCESS FULL    | SHOP        |      1 |     10 |     10 | 00:00:00.01 |       5 |     0 |
|* 14 |      TABLE ACCESS FULL    | ORD         |      1 |   500K |   500K | 00:00:00.96 |   22422 | 22422 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("S"."SHOP_NO"="O"."SHOP_NO")
   5 - filter("S"."ADDR" LIKE '서울%')
   6 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))
   8 - access("S"."SHOP_NO"="O"."SHOP_NO")
   9 - filter("S"."ADDR" LIKE '경기%')
  10 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))
  12 - access("S"."SHOP_NO"="O"."SHOP_NO")
  13 - filter("S"."ADDR" LIKE '부산%')
  14 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))

문제

3.4초가 어디서 나오는지 실행계획의 줄 번호와 Buffers, Reads로 설명하고, 권역을 하나 더하면 얼마가 되는지 쓰시오.
권역이 늘어도 ORD를 한 번만 읽도록 INSERT를 고치시오. 결과 36행이 바뀌지 않으려면 무엇을 지켜야 하는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 권역은 갈래가 아니라 값이다 : 매장 200곳에 먼저 권역을 붙이고, 주문은 한 번만 읽는다
INSERT INTO RGN_MON_ORD (RGN_CD, ORD_YM, ORD_CNT)
SELECT s.RGN_CD, SUBSTR(o.ORD_DT, 1, 6), COUNT(*)
  FROM ORD o,
       (SELECT SHOP_NO,
               CASE WHEN ADDR LIKE '서울%' THEN 'SEOUL'
                    WHEN ADDR LIKE '경기%' THEN 'GYEONGGI'
                    WHEN ADDR LIKE '부산%' THEN 'BUSAN'
               END AS RGN_CD
          FROM SHOP) s
 WHERE s.SHOP_NO = o.SHOP_NO
   AND s.RGN_CD IS NOT NULL          -- 세 권역 밖 매장 87곳은 원래도 담지 않았다
   AND o.ORD_DT BETWEEN '20130101' AND '20131231'
 GROUP BY s.RGN_CD, SUBSTR(o.ORD_DT, 1, 6)

-- 대구를 더할 때는 CASE 에 한 줄만 더한다 : WHEN ADDR LIKE '대구%' THEN 'DAEGU'

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

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Starts | E-Rows | A-Rows |      A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |             |      1 |        |      0 | 00:00:01.19 |   22439 | 22422 |
|   1 |  LOAD TABLE CONVENTIONAL  | RGN_MON_ORD |      1 |        |      0 | 00:00:01.19 |   22439 | 22422 |
|   2 |   HASH GROUP BY           |             |      1 |     36 |     36 | 00:00:01.18 |   22427 | 22422 |
|*  3 |    HASH JOIN              |             |      1 |  25011 |   283K | 00:00:01.05 |   22427 | 22422 |
|*  4 |     TABLE ACCESS FULL     | SHOP        |      1 |     10 |    113 | 00:00:00.01 |       5 |     0 |
|*  5 |     TABLE ACCESS FULL     | ORD         |      1 |   500K |   500K | 00:00:00.96 |   22422 | 22422 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SHOP_NO"="O"."SHOP_NO")
   4 - filter(CASE  WHEN "ADDR" LIKE '서울%' THEN 'SEOUL' WHEN "ADDR" LIKE '경기%' THEN 'GYEONGGI'
              WHEN "ADDR" LIKE '부산%' THEN 'BUSAN' END  IS NOT NULL)
   5 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))

모범 답안 서술

6번, 10번, 14번 줄이 같은 2013년 주문을 갈래마다 FULL로 읽는다(Buffers 22,422씩, Reads도 22,422씩). 갈래마다 다른 것은 5번, 9번, 13번 줄의 매장 주소 조건뿐인데, 그 조건이 매장 쪽에 있어서 주문은 매번 1년 치를 다 읽고 해시 조인에서 버린다. 그래서 3 × 22,422블록, 곧 Buffers 67,293에 Reads 67,266이고, 3.41초의 대부분이 이 디스크 읽기다. 갈래를 하나 더하면 22,427블록이 그대로 더해져 약 9만 블록, 4.5초가 된다. ORD는 한 번만 읽고, 권역은 갈래가 아니라 값으로 나눈다. 매장 200곳에 먼저 CASE로 권역을 붙이고(작은 쪽에서 분류), 그 결과를 주문과 한 번 조인한 뒤 권역과 월로 묶는다. Buffers는 67,293 → 22,439, A-Time은 3.41 → 1.19초다. 대구 권역은 CASE에 한 줄을 더하면 되고, 읽는 양은 늘지 않는다. 결과 36행을 지키려면 세 권역에 들지 않는 매장을 걸러야 한다. 원래 SQL에서는 그 매장 87곳의 주문이 어느 갈래에도 걸리지 않아 담기지 않았다. 고친 SQL의 CASE는 그 매장에 NULL을 돌려주므로, RGN_CD IS NOT NULL을 빼먹으면 NULL 권역 12행이 더 생긴다. RGN_CD가 NOT NULL이라 INSERT는 ORA-01400으로 멈춘다. 권역은 주소 앞 두 글자로 정해져 서로 겹치지 않으므로 한 매장이 두 권역에 들어갈 일은 없다. 대조는 권역별 1년 합계로 한다. 서울 155,068건, 경기 102,545건, 부산 25,011건으로 원래와 같다.

해설

CASE는 첫 번째로 맞는 값 하나만 고른다: 권역처럼 서로 겹치지 않는 분류라서 이 방법이 맞다. '수도권'과 '서울'처럼 한 행을 두 분류에 모두 세야 한다면 CASE 하나로는 안 된다(SUBQ-063).
권역 코드를 매장 테이블의 컬럼으로 두는 안: SHOP에 RGN_CD를 두면 CASE가 SQL에서 사라지고 다른 리포트도 같은 분류를 쓴다. 매장이 200곳뿐이라 관리 비용이 작다. 다만 주소가 바뀔 때 함께 고쳐야 한다.
모든 매장을 담는 요구라면: 87곳까지 '기타'로 담으려면 CASE에 ELSE 'ETC'를 둔다. 결과가 바뀌는 일이므로 화면 주인에게 먼저 확인한다.
해시 조인의 build는 걸러진 매장 113행이라 메모리 걱정이 없다. 큰 테이블을 갈래마다 다시 읽는 SQL을 보면, 갈래를 가르는 조건이 작은 쪽에 있는지부터 본다(교재 06화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황