POS 테이블의 비트맵을 지우고 따로 만든 리포트 마트에서도 자유 조합 리포트는 마트를 통째로 읽는다

실전DDL버거132 / 700

상황

POS 테이블에 비트맵 인덱스를 만들었다가 점심 결제가 멈춘 일 뒤로, 버거 본사 BI팀은 매일 밤 리포트용 마트 DW_ORD_ITEM을 따로 만들어 리포트를 그쪽으로 옮겼다. BI팀은 요청이 올 때마다 마트에 B-tree 인덱스를 하나씩 만들어 지금 셋이다. 그런데 BI 도구의 자유 조합 리포트(S1)는 여전히 마트를 통째로 읽고, 점장 리포트(S2)도 700블록 넘게 읽는다. 박팀장은 "마트의 인덱스를 처음부터 다시 설계하고 밤 적재 절차까지 적어 와라"고 했다.

테이블과 인덱스

DW_ORD_ITEM 주문상품 리포트 마트: 매일 02:00에 TRUNCATE 뒤 ORD_ITEM · ORD · SHOP · ITEM을 조인해 INSERT /*+ APPEND */로 다시 채운다 · 낮에는 읽기만 한다 · 2,472,717행 · 15,200블록 · ORD_DT 순으로 쌓인다
컬럼과 분포: ORD_NO(약 100만 가지) · ORD_DT VARCHAR2(8) 730일 · ORD_HH 14가지(09 ~ 22시, 고르게) · DOW_CD 7가지 · SHOP_NO 200곳 · RGN_CD 8곳(매장이 고르게 나뉜다) · ITEM_ID 92가지 · ITEM_TYPE_CD 여러 분류(버거 '100100'이 18종, 약 20%) · ORD_ITEM_QTY
지금 인덱스(B-tree): DW_OI_X01 (ORD_DT, SHOP_NO) · DW_OI_X02 (ITEM_ID, ORD_DT) · DW_OI_X03 (RGN_CD, ITEM_TYPE_CD) · 리프는 각각 약 9,200 · 9,200 · 7,100블록
실행 횟수: S1 하루 약 400번(조합은 매번 다르다) · S2 하루 약 2,000번 · S3 하루 수십 번 · 밤 적재 1번(지금 02:00 ~ 03:10, 그중 인덱스 셋을 고치는 데 약 25분)

원본 SQL

sql

-- S1 자유 조합 리포트 (BI 도구가 고른 조건만 WHERE에 붙인다 · 아래는 한 예)
SELECT ITEM_ID, SUM(ORD_ITEM_QTY) AS qty, COUNT(*) AS line_cnt
  FROM DW_ORD_ITEM
 WHERE RGN_CD       = :v_rgn
   AND ITEM_TYPE_CD = '100100'
   AND DOW_CD       IN ('1', '7')
   AND ORD_HH       BETWEEN '11' AND '13'
 GROUP BY ITEM_ID;

-- S2 점장 리포트 : 매장 하나의 한 달 일별 메뉴 판매
SELECT ORD_DT, ITEM_ID, SUM(ORD_ITEM_QTY) AS qty
  FROM DW_ORD_ITEM
 WHERE SHOP_NO = :v_shop
   AND ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY ORD_DT, ITEM_ID;

-- S3 고객센터 : 주문 한 건의 상품
SELECT ITEM_ID, ORD_ITEM_QTY FROM DW_ORD_ITEM WHERE ORD_NO = :v_ord_no;

-- 밤 적재 (02:00)
TRUNCATE TABLE DW_ORD_ITEM;
INSERT /*+ APPEND */ INTO DW_ORD_ITEM
SELECT ... ;
COMMIT;

실행계획 학습용 예시 · 마트는 ORD_ITEM에서 만든 파생 테이블 · 조합 건수는 컬럼이 고르게 분포한다고 보고 센 값 · 비트맵 블록 수는 어림값

xplan

[S1 · 한 지역 · 버거 · 주말 · 11 ~ 13시]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |     18 |00:00:00.21 |   15200 |
|   1 |  HASH GROUP BY                       |             |      1 |     18 |     18 |00:00:00.21 |   15200 |
|*  2 |   TABLE ACCESS FULL                  | DW_ORD_ITEM |      1 |   3705 |   3705 |00:00:00.20 |   15200 |
-------------------------------------------------------------------------------------------------------------
   2 - filter(("RGN_CD"=:V_RGN AND "ITEM_TYPE_CD"='100100' AND INTERNAL_FUNCTION("DOW_CD")
              AND "ORD_HH">='11' AND "ORD_HH"<='13'))

[S2 · SH0042 · 2013년 11월]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |    488 |00:00:00.02 |     737 |
|   1 |  HASH GROUP BY                       |             |      1 |    515 |    488 |00:00:00.02 |     737 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DW_ORD_ITEM |      1 |    515 |    515 |00:00:00.02 |     737 |
|*  3 |    INDEX RANGE SCAN                  | DW_OI_X01   |      1 |    515 |    515 |00:00:00.01 |     385 |
-------------------------------------------------------------------------------------------------------------
   3 - access("ORD_DT">=:V_FROM AND "SHOP_NO"=:V_SHOP AND "ORD_DT"<=:V_TO)
       filter("SHOP_NO"=:V_SHOP)

문제

S1이 지금 인덱스 셋을 제대로 쓰지 못하고 FULL로 읽는 이유와, S2가 737블록을 읽는 이유를 설명하시오.
S1, S2, S3와 밤 적재를 함께 보고 마트의 인덱스를 다시 설계하시오. 컬럼마다 비트맵으로 할지 B-tree로 할지, 지금 인덱스를 어떻게 할지 정하고 DDL과 밤 적재 절차를 작성하시오.
고친 설계에서 S1과 S2의 읽는 양을 어림하고, 이 비트맵 설계를 POS가 쓰는 운영 테이블에 그대로 옮기면 안 되는 이유를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① S1 (자유 조합) : 값의 종류가 적은 컬럼마다 비트맵
DROP INDEX DW_OI_X03;
CREATE BITMAP INDEX DW_OI_BX_RGN  ON DW_ORD_ITEM (RGN_CD);
CREATE BITMAP INDEX DW_OI_BX_TYPE ON DW_ORD_ITEM (ITEM_TYPE_CD);
CREATE BITMAP INDEX DW_OI_BX_DOW  ON DW_ORD_ITEM (DOW_CD);
CREATE BITMAP INDEX DW_OI_BX_HH   ON DW_ORD_ITEM (ORD_HH);
CREATE BITMAP INDEX DW_OI_BX_ITEM ON DW_ORD_ITEM (ITEM_ID);
CREATE BITMAP INDEX DW_OI_BX_DT   ON DW_ORD_ITEM (ORD_DT);

-- ② S2 (모양이 늘 같고 하루 2,000번) : = 조건 매장을 앞에, 범위 날짜를 뒤에, 쓰는 컬럼까지 담은 B-tree
DROP INDEX DW_OI_X01;
CREATE INDEX DW_OI_X04 ON DW_ORD_ITEM (SHOP_NO, ORD_DT, ITEM_ID, ORD_ITEM_QTY);

-- ③ X02 (ITEM_ID, ORD_DT) 를 쓰던 상품 추이는 비트맵 ITEM_ID 와 ORD_DT 를 겹쳐 받는다
DROP INDEX DW_OI_X02;

-- ④ S3 는 마트가 아니라 운영 ORD_ITEM 의 PK 로 보낸다 (마트의 ORD_NO 에는 인덱스를 두지 않는다)
SELECT ITEM_ID, ORD_ITEM_QTY FROM ORD_ITEM WHERE ORD_NO = :v_ord_no;

-- ⑤ 밤 적재 : 인덱스를 쉬게 하고 넣은 뒤 한 번에 다시 만든다 (비트맵 여섯 개와 X04 모두)
TRUNCATE TABLE DW_ORD_ITEM;
ALTER INDEX DW_OI_BX_RGN UNUSABLE;
ALTER INDEX DW_OI_X04    UNUSABLE;
INSERT /*+ APPEND */ INTO DW_ORD_ITEM SELECT ... ;
COMMIT;
ALTER INDEX DW_OI_BX_RGN REBUILD NOLOGGING;
ALTER INDEX DW_OI_X04    REBUILD NOLOGGING;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DW_ORD_ITEM');

개선 후 실행계획 학습용 예시 · 마트는 ORD_ITEM에서 만든 파생 테이블 · 조합 건수는 컬럼이 고르게 분포한다고 보고 센 값 · 비트맵 블록 수는 어림값

xplan

[S1 · 고친 뒤]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |      1 |        |     18 |00:00:00.05 |    3540 |
|   1 |  HASH GROUP BY                        |               |      1 |     18 |     18 |00:00:00.05 |    3540 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | DW_ORD_ITEM   |      1 |   3705 |   3705 |00:00:00.05 |    3540 |
|   3 |    BITMAP CONVERSION TO ROWIDS        |               |      1 |        |   3705 |00:00:00.01 |     260 |
|   4 |     BITMAP AND                        |               |      1 |        |      3 |00:00:00.01 |     260 |
|*  5 |      BITMAP INDEX SINGLE VALUE        | DW_OI_BX_RGN  |      1 |        |      6 |00:00:00.01 |      45 |
|*  6 |      BITMAP INDEX SINGLE VALUE        | DW_OI_BX_TYPE |      1 |        |      8 |00:00:00.01 |      60 |
|   7 |      BITMAP OR                        |               |      1 |        |      4 |00:00:00.01 |      85 |
|*  8 |       BITMAP INDEX SINGLE VALUE       | DW_OI_BX_DOW  |      1 |        |      5 |00:00:00.01 |      42 |
|*  9 |       BITMAP INDEX SINGLE VALUE       | DW_OI_BX_DOW  |      1 |        |      5 |00:00:00.01 |      43 |
|  10 |      BITMAP MERGE                     |               |      1 |        |      3 |00:00:00.01 |      70 |
|* 11 |       BITMAP INDEX RANGE SCAN         | DW_OI_BX_HH   |      1 |        |     12 |00:00:00.01 |      70 |
---------------------------------------------------------------------------------------------------------------
   5 - access("RGN_CD"=:V_RGN)
   6 - access("ITEM_TYPE_CD"='100100')
   8 - access("DOW_CD"='1')
   9 - access("DOW_CD"='7')
  11 - access("ORD_HH">='11' AND "ORD_HH"<='13')

[S2 · 고친 뒤]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |      1 |        |    488 |00:00:00.01 |       6 |
|   1 |  SORT GROUP BY NOSORT                 |               |      1 |    515 |    488 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN                    | DW_OI_X04     |      1 |    515 |    515 |00:00:00.01 |       6 |
---------------------------------------------------------------------------------------------------------------
   2 - access("SHOP_NO"=:V_SHOP AND "ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

모범 답안 서술

S1의 조건은 지역, 상품군, 요일, 시간대 넷인데 X03 (RGN_CD, ITEM_TYPE_CD)이 받는 것은 앞의 둘뿐이다. 그 둘로 찾으면 2,472,717 × 1/8 × 20%, 약 61,800건이 테이블로 넘어가고, 마트가 날짜순으로 쌓여 있어 그 행들은 거의 모든 블록에 흩어져 있다. 그래서 옵티마이저는 FULL(15,200블록)을 고르고 요일과 시간대는 테이블에서 거른다. 조합이 매번 달라서 결합 B-tree를 하나 더 만들어도 다음 조합에는 맞지 않는다. S2는 X01 (ORD_DT, SHOP_NO)을 타지만 선두가 범위 조건인 ORD_DT라서 한 달 치 리프 약 383블록을 다 읽고, 뒤의 SHOP_NO는 filter로 거른다. 남은 515건마다 테이블을 찾아가 약 352블록을 더 읽어 737블록이다. 설계는 SQL의 성격에 따라 나눈다. S1처럼 조합이 정해지지 않은 자유 조합 리포트는 비트맵으로 받는다. 값의 종류가 적은 컬럼(지역 8, 상품군, 요일 7, 시간대 14, 상품 92, 날짜 730)마다 비트맵 인덱스를 하나씩 두고 BITMAP AND, OR, MERGE로 겹친다. S1의 예시 조합은 비트맵 260블록으로 3,705건을 골라 테이블 약 3,280블록을 읽는다(15,200 → 약 3,540). 건수만 세는 조합이면 테이블에 가지 않아 260블록 안팎이다. S2처럼 모양이 늘 같고 하루 2,000번 도는 SQL은 B-tree가 맞다. = 조건인 SHOP_NO를 앞에, 범위인 ORD_DT를 뒤에 두고 ITEM_ID와 ORD_ITEM_QTY까지 담은 (SHOP_NO, ORD_DT, ITEM_ID, ORD_ITEM_QTY)는 한 매장의 한 달 515건을 리프 3블록 남짓에서 읽고 테이블에 가지 않는다. 정렬된 순서 그대로 묶으므로 GROUP BY도 정렬 없이 끝난다(737 → 6). 하루 약 147만 → 1만 2천 블록이다. X01은 이 인덱스가 대신하므로 지우고, X02와 X03은 비트맵이 대신한다. S3의 ORD_NO는 종류가 100만 가지라 비트맵이 작아지지 않는다. 마트에 인덱스를 두지 않고 운영 ORD_ITEM의 PK로 보낸다. 밤 적재는 TRUNCATE 뒤 인덱스를 UNUSABLE로 두고 APPEND로 넣은 다음 REBUILD로 한 번에 다시 만든다. 인덱스를 둔 채 넣으면 넣는 동안 인덱스를 함께 고치느라 적재가 길어지고 비트맵도 덜 압축된다. 비트맵 인덱스의 항목 하나는 수많은 행의 비트를 덮어서, 한 행을 넣거나 고치면 그 항목 전체를 잠근다. 이 설계를 POS가 쓰는 ORD나 ORD_ITEM에 옮기면 계산대마다 INSERT가 서로를 기다린다. 비트맵은 밤에 한 번 채우고 낮에는 읽기만 하는 이 마트에만 둔다.

해설

함정: '분석 마트니까 비트맵'이라며 모든 인덱스를 비트맵으로 바꾸면 S2는 매장 비트맵과 날짜 범위 비트맵을 겹친 뒤 테이블을 350블록 가까이 읽는다. 모양이 고정되고 자주 도는 SQL에는 커버링 B-tree가 더 싸다.
비트맵은 값의 종류가 적을수록 작지만, 날짜(730가지)나 상품(92가지)처럼 중간 정도여도 날짜순으로 쌓인 마트에서는 같은 값이 몰려 있어 잘 압축된다. 종류가 행 수에 가까운 ORD_NO는 B-tree보다 커질 수 있다.
S1의 결과가 매일 같은 몇 가지 조합에 몰린다면 그 조합을 미리 집계한 요약 테이블이나 구체화 뷰가 더 낫다(SORT 단원).
적재 시간은 인덱스 수와 함께 는다. UNUSABLE로 두고 넣은 뒤 REBUILD하면 넣는 동안의 인덱스 유지 비용이 없어지고, REBUILD는 NOLOGGING과 병렬로 돌릴 수 있다(교재 14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 마트는 ORD_ITEM에서 만든 파생 테이블 · 조합 건수는 컬럼이 고르게 분포한다고 보고 센 값 · 비트맵 블록 수는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 마트는 ORD_ITEM에서 만든 파생 테이블 · 조합 건수는 컬럼이 고르게 분포한다고 보고 센 값 · 비트맵 블록 수는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황