매장과 요일, 시간대를 골라 판매 건수 하나를 세는데 분석 사본을 통째로 읽는다

기초SELECT유통130 / 700

상황

본사 영업기획팀은 BI 도구의 「판매 건수 조회」에서 매장, 요일, 시간대, 월을 마음대로 골라 판매 건수를 센다. 고르는 조합이 매번 달라서 하루 약 3,000번, 서로 다른 모양의 COUNT가 들어온다. 운영 판매 테이블을 건드리지 않으려고 매일 새벽 분석 사본 CF10_DW를 다시 만들어 쓰는데, 건수 하나에 사본 전체를 읽는다. 조민우는 "자주 쓰는 조합마다 결합 인덱스를 만들자"고 한다. 아래는 매장 1003, 토요일, 12시대다.

테이블과 인덱스

CF10_DW 판매 분석 사본: 매일 04:00에 CF10에서 CTAS로 다시 만든다 · 낮에는 읽기만 한다 · 500,000행 · 5,040블록 · CF10 컬럼에 시간대 SLE_HH(SUBSTR(SLE_HMS, 1, 2))와 월 SLE_YM을 더했다
분포: SHP_NO 다섯 곳에 약 10만 건씩 · SLE_DOW 일곱 값에 약 14%씩 · SLE_HH 14가지이고 12시대가 약 52% · SLE_YM 24가지
매장 1003의 토요일 12시대: 7,488건
인덱스는 없다 · 통계 최신

원본 SQL

sql

SELECT COUNT(*)
  FROM CF10_DW
 WHERE SHP_NO  = :v_shp
   AND SLE_DOW = :v_dow
   AND SLE_HH  = :v_hh

실행계획 학습용 예시 · 시간대 분포는 문제에 적은 값 · 비트맵 블록 수는 어림값

xplan

------------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |      1 |00:00:00.03 |    5040 |
|   1 |  SORT AGGREGATE     |         |      1 |      1 |      1 |00:00:00.03 |    5040 |
|*  2 |   TABLE ACCESS FULL | CF10_DW |      1 |   1020 |   7488 |00:00:00.03 |    5040 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("SLE_HH"=:V_HH AND "SLE_DOW"=:V_DOW AND "SHP_NO"=:V_SHP))

문제

조합마다 결합 B-tree 인덱스를 만드는 방법이 이 화면에 맞지 않는 이유를 설명하시오.
어떤 조합이 와도 테이블을 읽지 않고 건수를 셀 수 있도록 인덱스를 설계해 DDL을 작성하시오. 운영 CF10에 같은 인덱스를 만들면 안 되는 이유도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 값의 종류가 적은 컬럼마다 비트맵 인덱스 하나씩 : 어떤 조합이 와도 비트맵끼리 AND 해서 센다
CREATE BITMAP INDEX BX_CF10DW_SHP ON CF10_DW (SHP_NO);
CREATE BITMAP INDEX BX_CF10DW_DOW ON CF10_DW (SLE_DOW);
CREATE BITMAP INDEX BX_CF10DW_HH  ON CF10_DW (SLE_HH);
CREATE BITMAP INDEX BX_CF10DW_YM  ON CF10_DW (SLE_YM);
-- 새벽 CTAS 가 끝난 뒤에 만든다 (사본을 다시 만들 때마다 같이)

SELECT COUNT(*)            -- SQL 은 그대로
  FROM CF10_DW
 WHERE SHP_NO  = :v_shp
   AND SLE_DOW = :v_dow
   AND SLE_HH  = :v_hh;

개선 후 실행계획 학습용 예시 · 시간대 분포는 문제에 적은 값 · 비트맵 블록 수는 어림값

xplan

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |      1 |00:00:00.01 |      58 |
|   1 |  SORT AGGREGATE              |               |      1 |      1 |      1 |00:00:00.01 |      58 |
|   2 |   BITMAP CONVERSION COUNT    |               |      1 |   1020 |      1 |00:00:00.01 |      58 |
|   3 |    BITMAP AND                |               |      1 |        |      1 |00:00:00.01 |      58 |
|*  4 |     BITMAP INDEX SINGLE VALUE| BX_CF10DW_HH  |      1 |        |      3 |00:00:00.01 |      31 |
|*  5 |     BITMAP INDEX SINGLE VALUE| BX_CF10DW_DOW |      1 |        |      1 |00:00:00.01 |      12 |
|*  6 |     BITMAP INDEX SINGLE VALUE| BX_CF10DW_SHP |      1 |        |      2 |00:00:00.01 |      15 |
--------------------------------------------------------------------------------------------------------
   4 - access("SLE_HH"=:V_HH)
   5 - access("SLE_DOW"=:V_DOW)
   6 - access("SHP_NO"=:V_SHP)

모범 답안 서술

매장, 요일, 시간대, 월 넷 가운데 무엇을 고르느냐에 따라 조건의 조합은 15가지다. 결합 B-tree 인덱스는 선두 컬럼에 조건이 있어야 범위를 좁힐 수 있으므로, 모든 조합을 받으려면 순서를 바꾼 인덱스를 여러 개 만들어야 한다. 사본을 다시 만들 때마다 그 인덱스를 전부 다시 만들어야 하고, 화면에 고를 항목이 하나 늘면 또 늘어난다. 비트맵 인덱스는 컬럼 값마다 '이 값인 행은 1, 아니면 0'인 비트 열을 둔다. 매장 다섯 곳, 요일 일곱 가지, 시간대 14가지처럼 값의 종류가 적은 컬럼은 비트 열이 몇 개뿐이고 압축되어 작다. 컬럼마다 비트맵 인덱스를 하나씩 두면 어떤 조합이 와도 고른 값의 비트맵을 BITMAP AND로 겹치고, BITMAP CONVERSION COUNT로 1의 개수만 센다. 테이블은 읽지 않는다. 매장 1003, 토요일, 12시대는 비트맵 세 개 58블록으로 7,488건을 센다(5,040 → 58). 하루 3,000번이면 약 1,512만 → 17만 블록이다. 이 사본은 새벽에 CTAS로 다시 만들고 낮에는 읽기만 하므로 비트맵 인덱스를 쓰기에 알맞다. 인덱스는 CTAS가 끝난 뒤에 만든다. 운영 CF10에는 같은 인덱스를 만들면 안 된다. 비트맵 인덱스의 항목 하나는 수많은 행의 비트를 덮어서, POS가 판매 한 건을 INSERT하면 그 항목 전체를 잠근다. 다른 매장 계산대의 INSERT가 줄을 서고 데드락도 생긴다(LOCK 단원의 비트맵 사고).

해설

함정: 비트맵 인덱스 하나만으로는 이득이 작다. 12시대(52%)처럼 흔한 값 하나로 행을 찾아 테이블을 읽는다면 B-tree와 다르지 않다. 여러 비트맵을 겹쳐 남는 행이 적어지거나, 이번처럼 건수만 세서 테이블을 읽지 않을 때 효과가 크다.
화면이 고르는 조합이 늘 같다면 그 조합의 결합 B-tree가 더 낫다. 매장과 월이 고정된 화면이라면 (SHP_NO, SLE_YM) 인덱스 몇 블록으로 끝난다. 비트맵은 조합이 정해지지 않은 임의 질의(ad hoc)를 위한 것이다.
15가지 조합의 건수를 한 표에 다 띄우는 화면이라면 COUNT를 여러 번 보내는 대신 GROUP BY 한 번으로 사본을 한 번만 읽는 편이 낫다.
비트맵 인덱스는 NULL도 담는다. B-tree와 달리 '값이 비어 있는 행' 같은 IS NULL 조건도 비트맵으로 찾는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 시간대 분포는 문제에 적은 값 · 비트맵 블록 수는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 시간대 분포는 문제에 적은 값 · 비트맵 블록 수는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황