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

8. 비트맵 인덱스




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로 추정된다.
  1. 비트맵 인덱스에서 특정한 양의 데이터에 대해서 실제 테이블을 액세스하는 '계산된 비용'은 데이터 군집성(clustering)과
    데이터 흩어짐(scattering)에 상관없이 같다.
  2. 비트맵 인덱스와 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로 예상과 매우 비슷하게 나왔다.
  1. 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 |       |       |            |          |
--------------------------------------------------------------------------------------
--> 책과 다름
  1. 여기서 사용한 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)
  1. 옵티마이저는 언제나 비용이 가장 낮은 실행계획을 선택해야 한다. 위의 결과에서는 비용이 더 높은 경우(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일
  • 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 (주)비투엔컬설팅에서 번역한 '비용기반의 오라클 원리'를 참고하였습니다.

문서정보

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