PART-037

파티셔닝 승인이 났는데 주문 테이블을 무엇으로 나눌지 네 사람의 말이 다 다르다

실전DDL한빛유통412 / 700

상황

박팀장이 근거 수치를 들고 파티셔닝 옵션 예산을 받아 냈다. 첫 대상은 T_ORD_BIG이고, 네 사람이 안을 냈다. (가) 조민우는 가장 자주 도는 Q2가 고객으로 찾으니 HASH (CUS_ID) 16칸으로 나누자고 했다. (나) 오과장은 미결 점검 Q4가 PENDING만 보면 되니 LIST (ORD_ST)로 나누자고 했다. (다) 선배는 주문일 월별 INTERVAL을 냈다. (라) 강도현의 옛 메모에는 주문일 월별 칸 안을 HASH (CUS_ID) 8조각으로 나누는 안이 있다. 박팀장: "안마다 SQL 넷에 대어 보고, 하나를 골라 DDL까지 가져와."

테이블과 인덱스

T_ORD_BIG 대용량 주문: 1,000,000행 · 5,826블록 · 파티션 없음. ORD_DT는 2022-01-01 ~ 2024-12-31이고 전부 자정이며 한 달은 약 27,400 ~ 28,300행이다.
ORD_ST는 COMPLETE 90% · PENDING 8% · CANCEL 2%이고 결제와 취소 때 바뀐다. 고객은 2,000명이고 고객 한 명은 3년에 약 500건(최근 12개월 약 167건)을 주문했다.
지금 인덱스: IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) · IX_ORD_BIG_CUS (CUS_ID, ORD_DT). 통계 최신. 오늘은 2024년 12월 16일이다.
연습 DB에 (가)안을 지어 Q1과 Q3을 재 봤다(아래 실행계획, 인덱스는 같은 두 개를 LOCAL로 만들었다). (다)안 모양은 교재 14화의 T_ORD_BIG_P14와 같다(칸 하나 FULL 161블록, 커버링 LOCAL 인덱스로 한 달 89블록).

원본 SQL

sql

-- Q1 「월 마감 일별 매출」 (월초 주간 하루 수백 번)
SELECT ORD_DT, COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG
 WHERE ORD_DT >= :v_mon
   AND ORD_DT <  ADD_MONTHS(:v_mon, 1)
 GROUP BY ORD_DT;

-- Q2 「고객 주문 내역」 최근 12개월 (하루 약 2만 번)
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE CUS_ID = :v_cus
   AND ORD_DT >= ADD_MONTHS(TRUNC(SYSDATE), -12)
 ORDER BY ORD_DT DESC;

-- Q3 「보관 정리」 36개월 지난 달 (매월 1일)
DELETE FROM T_ORD_BIG
 WHERE ORD_DT < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -35);

-- Q4 「미결 점검」 최근 7일 PENDING (매시간)
SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG
 WHERE ORD_ST = 'PENDING'
   AND ORD_DT >= TRUNC(SYSDATE) - 7;

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

xplan

[지금 · 파티션 없음 · 한 번 실행]
  Q1  INDEX RANGE SCAN IX_ORD_BIG_DT_AMT (커버링)                  91블록
  Q2  INDEX RANGE SCAN IX_ORD_BIG_CUS + 테이블 167행                171블록
  Q3  DELETE 약 28,000행 · 인덱스 둘도 고친다 · redo 약 10MB · 그 뒤 전체 스캔은 5,826 그대로
  Q4  TABLE ACCESS FULL                                          5,826블록

[(가) HASH (CUS_ID) 16 · Q1 · 2024년 6월]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |      1 |        |     30 | 00:00:00.02 |     132 |        |       |
|   1 |  HASH GROUP BY        |                     |      1 |     30 |     30 | 00:00:00.02 |     132 |        |       |
|*  2 |   FILTER              |                     |      1 |        |  27390 | 00:00:00.02 |     132 |        |       |
|   3 |    PARTITION HASH ALL |                     |      1 |  27390 |  27390 | 00:00:00.02 |     132 |      1 |    16 |
|*  4 |     INDEX RANGE SCAN  | IX_ORD_BIG_H_DT_AMT |     16 |  27390 |  27390 | 00:00:00.01 |     132 |      1 |    16 |
-------------------------------------------------------------------------------------------------------------------------

   2 - filter(:V_MON<ADD_MONTHS(:V_MON,1))
   4 - access("ORD_DT">=:V_MON AND "ORD_DT"<ADD_MONTHS(:V_MON,1))

[(가) · Q3 · 2022년 1월]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT     |             |      1 |        |      0 | 00:00:01.92 |    118K |        |       |
|   1 |  DELETE              | T_ORD_BIG_H |      1 |        |      0 | 00:00:01.92 |    118K |        |       |
|   2 |   PARTITION HASH ALL |             |      1 |  27390 |  28304 | 00:00:00.12 |    5842 |      1 |    16 |
|*  3 |    TABLE ACCESS FULL | T_ORD_BIG_H |     16 |  27390 |  28304 | 00:00:00.10 |    5842 |      1 |    16 |
----------------------------------------------------------------------------------------------------------------

   3 - filter("ORD_DT"<ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-35))

문제

네 안을 Q1 ~ Q4에 대어 보고, 안마다 칸을 고르는지와 몇 칸을 여는지를 근거로 득실을 정리하시오.
고른 안의 테이블과 인덱스 DDL을 쓰고, Q3을 대신할 문장과 Q2의 인덱스를 LOCAL과 GLOBAL 가운데 무엇으로 할지 근거와 함께 쓰시오.
고른 안의 대가와, 해시 서브파티션을 더해야 할 조건을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (다) 주문일 월별 INTERVAL : Q1 · Q3 · Q4 가 모두 주문일로 칸을 고른다
CREATE TABLE T_ORD_BIG_P
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_P_DT_AMT ON T_ORD_BIG_P (ORD_DT, ORD_AMT) LOCAL;
-- Q2 : 고객 + 주문일 (LOCAL · 12개월 조건이 칸을 13개로 묶는다 · 칸 정리에 인덱스 작업이 없다)
CREATE INDEX IX_ORD_BIG_P_CUS ON T_ORD_BIG_P (CUS_ID, ORD_DT) LOCAL;

-- Q3 : 행을 지우지 않고 36개월 지난 달 칸을 들어낸다 (매월 1일)
ALTER TABLE T_ORD_BIG_P DROP PARTITION FOR (DATE '2022-01-01');

-- Q1 · Q2 · Q4 SQL 은 그대로 (테이블 이름만 바꾼다)

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

xplan

[Q2 · (다) · LOCAL (CUS_ID, ORD_DT) · 2024-12-16]
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name             | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart |   Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                  |      1 |        |    167 | 00:00:00.01 |     193 |        |         |
|   1 |  SORT ORDER BY                               |                  |      1 |    167 |    167 | 00:00:00.01 |     193 |        |         |
|   2 |   PARTITION RANGE ITERATOR                   |                  |      1 |    167 |    167 | 00:00:00.01 |     193 |    KEY | 1048575 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_BIG_P      |     13 |    167 |    167 | 00:00:00.01 |     193 |    KEY | 1048575 |
|*  4 |     INDEX RANGE SCAN                         | IX_ORD_BIG_P_CUS |     13 |    167 |    167 | 00:00:00.01 |      26 |    KEY | 1048575 |
-----------------------------------------------------------------------------------------------------------------------------------------------

   4 - access("CUS_ID"=:V_CUS AND "ORD_DT">=ADD_MONTHS(TRUNC(SYSDATE@!),-12))

  (실행 때 연 칸 — 2023년 12월 ~ 2024년 12월 13칸)

[네 안 · 한 번 실행 (연습 DB에서 재거나 같은 규모로 어림)]
            Q1 (한 달)        Q2 (고객 12개월)     Q3 (월 1회)                  Q4 (최근 7일)
  지금          91              171                DELETE · redo 약 10MB         5,826
  (가)         132 (16칸)        171 (1칸)          DELETE · 16칸 FULL 118K        5,842 (16칸)
  (나)         약 95 (3칸)       약 176 (3칸)        DELETE                        약 5 (P_PENDING) · 결제마다 행 이동
  (다)         161 · 인덱스 89    193 (13칸)          DROP PARTITION · 약 0.01MB     161 ~ 322 (1 ~ 2칸)
  (라)         약 170 (8조각)     193 (13칸 × 1조각)   DROP PARTITION                약 340 (16조각)
  세그먼트     (가) 16 · (나) 3 · (다) 36 · (라) 288

모범 답안 서술

네 안을 SQL 넷에 대어 보면 판가름이 난다(아래 숫자는 연습 DB에서 재거나 같은 규모로 어림한 값이다). (가) HASH (CUS_ID) 16칸은 Q2에서만 칸 하나를 고른다. 그런데 Q2는 지금도 인덱스로 171블록이라 얻는 것이 없다. 주문일로 묻는 Q1과 Q4는 해시 칸을 고르지 못해 16칸을 모두 연다. Q1은 PARTITION HASH ALL(1 ~ 16)에 인덱스 Starts 16으로 132블록이고, Q4는 16칸 FULL 5,842블록이다. 결정적으로 한 달 치가 16칸에 흩어져 있어 Q3을 칸째 들어낼 수 없다. 연습 DB의 Q3은 여전히 DELETE로 16칸을 다 읽고 28,304행을 지웠다(118K블록). (나) LIST (ORD_ST)는 Q4를 P_PENDING 칸 안의 인덱스로 몇 블록에 끝낸다. 하지만 ORD_ST는 결제와 취소 때 바뀌는 값이라 결제마다 행이 칸을 옮겨야 한다. ROW MOVEMENT를 켜지 않으면 ORA-14402로 막히고, 켜면 결제마다 행을 지우고 다시 넣는다(PART-033). Q3도 DELETE로 남는다. (다) 주문일 월별 INTERVAL은 Q1, Q3, Q4가 모두 주문일로 칸을 고른다. Q1은 칸 하나(161블록, 커버링 인덱스로는 89블록), Q4는 최근 7일이 든 칸 한두 개(161 ~ 322블록)로 지금의 5,826블록에서 크게 준다. Q3은 행을 지우지 않고 DROP PARTITION으로 칸째 들어낸다(redo 약 0.01MB). Q2는 주문일 12개월 조건이 칸 13개를 고르고, 칸마다 LOCAL 인덱스 (CUS_ID, ORD_DT)를 한 번씩 찾아 약 193블록이다(인덱스 Starts 13). (라)는 (다)의 칸 안을 HASH (CUS_ID) 8조각으로 다시 나눈 것이다. Q2는 칸마다 조각 하나를 고르지만 어차피 인덱스로 찾으므로 193블록 그대로이고, Q1과 Q4는 조각 8개씩을 모두 열어 조금 더 읽는다. 세그먼트는 36개에서 288개로 늘어난다(첫 익스텐트 8MB면 약 2.3GB). 해시 조각은 동시 입력 경합이나 같은 해시 키로 나눈 테이블과의 파티션 와이즈 조인이 있을 때 값을 하는데, T_ORD_BIG에는 둘 다 없다. 그래서 (다)를 고른다. 테이블은 주문일 월별 INTERVAL로 짓고, Q1용 (ORD_DT, ORD_AMT)와 Q2용 (CUS_ID, ORD_DT)를 모두 LOCAL로 만든다. Q3은 ALTER TABLE T_ORD_BIG_P DROP PARTITION FOR (DATE '2022-01-01')처럼 값으로 칸을 지목해 들어낸다. Q2 인덱스를 GLOBAL로 두면 칸과 상관없이 한 번에 찾아 171블록이다. LOCAL보다 한 번에 약 22블록, 하루 2만 번이면 약 44만 블록을 아낀다. 대신 매월 DROP PARTITION에 UPDATE GLOBAL INDEXES를 붙여야 하고, 빠뜨리면 인덱스가 UNUSABLE이 되어 Q2가 에러 없이 FULL로 떨어진다. 여기서는 칸 정리를 단순하게 지키는 쪽을 택해 LOCAL로 둔다. 하루 44만 블록이 CPU에 부담이 되는 날이 오면 그때 GLOBAL과 정리 절차를 함께 바꾼다. (다)의 대가는 셋이다. Q2가 지금보다 한 번에 약 22블록 더 읽고, Q1은 지금의 커버링 인덱스 91블록과 비슷하거나 조금 더 읽는다(파티션이 조회를 빠르게 하는 것은 아니다). 새 칸 이름이 SYS_P…로 붙어 정리 스크립트는 PARTITION FOR로 칸을 지목해야 한다. 해시 서브파티션은 주문 등록이 한 칸에 몰려 경합이 생기거나(PART-036), 같은 CUS_ID 해시로 나눈 큰 테이블과 대량 조인을 하게 될 때 더한다.

해설

키를 고르는 순서: 들어내는 단위(Q3) → 넓은 범위 조회(Q1, Q4) → 가장 자주 도는 좁은 조회(Q2)는 칸 안의 인덱스로 푼다. 가장 자주 도는 SQL 하나로 키를 정하면(조민우의 안) 다른 SQL이 모두 칸을 다 연다.
(나)는 Q4만 보면 가장 싸다. 미결 점검은 칸이 아니라 PENDING만 담는 함수 기반 인덱스로도 풀 수 있다(PART-033).
Q2를 GLOBAL로 둘 때의 위험은 교재 14화에서 잰 그대로다. UPDATE GLOBAL INDEXES 없이 칸을 들어내자 같은 모양의 조회가 3 → 3,505블록이 되었다. 23ai는 이 절을 붙여도 지운 키를 고아 항목으로 남겨 DDL 자체는 싸다.
파티션이 조회를 빠르게 하는 것은 아니다. Q1은 지금도 커버링 인덱스로 91블록이다(교재 14화 §7.2). 이 설계로 얻는 것은 Q3과 Q4, 그리고 해마다 늘어나는 데이터의 관리다.
칸 정리 스크립트, 통계 수집(INCREMENTAL), 칸 이름 규칙도 설계서에 함께 적는다. 키를 고른 근거(SQL 넷의 표)도 남겨 두어야 다음 사람이 해시 조각을 더할지 판단할 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황