PART-034

주문 100만 행을 하루 단위 INTERVAL 칸으로 옮겼더니 47MB가 8.6GB가 되었다

중급DDL한빛유통409 / 700

상황

조민우가 「일별 매출」 화면을 빠르게 하려고 T_ORD_BIG을 하루 단위 INTERVAL 파티션 복사본 T_ORD_BIG_PD로 지었다. "화면이 하루씩 보니까 하루 칸이면 제일 적게 읽겠죠." 6초면 끝나던 CTAS가 4분 넘게 걸렸고, 연습 DB 데이터 파일이 8GB 넘게 늘어 Free의 12GB 한도에 거의 찼다. 통계 수집도 9분이 넘게 걸렸다. 박팀장: "하루 칸이 맞는지 이 테이블을 쓰는 SQL 넷으로 따져 보고, 다시 지어라."

테이블과 인덱스

T_ORD_BIG_PD(문제 설정): T_ORD_BIG 1,000,000행(5,826블록, 47MB)을 PARTITION BY RANGE (ORD_DT) INTERVAL (NUMTODSINTERVAL(1, 'DAY'))로 나눠 CTAS로 지었다. 2022-01-01 ~ 2024-12-31의 1,096칸이고, 칸 하나는 약 913행, FULL로 약 6블록이다.
이 버전은 파티션의 첫 익스텐트를 8MB(1,024블록)로 잡는다(교재 14화 §8.5).
ORD_DT는 전부 자정이다. 인덱스는 없다. 통계는 최신이다.
이 테이블을 쓰는 SQL: Q1 「일별 매출」 하루(하루 수백 번) · Q2 「월 마감」 한 달(월초 하루 수백 번) · Q3 「분기 리포트」 석 달(분기마다 수십 번) · Q4 「보관 정리」 36개월 지난 달을 지운다(매월 1일)
참고(교재 14화 실측): 월별 칸 T_ORD_BIG_P14는 칸 하나 FULL 161블록, 6 ~ 8월 석 달 493블록이다.

원본 SQL

sql

CREATE TABLE T_ORD_BIG_PD
PARTITION BY RANGE (ORD_DT) INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
( PARTITION P20220101 VALUES LESS THAN (DATE '2022-01-02') )
AS SELECT * FROM T_ORD_BIG;

-- Q1 「일별 매출」 (:v_dt = 2024-06-12)
SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG_PD
 WHERE ORD_DT = :v_dt;

-- Q2 「월 마감」 (:v_mon = 2024-06-01) · Q3 은 같은 모양으로 석 달
SELECT ORD_DT, COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG_PD
 WHERE ORD_DT >= :v_mon
   AND ORD_DT <  ADD_MONTHS(:v_mon, 1)
 GROUP BY ORD_DT;

-- Q4 「보관 정리」 : 36개월 지난 달의 하루 칸을 날마다 하나씩 지운다 (한 달에 DDL 28 ~ 31번)
--   ALTER TABLE T_ORD_BIG_PD DROP PARTITION FOR (DATE '2022-01-01');
--   ALTER TABLE T_ORD_BIG_PD DROP PARTITION FOR (DATE '2022-01-02');  …

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

xplan

[Q1 · 2024-06-12]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |      1 |        |      1 | 00:00:00.01 |       6 |        |       |
|   1 |  SORT AGGREGATE          |              |      1 |      1 |      1 | 00:00:00.01 |       6 |        |       |
|   2 |   PARTITION RANGE SINGLE |              |      1 |    913 |    912 | 00:00:00.01 |       6 |    KEY |   KEY |
|*  3 |    TABLE ACCESS FULL     | T_ORD_BIG_PD |      1 |    913 |    912 | 00:00:00.01 |       6 |    KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------

[Q2 · 2024년 6월]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |     30 | 00:00:00.02 |     181 |        |       |
|   1 |  HASH GROUP BY              |              |      1 |     30 |     30 | 00:00:00.02 |     181 |        |       |
|*  2 |   FILTER                    |              |      1 |        |  27390 | 00:00:00.02 |     181 |        |       |
|   3 |    PARTITION RANGE ITERATOR |              |      1 |  27390 |  27390 | 00:00:00.02 |     181 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | T_ORD_BIG_PD |     30 |  27390 |  27390 | 00:00:00.02 |     181 |    KEY |   KEY |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_MON<ADD_MONTHS(:V_MON,1))
   4 - filter(("ORD_DT">=:V_MON AND "ORD_DT"<ADD_MONTHS(:V_MON,1)))

[Q3 · 2024년 4 ~ 6월]  같은 모양 · 4번 줄 Starts 91 · Buffers 549

[USER_SEGMENTS]
  T_ORD_BIG_PD    세그먼트 1,096개 · 8,768 MB      (원본 T_ORD_BIG 세그먼트 1개 · 47 MB)

[만들고 관리하는 데 든 시간]
  CTAS 4분 12초 (원본 복사는 6초) · 통계 수집 9분 40초 · Q2 첫 하드 파싱 0.31초 (월 칸 테이블은 0.02초)

문제

하루 칸 설계가 Q1 ~ Q4에 각각 주는 득실을 실행계획과 세그먼트 정보로 설명하시오.
칸 크기를 다시 정해 DDL을 쓰고, Q1이 손해 보지 않게 하는 방법과 Q1 ~ Q4의 바뀌는 숫자를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 칸은 월 단위 (들어내는 단위 · 넓은 범위 조회의 단위) · 칸 크기에 맞게 첫 익스텐트를 작게
CREATE TABLE T_ORD_BIG_PM
STORAGE (INITIAL 64K)
PARTITION BY RANGE (ORD_DT) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION P202201 VALUES LESS THAN (DATE '2022-02-01') )
AS SELECT * FROM T_ORD_BIG;

-- Q1 (하루) 은 칸 안의 인덱스가 맡는다 · 금액까지 담아 테이블을 읽지 않는다 (LOCAL Prefixed)
CREATE INDEX IX_ORD_BIG_PM_DT_AMT ON T_ORD_BIG_PM (ORD_DT, ORD_AMT) LOCAL;

-- Q4 : 36개월 지난 달을 칸 하나로 들어낸다 (매월 1일 한 번)
ALTER TABLE T_ORD_BIG_PM DROP PARTITION FOR (DATE '2022-01-01');

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

xplan

[Q1 · 2024-06-12]
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |      1 |        |      1 | 00:00:00.01 |       5 |        |       |
|   1 |  SORT AGGREGATE          |                      |      1 |      1 |      1 | 00:00:00.01 |       5 |        |       |
|   2 |   PARTITION RANGE SINGLE |                      |      1 |    913 |    912 | 00:00:00.01 |       5 |    KEY |   KEY |
|*  3 |    INDEX RANGE SCAN      | IX_ORD_BIG_PM_DT_AMT |      1 |    913 |    912 | 00:00:00.01 |       5 |    KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------

   3 - access("ORD_DT"=:V_DT)

[Q2 · 2024년 6월]  PARTITION RANGE ITERATOR · TABLE ACCESS FULL Starts 1 · Buffers 161
[Q3 · 2024년 4 ~ 6월]  Starts 3 · Buffers 493
[Q4]  DROP PARTITION 한 번 (교재 14화 실측 모양 · redo 약 0.01 MB)
[USER_SEGMENTS]  테이블 36개 · 약 50 MB (8MB 기본값이었다면 288 MB) · 인덱스 36개

모범 답안 서술

하루 칸은 Q1에만 좋다. Q1은 칸 하나(PARTITION RANGE SINGLE, 약 913행)를 6블록에 읽는다. 하지만 칸이 잘면 넓은 범위를 묻는 SQL은 칸을 그만큼 많이 연다. Q2는 한 달을 보려고 칸 30개를 열었고(4번 줄 Starts 30, 181블록), Q3은 석 달에 칸 91개를 연다(Starts 91, 549블록). 같은 일을 월 칸이면 161블록과 493블록에 한다. 칸마다 세그먼트 머리를 따로 읽기 때문이다. Q4는 한 달을 지우려고 하루 칸마다 DDL을 하나씩, 한 달에 30번 남짓 돌려야 한다. 더 큰 값은 공간과 관리에서 치른다. 이 버전은 칸마다 첫 익스텐트를 8MB로 잡는다. 칸 하나의 데이터는 약 6블록(50KB 남짓)인데 칸마다 8MB를 잡으니 1,096칸 × 8MB = 8,768MB, 약 8.6GB가 되었다. 원본 47MB의 190배다. CTAS가 4분, 통계 수집이 9분 40초로 늘어난 것도, Q2의 첫 하드 파싱이 0.31초 걸리는 것도 칸 수(1,096개)에 비례하는 일이다. 칸 크기는 들어내는 단위와 넓은 범위 조회로 정한다. 보관 정리(Q4)가 월 단위이고 Q2와 Q3도 월 단위로 묻으므로 칸은 월로 둔다(INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))). 칸이 36개라 8MB 기본값이어도 288MB이고, STORAGE (INITIAL 64K)를 주면 약 50MB다. 칸보다 좁은 범위인 Q1은 칸 안의 인덱스가 맡는다. LOCAL 인덱스 (ORD_DT, ORD_AMT)는 금액까지 담고 있어서 Q1이 테이블을 읽지 않고 그날 키만 읽는다. 바뀌는 숫자는 Q1 6 → 약 5블록(손해가 없다), Q2 181 → 161, Q3 549 → 493, Q4 DDL 30번 남짓 → DROP PARTITION 한 번이다. 세그먼트는 1,096개 → 36개(인덱스 36개 별도), 공간은 8,768MB → 약 50MB다.

해설

칸 크기를 정하는 순서: ① 들어내는 단위(보관 규정이 월이면 월) ② 넓은 범위 조회가 몇 칸을 여는가 ③ 칸 하나의 크기와 세그먼트 수. 하루나 고객 하나 같은 좁은 범위는 칸이 아니라 칸 안의 인덱스로 푼다.
칸 수 자체가 비용이다. 칸이 1,096개면 통계 수집, 사전 조회, 하드 파싱, 칸 DDL이 모두 칸 수에 비례해 무거워진다.
하루 칸이 맞는 경우도 있다. 하루 수천만 행이 들어오고 날마다 하루씩 들어내는 로그라면 하루가 관리 단위다. 그때도 첫 익스텐트는 칸 크기에 맞춘다.
8MB 첫 익스텐트는 CTAS로 채우면 읽기에는 손해가 없고 자리만 차지한다. 빈 칸에 APPEND로 채우면 칸마다 빈 블록 약 1,006개를 안아 읽기까지 무거워진다(교재 14화 §8.5, PART-032).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황