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

I 시작하면서

데이터 베이스 조건 (테스트 db version : oracle 10.2.0.3)

alter system set db_file_multiblock_read_count=8;
exec dbms_stats.delete_system_stats;
alter session set "_optimizer_cost_model"=io;

테이블 스페이스 조건

8K 블럭 사이즈
LMT(Locally Management Tablespace) UNIFORM SIZE 1M
SEGMENT SAPCE NAMAGEMENT MANUAL

테스트 테이블, 비트맵 인덱스 생성

n1 : 20개의 distinct 값. 테이블 전체에 골고로 흩어져 있다(scattered)
n2 : 20개의 distinct 값. 특정값의 모든 로우가 500개 그룹에 모여 있다(clustered)
n3, n4 : 위의 컬럼과 비슷하지만 25개의 distinct 값. 비트맵 인덱스 생성.
n5, n6 : 위의 컬럼과 같고, B-tree 인덱스 생성.
크기를 증가시키고자 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 참조)