꿈꾸는 개발자, DBA 커뮤니티 구루비

IV 재미있는 사례들

다중컬럼 인덱스

비트맵 인덱스 사용의 가장 중요한 이점은 이들의 임의의 방식으로 결합할 수 있다는 것이므로, 인덱스 내부에서 컬럼을 미리 결합시키는 것은 별다른 이득이 없다.
두 개의 컬럼이 항상 동시에 동시에 사용될 수도 있다.
컬럼에 대한 distinct 값이 테이블 전체에 분산된 방식에 따라, 두 컬럼에 대한 단일 비트맵 인덱스가 개별적인 두 개의 비트맵 인덱스 크기의 합보다 실제로 더 작을 수 있다.
다중 컬럼 인덱스와 관련된 계산식은 지금까지 보았던 것과 같다.

비트맵 조인 인덱스

9i에서 비트맵 인덱스의 기능향상 중 하나는 비트맵 조인 인덱스의 추가이다.


create bitmap index fct_dim_name on fact_table(dim.dim_name)
from
	dim_table	dim,
	fact_table	fct
where
	dim.id = fct.dim_id
;

create bitmap index fct_dim_par on fact_table(dim.par_name)
from
	dim_table	dim,
	fact_table	fct
where
	dim.id = fct.dim_id
;

첫번째 예제는 매우 큰 팩트 테이블에 긴 디멘션명을 사용하는 인덱스를 생성하지만, 이 디멘션 명이 팩트 테이블에 수백만번 저장되지는 않는다.
두번째 예제는 인덱스는 디멘션 테이블의 속성에 대한 쿼리를 통해서 팩트 테이블을 엑세스할 수 있다.
이 속성은 디멘션 ID보다 아주 적은 distinct 값을 가지므로, 인덱스가 매우 작으며 더 유용하다.


select
	count(fct.id)
from
	dim_table	dim,
	fact_table	fct
where
	dim.par_name = 'Parent_001'
and	fct.dim_id = dim.id
;
-----------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |     9 |  2149 |
|   1 |  SORT AGGREGATE               |             |     1 |     9 |       |
|   2 |   TABLE ACCESS BY INDEX ROWID | FACT_TABLE  | 10000 | 90000 |  2149 |
|   3 |    BITMAP CONVERSION TO ROWIDS|             |       |       |       |
|*  4 |     BITMAP INDEX SINGLE VALUE | FCT_DIM_PAR |       |       |       |
-----------------------------------------------------------------------------

이 쿼리가 10,000개의 로우를 리턴할 것이고 결정했다.
80/20 분할을 적용하여 계산하면 2,242개의 블럭을 방문해야 한다.
(이 정도는 db_file_multiblock_read_count 조정에 따른 오차범위에 들어온다)

비트맵 변환

비트맵 인덱스는 본질적으로(정교하게 패키지된) 0과 1의 2차원 배열이다. 배열의 각 컬럼은 인덱스 키의 distinct 값 중 하나에 해당하며, 배열의 각 로우는 테이블 내 특정 로우의 위치에 해당한다.
배열의 엔트리를 테이블 엔트리로 변환하는 계산이 bitmap conversion 계산이다.
bitmap conversion(to rowids) : 배열에서 테이블 쪽으로 변환하는 경우
bitmap conversion(from rowids) : B-tree to bitmap conversion


select
	small_vc
from
	t1
where
	n1 = 33
and	n2 = 21
;
--------------------------------------------------------------------------
| Id  | Operation                        | Name  | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |       |   400 |  6800 |   183 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1    |   400 |  6800 |   183 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |       |       |       |       |
|   3 |    BITMAP AND                    |       |       |       |       |
|   4 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |       |
|*  5 |      INDEX RANGE SCAN            | T1_I1 |       |       |    41 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |       |
|*  7 |      INDEX RANGE SCAN            | T1_I2 |       |       |    41 |
--------------------------------------------------------------------------

옵티마이저가 사용한 계산식은 (테이블을 방문하기 전의)index range scan 비용, 두 조건절의 선택도 결합, 그리고 비트맵과 관련된 80/20 분할 규칙 등 몇 가지를 단순히 모아놓은 것이다.
p.248 참고. 억지로 계산식을 끼워 맞춘듯한..
오라클이 비트맵 엔트리와 rowid를 서로 변환할 수 있다면, 실행계획의 어떤 지점에서도 이 변환을 수행할 수 있다.


select
	d1,
	count(*)
from
	t1
where
	n1 = 2
and	d1 between to_date('&m_today', 'DD-MON-YYYY')
	       and to_date('&m_future','DD-MON-YYYY')
group by
	d1
;
m_today의 값을 입력하십시오: 20090101
구   8: and     d1 between to_date('&m_today', 'DD-MON-YYYY')
신   8: and     d1 between to_date('20090101', 'DD-MON-YYYY')
m_future의 값을 입력하십시오: 20090131
구   9:                and to_date('&m_future','DD-MON-YYYY')
신   9:                and to_date('20090131','DD-MON-YYYY')
------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |   394 |  4334 |    57 |
|   1 |  HASH GROUP BY                  |                  |   394 |  4334 |    57 |
|*  2 |   FILTER                        |                  |       |       |       |
|*  3 |    VIEW                         | index$_join$_001 |   500 |  5500 |    37 |
|*  4 |     HASH JOIN                   |                  |       |       |       |
|   5 |      BITMAP CONVERSION TO ROWIDS|                  |   500 |  5500 |     2 |
|*  6 |       BITMAP INDEX RANGE SCAN   | T1_D1            |       |       |       |
|   7 |      BITMAP CONVERSION TO ROWIDS|                  |   500 |  5500 |    28 |
|*  8 |       BITMAP INDEX SINGLE VALUE | T1_N1            |       |       |       |
------------------------------------------------------------------------------------

두개의 비트맵 인덱스로 출발하여, 차례대로 각각의 인덱스에서 약간의 리프 블록 데이터를 얻은 후,그 결과를 메모리내 B-tree 인덱스로 효과적으로 변환한다.
두개의 B-tree 인덱스 조각을 얻으면, 이들 사이에 인덱스 해시 조인을 수행할 수 있다.

요약

비트맵 인덱스는 데이터의 흩어짐에 대한 정보가 없으므로, 옵티마이저는 일부 숫자 값을 만들어 내야 한다. (일부 쿼리에 문제가 발생할 수 밖에 없다)
CPU costing을 활성화 하면 일부 실행계획이 극적으로 달라진다.