h2.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 자원을 더 많이 사용한다면 판단은 좀 더 어려워진다.
h5.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를 이용해서 제어할수있다.