점심 피크 분석에서 거르는 시간 컬럼을 인덱스에 넣었는데 블록은 3분의 1밖에 줄지 않았다

중급SELECT유통106 / 700

상황

매장운영팀의 「점심 피크 분석」 대시보드는 분기를 고르면 매장마다 12시대(12:00:00 ~ 12:59:59) 판매 건수를 보여 준다. 30분마다 새로 고치고 점장 다섯 명도 수시로 열어서 하루 100번쯤 돈다. 조민우는 "테이블에서 거르는 SLE_HMS를 인덱스에 넣으면 테이블에 덜 간다"며 (SLE_DT, SLE_HMS) 인덱스를 INVISIBLE로 만들어 쟀다. 1,430블록이 921블록이 됐다. 조민우는 "점심 판매가 절반이니 절반쯤은 줄 줄 알았다"고 한다. 아래는 2023년 4분기다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(블록당 약 109행) · 판매 날짜순으로 쌓였고, 하루 안에서는 판매번호(곧 시각) 순이다 · POS가 하루 약 700건을 INSERT한다
SLE_HMS VARCHAR2(6) 'HHMISS' · 2023년 4분기 62,700건 · 그중 12시대 33,451건(53%) · 매장별 6,631 ~ 6,759건
인덱스 IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393 · CLUSTERING_FACTOR 9,921(교재 03화 실측) · 「일별 판매 목록」 같은 다른 SQL도 쓴다
조민우 안 IX_CF10_DT_HMS (SLE_DT, SLE_HMS): INVISIBLE · BLEVEL 2 · 리프 약 1,520 · 통계 최신

원본 SQL

sql

SELECT SHP_NO, COUNT(*) AS lunch_cnt
  FROM CF10
 WHERE SLE_DT  BETWEEN :v_from AND :v_to
   AND SLE_HMS BETWEEN '120000' AND '125959'
 GROUP BY SHP_NO
 ORDER BY SHP_NO

실행계획 학습용 예시 · 건수는 적재 데이터(CF10) 그대로

xplan

[지금 · IX_CF10_SLE_DT]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      5 |00:00:00.03 |    1430 |
|   1 |  SORT GROUP BY                       |                |      1 |      5 |      5 |00:00:00.03 |    1430 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   2800 |  33451 |00:00:00.03 |    1430 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    11K |  62700 |00:00:00.01 |     177 |
-----------------------------------------------------------------------------------------------------------------
   2 - filter(("SLE_HMS">='120000' AND "SLE_HMS"<='125959'))
   3 - access("SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO)

[조민우 안 · IX_CF10_DT_HMS · 세션에서만 켜고]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      5 |00:00:00.02 |     921 |
|   1 |  SORT GROUP BY                       |                |      1 |      5 |      5 |00:00:00.02 |     921 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   2800 |  33451 |00:00:00.02 |     921 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_DT_HMS |      1 |   2800 |  33451 |00:00:00.01 |     192 |
-----------------------------------------------------------------------------------------------------------------
   3 - access("SLE_DT">=:V_FROM AND "SLE_HMS">='120000' AND "SLE_DT"<=:V_TO AND "SLE_HMS"<='125959')
       filter(("SLE_HMS">='120000' AND "SLE_HMS"<='125959'))

문제

테이블로 넘기는 행은 62,700 → 33,451로 47% 줄었는데 테이블 쪽 Buffers는 1,253 → 729로 42%만 준 이유를, 남는 행이 어느 블록에 있는지로 설명하시오. 조민우 안에서 SLE_HMS가 access와 filter에 함께 나오는 이유도 설명하시오.
이 화면이 테이블을 읽지 않도록 인덱스를 설계하고 DDL을 작성하시오. 조민우 안과 기존 IX_CF10_SLE_DT를 어떻게 할지와 그 대가도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 쓰는 컬럼 셋을 다 담는다 · 범위 조건 SLE_DT 가 선두 (SLE_HMS 가 선두면 2년 치 점심 53% 를 읽는다)
CREATE INDEX IX_CF10_DT_HMS_SHP ON CF10 (SLE_DT, SLE_HMS, SHP_NO) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 재고 → T2 → VISIBLE

SELECT SHP_NO, COUNT(*) AS lunch_cnt                         -- SQL 은 그대로
  FROM CF10
 WHERE SLE_DT  BETWEEN :v_from AND :v_to
   AND SLE_HMS BETWEEN '120000' AND '125959'
 GROUP BY SHP_NO
 ORDER BY SHP_NO;

-- 조민우 안은 새 인덱스의 접두부라 필요 없다
DROP INDEX IX_CF10_DT_HMS;
-- IX_CF10_SLE_DT 는 「일별 판매 목록」 등이 쓴다 · DBA_INDEX_USAGE 로 본 뒤 정리를 따로 정한다

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터(CF10) 그대로

xplan

----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |      1 |        |      5 |00:00:00.01 |     211 |
|   1 |  SORT GROUP BY     |                    |      1 |      5 |      5 |00:00:00.01 |     211 |
|*  2 |   INDEX RANGE SCAN | IX_CF10_DT_HMS_SHP |      1 |   2800 |  33451 |00:00:00.01 |     211 |
----------------------------------------------------------------------------------------------------
   2 - access("SLE_DT">=:V_FROM AND "SLE_HMS">='120000' AND "SLE_DT"<=:V_TO AND "SLE_HMS"<='125959')
       filter(("SLE_HMS">='120000' AND "SLE_HMS"<='125959'))

모범 답안 서술

지금 계획은 3번 줄 인덱스에서 4분기 62,700행을 넘기고, 2번 줄이 테이블에서 SLE_HMS를 확인해 33,451행을 남긴다. 테이블 쪽 Buffers는 1,430 - 177 = 1,253블록이다. 조민우 안은 SLE_HMS를 리프에서 걸러 33,451행만 테이블로 넘긴다. 하지만 테이블 쪽 Buffers는 729블록으로 42%만 줄었다. 테이블 쪽 Buffers는 넘겨받은 행 수가 아니라 방문한 블록 수이기 때문이다. CF10은 날짜순, 하루 안에서는 시각 순으로 쌓여 있어 하루치 약 700건이 13블록에 들어 있다. 12시대 판매는 그날 판매의 53%이고 그 13블록 가운데 가운데쪽 8개쯤에 퍼져 있다. 남는 행이 그 블록들에 나뉘어 있으니, 걸러 낸 행이 들어 있던 블록 가운데 상당수는 어차피 다시 방문해야 한다. 거른 비율이 작고(47%) 남는 행이 여러 블록에 퍼져 있으면, 필터 컬럼을 인덱스로 옮겨도 블록은 그만큼 줄지 않는다. 조민우 안에서 SLE_HMS가 access와 filter에 함께 나오는 것은, 범위 조건인 SLE_DT 뒤에 와서 읽기 시작점만 정할 뿐 읽을 구간을 줄이지 못하기 때문이다. 4분기 리프를 다 읽으며(192블록) 리프에서 거른다. 그래서 테이블에 아예 가지 않도록 SQL이 쓰는 SLE_DT, SLE_HMS, SHP_NO를 다 담은 (SLE_DT, SLE_HMS, SHP_NO) 커버링 인덱스를 만든다. 4분기 리프 약 209블록만 읽고 끝난다(Buffers 1,430 → 211, 하루 100번이면 약 14만 → 2만 블록). 선두는 범위 조건이어도 SLE_DT로 둔다. SLE_HMS를 선두로 두면 2년 치 12시대(전체의 53%)를 다 읽고 날짜로 걸러야 한다. 조민우 안 (SLE_DT, SLE_HMS)는 새 인덱스의 접두부라서 만들 필요가 없으니 지운다. IX_CF10_SLE_DT도 새 인덱스의 접두부지만 「일별 판매 목록」 같은 다른 SQL이 쓰므로, DBA_INDEX_USAGE와 V$SQL_PLAN으로 확인한 뒤 따로 정한다. 대가는 POS가 판매를 INSERT할 때마다 인덱스를 하나 더 고친다는 것이다. 하루 약 700건이라 작다. 또 대시보드에 컬럼이 하나 늘면 커버링이 깨져 테이블 액세스가 돌아오므로, 전제하는 컬럼 목록을 T2 승인서에 적는다.

해설

함정: "필터 컬럼을 인덱스에 넣으면 테이블 액세스가 준다"는 맞는 말이지만, 줄어드는 것은 행이고 Buffers는 블록이다. 거르는 비율이 크고(ACC-033처럼 262줄에서 3줄) 남는 행이 적을 때 효과가 크다. 남는 행이 절반이면 커버링까지 가야 한다.
데이터 함정: 이 테이블의 SLE_HMS에는 오전 8 ~ 9시 판매 33,241건이 앞의 0이 빠진 다섯 자리('80218')로 들어 있다. 12시대 분석에는 영향이 없지만, 대시보드에 시간대 선택을 붙여 '080000' ~ '095959'를 고르면 한 건도 안 나온다. 시간대 조건을 넓히기 전에 데이터부터 바로잡는다(교재 03화의 문자 날짜와 같은 종류의 문제).
(SLE_DT, SHP_NO, SLE_HMS) 순서도 커버링이 되고 일량은 비슷하다. 점장 앱이 '매장 =' 조건으로 함께 쓸 수 있다면 (SHP_NO, SLE_DT, SLE_HMS)가 낫다(ACC-016). 순서는 이 테이블을 쓰는 SQL 목록을 보고 정한다.
분기가 끝나면 숫자가 바뀌지 않으므로, 지난 분기는 밤에 한 번 집계해 두는 요약 테이블로 돌리는 방법도 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 데이터(CF10) 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터(CF10) 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황