VIP 캠페인 대상 주문 415건을 세는 데 다른 리포트용 인덱스를 타고 1,400블록 가까이 읽는다

중급SELECT한빛유통131 / 700

상황

마케팅팀은 캠페인을 설계할 때마다 분석 마트 DW_ORD_BIG에서 등급, 지역, 주문 상태, 기간을 조합해 대상 주문의 건수와 금액을 뽑는다. 조건은 캠페인마다 달라서 IN 목록과 부정 조건(<>), 기간이 섞인다. 하루 수백 번 실행된다. 이번 캠페인 조건은 A등급, 지역 RGN05와 RGN07, 취소 제외, 2024년 2분기다. 옵티마이저는 「지역별 월 매출」 리포트용 인덱스를 골랐다.

테이블과 인덱스

DW_ORD_BIG 주문 분석 마트: 매일 밤 T_ORD_BIG에 M_CUS의 CUS_GD · RGN_ID · GND_TP와 월 ORD_YM을 붙여 ORD_DT 순으로 다시 만든다(CTAS) · 낮에는 읽기만 한다 · 1,000,000행 · 6,980블록(블록당 약 143행) · ORD_ST는 NOT NULL
분포: CUS_GD A 1% · B 9% · C 90% · RGN_ID 8곳에 12.5%씩 · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2% · 2024년 2분기 83,083건
이번 조건의 결과는 415건이다 · A등급 고객 20명은 RGN01과 RGN05에만 10명씩 있고, A등급 고객의 주문은 모두 COMPLETE다
인덱스 IX_DWO_YM_RGN (ORD_YM, RGN_ID): 「지역별 월 매출」 리포트용 B-tree · BLEVEL 2 · 리프 약 2,900블록 · 다른 인덱스는 없다 · 통계 최신

원본 SQL

sql

SELECT COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM DW_ORD_BIG
 WHERE CUS_GD = 'A'
   AND RGN_ID IN ('RGN05', 'RGN07')
   AND ORD_ST <> 'CANCEL'
   AND ORD_YM BETWEEN '202404' AND '202406'

실행계획 학습용 예시 · 조합 건수는 적재 스크립트의 MOD 규칙에서 센 값 · 비트맵 블록 수는 어림값

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |      1 |00:00:00.03 |    1378 |
|   1 |  SORT AGGREGATE                      |               |      1 |      1 |      1 |00:00:00.03 |    1378 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DW_ORD_BIG    |      1 |    204 |    415 |00:00:00.03 |    1378 |
|*  3 |    INDEX RANGE SCAN                  | IX_DWO_YM_RGN |      1 |  20770 |  20770 |00:00:00.01 |     218 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("CUS_GD"='A' AND "ORD_ST"<>'CANCEL'))
   3 - access("ORD_YM">='202404' AND "ORD_YM"<='202406')
       filter(("RGN_ID"='RGN05' OR "RGN_ID"='RGN07'))

문제

3번 줄이 20,770건을 넘기고 2번 줄이 그중 415건만 남기는 이유를 access와 filter로 설명하시오. 이 마트에 캠페인 조합마다 결합 B-tree 인덱스를 만드는 방법이 맞지 않는 이유도 설명하시오.
IN 목록, 부정 조건, 기간이 섞인 조합을 인덱스만으로 좁힐 수 있도록 인덱스를 설계해 DDL을 작성하시오. 고친 실행계획에서 각 조건이 어떤 연산으로 처리되는지와, 건수만 셀 때는 무엇이 달라지는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 조건에 나오는 저카디널리티 컬럼마다 비트맵 인덱스 하나씩 (밤에 마트를 다시 만든 뒤 만든다)
CREATE BITMAP INDEX BX_DWO_GD  ON DW_ORD_BIG (CUS_GD);
CREATE BITMAP INDEX BX_DWO_RGN ON DW_ORD_BIG (RGN_ID);
CREATE BITMAP INDEX BX_DWO_ST  ON DW_ORD_BIG (ORD_ST);
CREATE BITMAP INDEX BX_DWO_YM  ON DW_ORD_BIG (ORD_YM);
CREATE BITMAP INDEX BX_DWO_GND ON DW_ORD_BIG (GND_TP);

SELECT COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt     -- SQL 은 그대로
  FROM DW_ORD_BIG
 WHERE CUS_GD = 'A'
   AND RGN_ID IN ('RGN05', 'RGN07')
   AND ORD_ST <> 'CANCEL'
   AND ORD_YM BETWEEN '202404' AND '202406';

개선 후 실행계획 학습용 예시 · 조합 건수는 적재 스크립트의 MOD 규칙에서 센 값 · 비트맵 블록 수는 어림값

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |      1 |        |      1 |00:00:00.01 |     341 |
|   1 |  SORT AGGREGATE                       |               |      1 |      1 |      1 |00:00:00.01 |     341 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | DW_ORD_BIG    |      1 |    204 |    415 |00:00:00.01 |     341 |
|   3 |    BITMAP CONVERSION TO ROWIDS        |               |      1 |        |    415 |00:00:00.01 |      45 |
|   4 |     BITMAP MINUS                      |               |      1 |        |      1 |00:00:00.01 |      45 |
|   5 |      BITMAP AND                       |               |      1 |        |      1 |00:00:00.01 |      40 |
|*  6 |       BITMAP INDEX SINGLE VALUE       | BX_DWO_GD     |      1 |        |      1 |00:00:00.01 |       3 |
|   7 |       BITMAP OR                       |               |      1 |        |      1 |00:00:00.01 |      34 |
|*  8 |        BITMAP INDEX SINGLE VALUE      | BX_DWO_RGN    |      1 |        |      2 |00:00:00.01 |      17 |
|*  9 |        BITMAP INDEX SINGLE VALUE      | BX_DWO_RGN    |      1 |        |      2 |00:00:00.01 |      17 |
|  10 |       BITMAP MERGE                    |               |      1 |        |      1 |00:00:00.01 |       3 |
|* 11 |        BITMAP INDEX RANGE SCAN        | BX_DWO_YM     |      1 |        |      3 |00:00:00.01 |       3 |
|* 12 |      BITMAP INDEX SINGLE VALUE        | BX_DWO_ST     |      1 |        |      1 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------------------
   6 - access("CUS_GD"='A')
   8 - access("RGN_ID"='RGN05')
   9 - access("RGN_ID"='RGN07')
  11 - access("ORD_YM">='202404' AND "ORD_YM"<='202406')
  12 - access("ORD_ST"='CANCEL')

모범 답안 서술

3번 줄은 IX_DWO_YM_RGN을 읽는다. access는 선두 컬럼 ORD_YM의 범위(2분기 83,083항목)뿐이고, 범위 조건 뒤의 RGN_ID는 리프를 읽으며 거르는 filter다. 그래서 2분기 리프 218블록을 다 읽고 두 지역의 20,770건을 테이블로 넘긴다. CUS_GD와 ORD_ST는 인덱스에 없으므로 2번 줄에서 20,770건마다 테이블 블록을 찾아가 거르고 415건만 남긴다(테이블 쪽 약 1,160블록). 캠페인마다 조건 조합이 달라서 결합 B-tree로 받으려면 조합마다 컬럼 순서를 바꾼 인덱스가 필요하다. IN 목록과 <> 조건은 B-tree에서 범위를 좁히는 데도 잘 쓰이지 않는다. 밤마다 마트를 다시 만들 때 그 인덱스도 전부 다시 만들어야 한다. 마트는 밤에 다시 만들고 낮에는 읽기만 하므로 저카디널리티 컬럼마다 비트맵 인덱스를 하나씩 둔다. 고친 계획에서 A등급은 비트맵 하나(6번)로, 지역 IN 목록은 두 값의 비트맵을 BITMAP OR로 합쳐서(7번), 2분기는 세 달의 비트맵을 BITMAP MERGE로 합쳐서(10번) 처리한다. 이 셋을 BITMAP AND로 겹친 뒤, 취소 제외(<>)는 CANCEL의 비트맵을 BITMAP MINUS로 뺀다(4번). 남은 415건의 ROWID만 테이블에서 읽으므로 Buffers는 1,378 → 341이다. 건수만 세고 금액(SUM)을 빼면 3번 줄이 BITMAP CONVERSION COUNT로 바뀌고 테이블 줄이 없어진다. 비트맵 45블록으로 끝난다. 같은 비트맵 인덱스를 운영 T_ORD_BIG에 만들면 안 된다. 주문 입력이나 상태 UPDATE가 비트맵 항목 하나를 잠그면 그 항목이 덮는 수많은 행이 함께 잠겨, 다른 주문의 DML이 줄을 선다(LOCK 단원).

해설

함정: 비트맵이 도는 것은 남는 행이 적을 때다. 이번 조합은 A등급(1%)이 섞여 415건으로 줄었다. C등급(90%)과 지역 하나처럼 남는 행이 수만 건이면 비트맵으로 찾아도 테이블 블록을 거의 다 읽으므로 FULL이 낫다.
비트맵 인덱스는 NULL도 담고, 부정 조건은 MINUS로, IN 목록과 OR는 OR로 처리한다. B-tree 인덱스는 NULL을 담지 않아 단일 컬럼 인덱스로 IS NULL을 받지 못하고, <>로는 범위를 좁히지 못한다.
비트맵 인덱스가 없어도 옵티마이저가 B-tree 인덱스 여럿을 비트맵으로 바꿔 합치는 계획(BITMAP CONVERSION FROM ROWIDS)을 쓸 때가 있다. 다만 ROWID를 비트맵으로 바꾸는 비용이 들어 저장된 비트맵만큼 싸지 않다(교재 08화 OR 표의 BITMAP OR).
마트를 다시 만들 때 비트맵 인덱스를 둔 채 행을 넣으면 적재가 느려진다. CTAS 뒤에 만들거나 UNUSABLE로 두었다가 REBUILD한다(ACC-057).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 조합 건수는 적재 스크립트의 MOD 규칙에서 센 값 · 비트맵 블록 수는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 조합 건수는 적재 스크립트의 MOD 규칙에서 센 값 · 비트맵 블록 수는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황