매장과 기간만 고르면 40블록 안팎인 판매 건수 타일이 카테고리를 함께 고르면 400블록 넘게 읽는다

중급DDL유통133 / 700

상황

유통 정보계의 BI 도구에는 「판매 건수」 타일이 있다. 매장, 기간, 카테고리를 마음대로 골라 판매 줄 수를 센다. 매장과 기간만 고르면 비트맵 인덱스 두 개로 40블록 안팎이면 끝나는데, 카테고리를 함께 고르면 400블록 넘게 읽는다. 하루 약 2,000번 실행되고 그중 절반이 카테고리를 고른다. 카테고리는 판매상품 사본이 아니라 상품 테이블(CF01)에 있어서, BI 도구는 상품과 조인하는 SQL을 만든다. BI 도구가 만드는 SQL은 고칠 수 없다. 아래는 매장 1003, 2023년 12월, 쉐이크(CTG_NO = 112)다.

테이블과 인덱스

DW_SLE_ITM 정보계 판매상품 사본(CF11에 판매일자와 매장을 붙인 파생 테이블): 1,096,708행 · 8,860블록(블록당 약 124행) · SLE_DT · SHP_NO · ITM_NO · SLE_NO · SLE_QTY · SLE_AMT · 매일 02:00에 CTAS로 다시 만들고 낮에는 읽기만 한다 · 날짜순으로 쌓인다
2023년 12월 46,322줄(약 374블록) · 그중 매장 1003이 9,297줄 · 그중 쉐이크 상품 4개가 344줄
CF01 정보계 상품(운영에서 밤마다 복제하고 낮에는 읽기만 한다): 109행 · 5블록 · CF01_PK (ITM_NO) · CTG_NO 말단 카테고리 14개 · 쉐이크(112)는 상품 4개
인덱스: 비트맵 BX_DSI_DT (SLE_DT) · BX_DSI_SHP (SHP_NO)(사본을 다시 만든 뒤 만든다) · ITM_NO에는 인덱스가 없다 · STAR_TRANSFORMATION_ENABLED는 기본값(FALSE) · 통계 최신

원본 SQL

sql

SELECT COUNT(*) AS line_cnt
  FROM DW_SLE_ITM f, CF01 p
 WHERE p.ITM_NO = f.ITM_NO
   AND p.CTG_NO = :v_ctg
   AND f.SHP_NO = :v_shp
   AND f.SLE_DT BETWEEN :v_from AND :v_to

실행계획 학습용 예시 · 건수와 비트맵 블록 수는 문제에 적은 값 · 사본의 비트맵은 ACC-055와 같은 방식으로 둔다

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |            |      1 |        |      1 |00:00:00.02 |     414 |
|   1 |  SORT AGGREGATE                        |            |      1 |      1 |      1 |00:00:00.02 |     414 |
|*  2 |   FILTER                               |            |      1 |        |    344 |00:00:00.02 |     414 |
|*  3 |    HASH JOIN                           |            |      1 |    660 |    344 |00:00:00.02 |     414 |
|*  4 |     TABLE ACCESS FULL                  | CF01       |      1 |      8 |      4 |00:00:00.01 |       4 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| DW_SLE_ITM |      1 |   9200 |   9297 |00:00:00.02 |     410 |
|   6 |      BITMAP CONVERSION TO ROWIDS       |            |      1 |        |   9297 |00:00:00.01 |      36 |
|   7 |       BITMAP AND                       |            |      1 |        |      1 |00:00:00.01 |      36 |
|*  8 |        BITMAP INDEX SINGLE VALUE       | BX_DSI_SHP |      1 |        |      2 |00:00:00.01 |      20 |
|   9 |        BITMAP MERGE                    |            |      1 |        |      1 |00:00:00.01 |      16 |
|* 10 |         BITMAP INDEX RANGE SCAN        | BX_DSI_DT  |      1 |        |     31 |00:00:00.01 |      16 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   3 - access("P"."ITM_NO"="F"."ITM_NO")
   4 - filter("P"."CTG_NO"=:V_CTG)
   8 - access("F"."SHP_NO"=:V_SHP)
  10 - access("F"."SLE_DT">=:V_FROM AND "F"."SLE_DT"<=:V_TO)

[매장과 기간만 고를 때]  BITMAP CONVERSION COUNT · BITMAP AND(BX_DSI_SHP, BX_DSI_DT) · Buffers 36 · 테이블에 가지 않는다

문제

카테고리를 고르면 400블록 넘게 읽는 이유를 실행계획의 줄 번호로 설명하시오. 매장과 기간처럼 카테고리도 비트맵으로 겹치지 못하는 이유도 설명하시오.
BI 도구의 SQL을 고치지 않고 카테고리 조건까지 비트맵으로 겹쳐 셀 수 있도록 인덱스를 설계해 DDL을 작성하시오. 고친 실행계획에서 무엇이 달라지는지 설명하시오.
판매상품 사본에 카테고리 컬럼을 붙이는 방법, ITM_NO 비트맵과 스타 변환을 쓰는 방법과 비교하고, 고른 인덱스를 쓰기 위한 조건과 대가를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- CF01.CTG_NO 값마다 DW_SLE_ITM 의 행을 가리키는 비트맵 조인 인덱스 (조인 결과를 인덱스에 미리 담는다)
CREATE BITMAP INDEX BJX_DSI_CTG
    ON DW_SLE_ITM (CF01.CTG_NO)
  FROM DW_SLE_ITM, CF01
 WHERE DW_SLE_ITM.ITM_NO = CF01.ITM_NO
NOLOGGING;

-- 밤 적재 순서 : 02:00 CTAS 로 사본을 만든다 → CF01 복제 → 비트맵 셋(BX_DSI_DT · BX_DSI_SHP · BJX_DSI_CTG)을 만든다 → 통계
-- BI 도구의 SQL 은 그대로
SELECT COUNT(*) AS line_cnt
  FROM DW_SLE_ITM f, CF01 p
 WHERE p.ITM_NO = f.ITM_NO
   AND p.CTG_NO = :v_ctg
   AND f.SHP_NO = :v_shp
   AND f.SLE_DT BETWEEN :v_from AND :v_to;

개선 후 실행계획 학습용 예시 · 건수와 비트맵 블록 수는 문제에 적은 값 · 사본의 비트맵은 ACC-055와 같은 방식으로 둔다

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |      1 |00:00:00.01 |      48 |
|   1 |  SORT AGGREGATE               |             |      1 |      1 |      1 |00:00:00.01 |      48 |
|*  2 |   FILTER                      |             |      1 |        |      1 |00:00:00.01 |      48 |
|   3 |    BITMAP CONVERSION COUNT    |             |      1 |    660 |      1 |00:00:00.01 |      48 |
|   4 |     BITMAP AND                |             |      1 |        |      1 |00:00:00.01 |      48 |
|*  5 |      BITMAP INDEX SINGLE VALUE| BJX_DSI_CTG |      1 |        |      2 |00:00:00.01 |      12 |
|*  6 |      BITMAP INDEX SINGLE VALUE| BX_DSI_SHP  |      1 |        |      2 |00:00:00.01 |      20 |
|   7 |      BITMAP MERGE             |             |      1 |        |      1 |00:00:00.01 |      16 |
|*  8 |       BITMAP INDEX RANGE SCAN | BX_DSI_DT   |      1 |        |     31 |00:00:00.01 |      16 |
---------------------------------------------------------------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   5 - access("P"."CTG_NO"=:V_CTG)
   6 - access("F"."SHP_NO"=:V_SHP)
   8 - access("F"."SLE_DT">=:V_FROM AND "F"."SLE_DT"<=:V_TO)

모범 답안 서술

CTG_NO는 판매상품 사본이 아니라 CF01의 컬럼이다. 비트맵 인덱스는 그 테이블의 컬럼 값마다 비트 열을 두므로, 사본의 비트맵으로는 매장(8번 줄)과 기간(10번 줄)만 겹칠 수 있다. 그래서 7번 줄 BITMAP AND가 고른 매장 1003의 12월 9,297줄을 5번 줄이 테이블에서 읽는다. 사본이 날짜순으로 쌓여 있어 그 9,297줄은 12월 치 약 374블록에 고루 퍼져 있으므로 테이블 블록을 거의 다 읽는다. 그다음 3번 줄에서 CF01의 쉐이크 상품 4개와 해시 조인해 344줄만 남긴다(합계 414). 카테고리를 고르지 않으면 테이블에 가지 않고 비트맵 36블록으로 세던 SQL이, 카테고리 하나 때문에 테이블을 읽고 조인까지 하게 된 것이다. 비트맵 조인 인덱스는 이 조인을 미리 해 두는 인덱스다. CF01.CTG_NO 값마다 '그 카테고리 상품의 판매상품 행'을 가리키는 비트 열을 DW_SLE_ITM 쪽에 만든다. 그러면 BI 도구가 보낸 SQL을 그대로 두어도 옵티마이저가 카테고리 비트맵(5번 줄)을 매장, 기간의 비트맵과 BITMAP AND로 겹치고 BITMAP CONVERSION COUNT로 센다. 상품 테이블과 조인하지 않아 CF01 줄이 계획에서 사라지고, 사본 테이블도 읽지 않는다. Buffers는 414 → 48이고, 카테고리를 고르는 하루 약 1,000번으로 치면 약 41만 → 5만 블록이다. 다른 방법과 비교한다. 첫째, 밤 적재 때 사본에 CTG_NO 컬럼을 붙이고 보통 비트맵을 두는 방법은 읽는 양이 같다. 하지만 BI 도구의 SQL은 CF01의 CTG_NO로 거르므로 사본에 붙인 컬럼을 쓰지 않는다. SQL을 고칠 수 없는 이 화면에는 맞지 않는다. 둘째, ITM_NO에 비트맵을 두고 스타 변환을 켜면 CF01에서 쉐이크 상품 키 4개를 고른 뒤 그 키마다 ITM_NO 비트맵을 읽어 합친다(BITMAP KEY ITERATION과 MERGE). 결과는 같지만 상품이 많은 카테고리일수록 키마다 비트맵을 읽는 횟수가 늘고, 세션이나 시스템 파라미터를 바꿔야 한다. 차원 하나의 속성으로 거르는 이 타일에는 비트맵 조인 인덱스가 단순하다. 조건도 있다. 조인 컬럼인 CF01.ITM_NO가 PK(또는 유니크)여야 하고, 인덱스의 컬럼은 차원 테이블의 컬럼이어야 한다. 대가는 밤 적재가 길어지는 것이다. 사본을 다 만든 뒤 CF01과 조인해 인덱스를 만들어야 하고, 상품의 카테고리가 바뀌면 그 상품의 판매상품 행 전체가 다른 비트 열로 옮겨 간다. 그래서 밤에 한 번 만들고 낮에는 읽기만 하는 이 사본에 두고, 운영 CF11처럼 판매가 계속 들어오는 테이블에는 두지 않는다.

해설

함정: 비트맵 조인 인덱스를 만들었다고 SQL에서 조인을 지우면 안 된다. 옵티마이저는 SQL에 같은 조인과 같은 차원 조건이 있을 때 이 인덱스를 쓴다. BI 도구의 SQL을 그대로 두는 것이 맞다.
이 인덱스는 건수를 셀 때 가장 크게 이긴다. 수량 합계(SUM(SLE_QTY))처럼 사본의 값이 필요하면 겹쳐서 남은 344줄은 테이블에서 읽어야 한다. 그래도 9,297줄을 읽고 조인하는 것보다는 적게 읽는다.
스타 변환은 차원이 여럿이고 조합이 정해지지 않은 리포트에서 힘을 낸다(JOIN-067, JOIN-070). 차원 하나의 속성 하나로 자주 거르는 화면이라면 비트맵 조인 인덱스가 계획이 단순하고 실행할 때 할 일이 적다.
비트맵 조인 인덱스도 비트맵 인덱스라서, 행 단위 DML이 잦은 테이블에 두면 비트맵 항목을 잠그는 문제가 그대로 생긴다(LOCK-023). 밤에 다시 만드는 사본에만 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수와 비트맵 블록 수는 문제에 적은 값 · 사본의 비트맵은 ACC-055와 같은 방식으로 둔다 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수와 비트맵 블록 수는 문제에 적은 값 · 사본의 비트맵은 ACC-055와 같은 방식으로 둔다 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황