비용기반의 오라클 원리 (2009년)
비트맵인덱스 - 시작하면서 0 0 31,939

by 구루비스터디 비트맵 인덱스 bitmap index [2023.09.23]


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 참조)


"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4417

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입