주문서 화면을 빠르게 하려고 주문과 주문상세를 같은 블록에 묶자는 제안서가 올라왔다

실전DDL한빛유통129 / 700

상황

쇼핑몰 「주문서 상세」 화면은 주문 한 건과 그 주문의 상세 1 ~ 3줄을 함께 보여 준다. 고객과 상담원이 하루 약 5만 번 연다. 조민우의 제안서는 이렇다. "주문과 주문상세는 늘 ORD_SEQ로 같이 읽는다. 두 테이블을 ORD_SEQ 인덱스 클러스터에 같이 넣으면 한 블록에서 끝난다." 복사본으로 재 보니 주문서 상세는 6블록에서 4블록이 됐다. 박팀장은 "주문 테이블을 읽는 다른 SQL까지 하루 합계로 따져 오라"고 했다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록(블록당 약 112행) · ORD_SEQ 순으로 쌓였다 · PK_T_ORD (ORD_SEQ) BLEVEL 1 · 다른 인덱스는 없다 · 2024년 6월 4,110건
T_ORD_DET 주문상세: 200,000행 · 896블록(블록당 약 223행) · 주문당 1 ~ 3줄 · (ORD_SEQ, ORD_DET_NO) 순으로 쌓였다 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) BLEVEL 1 · CLUSTERING_FACTOR 약 900
복사본: 인덱스 클러스터 C_ORD (ORD_SEQ) · SIZE 160 · 클러스터 인덱스 C_ORD_IDX BLEVEL 1 · 두 테이블을 담은 클러스터 블록 약 2,010개
하루 실행 횟수: S1 주문서 상세 약 5만 번 · S2 「월간 결제수단별 매출」(정산 대시보드) 약 300번 · S3 야간 정산 적재(T_ORD를 한 번 읽어 T_ORD_STLM에 APPEND) 1번 · 주문 입력 하루 약 140건

원본 SQL

sql

-- 제안서의 DDL
CREATE CLUSTER C_ORD (ORD_SEQ NUMBER(18)) SIZE 160;
CREATE INDEX C_ORD_IDX ON CLUSTER C_ORD;
CREATE TABLE T_ORD_C     CLUSTER C_ORD (ORD_SEQ) AS SELECT * FROM T_ORD;
CREATE TABLE T_ORD_DET_C CLUSTER C_ORD (ORD_SEQ) AS SELECT * FROM T_ORD_DET;

-- S1 주문서 상세
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.PAY_TP, o.ORD_AMT,
       d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY, d.UNT_PRC
  FROM T_ORD o, T_ORD_DET d
 WHERE o.ORD_SEQ = :v_ord
   AND d.ORD_SEQ = o.ORD_SEQ;

-- S2 월간 결제수단별 매출
SELECT PAY_TP, COUNT(*), SUM(PAY_AMT)
  FROM T_ORD
 WHERE ORD_DT >= TO_DATE(:v_ym, 'YYYYMM')
   AND ORD_DT <  ADD_MONTHS(TO_DATE(:v_ym, 'YYYYMM'), 1)
 GROUP BY PAY_TP;

실행계획 학습용 예시 · 행 수와 쌓인 순서는 적재 스크립트 그대로 · 클러스터 블록 수는 SIZE로 센 어림값

xplan

[운영 · S1]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |      2 |00:00:00.01 |       6 |
|   1 |  NESTED LOOPS                         |              |      1 |      2 |      2 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID         | T_ORD        |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX UNIQUE SCAN                  | PK_T_ORD     |      1 |      1 |      1 |00:00:00.01 |       2 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_DET    |      1 |      2 |      2 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN                   | PK_T_ORD_DET |      1 |      2 |      2 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------
   3 - access("O"."ORD_SEQ"=:V_ORD)
   5 - access("D"."ORD_SEQ"=:V_ORD)

[복사본 · S1]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |      2 |00:00:00.01 |       4 |
|   1 |  NESTED LOOPS                         |              |      1 |      2 |      2 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS CLUSTER                | T_ORD_C      |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX UNIQUE SCAN                  | C_ORD_IDX    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS CLUSTER                | T_ORD_DET_C  |      1 |      2 |      2 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------------
   3 - access("O"."ORD_SEQ"=:V_ORD)
   4 - filter("D"."ORD_SEQ"=:V_ORD)

[운영 · S2 · 2024년 6월]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |      3 |00:00:00.01 |     896 |
|   1 |  HASH GROUP BY                        |              |      1 |      3 |      3 |00:00:00.01 |     896 |
|*  2 |   TABLE ACCESS FULL                   | T_ORD        |      1 |   4110 |   4110 |00:00:00.01 |     896 |
-----------------------------------------------------------------------------------------------------------------

[복사본 · S2 · 2024년 6월]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |      3 |00:00:00.02 |    2010 |
|   1 |  HASH GROUP BY                        |              |      1 |      3 |      3 |00:00:00.02 |    2010 |
|*  2 |   TABLE ACCESS FULL                   | T_ORD_C      |      1 |   4110 |   4110 |00:00:00.02 |    2010 |
-----------------------------------------------------------------------------------------------------------------
   2 - filter(("ORD_DT">=TO_DATE(:V_YM,'YYYYMM') AND "ORD_DT"<ADD_MONTHS(TO_DATE(:V_YM,'YYYYMM'),1)))

문제

클러스터로 S1이 줄어든 것이 2블록뿐인 이유를 주문상세가 지금 쌓여 있는 모양으로 설명하시오. S2가 896블록에서 2,010블록이 되는 이유도 설명하시오.
S1, S2, S3의 실행 횟수로 하루 읽는 양을 비교하고 제안을 받아들일지 판단하시오. 판단의 근거를 확인하는 SQL과 필요한 DDL을 작성하시오.
클러스터 테이블의 제약이 주문 테이블에 주는 영향을 설명하고, 인덱스 클러스터가 맞는 테이블의 조건을 정리하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 판단 근거 : 주문상세가 이미 ORD_SEQ 순으로 모여 있는가 (CF 가 테이블 블록 수에 가까운가)
SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR
  FROM USER_INDEXES
 WHERE INDEX_NAME IN ('PK_T_ORD', 'PK_T_ORD_DET');

-- 제안을 받지 않는다 : 개발 서버의 복사본만 정리하고 운영은 지금의 힙 구조 그대로
DROP CLUSTER C_ORD INCLUDING TABLES;

-- S1 · S2 는 그대로
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.PAY_TP, o.ORD_AMT,
       d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY, d.UNT_PRC
  FROM T_ORD o, T_ORD_DET d
 WHERE o.ORD_SEQ = :v_ord
   AND d.ORD_SEQ = o.ORD_SEQ;

개선 후 실행계획 학습용 예시 · 행 수와 쌓인 순서는 적재 스크립트 그대로 · 클러스터 블록 수는 SIZE로 센 어림값

xplan

[하루 합계 · 블록]
                    실행 횟수   운영 (힙)                복사본 (인덱스 클러스터)
S1 주문서 상세        50,000    × 6     = 300,000       × 4      = 200,000
S2 월간 결제수단별       300    × 896   = 268,800       × 2,010  = 603,000
S3 야간 정산 적재          1    × 896   =     896       × 2,010  =   2,010
합계                                      569,696                  805,010

[USER_INDEXES]
INDEX_NAME     BLEVEL  LEAF_BLOCKS  CLUSTERING_FACTOR
PK_T_ORD            1          187                896
PK_T_ORD_DET        1          532                901

모범 답안 서술

복사본에서 S1은 정말 6 → 4블록이 됐다. 운영에서는 PK_T_ORD로 주문을 찾고(인덱스 2 + 테이블 1), PK_T_ORD_DET로 상세를 찾는다(인덱스 2 + 테이블 1). 클러스터에서는 클러스터 인덱스로 블록 주소를 한 번 찾고(2), 그 블록에서 주문(1)과 상세(1)를 함께 읽는다. 줄어든 것이 2블록뿐인 이유는 주문상세가 이미 ORD_SEQ 순으로 쌓여 있어서다. PK_T_ORD_DET의 CLUSTERING_FACTOR가 약 900으로 테이블 블록 수(896)와 같다. 한 주문의 상세 1 ~ 3줄은 지금도 한 블록에 모여 있으므로, 클러스터가 아낄 수 있는 것은 인덱스 하나를 덜 타는 몫뿐이다. 대신 T_ORD를 FULL로 읽는 SQL은 두 배 넘게 무거워진다. 클러스터 블록에는 주문 행과 주문상세 행이 섞여 있어서, 주문만 읽으려 해도 두 테이블이 함께 들어 있는 블록 약 2,010개를 다 읽는다. S2는 896 → 2,010블록이고, 야간 정산 적재(S3)가 T_ORD를 읽는 단계도 같다. 하루 합계로 보면 S1은 30만 → 20만 블록으로 10만 블록을 아끼고, S2는 268,800 → 603,000블록으로 33만 블록 넘게 는다. 합계는 약 57만 → 81만 블록이라 하루 23만 블록 남짓 손해다. 그래서 제안을 받지 않는다. 클러스터 테이블의 제약도 있다. TRUNCATE TABLE과 직접 경로 적재(APPEND)를 쓸 수 없고 파티션도 할 수 없다. 주문 테이블을 나중에 월별 파티션으로 나누거나 정리 배치를 파티션 단위로 바꾸는 길이 막힌다. 클러스터 키 하나에 SIZE만큼 자리를 잡으므로 상세가 많은 주문은 체인 블록으로 넘친다. 인덱스 클러스터가 맞는 경우는 키 하나에 딸린 자식 행이 많고 지금은 여러 블록에 흩어져 있으며, 키 단위로 함께 읽고 테이블 전체를 읽는 일은 드문 테이블이다. 주문과 주문상세는 그 조건에 맞지 않는다.

해설

함정: '늘 같이 읽는다'는 클러스터의 필요조건일 뿐이다. 같이 읽는 행이 지금 이미 한 블록에 모여 있다면 클러스터가 아낄 것이 거의 없다. 먼저 CLUSTERING_FACTOR로 지금 얼마나 흩어져 있는지 본다.
S1을 1블록 더 줄이고 싶다면 T_ORD_DET를 IOT로 만드는 방법이 있다(상세 테이블 액세스가 없어진다). 하지만 상품별 실적처럼 ITM_ID로 찾는 보조 인덱스가 논리 ROWID를 쓰게 되므로 그 SQL까지 재 봐야 한다(ACC-023, ACC-051).
다중 테이블 클러스터는 DROP TABLE도 안에서 행을 하나씩 지우며 느리다. 전체를 지울 때는 TRUNCATE CLUSTER를 쓰고, 그러면 묶인 두 테이블이 함께 비워진다.
구조 변경의 효과는 SQL 하나의 Buffers가 아니라 그 테이블을 읽고 쓰는 SQL 전부의 하루 합계로 판단한다(ACC-053과 같은 방법).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수와 쌓인 순서는 적재 스크립트 그대로 · 클러스터 블록 수는 SIZE로 센 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수와 쌓인 순서는 적재 스크립트 그대로 · 클러스터 블록 수는 SIZE로 센 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황