PART-039

주문을 매장 200곳 × 24개월, 4,800조각으로 나누자는 안이 올라왔다

실전DDL버거414 / 700

상황

버거 본사 IT가 분석 DB의 주문 사본을 다시 지으려 한다. 매장 관리자 화면이 가장 자주 도니 매장마다 월마다 조각을 따로 두자는 안 (나)가 올라왔다. "매장 화면이 조각 하나만 읽으니 제일 빠르다." 오과장은 월 한 단계 안 (가)와 매장 해시 안 (다)를 함께 비교해 보자고 했다. 분석 DB에는 지난해 월 정리 뒤 GLOBAL 인덱스가 UNUSABLE이 된 장애 이후로 "GLOBAL 인덱스를 두지 않는다"는 운영 규칙이 있다.

테이블과 인덱스

원본 ORD_LIST(교재 14화): 주문 1,000,000행 · PARTITION BY LIST (ORD_YM) 24칸 · 빈 테이블에 APPEND로 채워 칸 하나 FULL ≈ 1,980블록. ORD_RANDOM에서 적재해 행이 매장끼리 섞여 있다(매장 하나의 한 달 205건이 거의 모두 다른 블록에 있다).
한 달 약 41,000행(CTAS로 지으면 약 935블록) · 매장 200곳 · 매장 하나의 한 달 약 205행, 2년 약 4,900행이다.
SQL 다섯: Q1 「매장 주문 내역」 SHOP_NO와 ORD_YM(하루 약 3,000번) · Q2 「월 매장 실적」 ORD_YM 전 매장(하루 약 30번) · Q3 「영수증 조회」 ORD_NO와 ORD_YM(하루 약 6만 번) · Q4 「월 정리」 24개월 지난 달을 들어낸다(월 1회) · Q5 「폐점 매장 정리」 폐점 매장의 주문을 지운다(한 해 몇 번)
(가) LIST (ORD_YM) 월 한 단계 + LOCAL PK (ORD_NO, ORD_YM) + LOCAL 인덱스 (SHOP_NO, ORD_DT, ORD_HMS)
(나) LIST (ORD_YM) × LIST (SHOP_NO) 매장마다 한 조각(200개와 DEFAULT) · 서브파티션 키가 없는 (ORD_NO, ORD_YM)은 LOCAL 유니크가 될 수 없어 LOCAL 일반 인덱스 (ORD_NO)를 둔다
(다) LIST (ORD_YM) × HASH (SHOP_NO) 8조각 · 인덱스는 (나)와 같다
세 안 모두 CTAS와 STORAGE (INITIAL 64K)로 짓는다.

원본 SQL

sql

-- Q1 「매장 주문 내역」
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM ORD_LIST
 WHERE SHOP_NO = :v_shop
   AND ORD_YM  = :v_ym
 ORDER BY ORD_DT, ORD_HMS;

-- Q2 「월 매장 실적」
SELECT SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD_LIST
 WHERE ORD_YM = :v_ym
 GROUP BY SHOP_NO;

-- Q3 「영수증 조회」
SELECT ORD_NO, ORD_YM, ORD_DT, ORD_HMS, SHOP_NO
  FROM ORD_LIST
 WHERE ORD_NO = :ord_no
   AND ORD_YM = :ord_ym;

-- Q4 「월 정리」
ALTER TABLE ORD_LIST DROP PARTITION P201201;

-- Q5 「폐점 매장 정리」
DELETE FROM ORD_LIST WHERE SHOP_NO = :v_closed_shop;

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

xplan

[지금 ORD_LIST · Q1 · SH0042 · 201306]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |    207 | 00:00:00.03 |    1980 |        |       |
|   1 |  SORT ORDER BY          |          |      1 |    205 |    207 | 00:00:00.03 |    1980 |        |       |
|   2 |   PARTITION LIST SINGLE |          |      1 |    205 |    207 | 00:00:00.03 |    1980 |    KEY |   KEY |
|*  3 |    TABLE ACCESS FULL    | ORD_LIST |      1 |    205 |    207 | 00:00:00.03 |    1980 |    KEY |   KEY |
----------------------------------------------------------------------------------------------------------------

   3 - filter(("SHOP_NO"=:V_SHOP AND "ORD_YM"=:V_YM))

[시험 DB · (나) · Q1]
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |      1 |        |    207 | 00:00:00.01 |       6 |        |       |
|   1 |  SORT ORDER BY           |             |      1 |    205 |    207 | 00:00:00.01 |       6 |        |       |
|   2 |   PARTITION LIST SINGLE  |             |      1 |    205 |    207 | 00:00:00.01 |       6 |    KEY |   KEY |
|   3 |    PARTITION LIST SINGLE |             |      1 |    205 |    207 | 00:00:00.01 |       6 |    KEY |   KEY |
|   4 |     TABLE ACCESS FULL    | ORD_LIST_SS |      1 |    205 |    207 | 00:00:00.01 |       6 |    KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------

[시험 DB · (나) · Q3 · 777777 · 201307]
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name            | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                 |      1 |        |      1 | 00:00:00.01 |     201 |        |       |
|   1 |  PARTITION LIST SINGLE                       |                 |      1 |      1 |      1 | 00:00:00.01 |     201 |    KEY |   KEY |
|   2 |   PARTITION LIST ALL                         |                 |      1 |      1 |      1 | 00:00:00.01 |     201 |      1 |   201 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | ORD_LIST_SS     |    200 |      1 |      1 | 00:00:00.01 |     201 |    KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                         | ORD_LIST_SS_X01 |    200 |      1 |      1 | 00:00:00.01 |     200 |    KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------------------

   4 - access("ORD_NO"=:ORD_NO)

[시험 DB · 세그먼트]  (가) 24개 · (나) 4,800개 · (다) 192개

문제

세 안을 Q1 ~ Q5에 대어 보고, 한 번 실행의 블록 수와 하루 합계로 비교하시오.
고른 안의 DDL(테이블과 인덱스)을 쓰고, Q4와 Q5를 어떻게 처리할지 쓰시오.
(나)안이 가장 빠른 Q1을 버리는 근거와, 고른 안에서 Q1을 더 줄일 방법과 그 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (가) 월 한 단계 · 매장 조회는 칸 안의 LOCAL 인덱스로
CREATE TABLE ORD_LIST_M
STORAGE (INITIAL 64K)
PARTITION BY LIST (ORD_YM)
( PARTITION P201201 VALUES ('201201'),
  PARTITION P201202 VALUES ('201202'),
  -- … 월마다 한 줄
  PARTITION P201312 VALUES ('201312'),
  PARTITION P_DEFAULT VALUES (DEFAULT) )
AS SELECT * FROM ORD_LIST;

-- Q3 : 파티션 키가 들어 있어 LOCAL 유니크가 된다 · 칸 하나에서 찾는다
ALTER TABLE ORD_LIST_M ADD CONSTRAINT ORD_LIST_M_PK
  PRIMARY KEY (ORD_NO, ORD_YM) USING INDEX LOCAL;
-- Q1 · Q5 : 매장 + 주문 시각 (정렬까지 인덱스가 맡는다)
CREATE INDEX ORD_LIST_M_X02 ON ORD_LIST_M (SHOP_NO, ORD_DT, ORD_HMS) LOCAL;

-- Q4 : 칸째 들어낸다 (GLOBAL 인덱스가 없어 인덱스 작업이 없다)
ALTER TABLE ORD_LIST_M DROP PARTITION P201201;

-- Q5 (한 해 몇 번 · 약 4,900행) : 매장 인덱스로 칸마다 찾아 지운다
DELETE FROM ORD_LIST_M WHERE SHOP_NO = :v_closed_shop;
COMMIT;

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

xplan

[(가) · Q1 · SH0042 · 201306]
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |    207 | 00:00:00.01 |     209 |        |       |
|   1 |  PARTITION LIST SINGLE              |                |      1 |    205 |    207 | 00:00:00.01 |     209 |    KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID | ORD_LIST_M     |      1 |    205 |    207 | 00:00:00.01 |     209 |    KEY |   KEY |
|*  3 |    INDEX RANGE SCAN                 | ORD_LIST_M_X02 |      1 |    205 |    207 | 00:00:00.01 |       4 |    KEY |   KEY |
----------------------------------------------------------------------------------------------------------------------------------

   3 - access("SHOP_NO"=:V_SHOP)

  (인덱스가 ORD_DT, ORD_HMS 순으로 키를 주므로 SORT ORDER BY 가 없다)

[(가) · Q3]  PARTITION LIST SINGLE · INDEX UNIQUE SCAN ORD_LIST_M_PK · 3블록

[세 안 · 한 번 실행과 하루 합계 (Q1 3,000번 · Q2 30번 · Q3 6만 번)]
           Q1        Q2       Q3      하루 합계        세그먼트   Q4             Q5
  (가)     209       936       3      약 84만          24        DROP 한 번      DELETE 약 4,900행
  (나)       6     1,200     201      약 1,211만     4,800        DROP 한 번      DROP SUBPARTITION × 24
  (다)     123       984      17      약 142만        192        DROP 한 번      DELETE 약 4,900행

모범 답안 서술

세 안을 Q1 ~ Q5에 대어 본다. 하루 합계는 Q1 3,000번, Q2 30번, Q3 6만 번으로 셈한다. (가) 월 한 단계 + LOCAL 인덱스: Q1은 ORD_YM으로 칸 하나를 고르고 LOCAL 인덱스 (SHOP_NO, ORD_DT, ORD_HMS)로 그 매장의 205건을 찾는다. 행이 매장끼리 섞여 들어가 있어 205건이 거의 모두 다른 블록에 있으므로 약 209블록이다. Q2는 칸 하나 FULL 약 936블록, Q3은 LOCAL PK (ORD_NO, ORD_YM)로 칸 하나에서 3블록이다. 하루 합계는 약 62.7만 + 2.8만 + 18만 = 약 84만 블록이다. (나) 월 × 매장 200조각: Q1은 조각 하나(205건, 약 6블록)만 읽어 가장 싸다. 그러나 Q3이 무너진다. 주문번호는 어느 매장 조각인지 알려 주지 않으므로, 월 칸을 고른 뒤 그 안의 조각 200개마다 인덱스를 한 번씩 뒤진다(인덱스 Starts 200, 약 201블록). 게다가 서브파티션 키 SHOP_NO가 없는 (ORD_NO, ORD_YM)은 LOCAL 유니크 인덱스가 될 수 없어(ORA-14039) 일반 인덱스로 둬야 하고, 주문번호의 유일성은 DB가 지키지 못한다. 하루 6만 번이면 Q3만 약 1,206만 블록, 합계 약 1,211만 블록이다. 세그먼트도 4,800개가 되어 통계 수집과 칸 DDL, 사전 조회가 모두 무거워지고, 첫 익스텐트 8MB 기본값이면 37.5GB를 잡는다. (다) 월 × HASH (SHOP_NO) 8조각: Q1은 매장 약 25곳이 섞인 조각 하나를 FULL로 읽어 약 123블록, Q2는 8조각 약 984블록, Q3은 조각 8개의 인덱스를 뒤져 약 17블록이다. 합계 약 142만 블록으로 (가)보다 많다. 조각의 뜻(어느 매장들인지)을 사람이 모르므로 폐점 매장을 조각째 다룰 수도 없다. 그래서 (가)를 고른다. 하루 합계가 가장 적고(약 84만), 세그먼트가 24개뿐이며, Q3이 칸 하나에서 끝나고 PK로 유일성도 지킨다. Q4는 칸째 DROP PARTITION 한 번이고 LOCAL 인덱스만 있으니 인덱스 작업도 없다. Q5(폐점 매장, 한 해 몇 번, 약 4,900행)는 매장 인덱스로 칸마다 찾아 지우는 DELETE로 충분하다. 한 해 몇 번 있는 일 때문에 4,800조각을 둘 까닭은 없다. (나)가 Q1을 6블록에 끝내는 이점은 버린다. Q1은 (가)에서 209블록이지만 하루 합계로는 약 63만 블록이고, (나)가 Q3에서 더 쓰는 약 1,188만 블록에 비하면 작다. (나)에 GLOBAL 유니크 인덱스 (ORD_NO)를 두면 Q3은 4블록이 되지만, 분석 DB 운영 규칙(GLOBAL 인덱스를 두지 않는다)에 어긋나고 4,800조각의 관리 부담은 그대로다. (가)에서 Q1을 더 줄이려면 마감된 달의 칸을 SHOP_NO 순으로 정렬해 다시 짓는다. 정렬한 복사본을 CTAS로 만들고 그 칸과 맞바꾸면, 매장 하나의 205건이 약 5블록에 모여 Q1이 약 9블록으로 준다. 대가는 달마다 칸 하나를 다시 쓰는 작업이 생긴다는 것과, 아직 쌓이는 이번 달 칸은 그 덕을 보지 못한다는 것이다.

해설

서브파티션 키는 유니크 인덱스에도 들어간다. 복합 파티션에서 LOCAL 유니크 인덱스는 파티션 키와 서브파티션 키를 모두 품어야 한다(ORA-14039). 주문번호처럼 그 자체로 유일한 키를 칸과 상관없이 지키려면 GLOBAL이 필요해지고, 그러면 칸 DDL마다 UPDATE GLOBAL INDEXES를 붙여야 한다.
가장 빠른 SQL 하나로 고르지 않는다. 실행 한 번의 블록 수에 하루 호출 수를 곱해 합계를 비교한다. (나)는 Q1 한 번만 보면 35배 빠르지만, 하루 6만 번 도는 Q3을 67배 느리게 한다.
매장이 5곳이면 매장 조각이 맞을 수 있다(PART-035). 조각 수가 늘수록 세그먼트 관리와 조각마다 뒤지는 조회의 값이 커진다. 조각 수는 근거와 함께 정한다(교재 14화 §8.5 처방).
칸을 정렬해 다시 짓고 맞바꾸는 일(EXCHANGE PARTITION)은 대량 처리 문제에서 다룬다. 정렬 순서는 가장 자주 도는 조회의 조건 순서로 정한다(교재 04화의 클러스터링 팩터).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황