by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

B*Tree Index Combination




B*Tree Index Combination

B*Tree Index Combination?

● Index Scan을 통해 B*Tree Index 의 Key와 ROWID 값을 읽는다.
● Key 값과 ROWID 값을 Bitmap 값으로 변환한다.
● Bitmap 연산을 통해 원하는 결과(Bitmap 값)를 도출한다.
● Bitmap 값을 다시 ROWID 로 변환해서 원하는 Data를 추출한다.

B*Tree Index에 대한 Index Combination 은 B*Tree Index 를동적으로 Bitmap Index 로 변환하는 과정을 거치며, 이런 의미에서
다른 DBMS에서는 동일한 기능을 Dynamic BitMap Index 라는 이름으로 부르기도 한다.

B*Tree Index Combination은 B*Tree Index을 마치 Bitmap Index 처럼 쓸 수 있는 기능을 제공한다는 의미에서
Index의 적용 범위를 한 단계 확장시켰다고 볼 수 있다.
Bitmap Index의 고질적인 문제인 DML Blocking 문제가 발생하기 때문에 Bitmap Index의 대안으로 사용할 수 있다.

SQL> create table t1 (c1 int,c2 int,c3 int);
테이블이 생성되었습니다.

--Column C1과 Column C2가 서로의 반대의 순서가 되게끔 Data생성
SQL> insert into t1
  2  select mod(level,10)+1,10-mod(level,10)-1,level
  3  from dual
  4  connect by level <= 10000;
10000 개의 행이 만들어졌습니다.

--인덱스생성
SQL> create index t1_n1 on t1(c1);
인덱스가 생성되었습니다.

SQL> create index t1_n2 on t1(c2);
인덱스가 생성되었습니다.

SQL> create index t1_n3 on t1(c3);
인덱스가 생성되었습니다.

SQL> exec dbms_stats.gather_table_stats('bshman','t1');
PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> explain plan for
  2  select count(c3)
  3  from t1
  4  where c1=1
  5  and c2=1;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |   900 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------
Column C1과 C2가 모두 조건절에 사용된 경우 B*Tree Index Combination을 선택한다고하였는데
선택하지 않았다...

SQL> select /*+ gather_plan_statistics index(t1 t1(c1)) */
  2  count(c3)
  3  from t1
  4  where c1=1
  5  and c2=1;

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |       |      1 |      1 |      1 |00:00:00.01 |      24 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |    100 |      0 |00:00:00.01 |      24 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |      1 |   1000 |   1000 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------

SQL> select /*+ gather_plan_statistics index_combine(t1 t1(c1) t1(c2)) */
  2    count(c3)
  3  from t1
  4    where c1=1
  5    and c2=1;

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name  | Starts | E-Rows | A-Rows | A-Time   | Buffers   |
-----------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                   |       |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | T1    |      1 |    100 |      0 |00:00:00.01 |       7 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |       |      1 |        |      0 |00:00:00.01 |       7 |
|   4 |     BITMAP AND                    |       |      1 |        |      0 |00:00:00.01 |       7 |
|   5 |      BITMAP CONVERSION FROM ROWIDS|       |      1 |        |      1 |00:00:00.01 |       3 |
|*  6 |       INDEX RANGE SCAN            | T1_N1 |      1 |        |   1000 |00:00:00.01 |       3 |
|   7 |      BITMAP CONVERSION FROM ROWIDS|       |      1 |        |      1 |00:00:00.01 |       4 |
|*  8 |       INDEX RANGE SCAN            | T1_N2 |      1 |        |   1000 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------

옵티마이저의 선택을 확인해보자...

SQL> drop table t1 purge;
테이블이 삭제되었습니다.

SQL> create table t1(c1 int,c2 int,c3 int);
테이블이 생성되었습니다.


SQL> insert into t1
  2  select mod(level,10)+1,mod(level,10),level
  3  from dual
  4  connect by level <= 10000;

10000 개의 행이 만들어졌습니다.

SQL> create index t1_n1 on t1(c1);
인덱스가 생성되었습니다.

SQL> create index t1_n2 on t1(c2);
인덱스가 생성되었습니다.

SQL> exec dbms_stats.gather_table_stats('bshman','t1',method_opt=>'for all colum
ns size 1',cascade=>true);
PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> explain plan for
  2  select count(c3)
  3  from t1
  4  where c1=1
  5  and c2=1;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |   900 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select /*+ gather_plan_statistics index(t1 t1(c1)) */
  2  count(c3)
  3  from t1
  4  where c1=1
  5  and c2=1;
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |       |      1 |      1 |      1 |00:00:00.01 |      24 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |    100 |      0 |00:00:00.01 |      24 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |      1 |   1000 |   1000 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------

SQL> select /*+ gather_plan_statistics index_combine(t1 t1(c1) t1(c2)) */
  2    count(c3)
  3  from t1
  4    where c1=1
  5    and c2=1;
-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name  | Starts | E-Rows | A-Rows | A-Time   | Buffers   |
-----------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                   |       |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | T1    |      1 |    100 |      0 |00:00:00.01 |       7 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |       |      1 |        |      0 |00:00:00.01 |       7 |
|   4 |     BITMAP AND                    |       |      1 |        |      0 |00:00:00.01 |       7 |
|   5 |      BITMAP CONVERSION FROM ROWIDS|       |      1 |        |      1 |00:00:00.01 |       3 |
|*  6 |       INDEX RANGE SCAN            | T1_N1 |      1 |        |   1000 |00:00:00.01 |       3 |
|   7 |      BITMAP CONVERSION FROM ROWIDS|       |      1 |        |      1 |00:00:00.01 |       4 |
|*  8 |       INDEX RANGE SCAN            | T1_N2 |      1 |        |   1000 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------
옵티마이저는 Table Full을 선택하였다.
책에서는 B*Tree가 Logical Read가 많다고 했지만 실제로 테스트결과는 
B*Tree가 좀더 좋았다.

B*Tree Index Combination 이 발생한 경우에 대한 Plan Statistics 정보에서 Memory 사용량
정보가 추가로 출력 될 수 있다는 것에 유의해야 한다. Bitmap Conversion을 수행하기 위해
내부적으로 정렬(Sort Order by)과 Bitmap 연산이 발생하기 때문에 추가적인 Memory 사용이 불가피하다.

0Mem,1Mem,User-Mem 항목의 의미에 대해서는 제2장에서 참조.

B*Tree Index Combination 의 뛰어난 결과에 고무되었겠지만, 몇 가지 함정이 있다는 사실이있다.

● 우선 B*Tree Index Combination은 OLTP환경에서 Bitmap Index가 가진 장점을 흉내내기 위해 고안된것이라는 것을 명심하자. Bitmap Index를 대신할 수 있는 것은 아니다.OLTP 환경에서는 여전히 Bitmap Index의 사용이 필수 불가결하다.
● B*Tree Index Combination 이 항상 성능에 유리한 것은 아니다. 여러 개의 Index를 사용하는 과정에서 Table 방문 회수를 상당히 줄일수 있는 경우에만 유리하다고 할수있다.
● B*Tree Index Combination은 내부적으로 추가적인 연산을 수행하기 때문에 좀 더 많은 Memory 사용을 필요로 한다. 같은 이유로 좀 더 많은 CPU 자원을 필요로 한다. 이것이 암시하는 것은 Logical reads 만이 성능 개선의 판단 기준이 아니라는것이다. Logical Read는 줄지만 Memory와 CPU 자원을 더 많이 사용한다면 판단은 좀 더 어려워진다.

Hints And Parameters
B*Tree Index Combination은 Index_COMBINE Hint를 통해서 제어한다.

select /*+ index_combine(t1) */ c1 from t1;
select /*+ index_combine(t1 t1_n1 t1_n2 */ c1 from t1;
select /*+ index_combine(t1 t1(c1) t1(c2) */ c1 from t1;

B*Tree Index Combination의 동작여부는 _B_TREE_BITMAP_PLANS Parameter를 이용해서 제어할수있다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.