PART-035

매장 포털은 빨라졌는데 본사의 월 판매 집계만 판매 테이블 120칸을 다 연다

중급SELECT유통410 / 700

상황

유통 본사는 매장 포털을 위해 판매를 "매장별, 월별 칸"으로 나눈 복사본 CF10_SR을 만들었다. 매장 포털의 「매장 월 판매」(하루 수천 번)는 칸 하나만 읽어 빨라졌다. 그런데 본사 영업관리팀의 「월 판매 집계」(전 매장, 월초 하루 수십 번)는 월별로만 나눈 예전 CF10_P보다 오히려 느리다.

테이블과 인덱스

CF10_SR 판매(파티션 복사본, 문제 설정): CF10 500,000행을 두 컬럼 범위 키 PARTITION BY RANGE (SHP_NO, SLE_DT)로 나눠 CTAS로 지었다(STORAGE (INITIAL 64K)). 매장마다 월별 24칸(P1001_202201 ~ P1001_202312 …)과 매장별 MAXVALUE 칸이 있고 맨 끝에 PMAX가 있어 모두 126칸이다. 데이터가 있는 칸은 120개이고, 칸 하나는 약 4,170행, FULL로 약 39블록이다.
판매가 있는 매장은 다섯 곳(SHP_NO 1001 ~ 1005, NUMBER)이다. SLE_DT는 VARCHAR2(8)이다. 2023년 6월 전 매장 판매는 20,953건이다.
CF10_P(월별 24칸): 칸 하나 FULL ≈ 191블록이다.
인덱스는 CF10_SR_PK (SLE_NO) GLOBAL뿐이다. 통계는 최신이다.

원본 SQL

sql

CREATE TABLE CF10_SR
STORAGE (INITIAL 64K)
PARTITION BY RANGE (SHP_NO, SLE_DT)
( PARTITION P1001_202201 VALUES LESS THAN (1001, '20220201'),
  -- … 1001번 매장의 달마다 한 줄
  PARTITION P1001_202312 VALUES LESS THAN (1001, '20240101'),
  PARTITION P1001_MAX    VALUES LESS THAN (1001, MAXVALUE),
  PARTITION P1002_202201 VALUES LESS THAN (1002, '20220201'),
  -- … 1005번 매장까지 같은 모양
  PARTITION PMAX         VALUES LESS THAN (MAXVALUE, MAXVALUE) )
AS SELECT * FROM CF10;

-- Q1 매장 포털 「매장 월 판매」 (하루 수천 번)
SELECT SLE_DT, COUNT(*) AS sle_cnt
  FROM CF10_SR
 WHERE SHP_NO = :v_shp
   AND SLE_DT BETWEEN :v_from AND :v_to
 GROUP BY SLE_DT;

-- Q2 본사 「월 판매 집계」 (월초 하루 수십 번)
SELECT SHP_NO, COUNT(*) AS sle_cnt, COUNT(DISTINCT SLE_DT) AS sle_days
  FROM CF10_SR
 WHERE SLE_DT BETWEEN :v_from AND :v_to
 GROUP BY SHP_NO;

-- Q3 본사 「매장 연간 추이」 (분기마다 몇 번) : SHP_NO = :v_shp AND SLE_DT 한 해

실행계획 학습용 예시

xplan

[Q1 · 1003번 매장 · 2023년 6월]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |     30 | 00:00:00.01 |      39 |        |       |
|   1 |  HASH GROUP BY              |         |      1 |     30 |     30 | 00:00:00.01 |      39 |        |       |
|*  2 |   FILTER                    |         |      1 |        |   4188 | 00:00:00.01 |      39 |        |       |
|   3 |    PARTITION RANGE ITERATOR |         |      1 |   4170 |   4188 | 00:00:00.01 |      39 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | CF10_SR |      1 |   4170 |   4188 | 00:00:00.01 |      39 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------

   2 - filter(:V_FROM<=:V_TO)
   4 - filter(("SHP_NO"=:V_SHP AND "SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO))

  (실행 때 고른 칸 — 1003번 매장의 2023년 6월 칸 하나)

[Q2 · 2023년 6월 · 전 매장]
--------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |      5 | 00:00:00.09 |    4690 |        |       |
|   1 |  SORT GROUP BY         |         |      1 |      5 |      5 | 00:00:00.09 |    4690 |        |       |
|*  2 |   FILTER               |         |      1 |        |  20953 | 00:00:00.08 |    4690 |        |       |
|   3 |    PARTITION RANGE ALL |         |      1 |  20800 |  20953 | 00:00:00.08 |    4690 |      1 |   126 |
|*  4 |     TABLE ACCESS FULL  | CF10_SR |    120 |  20800 |  20953 | 00:00:00.07 |    4690 |      1 |   126 |
--------------------------------------------------------------------------------------------------------------

   2 - filter(:V_FROM<=:V_TO)
   4 - filter(("SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO))

[Q3 · 1003번 매장 · 2023년]  PARTITION RANGE ITERATOR · KEY / KEY · Starts 12 · Buffers 468

문제

Q1은 칸 하나만 여는데 Q2는 칸을 전부 여는 까닭을 파티션 키의 컬럼 순서로 설명하시오.
Q1의 이점을 지키면서 Q2도 그달 칸만 읽도록 다시 설계해 DDL을 쓰고, Q1 ~ Q3의 Buffers가 어떻게 바뀌는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 월을 먼저 나누고(RANGE) 그 안을 매장으로 나눈다(LIST) · 앞으로 올 매장은 DEFAULT 조각이 받는다
CREATE TABLE CF10_RL
STORAGE (INITIAL 64K)
PARTITION BY RANGE (SLE_DT)
SUBPARTITION BY LIST (SHP_NO)
SUBPARTITION TEMPLATE
( SUBPARTITION S1001 VALUES (1001),
  SUBPARTITION S1002 VALUES (1002),
  SUBPARTITION S1003 VALUES (1003),
  SUBPARTITION S1004 VALUES (1004),
  SUBPARTITION S1005 VALUES (1005),
  SUBPARTITION S_ETC VALUES (DEFAULT) )
( PARTITION P202201 VALUES LESS THAN ('20220201'),
  PARTITION P202202 VALUES LESS THAN ('20220301'),
  -- … 월마다 한 줄
  PARTITION P202312 VALUES LESS THAN ('20240101'),
  PARTITION PMAX    VALUES LESS THAN (MAXVALUE) )
AS SELECT * FROM CF10;

ALTER TABLE CF10_RL ADD CONSTRAINT CF10_RL_PK PRIMARY KEY (SLE_NO);   -- GLOBAL (판매번호로 찾는 조회용)

-- Q1 ~ Q3 SQL 은 그대로 (테이블 이름만 바꾼다)

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

xplan

[Q2 · 2023년 6월 · 전 매장]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |      5 | 00:00:00.01 |     196 |        |       |
|   1 |  SORT GROUP BY              |         |      1 |      5 |      5 | 00:00:00.01 |     196 |        |       |
|*  2 |   FILTER                    |         |      1 |        |  20953 | 00:00:00.01 |     196 |        |       |
|   3 |    PARTITION RANGE ITERATOR |         |      1 |  20953 |  20953 | 00:00:00.01 |     196 |    KEY |   KEY |
|   4 |     PARTITION LIST ALL      |         |      1 |  20953 |  20953 | 00:00:00.01 |     196 |      1 |     6 |
|*  5 |      TABLE ACCESS FULL      | CF10_RL |      5 |  20953 |  20953 | 00:00:00.01 |     196 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------

   5 - filter(("SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO))

  (실행 때 연 조각 — 2023년 6월 칸의 매장 조각 다섯 개 · 빈 S_ETC 는 세그먼트가 없다)

[Q1 · 1003번 매장 · 2023년 6월]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |     30 | 00:00:00.01 |      39 |        |       |
|   1 |  HASH GROUP BY              |         |      1 |     30 |     30 | 00:00:00.01 |      39 |        |       |
|*  2 |   FILTER                    |         |      1 |        |   4188 | 00:00:00.01 |      39 |        |       |
|   3 |    PARTITION RANGE ITERATOR |         |      1 |   4188 |   4188 | 00:00:00.01 |      39 |    KEY |   KEY |
|   4 |     PARTITION LIST SINGLE   |         |      1 |   4188 |   4188 | 00:00:00.01 |      39 |    KEY |   KEY |
|*  5 |      TABLE ACCESS FULL      | CF10_RL |      1 |   4188 |   4188 | 00:00:00.01 |      39 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------

[Q3 · 1003번 매장 · 2023년]  RANGE ITERATOR + LIST SINGLE · Starts 12 · Buffers 468 (그대로)

모범 답안 서술

Q1은 3번 줄 PARTITION RANGE ITERATOR가 KEY로 칸 하나(1003번 매장의 2023년 6월)를 골라 39블록을 읽었다. Q2는 3번 줄이 PARTITION RANGE ALL(Pstart 1, Pstop 126)이고 4번 줄 Starts가 120이다. 데이터가 있는 120개 칸을 모두 열어 4,690블록을 읽었다. 까닭은 파티션 키의 컬럼 순서다. 두 컬럼 범위 키 (SHP_NO, SLE_DT)는 결합 인덱스처럼 첫 컬럼부터 비교하고, 첫 컬럼 값이 다르면 둘째 컬럼은 보지 않는다. (1001, '20231231')은 (1002, '20220101')보다 작다. 그래서 칸은 '1001번 매장의 24개월, 1002번 매장의 24개월, …' 순으로 늘어서고, 같은 달의 칸 다섯 개는 25칸씩 떨어져 흩어져 있다. Q1은 첫 컬럼 SHP_NO를 주므로 그 매장 구간 안에서 달을 고를 수 있지만, Q2는 첫 컬럼 조건 없이 둘째 컬럼(SLE_DT)만 주므로 칸을 하나도 고르지 못한다. 결합 인덱스의 선두 컬럼 없이 둘째 컬럼만 주면 범위를 잡지 못하는 것과 같은 원리다. 월을 먼저 나누고 그 안을 매장으로 나눈다. 주 파티션은 PARTITION BY RANGE (SLE_DT) 월별, 서브파티션은 SUBPARTITION BY LIST (SHP_NO)로 매장마다 한 조각이다(앞으로 올 매장은 DEFAULT 조각이 받는다). 그러면 Q2는 그달 칸 하나를 고르고 그 안의 매장 조각 다섯 개를 읽어(PARTITION RANGE ITERATOR와 PARTITION LIST ALL, Starts 5) 4,690 → 약 196블록이 된다. 월로만 나눈 CF10_P(191블록)와 거의 같다. Q1은 그달 칸에서 매장 조각 하나만 읽어 39블록 그대로이고, Q3은 열두 달의 그 매장 조각 12개를 읽어 468블록 그대로다. 대가는 세그먼트 수다. 칸마다 조각이 6개(매장 다섯과 DEFAULT)라서 데이터가 있는 조각만 120개이고 빈 DEFAULT 조각까지 150개다. 첫 익스텐트 8MB 기본값이면 1GB 가까이 잡으므로 INITIAL을 작게 준다. 매장이 새로 생기면 SET SUBPARTITION TEMPLATE으로 앞으로 만들 칸의 조각을 고치고, 이미 있는 칸은 DEFAULT 조각에서 떼어 낸다.

해설

두 컬럼 범위 키는 칸 경계가 (1001, '20220201')처럼 값의 쌍이고, 쌍끼리는 첫 값부터 비교한다. 둘째 컬럼만으로는 칸을 고를 수 없다는 점에서 결합 인덱스의 선두 컬럼 규칙과 똑같다.
Q2만 보면 월 한 단계(CF10_P, 칸 하나 191블록)로 충분하다. 매장 조각은 하루 수천 번 도는 Q1을 39블록에 묶어 두려고 두는 것이다. Q1이 드물다면 서브파티션 없이 CF10_P에 LOCAL 인덱스를 더하는 편이 단순하다.
SET SUBPARTITION TEMPLATE은 앞으로 만들 칸에만 적용된다. 이미 있는 칸에서 새 매장의 판매는 DEFAULT 조각에 들어가므로, 그 조각을 떼어 내는 일을 매장 개설 절차에 넣는다.
매장이 수백 곳이면 매장마다 조각은 지나치다. 세그먼트 수와 판매번호로 찾는 조회가 조각마다 인덱스를 뒤지는 문제가 커진다(PART-039).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황