비트맵 인덱스가 없는 주문 테이블에서 매장 분기 주문 추이가 BITMAP AND로 1,100블록 넘게 읽는다

중급SELECT버거120 / 700

상황

버거원 운영 주문 테이블 ORD_RANDOM에는 날짜 인덱스와 매장 FK 인덱스가 하나씩 있다. 점주 앱의 「오늘 주문」(S1)은 하루 약 1만 번, 본사 슈퍼바이저의 「매장 분기 추이」(S2)는 하루 약 300번 돈다. 둘 다 매장 하나와 날짜를 조건으로 준다. 점검 리포트에 두 SQL의 실행계획에 BITMAP 연산이 나온다는 지적이 올라왔다. DBA는 "비트맵 인덱스는 만든 적이 없다"고 한다. S1은 31블록이지만 S2는 1,132블록을 읽는다. 본사 DBA 규정상 이 테이블의 인덱스 수는 늘릴 수 없다(ACC-040).

테이블과 인덱스

ORD_RANDOM(운영본): 1,000,000행 · 22,414블록 · 무작위 순서로 적재 · ORD_DT VARCHAR2(8) 하루 약 1,370건 · SHOP_NO 200곳에 고르게(매장당 약 5,000건) · 2013년 10 ~ 12월 약 125,000건
매장 SH0042: 2013-12-24에 7건 · 2013년 10 ~ 12월에 641건(92일 모두 주문이 있다)
인덱스 ORD_RANDOM_PK (ORD_NO) · ORD_RANDOM_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 3,774 · CLUSTERING_FACTOR 999,958(교재 04화 실측)
인덱스 ORD_RANDOM_X04 (SHOP_NO): 매장 FK(FK_ORD_SHOP)의 자식 쪽 인덱스 · BLEVEL 2 · 리프 약 2,700블록 · CLUSTERING_FACTOR 약 999,900 · 통계 최신
비트맵 인덱스는 하나도 없다 · POS가 하루 약 1,370건 INSERT한다 · ORD_DT가 문자라서 옵티마이저는 기간의 행 수를 열 배쯤 낮게 본다

원본 SQL

sql

-- S1 점주 앱 「오늘 주문」 · 하루 약 1만 번
SELECT ORD_NO, ORD_HMS
  FROM ORD_RANDOM
 WHERE SHOP_NO = :v_shop
   AND ORD_DT  = :v_dt
 ORDER BY ORD_HMS;

-- S2 슈퍼바이저 「매장 분기 추이」 · 하루 약 300번
SELECT ORD_DT, COUNT(*) AS ord_cnt
  FROM ORD_RANDOM
 WHERE SHOP_NO = :v_shop
   AND ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY ORD_DT
 ORDER BY ORD_DT;

실행계획 학습용 예시 · X01의 리프와 CF는 교재 04화 실측 · B-tree를 비트맵으로 바꿔 합치는 계획은 운영(EE)의 모양

xplan

[S1 · SH0042 · 2013-12-24]
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      7 |00:00:00.01 |      31 |          |
|   1 |  SORT ORDER BY                       |                |      1 |      7 |      7 |00:00:00.01 |      31 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM     |      1 |      7 |      7 |00:00:00.01 |      31 |          |
|   3 |    BITMAP CONVERSION TO ROWIDS       |                |      1 |        |      7 |00:00:00.01 |      24 |          |
|   4 |     BITMAP AND                       |                |      1 |        |      1 |00:00:00.01 |      24 |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS   |                |      1 |        |      1 |00:00:00.01 |       8 |          |
|   6 |       SORT ORDER BY                  |                |      1 |        |   1368 |00:00:00.01 |       8 |   43K (0)|
|*  7 |        INDEX RANGE SCAN              | ORD_RANDOM_X01 |      1 |   1370 |   1368 |00:00:00.01 |       8 |          |
|   8 |      BITMAP CONVERSION FROM ROWIDS   |                |      1 |        |      1 |00:00:00.01 |      16 |          |
|*  9 |       INDEX RANGE SCAN               | ORD_RANDOM_X04 |      1 |   5000 |   5000 |00:00:00.01 |      16 |          |
-----------------------------------------------------------------------------------------------------------------------------
   7 - access("ORD_DT"=:V_DT)
   9 - access("SHOP_NO"=:V_SHOP)

[S2 · SH0042 · 2013-10-01 ~ 2013-12-31]
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                |      1 |        |     92 |00:00:00.14 |    1132 |          |
|   1 |  SORT GROUP BY                           |                |      1 |     63 |     92 |00:00:00.14 |    1132 | 6144  (0)|
|*  2 |   FILTER                                 |                |      1 |        |    641 |00:00:00.14 |    1132 |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED   | ORD_RANDOM     |      1 |     63 |    641 |00:00:00.13 |    1132 |          |
|   4 |     BITMAP CONVERSION TO ROWIDS          |                |      1 |        |    641 |00:00:00.11 |     491 |          |
|   5 |      BITMAP AND                          |                |      1 |        |      1 |00:00:00.11 |     491 |          |
|   6 |       BITMAP CONVERSION FROM ROWIDS      |                |      1 |        |      1 |00:00:00.01 |      16 |          |
|*  7 |        INDEX RANGE SCAN                  | ORD_RANDOM_X04 |      1 |   5000 |   5000 |00:00:00.01 |      16 |          |
|   8 |       BITMAP CONVERSION FROM ROWIDS      |                |      1 |        |      6 |00:00:00.10 |     475 |          |
|   9 |        SORT ORDER BY                     |                |      1 |        |   125K |00:00:00.08 |     475 | 3036K (0)|
|* 10 |         INDEX RANGE SCAN                 | ORD_RANDOM_X01 |      1 |  12500 |   125K |00:00:00.03 |     475 |          |
--------------------------------------------------------------------------------------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   7 - access("SHOP_NO"=:V_SHOP)
  10 - access("ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

문제

비트맵 인덱스가 없는데 두 실행계획에 BITMAP 연산이 나오는 이유를 줄 번호로 설명하시오. 두 인덱스에서 각각 무엇을 얼마나 읽는지도 설명하시오.
S1은 31블록인데 S2는 1,132블록인 이유를 설명하고, 매장과 날짜를 함께 담은 결합 인덱스 하나로 두 SQL을 읽을 때와 비교하시오.
인덱스 수를 늘리지 않고 두 SQL을 고치는 DDL을 작성하시오. 두 인덱스를 비트맵으로 바꿔 합치는 계획이 받아들일 만한 경우와 그렇지 않은 경우도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매장 FK 인덱스 X04 (SHOP_NO) 를 (SHOP_NO, ORD_DT) 로 바꿔 단다
--   선두가 그대로 SHOP_NO 라 FK 자식 쪽 인덱스 역할을 이어 받고, 인덱스 수도 그대로다
CREATE INDEX ORD_RANDOM_X05 ON ORD_RANDOM (SHOP_NO, ORD_DT) ONLINE INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- S1 · S2 를 세션에서 재 본다
ALTER INDEX ORD_RANDOM_X05 VISIBLE;
ALTER INDEX ORD_RANDOM_X04 INVISIBLE;     -- 2주 동안 X04 만 쓰던 SQL 이 없는지 본다
DROP INDEX ORD_RANDOM_X04;

-- S1 · S2 SQL 은 그대로

개선 후 실행계획 학습용 예시 · X01의 리프와 CF는 교재 04화 실측 · B-tree를 비트맵으로 바꿔 합치는 계획은 운영(EE)의 모양

xplan

[S1 · 고친 뒤]
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      7 |00:00:00.01 |      10 |          |
|   1 |  SORT ORDER BY                       |                |      1 |      7 |      7 |00:00:00.01 |      10 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM     |      1 |      7 |      7 |00:00:00.01 |      10 |          |
|*  3 |    INDEX RANGE SCAN                  | ORD_RANDOM_X05 |      1 |      7 |      7 |00:00:00.01 |       3 |          |
-----------------------------------------------------------------------------------------------------------------------------
   3 - access("SHOP_NO"=:V_SHOP AND "ORD_DT"=:V_DT)

[S2 · 고친 뒤]
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |     92 |00:00:00.01 |       5 |          |
|   1 |  SORT GROUP BY NOSORT                |                |      1 |     63 |     92 |00:00:00.01 |       5 |          |
|*  2 |   FILTER                             |                |      1 |        |    641 |00:00:00.01 |       5 |          |
|*  3 |    INDEX RANGE SCAN                  | ORD_RANDOM_X05 |      1 |     63 |    641 |00:00:00.01 |       5 |          |
-----------------------------------------------------------------------------------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   3 - access("SHOP_NO"=:V_SHOP AND "ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

[크기]  ORD_RANDOM_X05 · BLEVEL 2 · 리프 약 3,770블록 (X04 약 2,700블록)

모범 답안 서술

비트맵 인덱스가 없어도 옵티마이저는 B-tree 인덱스 두 개를 비트맵으로 바꿔 합치는 계획을 쓸 수 있다. S1에서 7번 줄은 X01로 그날 주문 1,368건의 ROWID를 읽고, 6번 줄에서 ROWID 순으로 정렬한 뒤 5번 줄 BITMAP CONVERSION FROM ROWIDS가 실행 중에 비트맵으로 바꾼다. 9번 줄은 X04로 매장 SH0042의 주문 5,000건의 ROWID를 읽어 8번 줄에서 비트맵으로 바꾼다. 4번 줄 BITMAP AND가 두 비트맵을 겹쳐 둘 다 맞는 7건만 남기고, 3번 줄이 다시 ROWID로 바꿔 테이블을 읽는다. 어느 인덱스 하나로만 찾으면 1,368건이나 5,000건을 모두 테이블에서 읽어야 하는데, 이 테이블은 CLUSTERING_FACTOR가 행 수만큼 나빠 한 건이 곧 한 블록이다. 그래서 옵티마이저는 두 인덱스를 합치는 쪽이 싸다고 보았다. 이 계획의 비용은 각 인덱스에서 조건에 맞는 ROWID를 전부 읽어야 한다는 데 있다. S1은 두 조건이 모두 좁아서 X01 8블록, X04 16블록에 테이블 7블록, 합계 31블록이다. S2는 날짜 조건이 분기라서 10번 줄이 X01에서 약 12만 5천 ROWID(475블록)를 읽고, 9번 줄에서 3MB를 써서 정렬한 뒤에야 비트맵이 된다. 겹쳐서 남는 것은 641건뿐이고, 그 641건을 테이블에서 한 블록씩 읽는다. 그래서 X01 475, X04 16, 테이블 641, 합계 1,132블록이다. 매장과 날짜를 함께 담은 (SHOP_NO, ORD_DT) 인덱스라면 두 조건이 한 구간으로 정해진다. S1은 그 매장의 그날 7항목을 리프 한 블록에서 찾아 테이블 7블록을 읽는다(31 → 10). S2는 그 매장의 분기 641항목을 리프 3블록에서 읽고, 날짜만 세므로 테이블에 가지 않고 정렬도 없다(1,132 → 5). 하루로 치면 S1은 약 31만 → 10만, S2는 약 34만 → 1,500블록이다. 인덱스 수를 늘릴 수 없으므로 매장 FK 인덱스 X04 (SHOP_NO)를 (SHOP_NO, ORD_DT)로 바꿔 단다. 선두가 그대로 SHOP_NO라 FK 자식 쪽 인덱스 역할은 그대로 맡고(ACC-067), 매장으로만 찾는 SQL도 그대로 쓸 수 있다. INVISIBLE로 만들어 세션에서 재고, VISIBLE로 바꾼 뒤 X04를 INVISIBLE로 지켜보다가 지운다. 대가는 항목마다 ORD_DT가 붙어 리프가 약 2,700 → 3,770블록으로 커지는 것이다. POS INSERT가 고치는 인덱스 수는 그대로다. 두 인덱스를 비트맵으로 합치는 계획이 나쁜 것은 아니다. S1처럼 두 조건이 모두 적은 ROWID만 고르면 수십 블록이면 끝난다. 조건 조합이 정해지지 않은 드문 조회나 인덱스를 더 둘 수 없는 테이블에서는 받아들일 만한 답이다. 하지만 한쪽 조건이 넓으면 그 범위의 ROWID를 전부 읽고 정렬하므로, 결과가 몇 건이든 그 범위만큼 비싸진다. 자주 도는 SQL에서 이 계획이 보이면 그 조합을 한 구간으로 읽는 결합 인덱스부터 따져 본다.

해설

함정: BITMAP이 보인다고 비트맵 인덱스가 있는 것은 아니다. BITMAP CONVERSION FROM ROWIDS 아래에 B-tree의 INDEX RANGE SCAN이 있으면 실행 중에 만든 비트맵이다. 저장된 비트맵 인덱스와 달리 DML 락 문제는 없지만, 실행할 때마다 ROWID를 읽고 정렬해 비트맵을 만드는 비용이 든다.
이 계획을 막으려고 숨은 파라미터 _b_tree_bitmap_plans를 끄거나 NO_INDEX_COMBINE 힌트를 넣는 것은 답이 아니다. S1처럼 이 계획이 가장 싼 SQL까지 나빠진다. 원인인 인덱스 구성을 고친다.
S1을 더 줄이려면 ACC-073의 점주 앱 인덱스처럼 (SHOP_NO, ORD_DT, ORD_HMS)로 만들어 정렬을 없앨 수 있고, ORD_NO까지 담으면 테이블에도 가지 않는다. 리프가 더 커지므로 S1의 호출 수와 견주어 정한다.
INDEX_COMBINE 힌트는 이 계획을 고르게 하는 힌트다. 인덱스 조인(ACC-041, ACC-042)은 두 인덱스를 ROWID로 해시 조인해 테이블을 피하지만, 이 계획은 두 인덱스로 행을 고른 뒤 결국 테이블을 읽는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · X01의 리프와 CF는 교재 04화 실측 · B-tree를 비트맵으로 바꿔 합치는 계획은 운영(EE)의 모양 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · X01의 리프와 CF는 교재 04화 실측 · B-tree를 비트맵으로 바꿔 합치는 계획은 운영(EE)의 모양 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황