JOIN-067

「주말 · 지역 · 메뉴 분류 판매」 리포트가 고른 조건과 상관없이 늘 6만 블록을 읽는다

중급SELECT버거217 / 700

상황

본사 BI팀은 작년에 정보계 구축 업체가 만든 판매 마트에서 리포트를 뽑는다. 「주말 · 지역 · 메뉴 분류 판매」는 연도, 지역, 주말 여부, 메뉴 분류를 골라 매장과 메뉴별 판매를 보는 리포트로, 지역 영업팀이 하루 150번쯤 돌린다. 구축 업체는 팩트 테이블의 조인 컬럼마다 비트맵 인덱스를 만들어 두고 "조건을 좁히면 인덱스로 빨라진다"고 인수인계했다. 그런데 조건을 아무리 좁혀도 팩트 테이블을 통째로 읽는다. 아래는 2013년 주말의 부산 지역 음료 판매를 조회한 실행계획이다.

테이블과 인덱스

DW_ORD_ITEM 판매 팩트: 2,472,717행 · 60,940블록 · ORD_DT · SHOP_NO · ITEM_ID · ORD_ITEM_QTY · SLE_AMT · 매일 밤 전날 판매를 날짜순으로 덧붙인다 · 2013년 주말 383,165줄, 그중 부산 매장 22,990줄, 그중 음료 5,058줄
비트맵 인덱스 BX_DOI_DT (ORD_DT) · BX_DOI_SHOP (SHOP_NO) · BX_DOI_ITEM (ITEM_ID)(각각 약 900 · 300 · 450블록) · 다른 인덱스 없음
DIM_DAY 날짜: 731행 · 5블록 · DT · YYYY · WKND_YN / DIM_SHOP 매장: 200행 · 5블록 · SHOP_NO · SHOP_NM · AREA_NM(부산 12곳) / ITEM 상품: 92행 · 5블록 · ITEM_TYPE_CD(음료 '100105' 14종)
차원 조건 하나씩만 보면 2013년 주말은 팩트의 15.5%, 부산은 6%, 음료는 22%다 · STAR_TRANSFORMATION_ENABLED = FALSE(기본값) · 통계 최신

원본 SQL

sql

SELECT s.SHOP_NM, t.ITEM_NM, SUM(f.ORD_ITEM_QTY) AS qty, SUM(f.SLE_AMT) AS amt
  FROM DW_ORD_ITEM f, DIM_DAY d, DIM_SHOP s, ITEM t
 WHERE f.ORD_DT  = d.DT
   AND f.SHOP_NO = s.SHOP_NO
   AND f.ITEM_ID = t.ITEM_ID
   AND d.YYYY    = :v_yyyy
   AND d.WKND_YN = 'Y'
   AND s.AREA_NM = :v_area
   AND t.ITEM_TYPE_CD = :v_type
 GROUP BY s.SHOP_NM, t.ITEM_NM

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |      1 |        |    168 |00:00:02.41 |   60955 |           |
|   1 |  HASH GROUP BY          |             |      1 |    168 |    168 |00:00:02.41 |   60955 | 1186K (0) |
|*  2 |   HASH JOIN             |             |      1 |   3212 |   5058 |00:00:02.40 |   60955 | 1290K (0) |
|*  3 |    TABLE ACCESS FULL    | DIM_SHOP    |      1 |     12 |     12 |00:00:00.01 |       5 |           |
|*  4 |    HASH JOIN            |             |      1 |  53535 |  84296 |00:00:02.36 |   60950 | 1236K (0) |
|*  5 |     TABLE ACCESS FULL   | ITEM        |      1 |     14 |     14 |00:00:00.01 |       5 |           |
|*  6 |     HASH JOIN           |             |      1 |   352K |   383K |00:00:02.12 |   60945 | 1347K (0) |
|*  7 |      TABLE ACCESS FULL  | DIM_DAY     |      1 |    104 |    104 |00:00:00.01 |       5 |           |
|   8 |      TABLE ACCESS FULL  | DW_ORD_ITEM |      1 |  2472K |  2472K |00:00:00.31 |   60940 |           |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("F"."SHOP_NO"="S"."SHOP_NO")
   3 - filter("S"."AREA_NM"=:V_AREA)
   4 - access("F"."ITEM_ID"="T"."ITEM_ID")
   5 - filter("T"."ITEM_TYPE_CD"=:V_TYPE)
   6 - access("F"."ORD_DT"="D"."DT")
   7 - filter(("D"."YYYY"=:V_YYYY AND "D"."WKND_YN"='Y'))

문제

비트맵 인덱스가 셋이나 있는데 옵티마이저가 팩트 테이블을 FULL로 읽은 이유를 차원 조건 하나하나의 선택도로 설명하시오. 세 조건을 모두 적용하면 팩트에서 몇 행이 남는지도 쓰시오.
세 비트맵 인덱스를 함께 쓰는 계획이 나오도록 고치고, 그 계획이 어떻게 동작하는지 서술하시오. 이 방법이 맞지 않는 경우와 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- BI 서비스로 접속한 세션에서만 스타 변환을 허용한다 (로그온 트리거나 서비스별 설정으로)
-- 비트맵 인덱스는 이미 있으므로 변환을 쓸지는 옵티마이저가 비용으로 정하게 둔다
ALTER SESSION SET STAR_TRANSFORMATION_ENABLED = TRUE;

SELECT s.SHOP_NM, t.ITEM_NM, SUM(f.ORD_ITEM_QTY) AS qty, SUM(f.SLE_AMT) AS amt
  FROM DW_ORD_ITEM f, DIM_DAY d, DIM_SHOP s, ITEM t
 WHERE f.ORD_DT  = d.DT
   AND f.SHOP_NO = s.SHOP_NO
   AND f.ITEM_ID = t.ITEM_ID
   AND d.YYYY    = :v_yyyy
   AND d.WKND_YN = 'Y'
   AND s.AREA_NM = :v_area
   AND t.ITEM_TYPE_CD = :v_type
 GROUP BY s.SHOP_NM, t.ITEM_NM;

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

xplan

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |             |      1 |        |    168 |00:00:00.16 |    4631 |           |
|   1 |  HASH GROUP BY                           |             |      1 |    168 |    168 |00:00:00.16 |    4631 | 1186K (0) |
|*  2 |   HASH JOIN                              |             |      1 |   3212 |   5058 |00:00:00.15 |    4631 | 1290K (0) |
|*  3 |    TABLE ACCESS FULL                     | DIM_SHOP    |      1 |     12 |     12 |00:00:00.01 |       5 |           |
|*  4 |    HASH JOIN                             |             |      1 |   3212 |   5058 |00:00:00.15 |    4626 | 1236K (0) |
|*  5 |     TABLE ACCESS FULL                    | ITEM        |      1 |     14 |     14 |00:00:00.01 |       5 |           |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED  | DW_ORD_ITEM |      1 |   3212 |   5058 |00:00:00.14 |    4621 |           |
|   7 |      BITMAP CONVERSION TO ROWIDS         |             |      1 |        |   5058 |00:00:00.05 |     902 |           |
|   8 |       BITMAP AND                         |             |      1 |        |     37 |00:00:00.05 |     902 |           |
|   9 |        BITMAP MERGE                      |             |      1 |        |     12 |00:00:00.01 |     214 | 1024K (0) |
|  10 |         BITMAP KEY ITERATION             |             |      1 |        |    104 |00:00:00.01 |     214 |           |
|* 11 |          TABLE ACCESS FULL               | DIM_DAY     |      1 |    104 |    104 |00:00:00.01 |       5 |           |
|* 12 |          BITMAP INDEX RANGE SCAN         | BX_DOI_DT   |    104 |        |    104 |00:00:00.01 |     209 |           |
|  13 |        BITMAP MERGE                      |             |      1 |        |     25 |00:00:00.02 |     266 | 1024K (0) |
|  14 |         BITMAP KEY ITERATION             |             |      1 |        |     12 |00:00:00.02 |     266 |           |
|* 15 |          TABLE ACCESS FULL               | DIM_SHOP    |      1 |     12 |     12 |00:00:00.01 |       5 |           |
|* 16 |          BITMAP INDEX RANGE SCAN         | BX_DOI_SHOP |     12 |        |     12 |00:00:00.02 |     261 |           |
|  17 |        BITMAP MERGE                      |             |      1 |        |     31 |00:00:00.02 |     422 | 1024K (0) |
|  18 |         BITMAP KEY ITERATION             |             |      1 |        |     14 |00:00:00.02 |     422 |           |
|* 19 |          TABLE ACCESS FULL               | ITEM        |      1 |     14 |     14 |00:00:00.01 |       5 |           |
|* 20 |          BITMAP INDEX RANGE SCAN         | BX_DOI_ITEM |     14 |        |     14 |00:00:00.02 |     417 |           |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("F"."SHOP_NO"="S"."SHOP_NO")
   3 - filter("S"."AREA_NM"=:V_AREA)
   4 - access("F"."ITEM_ID"="T"."ITEM_ID")
   5 - filter("T"."ITEM_TYPE_CD"=:V_TYPE)
  11 - filter(("D"."YYYY"=:V_YYYY AND "D"."WKND_YN"='Y'))
  12 - access("F"."ORD_DT"="D"."DT")
  15 - filter("S"."AREA_NM"=:V_AREA)
  16 - access("F"."SHOP_NO"="S"."SHOP_NO")
  19 - filter("T"."ITEM_TYPE_CD"=:V_TYPE)
  20 - access("F"."ITEM_ID"="T"."ITEM_ID")

모범 답안 서술

차원 조건은 모두 차원 테이블에 걸려 있다. 팩트 쪽에서 보면 2013년 주말은 15.5%(38만 행), 부산은 6%(약 15만 행), 음료는 22%(약 54만 행)다. 차원 하나만으로 팩트를 인덱스로 찾아가면 수십만 행을 ROWID로 읽어야 하므로, 옵티마이저는 팩트를 FULL로 한 번 읽고(8번 줄, 60,940블록) 세 차원을 해시 조인으로 거르는 계획을 골랐다. 차원을 하나씩 보면 맞는 판단이다. 하지만 셋을 모두 적용하면 15.5% × 6% × 22% ≈ 0.2%, 5,058행만 남는다. 6번 줄에서 38만 행, 4번 줄에서 8만 4천 행을 만들었다가 버린 셈이다. 세 조건을 겹쳐서 팩트를 찾는 방법이 스타 변환이다. 옵티마이저가 차원마다 '팩트의 조인 컬럼 IN (차원에서 고른 키)' 모양의 조건을 만들고, 그 키로 팩트의 비트맵 인덱스를 읽는다(BITMAP KEY ITERATION). 날짜 104개, 매장 12곳, 메뉴 14개의 비트맵을 각각 합친 뒤(BITMAP MERGE) 세 비트맵을 AND로 겹치면(BITMAP AND) 5,058행의 ROWID만 남는다. 그 행만 팩트에서 읽고 매장명과 메뉴명을 붙인다. 비트맵 인덱스는 이미 있으므로 STAR_TRANSFORMATION_ENABLED를 TRUE로 바꾸면 된다. Buffers는 60,955 → 4,631, 시간은 2.41 → 0.16초다. 맞지 않는 경우도 있다. 조건이 넓어 겹친 결과가 팩트의 몇 %를 넘으면 흩어진 블록을 ROWID로 하나씩 읽는 비용이 FULL보다 커진다. 옵티마이저는 비용으로 판단해 그런 리포트에서는 변환을 쓰지 않으므로, 파라미터는 켜 두되 힌트로 강제하지는 않는다. 또 차원 테이블을 두 번 읽고(키를 고를 때와 이름을 붙일 때, 3번과 15번 줄), 비트맵 인덱스는 DML에 약해서 낮 동안 여러 세션이 팩트에 행을 넣거나 고치는 시스템에는 쓰지 않는다. 이 마트는 밤에 한 번 적재하고 낮에는 읽기만 하므로 맞는다.

해설

파라미터를 켜는 범위: STAR_TRANSFORMATION_ENABLED는 시스템 전체로도 세션 단위로도 바꿀 수 있다. OLTP 업무와 같은 DB라면 BI 서비스의 세션에서만 켠다. TRUE이면 차원 결과를 임시 테이블로 만들어 두 번 읽기를 피할 수도 있고(TEMP TABLE TRANSFORMATION), TEMP_DISABLE이면 그 단계를 뺀다. 차원이 5블록짜리면 차이가 없다.
결합 B-tree 인덱스로는: (ITEM_ID, SHOP_NO, ORD_DT) 같은 결합 인덱스는 이 리포트 하나에는 잘 맞지만, 사용자가 조건 조합을 바꿀 때마다 다른 인덱스가 필요하다. 단일 컬럼 비트맵을 실행할 때 겹치는 스타 변환은 조합이 바뀌어도 인덱스 셋으로 버틴다.
비트맵 인덱스와 적재: 비트맵 인덱스의 한 항목은 여러 행을 덮으므로 한 세션이 행을 고치면 같은 항목에 걸린 다른 행의 DML도 기다린다. 적재는 한 세션의 APPEND 적재 한 문장으로 몰고, 대량 재적재 때는 인덱스를 UNUSABLE로 두었다가 다시 만든다(교재 14화).
차원 조건이 하나뿐이면: 2013년 전체처럼 조건이 하나이고 넓으면 스타 변환은 득이 없다. 겹칠 비트맵이 하나뿐이라 FULL과 해시 조인이 그대로 맞다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황