태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Bitmap Index vs. Btree Index

오라클 2008.05.29 17:27
금일(2008년 5월 29일)은 참으로 부끄러운 날입니다. 저는 특별히 애국자가 아니지만, 대한민국의 앞날이 참으로 걱정스럽습니다.

---------------------------------------------------------------------------------

얼마전 [Oracle is Mad(R)] 세미나에서 이런 논의가 있었다.

"지금 특정 테이블에 Bitmap Index가 정의되어 있는데, 성능 진단 팀에서 이것을 Btree Index로 바꾸라고 한다. 테이블은 매우 크고, Distinct Count가 만개 정도 되는데 Bitmap Index가 더 유리한 것 아닌가?"

그럴 수도 있겠다. 하지만 모든 것은 상황에 따라 다르다. Bitmap Index도 예외는 아니다. Distinct 값이 높다고 반드시 Btree Index가 유리하고 Distinct 값이 낮다고 반드시 Bitmap Index가 유리한 것은 아니다.

Bitmap Index는 단순히 Distinct 값이 낮은 컬럼을 위해 고안된 것이 아니라, DW/OLAP 환경에서 Star Schema에 대한 Query 성능을 극대화하기 위해 고안된 것이다. 정확하게 말하면 Star Transformation을 위해 고안된 것이다.

아래의 간단한 테스트 결과는 Bitmap Index가 Btree Index에 비해 효율적인 경우는 의외로 매우 드물다는 것을 잘 보여준다.

-- Create Objects
drop table t_bitmap purge;
create table t_bitmap(
  c_btree1 int,
  c_btree2 int,
  c_bitmap1 int,
  c_bitmap2 int,
  dummy char(100)
);

-- Distinct 개수가 낮은 값들을 Insert 한다.
-- Btree Index와 Bitmap Index를 비교하기 위해 두 벌씩 값을 생성한다
insert into t_bitmap
select
  mod(level, 100),
  mod(level, 10),
  mod(level, 100),
  mod(level, 10),
  'x'
from
  dual
connect by level <= 100000
;

commit;

-- Btree 인덱스 생성
create index t_bitmap_btree_n1 on t_bitmap(c_btree1);
create index t_bitmap_btree_n2 on t_bitmap(c_btree2);

-- Bitmap 인덱스 생성
create bitmap index t_bitmap_bitmap_n1 on t_bitmap(c_bitmap1);
create bitmap index t_bitmap_bitmap_n2 on t_bitmap(c_bitmap2);

exec dbms_stats.gather_table_stats(user, 't_bitmap', -
  method_opt=>'for all columns size 1');


Distinct 개수가 100개인 컬럼에 대해 Btree Index와 Bitmap Index를 비교해보면...

select /*+ gather_plan_statistics index(t_bitmap) */
  count(dummy)
from
  t_bitmap
where
  c_btree1 = 1
;

--------------------------------------------------------------------
| Id  | Operation                    | Name              | Buffers |
--------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |                   |   1004 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_BITMAP          |    1004 |
|*  3 |    INDEX RANGE SCAN          | T_BITMAP_BTREE_N1 |       4 |
--------------------------------------------------------------------

select /*+ gather_plan_statistics index(t_bitmap) */
  count(dummy)
from
  t_bitmap
where
  c_bitmap1 = 1
;

----------------------------------------------------------------------
| Id  | Operation                     | Name               | Buffers |
----------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |                    |    1003 |
|   2 |   TABLE ACCESS BY INDEX ROWID | T_BITMAP           |    1003 |
|   3 |    BITMAP CONVERSION TO ROWIDS|                    |       3 |
|*  4 |     BITMAP INDEX SINGLE VALUE | T_BITMAP_BITMAP_N1 |       3 |
----------------------------------------------------------------------

아무런 차이가 없다. 대부분의 일량이 테이블을 방문하는 것에서 발생하기 때문에 Bitmap Index에서의 일량이 조금 줄어드는 것으로는 성능 개선을 기대하기 힘들기 때문이다.

Bitmap Index의 자랑거리인 Bitmap 연산을 비교해봐도 결과는 크게 다르지 않다.

select /*+ gather_plan_statistics index(t_bitmap) */
  count(dummy)
from
  t_bitmap
where
  c_btree1 = 1 and
  c_btree2 = 1
;

--------------------------------------------------------------------
| Id  | Operation                    | Name              | Buffers |
--------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |                   |    1004 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T_BITMAP          |    1004 |
|*  3 |    INDEX RANGE SCAN          | T_BITMAP_BTREE_N1 |       4 |
--------------------------------------------------------------------

-- Bitmap Conversion Index Combination을 수행한다. 
select /*+ gather_plan_statistics
    index_combine(t_bitmap t_bitmap(c_btree1) t_bitmap(c_btree2)) */
  count(dummy)
from
  t_bitmap
where
  c_btree1 = 1 and
  c_btree2 = 1
;

-------------------------------------------------------------------------
| Id  | Operation                         | Name              | Buffers |
-------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                   |                   |    1025 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | T_BITMAP          |    1025 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |                   |      25 |
|   4 |     BITMAP AND                    |                   |      25 |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                   |       4 |
|*  6 |       INDEX RANGE SCAN            | T_BITMAP_BTREE_N1 |       4 |
|   7 |      BITMAP CONVERSION FROM ROWIDS|                   |      21 |
|*  8 |       INDEX RANGE SCAN            | T_BITMAP_BTREE_N2 |      21 |
-------------------------------------------------------------------------
  
select /*+ gather_plan_statistics index(t_bitmap) */
  count(dummy)
from
  t_bitmap
where
  c_bitmap1 = 1 and
  c_bitmap2 = 1
;

----------------------------------------------------------------------
| Id  | Operation                     | Name               | Buffers |
----------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |                    |    1007 |
|   2 |   TABLE ACCESS BY INDEX ROWID | T_BITMAP           |    1007 |
|   3 |    BITMAP CONVERSION TO ROWIDS|                    |       7 |
|   4 |     BITMAP AND                |                    |       7 |
|*  5 |      BITMAP INDEX SINGLE VALUE| T_BITMAP_BITMAP_N1 |       3 |
|*  6 |      BITMAP INDEX SINGLE VALUE| T_BITMAP_BITMAP_N2 |       4 |
----------------------------------------------------------------------


반면, COUNT(DUMMY) 대신에 COUNT(*)을 사용하면 Bitmap Index가 크게 유리한 것을 확인할 수 있다.

select /*+ gather_plan_statistics index(t_bitmap) */
  count(*)
from
  t_bitmap
where
  c_btree1 = 1 and
  c_btree2 = 1
;

--------------------------------------------------------------------
| Id  | Operation                    | Name              | Buffers |
--------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |                   |    1004 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T_BITMAP          |    1004 |
|*  3 |    INDEX RANGE SCAN          | T_BITMAP_BTREE_N1 |       4 |
--------------------------------------------------------------------

-- Index Combination을 시도 이용해 Bitmap Conversion 시도  

select /*+ gather_plan_statistics
    index_combine(t_bitmap t_bitmap(c_btree1) t_bitmap(c_btree2)) */
  count(*)
from
  t_bitmap
where
  c_btree1 = 1 and
  c_btree2 = 1
;

------------------------------------------------------------------------
| Id  | Operation                        | Name              | Buffers |
------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                  |                   |      25 |
|   2 |   BITMAP CONVERSION COUNT        |                   |      25 |
|   3 |    BITMAP AND                    |                   |      25 |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                   |       4 |
|*  5 |      INDEX RANGE SCAN            | T_BITMAP_BTREE_N1 |       4 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                   |      21 |
|*  7 |      INDEX RANGE SCAN            | T_BITMAP_BTREE_N2 |      21 |
------------------------------------------------------------------------

select /*+ gather_plan_statistics index(t_bitmap) */
  count(*)
from
  t_bitmap
where
  c_bitmap1 = 1 and
  c_bitmap2 = 1
;

---------------------------------------------------------------------
| Id  | Operation                    | Name               | Buffers |
---------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |                    |       7 |
|   2 |   BITMAP CONVERSION COUNT    |                    |       7 |
|   3 |    BITMAP AND                |                    |       7 |
|*  4 |     BITMAP INDEX SINGLE VALUE| T_BITMAP_BITMAP_N1 |       3 |
|*  5 |     BITMAP INDEX SINGLE VALUE| T_BITMAP_BITMAP_N2 |       4 |
---------------------------------------------------------------------
                      
                                             
비록 Bitmap Index가 최적의 성능을 보여주지만, B*Tree Index Combination Bitmap Conversion을 잘 이용하면 Btree Index만으로도 상당한 성능을 보장받을 수 있다는 것을 알 수 있다.(이러한 B*Tree Index Combination Bitmap Conversion 기능을 다른 DBMS에서는 Dynamic Bitmap Index라고 부르기도 한다)

COUNT(*) 쿼리가 조금 빨라진 것이 큰 의미가 있을까? 그런 경우도 있고 아닌 경우도 있을 것이다. 상황에 따라 다르다.


위의 테스트 결과와 Bitmap Index가 DML에 매우 취약하다는 사실을 복합해서 생각해보면 Bitmap Index의 활용 용도가 생각보다 매우 제한적이라는 사실을 알게 될 것이다.

혹시나 OLTP 환경에서 Bitmap Index를 사용할 계획에 있다면 포기해야할 것이고, OLAP 환경이라면 Bitmap Index를 원래 설계 목적대로 최적으로 사용하고 있는지 반문해볼 일이다.


신고
Trackback 0 : Comments 5
  1. oracler 2008.06.02 22:03 신고 Modify/Delete Reply

    이 글의 이슈와 무관한 질문을 해도 될까요...
    글중의 index hint 를 보니 갑자기 생각나서 질문 드리는건데요..

    힌트는 syntax 가 틀리면 그냥 무시하고 수행하는것으로
    (힌트가 애초에 없었던것처럼 아무런 영향을 미치지 않고) 알고 있는데,
    실제론 그 syntax 틀린 엉터리 힌트가 무시되는것이 아니고 참조되는 경우가 있어서
    과연 어떤 현상인지 궁금합니다.

    가령, 힌트가 없는 상태에서는 플랜에 ix_a_1 라는 인덱스를 사용하지 않다가
    여기에 indexx(a ix_a_1) 를 추가하면 a_1 인덱스를 타는것으로 플랜이 나옵니다.
    index 가 아니라 indexx 라고 힌트를 엉터리로 썼는데도 플랜에 분명히 영향을 미치는것이죠.
    세가지중의 하나겠죠. 저의 착각, 버그, 기술적인 당연한 이유.

    플랜 테스트할때 힌트를 완전히 지우기는 뭐해서 일부러 이렇게 syntax 틀리게 해서
    잠시 없는것처럼 대피시켜서 테스트할때가 있는데
    그럴때 플랜에 영향을 주면 당혹스러웠던적이 있습니다.
    오라클이 hint syntax 가 틀리면 완전히 무시하는건지 아니면 오브젝트 이름 정도는 참조를 하는건지...
    감사합니다.

  2. 욱짜 2008.06.03 10:01 신고 Modify/Delete Reply

    오라클의 버그가 아닌 한 그런 현상은 없을 거 같은데요... 혹시 재현가능한 테스트 스크립트를 만드실 수 있나요?

  3. oracler 2008.06.03 23:19 신고 Modify/Delete Reply

    음...문제는 제가 정확한 원인을 모르는 상태라는거죠.
    플랜에 영향을 주는 팩터가 워낙 다양한 마당에
    원인을 모르는 상태이므로 스크립트로는 그 상황을 못보여드릴듯..
    차후에 그 현상이 또다시 발생했을때 말씀드리겠습니다.
    물리적으로 전혀 다른 DB (다른 싸이트)에서 다른 유형의 SQL 로 여러번 발생했습니다.

  4. 박준연 2009.01.16 15:43 신고 Modify/Delete Reply

    마침 스터디 내용 중에 포함되던 것인데 큰 도움이 되었습니다.
    한가지 궁금한 것이 있어서 질문 드립니다.
    비트맵 컨버전을 수행하여 얻는 쿼리 성능 개선 효과도 있지만,
    반면 특정 상황에서는 오히려 성능저하의 원인이 될 수도 있다고 하는데,
    어떤 상황에서 성능저하의 원인이 되는지 알 수 있을까요?
    감사합니다!

  5. 욱짜 2009.01.16 16:47 신고 Modify/Delete Reply

    우선 용어를 다시 정의해야겠네요.
    위의 글에서 Bitmap Conversion이라고 표현한 것은 정확하게 표현하면 (B*Tree) Index Combination으로 불러야 할 거 같네요.

    질문 내용에 대한 답변은 이번에 출간된 [Optimizing Oracle Optimizer]에 잘 설명되어 있습니다.
    Page 200 ~ 209를 확인해보기 바랍니다. 성능 개선과 저하 여부는 Index를 읽는 과정에서 발생하는 일량과 Table로 방문하는 일량이 어떻게 줄어들고 늘어나느냐에 따라 결정됩니다.

Write a comment

티스토리 툴바