영수증 조회를 4블록에서 1블록으로 줄이겠다는 판매 테이블 재구성 제안서가 올라왔다

실전DDL유통128 / 700

상황

유통 본사에 외부 컨설턴트의 제안서가 들어왔다. "영수증 조회(SLE_NO = :v)가 판매 SQL 가운데 가장 많이 실행된다. CF10을 SLE_NO 해시 클러스터로 다시 만들면 4블록이 1블록이 된다." 개발 서버에 복사본 CF10_HC를 만들어 재 보니 영수증 조회는 정말 1블록이었다. 박팀장은 "판매 테이블을 쓰는 다른 화면이 어떻게 되는지 하루 전체로 따져서 적어 와라"고 했다. 조민우는 복사본에 날짜 인덱스까지 만들어 재 봤다.

테이블과 인덱스

CF10 판매(운영): 500,000행 · 4,583블록 · 판매 날짜순으로 쌓였다 · 2023-06-01의 706건이 13블록에 모여 있다 · 2023년 6월 20,953건
운영 인덱스 CF10_PK (SLE_NO) BLEVEL 2 · IX_CF10_SLE_DT (SLE_DT) BLEVEL 2 · 리프 1,393 · CLUSTERING_FACTOR 9,921(교재 03화 실측)
복사본 CF10_HC: 같은 500,000행을 단일 테이블 해시 클러스터 CF10_HCL(HASHKEYS 600000 · SIZE 80)에 넣었다 · 만들 때 미리 잡은 블록 약 6,000개 · 행은 SLE_NO의 해시 값에 따라 블록에 흩어진다
조민우가 복사본에 만든 CF10_HC_X01 (SLE_DT): 운영 날짜 인덱스와 같은 크기 · CLUSTERING_FACTOR 약 467,000 · 이 인덱스를 만들기 전에 S2는 복사본 FULL 6,012블록이었다
하루 실행 횟수: S1 영수증 조회 약 2,000번(반품, 재출력, 본사 대사) · S2 일별 판매 목록 약 300번 · S3 월별 매장 집계 약 50번 · POS 판매 INSERT 약 700건

원본 SQL

sql

-- 제안서의 DDL
CREATE CLUSTER CF10_HCL (SLE_NO VARCHAR2(15)) SIZE 80 HASHKEYS 600000;
CREATE TABLE CF10_HC CLUSTER CF10_HCL (SLE_NO) AS SELECT * FROM CF10;

-- S1 영수증 조회
SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO FROM CF10 WHERE SLE_NO = :v_sle_no;

-- S2 일별 판매 목록
SELECT SLE_NO, SLE_HMS, SHP_NO FROM CF10 WHERE SLE_DT = :v_dt ORDER BY SLE_NO;

-- S3 월별 매장 집계
SELECT SHP_NO, COUNT(*)
  FROM CF10
 WHERE SLE_DT BETWEEN :v_from AND :v_to
 GROUP BY SHP_NO;

실행계획 학습용 예시 · 운영 CF10 숫자는 교재 03화 실측 · 복사본 숫자는 해시 클러스터에 행이 고르게 흩어진다고 보고 센 값

xplan

[운영 CF10 · S1]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID         | CF10           |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX UNIQUE SCAN                  | CF10_PK        |      1 |      1 |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------
[운영 CF10 · S2 · 2023-06-01]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    706 |00:00:00.01 |      18 |
|   1 |  SORT ORDER BY                       |                |      1 |    685 |    706 |00:00:00.01 |      18 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    685 |    706 |00:00:00.01 |      18 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    685 |    706 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------------------
[운영 CF10 · S3 · 2023년 6월]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      5 |00:00:00.02 |     478 |
|   1 |  HASH GROUP BY                       |                |      1 |      5 |      5 |00:00:00.02 |     478 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |  20953 |  20953 |00:00:00.02 |     478 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  20953 |  20953 |00:00:00.01 |      62 |
----------------------------------------------------------------------------------------------------------------
[복사본 CF10_HC · S1]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      1 |00:00:00.01 |       1 |
|*  1 |  TABLE ACCESS HASH                   | CF10_HC        |      1 |      1 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------------------
[복사본 CF10_HC · S2 · 조민우의 X01]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    706 |00:00:00.02 |     667 |
|   1 |  SORT ORDER BY                       |                |      1 |    685 |    706 |00:00:00.02 |     667 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10_HC        |      1 |    685 |    706 |00:00:00.02 |     667 |
|*  3 |    INDEX RANGE SCAN                  | CF10_HC_X01    |      1 |    685 |    706 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------------------
[복사본 CF10_HC · S3]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      5 |00:00:00.05 |    6012 |
|   1 |  HASH GROUP BY                       |                |      1 |      5 |      5 |00:00:00.05 |    6012 |
|*  2 |   TABLE ACCESS FULL                  | CF10_HC        |      1 |  20953 |  20953 |00:00:00.05 |    6012 |
----------------------------------------------------------------------------------------------------------------

문제

제안서의 S1 숫자가 맞는 이유와, 같은 복사본에서 S2와 S3가 무거워지는 이유를 행이 블록에 놓이는 방식으로 설명하시오. 조민우의 날짜 인덱스가 S2를 18블록으로 돌리지 못한 이유도 설명하시오.
S1, S2, S3의 실행 횟수를 곱해 하루 읽는 양을 운영 CF10과 복사본으로 비교하고, 제안을 받아들일지 판단하시오. 판단에 맞는 SQL과 DDL을 작성하시오.
해시 클러스터의 다른 제약이 판매 테이블에 어떤 영향을 주는지 설명하고, 해시 클러스터가 맞는 테이블의 조건을 정리하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 제안을 받지 않는다 : 운영 CF10 은 판매 날짜순 힙 테이블 그대로 둔다
DROP CLUSTER CF10_HCL INCLUDING TABLES;      -- 개발 서버의 복사본 정리

-- S1 · S2 · S3 는 운영 CF10 에서 그대로
SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO FROM CF10 WHERE SLE_NO = :v_sle_no;

SELECT SLE_NO, SLE_HMS, SHP_NO FROM CF10 WHERE SLE_DT = :v_dt ORDER BY SLE_NO;

SELECT SHP_NO, COUNT(*)
  FROM CF10
 WHERE SLE_DT BETWEEN :v_from AND :v_to
 GROUP BY SHP_NO;

개선 후 실행계획 학습용 예시 · 운영 CF10 숫자는 교재 03화 실측 · 복사본 숫자는 해시 클러스터에 행이 고르게 흩어진다고 보고 센 값

xplan

[하루 합계 · 블록]
                  실행 횟수   운영 CF10 (힙)            복사본 CF10_HC (해시 클러스터)
S1 영수증 조회        2,000    × 4     =   8,000       × 1      =   2,000
S2 일별 판매 목록       300    × 18    =   5,400       × 667    = 200,100
S3 월별 매장 집계        50    × 478   =  23,900       × 6,012  = 300,600
합계                                       37,300                  502,700

모범 답안 서술

제안서의 숫자는 S1만 보면 맞다. 운영 CF10의 영수증 조회는 CF10_PK의 루트, 브랜치, 리프와 테이블 1블록을 읽어 4블록이고, 해시 클러스터에서는 SLE_NO의 해시 값으로 블록 주소를 바로 계산해 TABLE ACCESS HASH 1블록으로 끝난다. 문제는 판매를 날짜로 읽는 S2와 S3다. 운영 CF10은 판매 날짜순으로 쌓여 하루 706건이 13블록에 모여 있다. 그래서 날짜 인덱스로 읽어도 테이블은 13블록이다(S2 18블록, S3 478블록). 해시 클러스터는 행을 SLE_NO의 해시 값에 따라 블록 약 6,000개에 흩어 놓는다. 하루 706건이 약 660블록에 하나씩 떨어지므로 같은 날짜 인덱스를 만들어도 S2는 667블록이다. CF10_HC_X01의 CLUSTERING_FACTOR가 약 467,000으로 행 수에 가까운 것이 그 뜻이다. 인덱스는 찾는 길을 줄 뿐 행이 놓인 자리를 바꾸지 못한다. 한 달 20,953건이면 거의 모든 블록을 읽어야 하므로 S3는 FULL로 미리 잡은 6,012블록을 다 읽는다. 하루로 합치면 운영 CF10은 37,300블록, 복사본은 502,700블록으로 13배가 넘는다. S1에서 아끼는 6,000블록을 S2와 S3가 47만 블록으로 돌려받는다. 그래서 제안을 받지 않고 CF10을 날짜순 힙 테이블 그대로 둔다. 해시 클러스터의 다른 제약도 판매 테이블에 맞지 않는다. = 조건으로만 찾을 수 있고, HASHKEYS와 SIZE로 공간을 미리 잡으므로 FULL 스캔이 운영 테이블(4,511)보다 많은 6,012블록을 읽는다. 판매가 계획한 60만 건을 넘으면 같은 블록을 나눠 쓰는 체인이 생긴다. 클러스터 테이블은 TRUNCATE TABLE과 직접 경로 적재를 쓸 수 없고 파티션도 할 수 없어서, 앞으로 월별 파티션이나 대량 정리를 할 길이 막힌다. 해시 클러스터는 = 조건 조회가 압도적으로 많고, 범위로 읽는 SQL이 거의 없으며, 행 수를 미리 짐작할 수 있는 테이블에 맞는다. 판매 테이블은 날짜로 읽는 화면이 일량의 대부분이라 그 반대다.

해설

함정: 제안서처럼 가장 많이 실행되는 SQL 하나의 Buffers만 비교하면 구조 변경이 늘 이득으로 보인다. 테이블 구조는 그 테이블을 읽는 SQL 전부에 영향을 주므로, 실행 횟수를 곱한 하루 합계로 판단한다.
해시 클러스터에도 B-tree 인덱스를 만들 수 있지만, 행이 흩어진 모양(CF)은 인덱스로 고칠 수 없다. 조민우의 X01이 S2를 6,012 → 667로 줄였을 뿐 18블록으로 돌리지 못한 이유다.
S1을 더 줄이고 싶다면 구조보다 호출 횟수를 먼저 본다. 반품과 재출력 화면이 같은 영수증을 여러 번 조회하는지 확인한다.
S3는 (SLE_DT, SHP_NO) 인덱스를 두면 테이블에 가지 않는다(478 → 약 65블록). 하루 50번이므로 POS 적재 비용과 견주어 정한다(ACC-016).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 운영 CF10 숫자는 교재 03화 실측 · 복사본 숫자는 해시 클러스터에 행이 고르게 흩어진다고 보고 센 값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 운영 CF10 숫자는 교재 03화 실측 · 복사본 숫자는 해시 클러스터에 행이 고르게 흩어진다고 보고 센 값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황