PART-030

매장별로 나눴다는 판매 테이블인데 매장마다 조회 일량이 두 배씩 차이 난다

기초SELECT유통405 / 700

상황

유통 본사는 지난해 매장 포털을 열면서 판매를 "매장별로" 나눈 복사본 CF10_H를 만들었다. 매장 직원은 자기 매장의 한 달 판매를 일자별로 본다(하루 수천 번). 1002번 매장은 0.02초인데 다른 네 매장은 0.04초쯤 걸린다. 당시 설계 메모에는 "매장별 분산 → HASH. 칸 수는 2의 거듭제곱 규칙대로 4." 한 줄만 남아 있다.

테이블과 인덱스

CF10_H 판매(파티션 복사본, 문제 설정): CF10 500,000행을 PARTITION BY HASH (SHP_NO) PARTITIONS 4로 나눠 CTAS로 지었다. 칸 이름은 SYS_P2201 ~ SYS_P2204다.
판매가 있는 매장은 다섯 곳(SHP_NO 1001 ~ 1005, NUMBER)이고 매장마다 약 100,000행, 약 917블록이다. SLE_DT는 VARCHAR2(8)이다. 매장 하나의 한 달 판매는 약 4,200건이다.
인덱스는 CF10_H_PK (SLE_NO) GLOBAL뿐이다. 통계는 최신이다.
내년에 1006번 매장이 문을 연다. 매장이 문을 닫으면 그 매장의 판매를 따로 보관하고 지워야 한다.

원본 SQL

sql

CREATE TABLE CF10_H
PARTITION BY HASH (SHP_NO) PARTITIONS 4
AS SELECT * FROM CF10;

-- 매장 포털 「매장 일별 판매」 (:v_shp = 1003, :v_from = '20230601', :v_to = '20230630')
SELECT SLE_DT, COUNT(*) AS sle_cnt
  FROM CF10_H
 WHERE SHP_NO = :v_shp
   AND SLE_DT BETWEEN :v_from AND :v_to
 GROUP BY SLE_DT
 ORDER BY SLE_DT;

실행계획 학습용 예시

xplan

[:v_shp = 1003]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name   | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |      1 |        |     30 | 00:00:00.04 |    1840 |        |       |
|   1 |  SORT GROUP BY           |        |      1 |     30 |     30 | 00:00:00.04 |    1840 |        |       |
|*  2 |   FILTER                 |        |      1 |        |   4213 | 00:00:00.04 |    1840 |        |       |
|   3 |    PARTITION HASH SINGLE |        |      1 |   2096 |   4213 | 00:00:00.04 |    1840 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL    | CF10_H |      1 |   2096 |   4213 | 00:00:00.04 |    1840 |    KEY |   KEY |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   4 - filter(("SHP_NO"=:V_SHP AND "SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO))

  (실행 때 고른 칸 — 1번 SYS_P2201)

[:v_shp = 1002]  같은 모양 · 2번 칸 SYS_P2202 · A-Rows 4,188 · Buffers 921

[USER_TAB_PARTITIONS · 칸마다 든 매장 (SELECT DISTINCT SHP_NO FROM CF10_H PARTITION (…))]
  PARTITION_NAME   NUM_ROWS   BLOCKS   든 매장
  SYS_P2201         200,180    1,836   1001 · 1003
  SYS_P2202          99,950      917   1002
  SYS_P2203               0        0   —
  SYS_P2204         199,870    1,833   1004 · 1005

문제

매장마다 일량이 두 배씩 다른 까닭을 파티션 통계와 해시 파티션의 원리로 설명하시오.
이 테이블에 맞는 파티션 방식을 골라 DDL을 쓰고, 1003번 매장 조회가 몇 블록을 읽을지와 1006번 매장이 문을 열 때 할 일을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 값 몇 개를 사람이 나눈다 : 매장마다 칸 하나 · 앞으로 올 번호는 DEFAULT 칸이 받는다
CREATE TABLE CF10_L
PARTITION BY LIST (SHP_NO)
( PARTITION P1001 VALUES (1001),
  PARTITION P1002 VALUES (1002),
  PARTITION P1003 VALUES (1003),
  PARTITION P1004 VALUES (1004),
  PARTITION P1005 VALUES (1005),
  PARTITION P_ETC VALUES (DEFAULT) )
AS SELECT * FROM CF10;

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

-- 1006번 매장이 문을 열기 전 (P_ETC 가 비어 있을 때) : DEFAULT 칸에서 떼어 낸다 · 옮길 행이 없다
ALTER TABLE CF10_L SPLIT PARTITION P_ETC VALUES (1006)
  INTO (PARTITION P1006, PARTITION P_ETC) UPDATE INDEXES;

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

xplan

[:v_shp = 1003]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name   | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |      1 |        |     30 | 00:00:00.02 |     921 |        |       |
|   1 |  SORT GROUP BY           |        |      1 |     30 |     30 | 00:00:00.02 |     921 |        |       |
|*  2 |   FILTER                 |        |      1 |        |   4213 | 00:00:00.02 |     921 |        |       |
|   3 |    PARTITION LIST SINGLE |        |      1 |   4190 |   4213 | 00:00:00.02 |     921 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL    | CF10_L |      1 |   4190 |   4213 | 00:00:00.02 |     921 |    KEY |   KEY |
---------------------------------------------------------------------------------------------------------------

  (실행 때 고른 칸 — 3번 P1003 · 다섯 매장 모두 약 920블록)

모범 답안 서술

1003번 조회는 3번 줄이 PARTITION HASH SINGLE이라 칸 하나만 열었다. 그런데 1,840블록을 읽었고, 1002번은 같은 모양에 921블록이다. 파티션 통계를 보면 1번 칸에 1001번과 1003번, 4번 칸에 1004번과 1005번이 함께 들어 있고, 2번 칸에는 1002번 하나뿐이며 3번 칸은 비어 있다. 1003번 매장은 칸을 열 때마다 1001번 매장의 판매까지 함께 읽는다. 해시 파티션은 칸 수만 사람이 정하고, 어느 값이 어느 칸에 갈지는 오라클의 해시 함수가 정한다. 판매번호나 고객번호처럼 값 종류가 많으면 고르게 흩어지지만, SHP_NO는 값이 다섯 가지뿐이다. 다섯 개의 해시 값을 칸 넷에 나누면 고르게 나뉠 수가 없고, 이번처럼 두 매장이 한 칸에 몰리고 한 칸은 빈다. 설계 메모의 '2의 거듭제곱' 규칙도 값 종류가 많을 때의 이야기라 여기서는 소용없다. 값 몇 개를 사람이 정해 나누는 방식은 LIST다. 매장마다 칸을 하나씩 두고(PARTITION BY LIST (SHP_NO)), 앞으로 들어올지 모르는 매장 번호를 받을 DEFAULT 칸을 둔다. 1003번 조회는 PARTITION LIST SINGLE로 자기 매장 칸만 읽어 1,840 → 약 921블록이 된다. 칸 이름(P1003)만 봐도 어느 매장인지 알 수 있고, 매장이 문을 닫으면 그 칸째 보관 테이블과 맞바꾸거나 들어낼 수 있다. 1006번 매장이 문을 열기 전에는 비어 있는 P_ETC에서 SPLIT PARTITION P_ETC VALUES (1006) INTO (PARTITION P1006, PARTITION P_ETC)로 칸을 떼어 낸다. DEFAULT 칸이 있는 LIST 테이블에는 ADD PARTITION을 쓸 수 없다. 칸이 비어 있을 때 떼어 내면 옮길 행이 없어 바로 끝나고, 떼어 내지 않으면 새 매장 판매가 P_ETC에 쌓인다.

해설

해시 파티션은 값을 고르게 흩는 도구이지 "값마다 나누는" 도구가 아니다. 값 종류가 적은 키(매장, 지역, 상태)는 칸 수를 몇으로 하든 몰리거나 비는 칸이 생긴다. 이때는 LIST로 사람이 기준을 정한다.
해시 칸 이름(SYS_P2201)만 보고는 어느 매장이 어느 칸인지 알 수 없다. 칸째 관리하려면(폐점 매장 보관) 칸의 뜻을 사람이 아는 LIST가 맞다. 폐점 매장 칸은 EXCHANGE PARTITION으로 행을 옮기지 않고 빼낼 수 있다(대량 처리 문제에서 다룬다).
칸을 고른 뒤에도 한 달을 보려고 그 매장의 2년 치(약 921블록)를 읽는다. 칸 안의 일은 LOCAL 인덱스나 월과 매장을 함께 나누는 설계로 다룬다(PART-035).
매장이 수백 곳이면 매장마다 칸은 지나치다. 그때는 지역 묶음 LIST나 날짜 기준 칸에 인덱스를 더하는 쪽을 검토한다(PART-039).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황