PART-013

주문은 한 칸만 읽는데 주문상품은 매번 24칸을 다 뒤진다

실전SELECT버거388 / 700

상황

버거 본사는 주문상품도 주문과 같은 기준의 파티션 복사본 ORD_ITEM_RANGE로 옮겼다. 그 뒤 주문과 주문상품을 조인하는 세 SQL이 느려졌다. ① 「월간 매장별 메뉴 판매」는 월초에 본사가 수십 번 돌리고, ② POS 「영수증 상세」는 하루 약 6만 번, ③ 「매장 월 주문상품 내려받기」는 하루 약 400번 호출된다. 세 SQL 모두 ORD_RANGE 쪽은 한 칸만 연다. 아래 ①과 ③은 2013년 6월(③은 매장 SH0042), ②는 2013년 7월 주문 777777로 돌린 결과다.

테이블과 인덱스

ORD_RANGE 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_YM) 월별 25칸(P201201 ~ P201312과 빈 P_DEFAULT). 칸 하나는 FULL로 약 1,980블록이다. 한 달 약 41,000건이고, 매장 200개라서 매장 하나의 한 달 주문은 약 205건이다.
ORD_ITEM_RANGE 주문상품(파티션 복사본): ORD_ITEM 2,472,717행을 같은 기준(PARTITION BY RANGE (ORD_YM), 25칸)으로 나눴다. 칸 하나는 약 103,000행이고 FULL로 약 3,310블록(데이터 약 2,300블록과 빈 블록 약 1,006블록), 24칸 전체는 약 79,400블록이다. 주문 한 건에 상품은 약 2.5건이다.
주문상품의 ORD_YM은 늘 그 주문의 ORD_YM과 같다(적재할 때 주문에서 가져왔다).
LOCAL 인덱스: ORD_RANGE_PK (ORD_NO, ORD_YM), ORD_ITEM_RANGE_PK (ORD_NO, ITEM_ID, ORD_YM). 두 인덱스 모두 칸마다 높이 2다.
매달 1일 보관 기간이 지난 달을 두 테이블에서 DROP PARTITION으로 정리한다. 통계는 최신이다.

원본 SQL

sql

-- ① 월간 매장별 메뉴 판매
SELECT o.SHOP_NO, i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD_RANGE o, ORD_ITEM_RANGE i
 WHERE o.ORD_YM = :v_ym
   AND i.ORD_NO = o.ORD_NO
 GROUP BY o.SHOP_NO, i.ITEM_ID;

-- ② 영수증 상세
SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS, i.ITEM_ID, i.ORD_ITEM_QTY
  FROM ORD_RANGE o, ORD_ITEM_RANGE i
 WHERE o.ORD_NO = :ord_no
   AND o.ORD_YM = :ord_ym
   AND i.ORD_NO = o.ORD_NO;

-- ③ 매장 월 주문상품 내려받기
SELECT i.ORD_NO, i.ITEM_ID, i.ORD_ITEM_QTY
  FROM ORD_ITEM_RANGE i
 WHERE i.ORD_NO IN (SELECT o.ORD_NO
                      FROM ORD_RANGE o
                     WHERE o.ORD_YM = :v_ym
                       AND o.SHOP_NO = :v_shop);

실행계획 학습용 예시 · 교재 14화 규모

xplan

[① 월간 매장별 메뉴 판매 — :v_ym = '201306']
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name           | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                |      1 |        |    18K | 00:00:01.92 |   81396 |        |       |
|   1 |  HASH GROUP BY            |                |      1 |    18K |    18K | 00:00:01.92 |   81396 |        |       |
|*  2 |   HASH JOIN               |                |      1 |   103K |   102K | 00:00:01.74 |   81396 |        |       |
|   3 |    PARTITION RANGE SINGLE |                |      1 |    41K |    41K | 00:00:00.04 |    1980 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL     | ORD_RANGE      |      1 |    41K |    41K | 00:00:00.03 |    1980 |    KEY |   KEY |
|   5 |    PARTITION RANGE ALL    |                |      1 |  2472K |  2472K | 00:00:00.95 |   79416 |      1 |    25 |
|   6 |     TABLE ACCESS FULL     | ORD_ITEM_RANGE |     24 |  2472K |  2472K | 00:00:00.71 |   79416 |      1 |    25 |
------------------------------------------------------------------------------------------------------------------------

   2 - access("I"."ORD_NO"="O"."ORD_NO")
   4 - filter("O"."ORD_YM"=:V_YM)

[② 영수증 상세 — :ord_no = 777777, :ord_ym = '201307']
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |      3 | 00:00:00.01 |      55 |        |       |
|   1 |  NESTED LOOPS                        |                   |      1 |      2 |      3 | 00:00:00.01 |      55 |        |       |
|   2 |   PARTITION RANGE SINGLE             |                   |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID | ORD_RANGE         |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|*  4 |     INDEX UNIQUE SCAN                | ORD_RANGE_PK      |      1 |      1 |      1 | 00:00:00.01 |       2 |    KEY |   KEY |
|   5 |   PARTITION RANGE ALL                |                   |      1 |      2 |      3 | 00:00:00.01 |      52 |      1 |    25 |
|   6 |    TABLE ACCESS BY LOCAL INDEX ROWID | ORD_ITEM_RANGE    |     24 |      2 |      3 | 00:00:00.01 |      52 |      1 |    25 |
|*  7 |     INDEX RANGE SCAN                 | ORD_ITEM_RANGE_PK |     24 |      2 |      3 | 00:00:00.01 |      49 |      1 |    25 |
--------------------------------------------------------------------------------------------------------------------------------------

   4 - access("O"."ORD_NO"=:ORD_NO AND "O"."ORD_YM"=:ORD_YM)
   7 - access("I"."ORD_NO"=:ORD_NO)

[③ 매장 월 주문상품 내려받기 — :v_ym = '201306', :v_shop = 'SH0042']
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |    512 | 00:00:00.07 |   12348 |        |       |
|   1 |  NESTED LOOPS                        |                   |      1 |    513 |    512 | 00:00:00.07 |   12348 |        |       |
|   2 |   SORT UNIQUE                        |                   |      1 |    205 |    205 | 00:00:00.03 |    1980 |        |       |
|   3 |    PARTITION RANGE SINGLE            |                   |      1 |    205 |    205 | 00:00:00.03 |    1980 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL                | ORD_RANGE         |      1 |    205 |    205 | 00:00:00.03 |    1980 |    KEY |   KEY |
|   5 |   PARTITION RANGE ALL                |                   |    205 |      3 |    512 | 00:00:00.04 |   10368 |      1 |    25 |
|   6 |    TABLE ACCESS BY LOCAL INDEX ROWID | ORD_ITEM_RANGE    |   4920 |      3 |    512 | 00:00:00.04 |   10368 |      1 |    25 |
|*  7 |     INDEX RANGE SCAN                 | ORD_ITEM_RANGE_PK |   4920 |      3 |    512 | 00:00:00.03 |    9856 |      1 |    25 |
--------------------------------------------------------------------------------------------------------------------------------------

   4 - filter(("O"."SHOP_NO"=:V_SHOP AND "O"."ORD_YM"=:V_YM))
   7 - access("I"."ORD_NO"="O"."ORD_NO")

문제

세 SQL에서 ORD_ITEM_RANGE가 몇 칸을 여는지 Pstart, Pstop과 Starts로 쓰고, ORD_RANGE는 한 칸인데 주문상품 쪽은 칸을 고르지 못하는 까닭을 설명하시오.
결과가 바뀌지 않게 세 SQL을 고치고, SQL마다 Buffers가 어떻게 바뀌는지 어림하시오.
SQL을 고치는 대신 ORD_ITEM_RANGE에 ORD_NO 하나짜리 GLOBAL 인덱스를 만드는 안과 비교해, 세 SQL에서 무엇이 달라지는지와 그 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 조인 조건에 파티션 키를 더한다 → 조건 이행으로 i.ORD_YM = :v_ym 이 생긴다
SELECT o.SHOP_NO, i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD_RANGE o, ORD_ITEM_RANGE i
 WHERE o.ORD_YM = :v_ym
   AND i.ORD_NO = o.ORD_NO
   AND i.ORD_YM = o.ORD_YM
 GROUP BY o.SHOP_NO, i.ITEM_ID;

-- ② 같은 처방
SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS, i.ITEM_ID, i.ORD_ITEM_QTY
  FROM ORD_RANGE o, ORD_ITEM_RANGE i
 WHERE o.ORD_NO = :ord_no
   AND o.ORD_YM = :ord_ym
   AND i.ORD_NO = o.ORD_NO
   AND i.ORD_YM = o.ORD_YM;

-- ③ 서브쿼리 바깥의 주문상품에 파티션 키를 직접 준다
SELECT i.ORD_NO, i.ITEM_ID, i.ORD_ITEM_QTY
  FROM ORD_ITEM_RANGE i
 WHERE i.ORD_YM = :v_ym
   AND i.ORD_NO IN (SELECT o.ORD_NO
                      FROM ORD_RANGE o
                     WHERE o.ORD_YM = :v_ym
                       AND o.SHOP_NO = :v_shop);

개선 후 실행계획 학습용 예시 · 교재 14화 규모

xplan

[①]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name           | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                |      1 |        |    18K | 00:00:00.24 |    5290 |        |       |
|   1 |  HASH GROUP BY            |                |      1 |    18K |    18K | 00:00:00.24 |    5290 |        |       |
|*  2 |   HASH JOIN               |                |      1 |   103K |   102K | 00:00:00.18 |    5290 |        |       |
|   3 |    PARTITION RANGE SINGLE |                |      1 |    41K |    41K | 00:00:00.03 |    1980 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL     | ORD_RANGE      |      1 |    41K |    41K | 00:00:00.03 |    1980 |    KEY |   KEY |
|   5 |    PARTITION RANGE SINGLE |                |      1 |   103K |   103K | 00:00:00.06 |    3310 |    KEY |   KEY |
|*  6 |     TABLE ACCESS FULL     | ORD_ITEM_RANGE |      1 |   103K |   103K | 00:00:00.05 |    3310 |    KEY |   KEY |
------------------------------------------------------------------------------------------------------------------------

   2 - access("I"."ORD_NO"="O"."ORD_NO" AND "I"."ORD_YM"="O"."ORD_YM")
   4 - filter("O"."ORD_YM"=:V_YM)
   6 - filter("I"."ORD_YM"=:V_YM)          ← 조건 이행으로 옵티마이저가 만든 조건

[②]
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |      3 | 00:00:00.01 |       8 |        |       |
|   1 |  NESTED LOOPS                        |                   |      1 |      2 |      3 | 00:00:00.01 |       8 |        |       |
|   2 |   PARTITION RANGE SINGLE             |                   |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID | ORD_RANGE         |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|*  4 |     INDEX UNIQUE SCAN                | ORD_RANGE_PK      |      1 |      1 |      1 | 00:00:00.01 |       2 |    KEY |   KEY |
|   5 |   PARTITION RANGE SINGLE             |                   |      1 |      2 |      3 | 00:00:00.01 |       5 |    KEY |   KEY |
|   6 |    TABLE ACCESS BY LOCAL INDEX ROWID | ORD_ITEM_RANGE    |      1 |      2 |      3 | 00:00:00.01 |       5 |    KEY |   KEY |
|*  7 |     INDEX RANGE SCAN                 | ORD_ITEM_RANGE_PK |      1 |      2 |      3 | 00:00:00.01 |       2 |    KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------------

   7 - access("I"."ORD_NO"=:ORD_NO AND "I"."ORD_YM"=:ORD_YM)
       filter("I"."ORD_YM"=:ORD_YM)

[③]
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |    512 | 00:00:00.03 |    2902 |        |       |
|   1 |  NESTED LOOPS                        |                   |      1 |    513 |    512 | 00:00:00.03 |    2902 |        |       |
|   2 |   SORT UNIQUE                        |                   |      1 |    205 |    205 | 00:00:00.02 |    1980 |        |       |
|   3 |    PARTITION RANGE SINGLE            |                   |      1 |    205 |    205 | 00:00:00.02 |    1980 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL                | ORD_RANGE         |      1 |    205 |    205 | 00:00:00.02 |    1980 |    KEY |   KEY |
|   5 |   PARTITION RANGE SINGLE             |                   |    205 |      3 |    512 | 00:00:00.01 |     922 |    KEY |   KEY |
|   6 |    TABLE ACCESS BY LOCAL INDEX ROWID | ORD_ITEM_RANGE    |    205 |      3 |    512 | 00:00:00.01 |     922 |    KEY |   KEY |
|*  7 |     INDEX RANGE SCAN                 | ORD_ITEM_RANGE_PK |    205 |      3 |    512 | 00:00:00.01 |     410 |    KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------------

   7 - access("I"."ORD_NO"="O"."ORD_NO" AND "I"."ORD_YM"=:V_YM)
       filter("I"."ORD_YM"=:V_YM)

[GLOBAL 인덱스 ORD_ITEM_RANGE_GX01 (ORD_NO) 를 만들고 SQL 은 그대로 둘 때 — 어림]
  ①  81,396 → 81,396   (한 달 10만 행 조인은 여전히 해시 조인 · 주문상품 24칸 FULL)
  ②      55 → 약 9      (TABLE ACCESS BY GLOBAL INDEX ROWID)
  ③  12,348 → 약 3,100
  대가: 247만 키 인덱스를 적재 때마다 고친다 · 월 1일 DROP PARTITION 에 UPDATE GLOBAL INDEXES 필요

모범 답안 서술

세 SQL 모두 ORD_RANGE는 o.ORD_YM 조건으로 KEY 한 칸만 연다. 하지만 ORD_ITEM_RANGE는 칸을 전부 연다. ①은 5번 줄 PARTITION RANGE ALL(Pstart 1, Pstop 25)에 6번 줄 Starts 24로 79,416블록을 읽었고, ②는 5번 줄 RANGE ALL에 7번 줄 인덱스 Starts 24로 52블록, ③은 바깥 205행마다 24칸씩 7번 줄 인덱스 Starts 4,920으로 10,368블록을 읽었다. 까닭은 조인 조건이 i.ORD_NO = o.ORD_NO뿐이고 주문상품 쪽에는 파티션 키 ORD_YM 조건이 하나도 없다는 데 있다. 옵티마이저는 주문번호만 보고는 그 주문상품이 어느 달 칸에 있는지 모른다. ORD_ITEM_RANGE_PK도 LOCAL이고 ORD_YM이 맨 뒤에 있어서, 칸을 고르지 못하면 칸마다 인덱스를 한 번씩 뒤진다. 처방은 주문상품 쪽에도 파티션 키를 주는 것이다. ①과 ②는 조인 조건에 i.ORD_YM = o.ORD_YM을 더한다. o.ORD_YM = :v_ym이 있으므로 옵티마이저가 조건 이행으로 i.ORD_YM = :v_ym을 만들어 두 테이블 모두 KEY로 한 칸만 연다. ③은 서브쿼리 바깥에 i.ORD_YM = :v_ym을 직접 준다. 주문상품의 ORD_YM이 늘 주문과 같으므로 결과는 바뀌지 않는다. Buffers는 ① 81,396 → 약 5,290(주문상품 한 칸 3,310), ② 55 → 약 8, ③ 12,348 → 약 2,902로 준다. ②는 하루 6만 번이라 약 330만 → 약 48만 블록이다. GLOBAL 인덱스 (ORD_NO)를 만들면 SQL을 고치지 않아도 ②는 약 9블록, ③은 약 3,100블록으로 준다. 하지만 ①은 그대로다. 한 달 치 10만 행을 조인할 때는 인덱스로 10만 번 찾아가는 것보다 칸을 통째로 읽는 편이 싸므로, 칸을 고르지 못하는 한 여전히 24칸을 다 읽는다. 대가도 있다. 247만 키짜리 인덱스를 적재할 때마다 고쳐야 하고, 매달 1일의 DROP PARTITION에 UPDATE GLOBAL INDEXES를 붙이지 않으면 인덱스가 UNUSABLE이 되어 ②와 ③이 에러 없이 다시 느려진다. 그래서 SQL을 고치는 쪽을 권하고, '주문상품을 조회할 때는 늘 파티션 키 ORD_YM을 같이 준다'는 규칙을 코딩 표준으로 남긴다.

해설

조건 이행: o.ORD_YM = :v_ym AND i.ORD_YM = o.ORD_YM이면 옵티마이저가 i.ORD_YM = :v_ym을 만든다. 개선 후 계획 ①의 6번 줄 filter("I"."ORD_YM"=:V_YM)이 그 흔적이다. 조인 조건 하나로 두 테이블이 함께 칸을 고른다.
결과가 바뀌지 않는 전제: 주문상품의 ORD_YM이 늘 주문의 ORD_YM과 같아야 한다. 적재 규칙을 믿기 전에 SELECT COUNT(*) FROM ORD_RANGE o, ORD_ITEM_RANGE i WHERE i.ORD_NO = o.ORD_NO AND i.ORD_YM <> o.ORD_YM으로 한 번 확인한다.
분기 리포트처럼 여러 달을 조인할 때도 i.ORD_YM = o.ORD_YM이 있어야 칸끼리 짝지어 조인할 수 있다(파티션 와이즈 조인). 이것은 대량 처리 문제에서 따로 다룬다.
주문번호 하나로 찾는데 칸 수만큼 인덱스를 뒤지는 모양은 PART-002의 ORD_RANGE_PK와 같은 뿌리다. 자식 테이블에서는 조인 때문에 이 일이 바깥 행 수만큼 곱해진다(③의 Starts 4,920).
참조 파티션(PARTITION BY REFERENCE)으로 다시 지으면 자식이 부모의 칸 기준을 FK로 물려받는다. 설계 대안이지만 테이블을 다시 지어야 하고 활성 FK가 필요하다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황