8. 비트맵 인덱스 (by jongmali) [2009.07.10]
I 시작하면서
- 데이터 베이스 조건 (테스트 db version : oracle 10.2.0.3)
1. alter system set db_file_multiblock_read_count=8;
2. exec dbms_stats.delete_system_stats;
3. alter session set "_optimizer_cost_model"=io;
- 테이블 스페이스 조건
1. 8K 블럭 사이즈
2. LMT(Locally Management Tablespace) UNIFORM SIZE 1M
3. SEGMENT SAPCE NAMAGEMENT MANUAL
- 테스트 테이블, 비트맵 인덱스 생성
1. n1 : 20개의 distinct 값. 테이블 전체에 골고로 흩어져 있다(scattered)
2. n2 : 20개의 distinct 값. 특정값의 모든 로우가 500개 그룹에 모여 있다(clustered)
3. n3, n4 : 위의 컬럼과 비슷하지만 25개의 distinct 값. 비트맵 인덱스 생성.
4. n5, n6 : 위의 컬럼과 같고, B-tree 인덱스 생성.
5. 크기를 증가시키고자 PCTFREE를 매우 크게 설정.
drop table t1;
begin
begin execute immediate 'purge recyclebin';
exception when others then null;
end;
begin execute immediate 'begin dbms_stats.delete_system_stats; end;';
exception when others then null;
end;
begin execute immediate 'alter session set "_optimizer_cost_model"=io';
exception when others then null;
end;
end;
/
create table t1
pctfree 70
pctused 30
nologging
as
select
mod((rownum-1),20) n1, -- 20 values, scattered
trunc((rownum-1)/500) n2, -- 20 values, clustered
--
mod((rownum-1),25) n3, -- 25 values, scattered
trunc((rownum-1)/400) n4, -- 25 values, clustered
--
mod((rownum-1),25) n5, -- 25 values, scattered for btree
trunc((rownum-1)/400) n6, -- 25 values, clustered for btree
--
lpad(rownum,10,'0') small_vc,
rpad('x',220) padding
from
all_objects
where
rownum <= 10000
;
create bitmap index t1_i1 on t1(n1)
nologging
pctfree 90
;
create bitmap index t1_i2 on t1(n2)
nologging
pctfree 90
;
create bitmap index t1_i3 on t1(n3)
nologging
pctfree 90
;
create bitmap index t1_i4 on t1(n4)
nologging
pctfree 90
;
create index t1_i5 on t1(n5)
nologging
pctfree 90
;
create index t1_i6 on t1(n6)
nologging
pctfree 90
;
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
- 테이터의 클러스터링은 비트맵 인덱스 내 리프 블록의 개수에 극적인 영향을 미친다.
(n1 : 60개, n2 : 10개, n3 : 63개, n4 : 9개) - B-tree 인덱스의 크기는 영향을 받지 않는다.
(n5, n6 모두 217개) - 비트맵 인덱스 크기와 관련된 세부항목이 얼마나 직관적이지 못한지 보여준다
(t1_i1, t1_i3는 distinct 갯수가 증가할수록 리프블록의 개수 증가, t1_i2, ti_i4는 distinct 갯수가 증가했지만 오히려 반대 효과) - 테이블이 아주 크지 않으면, 비트맵 인덱스에 대한 distinct_key와 num_rows의 값이 같음을 알 수 있는데,
이것은 규칙에 의한 것이 아니라 우연히 그렇게 된 것이다.(8i 이하에서 모든 경우에 같은 값을 가진다) - 데이터가 흩어진 경우에 num_rows가 distinct_key보다 크다.
(각 키의 비트 문자열이 리프블록에 맞도록 여러 조각으로 쪼개져야 하기 때문) - 비트맵 인덱스의 clustering_factor는 단지 인덱스에 대한 num_rows 값의 복사본이다. clustering_factor는
테이블 내 데이터의 흩어짐과 직접적인 연관성이 없다.
(데이터의 흩어짐은 비트맵 인덱스 엔트리의 크기에 영향을 미친다) - avg_leaf_blocks_per_key는 아직 비트맵 인덱스와 어느 정도 관계가 있다.
(round(leaf_blocks/distinct_keys)) - avg_data_blocks_pers_key는 비트맵 인덱스와 전혀 관계가 없다.
(round(clustering_factor/distinct_keys)로 계산되지만, 비트맵 인덱스의 clustering_factor가 테이블을 표현하지 않는다)
- 몇가지 통계정보와 특히 clustering_factor의 의미가 비트맵 인덱스에 대해서 다르다면, 인덱스 사용의 추정 비용에 영햐을 미치는 것은 무엇일까?
- 같은 distinct 값을 가지는 n3~n6 컬럼에 '컬럼=상수' 쿼리의 autotrace 결과는?
n6 : B-tree Index on clustered column with 25 values
select
small_vc
from t1
where n6 = 2
;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 5600 | 54 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 400 | 5600 | 54 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I6 | 400 | | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
n5 : B-tree Index on scattered column with 25 values
select
small_vc
from t1
where n5 = 2
;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 5600 | 304 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 400 | 5600 | 304 (1)| 00:00:04 |
--------------------------------------------------------------------------
n4 : Bitmap Index on clustered column with 25 values
select
small_vc
from t1
where n4 = 2
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 5600 | 131 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 400 | 5600 | 131 (0)| 00:00:02 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | T1_I4 | | | | |
--------------------------------------------------------------------------------------
n3 : Bitmap Index on scattered column with 25 values
select
small_vc
from t1
where n3 = 2
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 5600 | 133 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 400 | 5600 | 133 (0)| 00:00:02 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | T1_I3 | | | | |
--------------------------------------------------------------------------------------
- B-tree 인덱스를 가진 컬럼에 대한 쿼리에서는 서로 다른 실행계획이 나타났다.
- 비트맵 인덱스의 비용은 거의 같다.(옵티마이저가 비트맵 인덱스에 대해서 어떤 비용 정보도 표시하지 않는다.
오직 10053 트레이스 파일에만 나타나는 결정적으로 유용한 정보) - 비트맵 인덱스의 계산된 비용은 어디에서 비롯된 것일까? 일부는 답변할 수 있지만 나머지는 추정할 수밖에 없다.
인덱스 컴포넌트
- 위의 n3, n4 조건으로 10053 trace 결과
n3 : Bitmap Index on scattered column with 25 values
-----------------------------------------------------
Access Path: index (AllEqRange)
Index: T1_I3
resc_io: 3.00 resc_cpu: 23214
ix_sel: 0.04 ix_sel_with_filters: 0.04
Cost: 3.00 Resp: 3.00 Degree: 0
Access path: Bitmap index - accepted
Cost: 133.31 Cost_io: 133.18 Cost_cpu: 1112318 Sel: 0.04
Not believed to be index-only
Best:: AccessPath: IndexBitmap
Cost: 133.31 Degree: 1 Resp: 133.31 Card: 400.00 Bytes: 0
n4 : Bitmap Index on clustered column with 25 values
-----------------------------------------------------
Access Path: index (AllEqRange)
Index: T1_I4
resc_io: 1.00 resc_cpu: 8171
ix_sel: 0.04 ix_sel_with_filters: 0.04
Cost: 1.00 Resp: 1.00 Degree: 0
Access path: Bitmap index - accepted
Cost: 131.31 Cost_io: 131.18 Cost_cpu: 1097275 Sel: 0.04
Not believed to be index-only
Best:: AccessPath: IndexBitmap
Cost: 131.31 Degree: 1 Resp: 131.31 Card: 400.00 Bytes: 0
--> 책과 다름
- bset_cst(Best.Cost)는 정수가 아니다. 소수점 두자리까지 보고되며, 그 후에 반올림된다.
- 인덱스의 비용(RSC_IO:3(resc_io: 3.00), RSC_IO:1(resc_io: 1.00))은 B-tree 인덱스와 같은 방식으로 유도된다.
- 당장 명확하지는 않지만 쿼리의 최종 비용은 기술된 인덱스 컴포넌트의 비용에 1.1의 팩터를 곱한 결과이다.
(두가지 유형의 인덱스를 가졌을때 B-tree 인덱스가 비트멥 인덱스보다 약간 유리하게,
옵티마이저가 불필요하게 B-tree를 비트맵으로 변환하는 위험성을 줄이는 데 목적이 있다) - 인덱스 t1_i3 사용 : 인덱스의 비용은 3이며, 3.3으로 증가한다. 그러나 best_cst가 116.54이므로
실제 테이블 블록을 억세스하는 비용은 116.54 - 3.3 = 113.24로 추정된다. - 인덱스 t1_i4 사용 : 인덱스의 비용은 1이며, 1.1로 증가한다. 그러나 best_cst가 114.34이므로
실제 테이블 블록을 억세스하는 비용은 114.34 - 1.1 = 113.24로 추정된다.
- 비트맵 인덱스에서 특정한 양의 데이터에 대해서 실제 테이블을 액세스하는 '계산된 비용'은 데이터 군집성(clustering)과
데이터 흩어짐(scattering)에 상관없이 같다. - 비트맵 인덱스와 B-tree 인덱스 사이에 적절한 테스트를 수행하면, 예상 비용이 얼마가 나오든지 수행된 일량은 양쪽 모두 같다.
런타임 엔진은 인덱스에서 소수 블럭을 요청한 후 테이블에서 블록 읽기를 요청한다. 이때 테이블 블록의 개수는 사용된 인덱스에 상관없이 같을 것이다.
- 비트맵 인덱스에서 옵티마이저는 테이블 내 데이터 흩어짐에 대한 중요한 정보를 잃어버렸으므로 데이터 흩어짐에 대한
추측으로서 몇 가지 매직 넘버를 만들어 내야 한다. - B-tree 인덱스를 비트맵 인덱스로 바꾼다면
-> 낮은 비용의 B-tree -> 비트맵 인덱스 : 더 높은 비용을 나타냄 (t1_i6, t1_i4)
-> 높은 비용의 B-tree -> 비트맵 인덱스 : 더 낮은 비용을 나타냄 (t1_i5, t1_i3)
테이블 컴포넌트
- 위와 같은 조건으로 n1, n2 컬럼에 10053 trace 결과 이들 두 조건절에 대한 테이블 관련 비용이 137.99 임을 알 수 있다. (113.24*500/400 에 매우 비슷한 값)
- 옵티마이저는 데이터 흩어짐에 대한 자신의 가정 내에서 매우 일관되게 작동하는것 같다.
- K. Gopalakrishnan에 따르면 옵티마이저는 대상 데이터의 80%가 빈틈없이 모여있고 나머지는 20%에 넓게 흩어져 있다고 가정한다.
- 전체 로우의 80%가 빈틈없이 모여 있으며 나머지 20%의 로우가 테이블 블럭의 나머지에 걸쳐 흩어져 있다고 가정(p.230 표 8-2 참조)
- db_file_multiblock_read_count 값을 변경하면 완전히 일관된 모습은 아니지만, 쿼리의 비용도 마찬가지로 달라진다. (p.231 표 8-3 참조)
II 비트맵 결합
select
small_vc
from
t1
where
n1 = 2 -- one in 20
and n3 = 2 -- one in 25
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 340 | 13 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 20 | 340 | 13 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE| T1_I3 | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| T1_I1 | | | | |
--------------------------------------------------------------------------------------
- 인덱스 스켄비용 : ceiling(60/20) + ceiling(63/25) = 6, 비트맵 비율 1.1에 의해 6.6
- 실행계획의 카디널리티 20
- 80/20 추정을 사용 : 20개의 로우중 4개는 넓게 흩어져 있으며, 나머지 16개는 모여있을 것이다.
평균적으로 블록당 9개의 로우가 존재하므로 16개 로우에 대해서 2개의 블록이 필요, 총 6개 블록이 필요하다. - 총 비용 : round(6.6 + 6) = 13
- 10053 트레이스 파일의 경우 best cost가 12.87로 예상과 매우 비슷하게 나왔다.
- bitmap and를 사용하는 실행계획을 확인할 때 주목해야 할 세부항목은 인덱스의 선택도 순서대로 정렬되는 듯하므로,
선택도가 제일 좋은(로우를 가장 많이 제거하는) 인덱스가 먼저 처리되어야 한다.
select
small_vc
from
t1
where
n2 = 2 -- one in 20
and n4 = 2 -- one in 25
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 340 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 20 | 340 | 9 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE| T1_I4 | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| T1_I2 | | | | |
--------------------------------------------------------------------------------------
--> 책과 다름
- 여기서 사용한 80/20 추정은 대용량 운영환경으로 갈수록 어긋나기 시작한다.
예를 들어, 데이터 크기가 증가하고 사용된 인덱스의 개수가 많아질수록 결과가 달라진다.
낮은 카디널리티
- 테이블에 여러 비트맵 인덱스가 존재한다면, 오라클은 쿼리가 효율적으로 실행되는 데 필요한 만큼 인덱스를 최대한 많이 사용한다.
- 비트맵 인덱스의 리프 블록 세트를 하나 더 스켄하는 비용이 조회해야 할 테이블 블록 개수의 감소량 보다 클 때 사용한다.
ex) 3천6백만 로우, 800M(107,543블록), 6가지의 속성 및 비트맵 인덱스를 가지는 테이블
- 성별 : 2가지
- 눈의 색 : 3가지
- 머리카락 색 : 7가지
- 거주지 : 31가지
- 연령대 : 47가지
- 직업분류 : 79가지
sex = 1
and eyes = 1
and hair = 1
and town = 15
and age = 25
and work = 40
- 인덱스 통계정보는 책 참조 (p.235 표 8-4)
- town, age, work를 지정하여 식별한 로우의 평군 개수를 산출하면, 옵티마이저가 이들 세 컬럼만으로 테이블 방문을
36,000,000/(31*47*79) = 313 로우로 제한하기에 충분하다고 결정할 것이다. 최악의 경우도 313개 테이블 블록을 방문하는 정도이다. - 차선책에 해당하는 hair 인덱스가 테이블 방문 횟수를 0으로 감소시킬지라도 672개의 인덱스 블록을 방문해야 하므로
인덱스 사용의 별 가치가 없다. - 위의 조건의 경우 실제로, 정밀도가 높은 세개의 인덱스 만을 사용한다. 쿼리의 총 비용은 314로 보고되었다.
- 그러나 이들 인덱스에 대해서(blevel + leaf_blocks*유효 인덱스 선택도)의 합계를 보면, 352(164+_114+74)가 된다.
이 합계는 1.1의 배율을 곱해서 테이블 방문 비용을 더하기 전의 값이다. - 최종적으로 보고된 비용은 두 인덱스의 통계정보를 무시하고 가장 비용이 낮은 인덱스의 값을 세 번 사용하는 것으로 생각한다.
추측비용 (목표치는 314) =
74 * 1.1 * 3 + (가장 비용이 낮은 인덱스를 1.1배 증가시켜서 세 번 사용)
0.8 * 313 / 355 + (로우의 80%는 블록 당 335개씩 모여있음)
0.2 * 313 = (로우의 20%는 개별 블록에 흩어져 있음)
244.2 + 62.6 + 0.75 = 307.55 (에러율 2.1%)
- hair 컬럼을 정렬하여 테스트 사례를 재생성한 후 다시 실행해 보면 4개의 인덱스를 선택한다. (비용 336)
- 옵티마이저는 언제나 비용이 가장 낮은 실행계획을 선택해야 한다. 위의 결과에서는 비용이 더 높은 경우(314 -> 336)에도
비트맵 인덱스를 사용하였다. 비트맵 인덱스 비용을 계산하는 코드에 약간의 문제가 있는 것이 분명하다. (p.237 ~238 참고)
NULL 컬럼
- 오라클에게 데이터에 대해서 가능한 많은 정보를 알려주는 것은 언제나 중요하다. NOT NULL 컬럼은 특히 중요한데,
이것은 옵티마이저에게 허용되는 여러 가능성에 큰 차이를 만들 수 있다.
alter table t1 modify n1 not null;
select
small_vc
from
t1
where
n1 != 2 -- one in 20, scattered
and n3 = 3 -- one in 25, scattered
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 380 | 6460 | 130 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 380 | 6460 | 130 (0)| 00:00:02 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP MINUS | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE| T1_I3 | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| T1_I1 | | | | |
--------------------------------------------------------------------------------------
alter table t1 modify n1 null;
select
small_vc
from
t1
where
n1 != 2 -- one in 20, scattered
and n3 = 3 -- one in 25, scattered
;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 380 | 6460 | 127 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 380 | 6460 | 127 (0)| 00:00:02 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP MINUS | | | | | |
| 4 | BITMAP MINUS | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| T1_I3 | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE| T1_I1 | | | | |
|* 7 | BITMAP INDEX SINGLE VALUE | T1_I1 | | | | |
---------------------------------------------------------------------------------------
- 두 번째 실행계획에 bitmap minus 연산이 두번 나타났음을 주목하라.
비트맵 인덱스는 모든 키가 NULL인 엔트리를 포함하기 때문에, 두 번의 minus 연산이 나타난다. - 해당 컬럼이 절대 NULL이 되지 않음을 알고 있다면, 이런 이유에서라도 테이블 정의에 사소한 정보를 더 포함해야 한다.
- 8i는 첫번째 실행계획이 두번째 실행 계획보다 비용이 낮은것으로 계산 되지만,
9i, 10g에서는 추가적인 bitmap minus 단계를 수행함으로써 쿼리의 비용을 낮출 수 있다고 생각하는 듯 하다.
이것은 합리적인 가정일 수 있지만 NULL값을 가진로우가 없다면 비트맵 비용계산에 대한 알고리즘이 완전히 정확하지 않은 상황도 존재하는 것 같다.
BITMAP MINUS
오라클은 bitmap minus를 수행할 때, 먼저 두 번째 비트맵을 취해서 1은 0으로, 0은 1로 각각 바꾼다.
그리고 이렇게 뒤바뀐 bitmap을 사용하여 bitmap and를 수행함으로써 bitmap minus 연산을 처리한다.
- 비트맵 or
create table t1
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 3000
)
select
/*+ ordered use_nl(v2) */
decode(
mod(rownum-1,1000),
0, rownum - 1,
null
) n1,
decode(
mod(rownum-1,1000),
0, rownum - 1,
null
) n2,
lpad(rownum-1,10,'0') small_vc
from
generator v1,
generator v2
where
rownum <= 1000000
;
create bitmap index t1_i1 on t1(n1);
create bitmap index t1_i2 on t1(n2);
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
select
small_vc
from
t1
where
n1 = 50000
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 13 | 1 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | T1_I1 | | | | |
--------------------------------------------------------------------------------------
- n1 컬럼이 NOT NULL인 로우는 정확히 1,000개, 1,000개의 distinct 값, density는 1/1,000이므로
옵티마이저는 카디널리티가 1이라고 결정할 것이다. n1을 n2로 변경해도 마찬가지.
select
small_vc
from
t1
where
n1 = 50000
or n2 = 50000
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1999 | 25987 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1999 | 25987 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP OR | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE| T1_I1 | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| T1_I2 | | | | |
--------------------------------------------------------------------------------------
- 카디널리티 1,999 : 오라클은 NULL을 고려하지 않고 테이블 내 전체 로우의 개수에 density를 적용한 것으로 보인다.
이것은 두 조건의 and 연산에 대한 표준공식을 사용하여 n1 조건에 의한 1,000개(1,000,000 * 1/1,000)의 로우에
n2 조건에 의한 1,000개의 로우를 더한 후, 서로 겹치는 한 개의 로우를 뺀 값이다.
select
small_vc
from
t1
where
n1 = 50000
or (n2 = 50000 and n2 is not null)
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 13000 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1000 | 13000 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP OR | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE| T1_I1 | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| T1_I2 | | | | |
--------------------------------------------------------------------------------------
- is not null 조건을 하나 추가하면, 계산된 카디널리티가 1,000으로 떨어진다.
select
small_vc
from
t1
where
(n1 = 50000 and n1 is not null)
or (n2 = 50000 and n2 is not null)
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 13 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP OR | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE| T1_I1 | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| T1_I2 | | | | |
--------------------------------------------------------------------------------------
- is not null 조건을 하나 더 추가하면, 카디널리티가 정확히 1까지 떨어진다.
III CPU costing
- CPU costing을 활성화했을 때 어떤 일이 일어나는가?
select
/*+ index(t1) */
small_vc
from
t1
where
n4 = 2
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 5600 | 127 (1)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 400 | 5600 | 127 (1)| 00:00:02 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | T1_I4 | | | | |
--------------------------------------------------------------------------------------
select
small_vc
from
t1
where
n6 = 2
;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 5600 | 54 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 400 | 5600 | 54 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I6 | 400 | | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
--> 책과 다름
- 비트맵 쿼리의 비용은 14가 증가 했지만, B-tree 쿼리의 비용은 겨우 1이 증가했다.(p.243, 10.2.0.3에서는 다름)
- B-tree 쿼리의 비용은 약 45개의 테이블 블록 방문을 근거로 계산된 것이며, 비트맵 쿼리의 비용은 약 100개의 테이블 블록
방문을 근거로 계산된 것이지만 실제 테이블 블록을 방문할 때 발생하는 일량은 인덱스에 상관없이 똑같다. - 비용의 차이 대부분은 비트맵에 의한 것이 틀림없다(그렇다고 가정)
– 10053 트레이스
****** trying bitmap/domain indexes ******
Access Path: index (AllEqRange)
Index: T1_I4
resc_io: 1.00 resc_cpu: 8171
ix_sel: 0.04 ix_sel_with_filters: 0.04
Cost: 1.00 Resp: 1.00 Degree: 0
Access path: Bitmap index - accepted
Cost: 126.57 Cost_io: 126.27 Cost_cpu: 1062271 Sel: 0.04
Not believed to be index-only
Best:: AccessPath: IndexBitmap
Cost: 126.57 Degree: 1 Resp: 126.57 Card: 400.00 Bytes: 0
Access Path: index (AllEqRange)
Index: T1_I6
resc_io: 54.00 resc_cpu: 573408
ix_sel: 0.04 ix_sel_with_filters: 0.04
Cost: 54.16 Resp: 54.16 Degree: 1
****** trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange Index: T1_I6
Cost: 54.16 Degree: 1 Resp: 54.16 Card: 400.00 Bytes: 0
- p.244 (추측성 글로서 최근의 버젼과는 맞지 않음)
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 조정에 따른 오차범위에 들어온다)
h3. 비트맵 변환
- 비트맵 인덱스는 본질적으로(정교하게 패키지된) 0과 1의 2차원 배열이다. 배열의 각 컬럼은 인덱스 키의
distinct 값 중 하나에 해당하며, 배열의 각 로우는 테이블 내 특정 로우의 위치에 해당한다.
- 배열의 엔트리를 테이블 엔트리로 변환하는 계산이 bitmap conversion 계산이다.
- bitmap conversion(to rowids) : 배열에서 테이블 쪽으로 변환하는 경우
- bitmap conversion(from rowids) : B-tree to bitmap conversion
{code:sql}
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을 활성화 하면 일부 실행계획이 극적으로 달라진다.
문서에 대하여
- 최초작성자 : 김종원
- 최초작성일 : 2009년 7월 10일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 번역한 '비용기반의 오라클 원리'를 참고하였습니다.*