판매번호 앞 여덟 자리가 판매일자라며 날짜 인덱스 둘을 다 지우자는 정비안이 올라왔다

중급DDL유통73 / 700

상황

유통 본사의 외주 DBA가 판매 테이블 인덱스 정비안을 올렸다. "판매번호 앞 8자리가 판매일자다. 날짜로 찾는 일은 PK(SLE_NO)로 대신할 수 있으니 날짜로 시작하는 인덱스 둘을 지우고, 날짜 조건은 판매번호 LIKE로 바꿔 쓴다. POS INSERT가 고칠 인덱스가 셋에서 하나로 준다." 박팀장은 이 안을 검토해서 실제로 정리할 수 있는 것만 골라 오라고 했다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(전체 스캔 4,510) · 판매 날짜순으로 쌓였다 · SLE_NO VARCHAR2(15) '20230601-0001' · SLE_DT VARCHAR2(8) · SHP_NO 1001 ~ 1005 · 하루 596 ~ 793건(2023-06-01은 706건, 테이블 약 26블록) · 2023년 6월 20,953건 · POS가 하루 약 700건을 INSERT한다
2023-06-12 개점 리허설 때 교육용 단말이 넣은 판매 14건은 판매번호가 'TR01-0001' ~ 'TR01-0014'이고 판매일자는 '20230612'다(실제 판매로 정산되었다) (문제 설정: 교재의 CF10에는 이런 행이 없다)
인덱스 CF10_PK (SLE_NO): BLEVEL 2 · 리프 약 1,680블록 / IX_CF10_SLE_DT (SLE_DT): 리프 1,393블록 / IX_CF10_DT_SHP (SLE_DT, SHP_NO): 리프 약 1,470블록(리프 한 블록에 약 340항목) · 통계 최신
S3은 BI 도구(IDX-018)가 만드는 SQL이라 고칠 수 없다

원본 SQL

sql

-- 정비안 ① 날짜로 시작하는 인덱스 둘을 지운다
DROP INDEX IX_CF10_SLE_DT;
DROP INDEX IX_CF10_DT_SHP;

-- 정비안 ② 날짜 조건은 판매번호 앞부분으로 바꿔 쓴다 (예: S1)
SELECT SLE_NO, SLE_HMS, SHP_NO
  FROM CF10
 WHERE SLE_NO LIKE :v_dt || '%'
 ORDER BY SLE_NO;

-- 지금 쓰는 SQL
-- S1 「일별 판매 목록」 (매장과 본사 · 하루 수백 번)   WHERE SLE_DT = :v_dt ORDER BY SLE_NO
-- S2 「일 마감 매장별 건수」 (하루 수십 번)            WHERE SLE_DT = :v_dt GROUP BY SHP_NO
-- S3 BI 「월 판매」 (MD 30명 · 고칠 수 없다)          WHERE "SLE_DT" BETWEEN '20230601' AND '20230630' GROUP BY "SHP_NO"

실행계획 학습용 예시

xplan

[S3 · 지금]
------------------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |      1 |        |      5 |00:00:00.01 |      64 |
|   1 |  HASH GROUP BY    |                |      1 |      5 |      5 |00:00:00.01 |      64 |
|*  2 |   INDEX RANGE SCAN| IX_CF10_DT_SHP |      1 |  20953 |  20953 |00:00:00.01 |      64 |
------------------------------------------------------------------------------------------------
   2 - access("SLE_DT">='20230601' AND "SLE_DT"<='20230630')

[S3 · 복제 DB에서 정비안대로 두 인덱스를 지운 뒤]
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      5 |00:00:00.04 |    4510 |
|   1 |  HASH GROUP BY     |      |      1 |      5 |      5 |00:00:00.04 |    4510 |
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |  20953 |  20953 |00:00:00.04 |    4510 |
-------------------------------------------------------------------------------------
   2 - filter(("SLE_DT">='20230601' AND "SLE_DT"<='20230630'))

[S2 · 지금]  INDEX RANGE SCAN IX_CF10_DT_SHP · access("SLE_DT"=:V_DT) · 인덱스만 · Buffers 4
[S2 · 판매번호 LIKE로 바꿔 쓴 뒤]  INDEX RANGE SCAN CF10_PK · access("SLE_NO" LIKE :V_DT||'%') · 테이블 26블록 · Buffers 31

문제

정비안대로 하면 S1 ~ S3이 각각 어떻게 되는지 설명하시오. 특히 고칠 수 없는 S3과, 판매번호 규칙에서 벗어난 판매가 결과에 주는 영향을 설명하시오.
인덱스가 중복인지 무엇으로 판단해야 하는지 설명하고, 이 테이블에서 실제로 정리할 인덱스를 정해 DDL을 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- (SLE_DT) 는 (SLE_DT, SHP_NO) 의 접두다 → 이것만 정리한다
ALTER INDEX IX_CF10_SLE_DT INVISIBLE;   -- S1 · S3 이 IX_CF10_DT_SHP 로 가는지, 다른 SQL 이 견디는지 2주 지켜본 뒤
DROP INDEX IX_CF10_SLE_DT;
-- IX_CF10_DT_SHP 와 S1 ~ S3 의 SQL 은 그대로 둔다 (판매번호 LIKE 로 바꾸지 않는다)

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

xplan

[S1 · IX_CF10_SLE_DT를 지운 뒤]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    706 |00:00:00.01 |      31 |
|   1 |  SORT ORDER BY                       |                |      1 |    706 |    706 |00:00:00.01 |      31 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    706 |    706 |00:00:00.01 |      31 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_DT_SHP |      1 |    706 |    706 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------------------
   3 - access("SLE_DT"=:V_DT)

[S2 · 그대로]  IX_CF10_DT_SHP · 인덱스만 · Buffers 4
[S3 · 그대로]  IX_CF10_DT_SHP · 인덱스만 · Buffers 64

모범 답안 서술

정비안의 전제는 "판매번호 앞 8자리 = 판매일자"인데, 이 관계는 사람이 아는 규칙이지 옵티마이저가 아는 사실이 아니다. SLE_DT = :v_dt나 SLE_DT BETWEEN …을 받은 옵티마이저는 SLE_NO로 정렬된 CF10_PK를 쓸 수 없다. 조건 컬럼이 그 인덱스에 없기 때문이다. S3은 BI 도구가 만드는 SQL이라 고칠 수 없다. 날짜 인덱스 둘이 사라지면 한 달을 볼 때마다 테이블 전체를 읽는다(Buffers 64 → 4,510, MD 30명). S1과 S2는 판매번호 LIKE로 바꿔 쓰면 CF10_PK로 그날 706항목을 찾아간다. S1은 판매번호 순으로 읽혀 정렬까지 없어지니 오히려 조금 낫지만, S2는 인덱스만 읽던 4블록이 테이블 26블록을 더 읽어 31블록이 된다(4 → 31). 더 큰 문제는 결과다. 교육용 단말의 판매 14건은 판매번호가 'TR01-…'이고 판매일자는 2023-06-12다. 판매번호 LIKE '20230612%'로 바꿔 쓴 S1과 S2에서는 이 14건이 빠져, 그날 판매 목록과 일 마감 건수가 정산과 맞지 않게 된다. 규칙에 기대어 바꿔 쓴 SQL은 규칙이 한 번이라도 깨지면 조용히 틀린다. 인덱스 중복은 값의 규칙이 아니라 컬럼과 그 순서로 판단한다. 하나가 다른 하나의 접두일 때, 즉 (A)와 (A, B)일 때 긴 쪽이 짧은 쪽의 일을 대신한다. 이 테이블에서는 (SLE_DT)가 (SLE_DT, SHP_NO)의 접두다. IX_CF10_SLE_DT만 INVISIBLE로 돌려 S1과 S3이 IX_CF10_DT_SHP로 가는지(S1 31블록, S3 64블록 그대로) 지켜본 뒤 지운다. POS INSERT가 고칠 인덱스는 셋에서 둘로 준다. SQL은 그대로 둔다.

해설

판매번호 규칙을 정말로 쓰고 싶다면 먼저 데이터베이스가 그 규칙을 지키게 한다. 교육용 판매 14건을 정리한 뒤 CHECK (SUBSTR(SLE_NO, 1, 8) = SLE_DT) 제약을 건다. 그래도 S3처럼 SLE_DT로 쓴 SQL은 SLE_DT로 시작하는 인덱스가 있어야 찾아간다. 고칠 수 없는 SQL이 있는 한 날짜 인덱스는 남는다.
정비안이 노린 POS INSERT 비용은 하루 700건 규모라 크지 않다. 인덱스 하나에 5만 건당 redo 약 12MB(교재 14화)를 하루 700건으로 환산하면 0.2MB 안팎이다. 지우는 이득과 잃는 것을 같은 단위로 비교한다.
판매번호 LIKE가 정답인 자리도 있다. 판매일자 컬럼이 없는 판매상품(CF11)에서는 판매번호 앞부분이 날짜를 찾는 유일한 길이다(IDX-007). 날짜 컬럼이 따로 있는 테이블에서는 그 컬럼으로 찾는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황