JOIN-070

판매 마트의 리포트 세 개가 모두 팩트를 통째로 읽는데, 업체는 리포트마다 결합 인덱스를 하나씩 만들자고 한다

실전DDL유통220 / 700

상황

본사가 BI 도구를 들이면서 판매와 판매상품을 합친 판매 마트 DW_SLE_ITM을 만들었다. 첫 리포트 세 개가 붙었는데 셋 다 팩트를 FULL로 읽는다. 구축 업체는 R1과 R2에 맞춘 결합 인덱스를 하나씩 만들자고 제안했다. 박팀장은 "BI 사용자는 조건을 계속 바꿀 텐데, 리포트가 늘 때마다 인덱스를 만들 거냐"며 대안을 비교해 오라고 했다. 마트는 매일 밤 02:00 배치가 전날 판매 약 1,500줄을 한 세션에서 덧붙이고, 낮에는 읽기만 한다. 호출 빈도는 R1이 하루 400번(거래처 담당 MD), R2가 30번(점장 회의), R3가 50번(경영진 대시보드)이다.

테이블과 인덱스

DW_SLE_ITM 판매 팩트: 1,096,708행 · 8,860블록 · SLE_DT · SHP_NO · ITM_NO · SLE_NO · SLE_QTY · SLE_AMT · 날짜순으로 쌓인다 · 인덱스 없음(적재가 느려진다며 만들지 않았다)
차원: W_DAY 날짜 730행 · 6블록(DT · YM · DOW_CD) / CF01 상품 109행 · 5블록(PTNR_NO 거래처 15곳 · CTG_NO) / CF02 카테고리 18행 · 4블록(UP_CTG_NO 상위 분류) / CF07 매장 6행 · 4블록
R1: 거래처 P07의 상품 8개가 판매 줄의 7.3% · 2023년 12월이 46,322줄(4.2%) · 둘을 겹치면 3,358줄(0.3%)
R2: 매장 1001과 1002가 38% · 2023년 주말이 15% · 음료 상위 분류(상품 26개)가 24% · 셋을 겹치면 15,037줄(1.4%)
R3: 조건 없이 2년 전체를 날짜별로 묶는다
통계 최신 · STAR_TRANSFORMATION_ENABLED = FALSE

원본 SQL

sql

-- [R1] 거래처 월 매출 (하루 400번)
SELECT p.ITM_NO, p.ITM_NM, SUM(f.SLE_QTY) AS qty, SUM(f.SLE_AMT) AS amt
  FROM DW_SLE_ITM f, CF01 p, W_DAY d
 WHERE p.PTNR_NO = :v_ptnr
   AND d.YM      = :v_ym
   AND f.ITM_NO  = p.ITM_NO
   AND f.SLE_DT  = d.DT
 GROUP BY p.ITM_NO, p.ITM_NM;

-- [R2] 매장 · 요일 · 분류 판매 (하루 30번)
SELECT f.SHP_NO, d.DOW_CD, g.CTG_NM, SUM(f.SLE_AMT) AS amt
  FROM DW_SLE_ITM f, W_DAY d, CF01 p, CF02 g
 WHERE f.SHP_NO IN (:v_shp1, :v_shp2)
   AND d.YM BETWEEN :v_ym_from AND :v_ym_to
   AND d.DOW_CD IN ('1', '7')
   AND g.UP_CTG_NO = :v_up_ctg
   AND p.CTG_NO = g.CTG_NO
   AND f.ITM_NO = p.ITM_NO
   AND f.SLE_DT = d.DT
 GROUP BY f.SHP_NO, d.DOW_CD, g.CTG_NM;

-- [R3] 일별 매출 추이 (하루 50번)
SELECT f.SLE_DT, SUM(f.SLE_AMT) AS amt
  FROM DW_SLE_ITM f
 GROUP BY f.SLE_DT
 ORDER BY f.SLE_DT;

실행계획 학습용 예시

xplan

[R1] 거래처 P07 · 2023년 12월
-----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |      1 |        |      8 |00:00:00.31 |    8871 |           |
|   1 |  HASH GROUP BY         |            |      1 |      7 |      8 |00:00:00.31 |    8871 | 1022K (0) |
|*  2 |   HASH JOIN            |            |      1 |   3104 |   3358 |00:00:00.31 |    8871 | 1156K (0) |
|*  3 |    TABLE ACCESS FULL   | CF01       |      1 |      7 |      8 |00:00:00.01 |       5 |           |
|*  4 |    HASH JOIN           |            |      1 |  45057 |  46322 |00:00:00.27 |    8866 | 1286K (0) |
|*  5 |     TABLE ACCESS FULL  | W_DAY      |      1 |     30 |     31 |00:00:00.01 |       6 |           |
|   6 |     TABLE ACCESS FULL  | DW_SLE_ITM |      1 |  1096K |  1096K |00:00:00.09 |    8860 |           |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("F"."ITM_NO"="P"."ITM_NO")
   3 - filter("P"."PTNR_NO"=:V_PTNR)
   4 - access("F"."SLE_DT"="D"."DT")
   5 - filter("D"."YM"=:V_YM)

[R2] 매장 1001과 1002 · 2023년 주말 · 음료 상위 분류
-----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |      1 |        |     12 |00:00:00.34 |    8875 |           |
|   1 |  HASH GROUP BY         |            |      1 |     12 |     12 |00:00:00.34 |    8875 | 1022K (0) |
|*  2 |   HASH JOIN            |            |      1 |  14382 |  15037 |00:00:00.34 |    8875 | 1290K (0) |
|*  3 |    HASH JOIN           |            |      1 |     26 |     26 |00:00:00.01 |       9 | 1156K (0) |
|*  4 |     TABLE ACCESS FULL  | CF02       |      1 |      3 |      3 |00:00:00.01 |       4 |           |
|   5 |     TABLE ACCESS FULL  | CF01       |      1 |    109 |    109 |00:00:00.01 |       5 |           |
|*  6 |    HASH JOIN           |            |      1 |  59200 |  62580 |00:00:00.30 |    8866 | 1286K (0) |
|*  7 |     TABLE ACCESS FULL  | W_DAY      |      1 |    104 |    104 |00:00:00.01 |       6 |           |
|*  8 |     TABLE ACCESS FULL  | DW_SLE_ITM |      1 |   416K |   416K |00:00:00.12 |    8860 |           |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("F"."ITM_NO"="P"."ITM_NO")
   3 - access("P"."CTG_NO"="G"."CTG_NO")
   4 - filter("G"."UP_CTG_NO"=:V_UP_CTG)
   6 - access("F"."SLE_DT"="D"."DT")
   7 - filter(("D"."YM">=:V_YM_FROM AND "D"."YM"<=:V_YM_TO AND INTERNAL_FUNCTION("D"."DOW_CD")))
   8 - filter(("F"."SHP_NO"=:V_SHP1 OR "F"."SHP_NO"=:V_SHP2))

[R3] 2년 전체 일별 매출
---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |      1 |        |    730 |00:00:00.29 |    8860 |           |
|   1 |  SORT GROUP BY       |            |      1 |    730 |    730 |00:00:00.29 |    8860 | 43008 (0) |
|   2 |   TABLE ACCESS FULL  | DW_SLE_ITM |      1 |  1096K |  1096K |00:00:00.08 |    8860 |           |
---------------------------------------------------------------------------------------------------------

문제

세 리포트마다 차원 조건을 하나씩 적용했을 때와 모두 적용했을 때 팩트에서 남는 행 수를 쓰고, 지금 계획의 일량을 하루 단위로 계산하시오.
업체 안(R1용 (ITM_NO, SLE_DT), R2용 (SHP_NO, SLE_DT, ITM_NO) 결합 인덱스)과 팩트의 차원 키마다 단일 컬럼 비트맵 인덱스를 두고 스타 변환을 쓰는 안을 리포트별 예상 블록과 확장성으로 비교하고, 권하는 안을 DDL로 작성하시오.
권하는 안이 야간 적재와 동시 DML에 주는 영향과 적재 절차를 서술하시오. R3는 어떻게 할지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 팩트의 차원 키마다 비트맵 인덱스 하나씩 : 조건 조합은 실행할 때 BITMAP AND 로 만든다
CREATE BITMAP INDEX BX_DSI_SLE_DT ON DW_SLE_ITM (SLE_DT) NOLOGGING;
CREATE BITMAP INDEX BX_DSI_SHP_NO ON DW_SLE_ITM (SHP_NO) NOLOGGING;
CREATE BITMAP INDEX BX_DSI_ITM_NO ON DW_SLE_ITM (ITM_NO) NOLOGGING;

-- ② BI 서비스로 접속한 세션에서만 스타 변환을 허용한다 (로그온 트리거나 서비스별 설정으로)
ALTER SESSION SET STAR_TRANSFORMATION_ENABLED = TRUE;

-- ③ 야간 적재 : 한 세션의 직접 경로 적재 한 문장으로 넣는다 (비트맵은 적재 끝에 한꺼번에 고친다)
INSERT /*+ APPEND */ INTO DW_SLE_ITM (SLE_DT, SHP_NO, ITM_NO, SLE_NO, SLE_QTY, SLE_AMT)
SELECT a.SLE_DT, a.SHP_NO, b.ITM_NO, b.SLE_NO, b.SLE_QTY, b.SLE_QTY * b.SLE_UPRC
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT = :v_yesterday
   AND b.SLE_NO = a.SLE_NO;
COMMIT;

-- ④ 과거 자료를 대량으로 다시 넣을 때만 : 인덱스를 UNUSABLE 로 두고 적재한 뒤 다시 만든다
-- ALTER INDEX BX_DSI_ITM_NO UNUSABLE;  (적재)  ALTER INDEX BX_DSI_ITM_NO REBUILD NOLOGGING;

-- ⑤ R3 는 그대로 둔다 : 조건 없는 2년 집계는 FULL 한 번이 가장 싸다

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

xplan

[R1] 비트맵 인덱스 셋과 스타 변환 뒤
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |               |      1 |        |      8 |00:00:00.02 |     446 |           |
|   1 |  HASH GROUP BY                          |               |      1 |      7 |      8 |00:00:00.02 |     446 | 1022K (0) |
|*  2 |   HASH JOIN                             |               |      1 |   3104 |   3358 |00:00:00.02 |     446 | 1156K (0) |
|*  3 |    TABLE ACCESS FULL                    | CF01          |      1 |      7 |      8 |00:00:00.01 |       5 |           |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED  | DW_SLE_ITM    |      1 |   3104 |   3358 |00:00:00.02 |     441 |           |
|   5 |     BITMAP CONVERSION TO ROWIDS         |               |      1 |        |   3358 |00:00:00.01 |      75 |           |
|   6 |      BITMAP AND                         |               |      1 |        |      4 |00:00:00.01 |      75 |           |
|   7 |       BITMAP MERGE                      |               |      1 |        |      2 |00:00:00.01 |      34 | 1024K (0) |
|   8 |        BITMAP KEY ITERATION             |               |      1 |        |     31 |00:00:00.01 |      34 |           |
|*  9 |         TABLE ACCESS FULL               | W_DAY         |      1 |     30 |     31 |00:00:00.01 |       6 |           |
|* 10 |         BITMAP INDEX RANGE SCAN         | BX_DSI_SLE_DT |     31 |        |     31 |00:00:00.01 |      28 |           |
|  11 |       BITMAP MERGE                      |               |      1 |        |      6 |00:00:00.01 |      41 | 1024K (0) |
|  12 |        BITMAP KEY ITERATION             |               |      1 |        |      8 |00:00:00.01 |      41 |           |
|* 13 |         TABLE ACCESS FULL               | CF01          |      1 |      7 |      8 |00:00:00.01 |       5 |           |
|* 14 |         BITMAP INDEX RANGE SCAN         | BX_DSI_ITM_NO |      8 |        |     22 |00:00:00.01 |      36 |           |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("F"."ITM_NO"="P"."ITM_NO")
   3 - filter("P"."PTNR_NO"=:V_PTNR)
   9 - filter("D"."YM"=:V_YM)
  10 - access("F"."SLE_DT"="D"."DT")
  13 - filter("P"."PTNR_NO"=:V_PTNR)
  14 - access("F"."ITM_NO"="P"."ITM_NO")

모범 답안 서술

지금의 일량: 세 리포트 모두 팩트를 FULL로 한 번 읽는다(8,860블록). R1은 거래처 조건만 보면 7.3%, 달 조건만 보면 4.2%라서 어느 하나로 인덱스를 타기에는 넓지만, 둘을 겹치면 3,358줄(0.3%)이다. R2는 매장 38%, 주말 15%, 음료 24%로 하나씩은 넓고 겹치면 15,037줄(1.4%)이다. R3는 조건이 없어 전부 읽어야 한다. 하루로 보면 R1이 400번 × 8,871 ≈ 355만 블록, R2가 30번 × 8,875 ≈ 27만 블록, R3가 50번 × 8,860 ≈ 44만 블록이다. 업체 안: R1용 (ITM_NO, SLE_DT)는 상품 8개마다 12월 범위만 읽으므로 약 400블록이다. R2용 (SHP_NO, SLE_DT, ITM_NO)는 매장 둘 × 주말 104일 범위를 읽고 상품 조건을 인덱스 안에서 걸러 약 3,000블록이다. 두 리포트만 보면 잘 맞는다. 하지만 인덱스가 리포트의 조건 조합에 묶여 있어서, 사용자가 거래처 대신 카테고리로, 주말 대신 공휴일로 조건을 바꾸면 쓸 수 없다. 조합마다 결합 인덱스를 만들면 인덱스가 계속 늘고, 하나가 리프 3천 블록 남짓이라 적재와 공간 비용도 그만큼 는다. 비트맵과 스타 변환 안(권함): 팩트의 차원 키 SLE_DT, SHP_NO, ITM_NO에 단일 컬럼 비트맵 인덱스를 하나씩 둔다. 스타 변환은 차원에서 고른 키 목록으로 각 비트맵을 읽어 합치고(BITMAP MERGE), 차원끼리 AND로 겹친 뒤(BITMAP AND) 남은 ROWID만 팩트에서 읽는다. R1은 날짜 31개와 상품 8개의 비트맵을 겹쳐 3,358행만 읽어 446블록(지금 8,871), R2는 매장 둘과 주말 104일과 상품 26개의 비트맵을 겹쳐 약 2,900블록이다. 조건 조합이 바뀌어도 같은 인덱스 셋으로 처리한다. 비트맵 인덱스는 값 종류가 적은 컬럼일수록 작아서 셋을 합쳐도 결합 인덱스 하나 크기 안쪽이다. 하루 일량은 R1이 약 18만, R2가 약 9만 블록으로 준다. R3: 조건 없이 2년 전체를 묶는 집계라 FULL 한 번이 가장 싸다. 옵티마이저는 차원 조건이 없으면 스타 변환을 쓰지 않으므로 손대지 않는다. 더 줄이려면 날짜별 요약 테이블이나 구체화 뷰를 따로 검토한다. 적재와 동시성: 비트맵 인덱스의 한 항목은 여러 행의 범위를 덮는다. 한 세션이 행 하나를 넣거나 고치면 같은 항목에 걸린 다른 행의 DML이 기다리므로, 낮에 여러 세션이 쓰는 테이블에는 쓰지 않는다. 이 마트는 밤에 한 세션이 적재하고 낮에는 읽기만 하므로 맞는다. 적재는 INSERT /*+ APPEND */ 한 문장으로 몰아 비트맵이 적재 끝에 한꺼번에 고쳐지게 하고, 행 단위 루프 적재는 쓰지 않는다. 과거 자료를 대량으로 다시 넣을 때는 인덱스를 UNUSABLE로 두고 적재한 뒤 다시 만든다. 스타 변환은 BI 서비스 세션에서만 켠다.

해설

비트맵 조인 인덱스: 차원 컬럼(거래처, 상위 분류)으로 팩트 행을 바로 가리키는 비트맵 조인 인덱스도 있다. 차원 조인을 더 줄이지만 차원 쪽 PK와 팩트 쪽 조인 조건이 정해져 있어야 하고, 적재 비용이 더 크다. 리포트가 늘어 차원 조인이 병목이 될 때 검토한다.
조건이 넓으면 변환하지 않는다: 겹친 결과가 팩트의 몇 %를 넘으면 흩어진 블록을 ROWID로 읽는 비용이 FULL보다 커진다. 옵티마이저는 그때 변환을 쓰지 않으므로, 힌트로 강제하지 않고 파라미터만 켜 두는 것이 맞다(JOIN-067).
파티션과 함께: 마트가 커지면 월 단위 Range 파티션과 로컬 비트맵 인덱스를 함께 쓴다. 적재와 인덱스 재생성이 그달 파티션으로 좁혀진다(교재 14화).
차원 키에 FK를 걸지: 마트는 적재 배치가 키를 맞춰 넣으므로 검사 비용을 들이지 않는 RELY 제약으로 관계만 알려 두는 경우가 많다. 다만 배치가 그 약속을 지킨다는 점검을 함께 남긴다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황