비용기반의 오라클 원리 (2009년)
비트맵 인덱스 0 0 32,545

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


{toc:style=disc|indent=20px|minLevel=2|maxLevel=3} \\ h2. I 시작하면서 \\ h6. 데이터 베이스 조건 (테스트 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; \\ h6. 테이블 스페이스 조건 # 8K 블럭 사이즈 # LMT(Locally Management Tablespace) UNIFORM SIZE 1M # SEGMENT SAPCE NAMAGEMENT MANUAL \\ h6. 테스트 테이블, 비트맵 인덱스 생성 # n1 : 20개의 distinct 값. 테이블 전체에 골고로 흩어져 있다(scattered) # n2 : 20개의 distinct 값. 특정값의 모든 로우가 500개 그룹에 모여 있다(clustered) # n3, n4 : 위의 컬럼과 비슷하지만 25개의 distinct 값. 비트맵 인덱스 생성. # n5, n6 : 위의 컬럼과 같고, B-tree 인덱스 생성. # 크기를 증가시키고자 PCTFREE를 매우 크게 설정. \\ {code:sql} 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; / {code} \\ | !http://www.gurubee.net/wiki/download/3637265/img1.JPG! | \\ * 테이터의 클러스터링은 비트맵 인덱스 내 리프 블록의 개수에 극적인 영향을 미친다. ** (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 결과는? {code:sql} 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 | | | | | -------------------------------------------------------------------------------------- {code} \\ - B-tree 인덱스를 가진 컬럼에 대한 쿼리에서는 서로 다른 실행계획이 나타났다. - 비트맵 인덱스의 비용은 거의 같다.(옵티마이저가 비트맵 인덱스에 대해서 어떤 비용 정보도 표시하지 않는다. - 오직 10053 트레이스 파일에만 나타나는 결정적으로 유용한 정보) - 비트맵 인덱스의 계산된 비용은 어디에서 비롯된 것일까? 일부는 답변할 수 있지만 나머지는 추정할 수밖에 없다. \\ h3. 인덱스 컴포넌트 - 위의 n3, n4 조건으로 10053 trace 결과 \\ {code:sql} 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 --> 책과 다름 {code} \\ * 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로 추정된다. \\ {info} * 비트맵 인덱스에서 특정한 양의 데이터에 대해서 실제 테이블을 액세스하는 '계산된 비용'은 데이터 군집성(clustering)과 데이터 흩어짐(scattering)에 상관없이 같다. * 비트맵 인덱스와 B-tree 인덱스 사이에 적절한 테스트를 수행하면, 예상 비용이 얼마가 나오든지 수행된 일량은 양쪽 모두 같다. * 런타임 엔진은 인덱스에서 소수 블럭을 요청한 후 테이블에서 블록 읽기를 요청한다. 이때 테이블 블록의 개수는 사용된 인덱스에 상관없이 같을 것이다. {info} \\ * 비트맵 인덱스에서 옵티마이저는 테이블 내 데이터 흩어짐에 대한 중요한 정보를 잃어버렸으므로 데이터 흩어짐에 대한 추측으로서 몇 가지 매직 넘버를 만들어 내야 한다. * B-tree 인덱스를 비트맵 인덱스로 바꾼다면 ** -> 낮은 비용의 B-tree -> 비트맵 인덱스 : 더 높은 비용을 나타냄 (t1_i6, t1_i4) ** -> 높은 비용의 B-tree -> 비트맵 인덱스 : 더 낮은 비용을 나타냄 (t1_i5, t1_i3) \\ h3. 테이블 컴포넌트 - 위와 같은 조건으로 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 참조) \\ h2. II 비트맵 결합 {code:sql} 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 | | | | | -------------------------------------------------------------------------------------- {code} \\ - 인덱스 스켄비용 : 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를 사용하는 실행계획을 확인할 때 주목해야 할 세부항목은 인덱스의 선택도 순서대로 정렬되는 듯하므로, 선택도가 제일 좋은(로우를 가장 많이 제거하는) 인덱스가 먼저 처리되어야 한다. \\ {code:sql} 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 | | | | | -------------------------------------------------------------------------------------- --> 책과 다름 {code} \\ * 여기서 사용한 80/20 추정은 대용량 운영환경으로 갈수록 어긋나기 시작한다. * 예를 들어, 데이터 크기가 증가하고 사용된 인덱스의 개수가 많아질수록 결과가 달라진다. \\ h3. 낮은 카디널리티 - 테이블에 여러 비트맵 인덱스가 존재한다면, 오라클은 쿼리가 효율적으로 실행되는 데 필요한 만큼 인덱스를 최대한 많이 사용한다. - 비트맵 인덱스의 리프 블록 세트를 하나 더 스켄하는 비용이 조회해야 할 테이블 블록 개수의 감소량 보다 클 때 사용한다. \\ ex) 3천6백만 로우, 800M(107,543블록), 6가지의 속성 및 비트맵 인덱스를 가지는 테이블 - 성별 : 2가지 - 눈의 색 : 3가지 - 머리카락 색 : 7가지 - 거주지 : 31가지 - 연령대 : 47가지 - 직업분류 : 79가지 \\ {code:sql} sex = 1 and eyes = 1 and hair = 1 and town = 15 and age = 25 and work = 40 {code} \\ - 인덱스 통계정보는 책 참조 (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의 배율을 곱해서 테이블 방문 비용을 더하기 전의 값이다. - 최종적으로 보고된 비용은 두 인덱스의 통계정보를 무시하고 가장 비용이 낮은 인덱스의 값을 세 번 사용하는 것으로 생각한다. \\ {code:sql} 추측비용 (목표치는 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%) {code} \\ - hair 컬럼을 정렬하여 테스트 사례를 재생성한 후 다시 실행해 보면 4개의 인덱스를 선택한다. (비용 336) \\ * 옵티마이저는 언제나 비용이 가장 낮은 실행계획을 선택해야 한다. 위의 결과에서는 비용이 더 높은 경우(314 -> 336)에도 비트맵 인덱스를 사용하였다. * 비트맵 인덱스 비용을 계산하는 코드에 약간의 문제가 있는 것이 분명하다. (p.237 ~238 참고) \\ h3. NULL 컬럼 - 오라클에게 데이터에 대해서 가능한 많은 정보를 알려주는 것은 언제나 중요하다. NOT NULL 컬럼은 특히 중요한데, 이것은 옵티마이저에게 허용되는 여러 가능성에 큰 차이를 만들 수 있다. \\ {code:sql} 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 | | | | | --------------------------------------------------------------------------------------- {code} \\ - 두 번째 실행계획에 bitmap minus 연산이 두번 나타났음을 주목하라. - 비트맵 인덱스는 모든 키가 NULL인 엔트리를 포함하기 때문에, 두 번의 minus 연산이 나타난다. - 해당 컬럼이 절대 NULL이 되지 않음을 알고 있다면, 이런 이유에서라도 테이블 정의에 사소한 정보를 더 포함해야 한다. - 8i는 첫번째 실행계획이 두번째 실행 계획보다 비용이 낮은것으로 계산 되지만, 9i, 10g에서는 추가적인 bitmap minus 단계를 수행함으로써 쿼리의 비용을 낮출 수 있다고 생각하는 듯 하다. - 이것은 합리적인 가정일 수 있지만 NULL값을 가진로우가 없다면 비트맵 비용계산에 대한 알고리즘이 완전히 정확하지 않은 상황도 존재하는 것 같다. \\ {info:title="BITMAP MINUS"} * 오라클은 bitmap minus를 수행할 때, 먼저 두 번째 비트맵을 취해서 1은 0으로, 0은 1로 각각 바꾼다. * 그리고 이렇게 뒤바뀐 bitmap을 사용하여 bitmap and를 수행함으로써 bitmap minus 연산을 처리한다. {info} \\ - 비트맵 or {code:sql} 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 | | | | | -------------------------------------------------------------------------------------- {code} \\ - n1 컬럼이 NOT NULL인 로우는 정확히 1,000개, 1,000개의 distinct 값, density는 1/1,000이므로 옵티마이저는 카디널리티가 1이라고 결정할 것이다. n1을 n2로 변경해도 마찬가지. \\ {code:sql} 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 | | | | | -------------------------------------------------------------------------------------- {code} \\ - 카디널리티 1,999 : 오라클은 NULL을 고려하지 않고 테이블 내 전체 로우의 개수에 density를 적용한 것으로 보인다. - 이것은 두 조건의 and 연산에 대한 표준공식을 사용하여 n1 조건에 의한 1,000개(1,000,000 * 1/1,000)의 로우에 n2 조건에 의한 1,000개의 로우를 더한 후, 서로 겹치는 한 개의 로우를 뺀 값이다. \\ {code:sql} 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 | | | | | -------------------------------------------------------------------------------------- {code} \\ - is not null 조건을 하나 추가하면, 계산된 카디널리티가 1,000으로 떨어진다. \\ {code:sql} 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 | | | | | -------------------------------------------------------------------------------------- {code} \\ - is not null 조건을 하나 더 추가하면, 카디널리티가 정확히 1까지 떨어진다. \\ h2. III CPU costing - CPU costing을 활성화했을 때 어떤 일이 일어나는가? \\ {code:sql} 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 | ------------------------------------------------------------------------------------- --> 책과 다름 {code} \\ - 비트맵 쿼리의 비용은 14가 증가 했지만, B-tree 쿼리의 비용은 겨우 1이 증가했다.(p.243, 10.2.0.3에서는 다름) - B-tree 쿼리의 비용은 약 45개의 테이블 블록 방문을 근거로 계산된 것이며, 비트맵 쿼리의 비용은 약 100개의 테이블 블록 방문을 근거로 계산된 것이지만 실제 테이블 블록을 방문할 때 발생하는 일량은 인덱스에 상관없이 똑같다. - 비용의 차이 대부분은 비트맵에 의한 것이 틀림없다(그렇다고 가정) \\ h6. 10053 트레이스 {code:sql} ****** 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 {code} - p.244 (추측성 글로서 최근의 버젼과는 맞지 않음) \\ h2. IV 재미있는 사례들 h3. 다중컬럼 인덱스 - 비트맵 인덱스 사용의 가장 중요한 이점은 이들의 임의의 방식으로 결합할 수 있다는 것이므로, 인덱스 내부에서 컬럼을 미리 결합시키는 것은 별다른 이득이 없다. - 두 개의 컬럼이 항상 동시에 동시에 사용될 수도 있다. - 컬럼에 대한 distinct 값이 테이블 전체에 분산된 방식에 따라, 두 컬럼에 대한 단일 비트맵 인덱스가 개별적인 두 개의 비트맵 인덱스 크기의 합보다 실제로 더 작을 수 있다. - 다중 컬럼 인덱스와 관련된 계산식은 지금까지 보았던 것과 같다. \\ h6. 비트맵 조인 인덱스 - 9i에서 비트맵 인덱스의 기능향상 중 하나는 비트맵 조인 인덱스의 추가이다. {code:sql} 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 ; {code} \\ - 첫번째 예제는 매우 큰 팩트 테이블에 긴 디멘션명을 사용하는 인덱스를 생성하지만, 이 디멘션 명이 팩트 테이블에 수백만번 저장되지는 않는다. - 두번째 예제는 인덱스는 디멘션 테이블의 속성에 대한 쿼리를 통해서 팩트 테이블을 엑세스할 수 있다. - 이 속성은 디멘션 ID보다 아주 적은 distinct 값을 가지므로, 인덱스가 매우 작으며 더 유용하다. \\ {code:sql} 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 | | | | ----------------------------------------------------------------------------- {code} \\ - 이 쿼리가 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 | -------------------------------------------------------------------------- {code} \\ - 옵티마이저가 사용한 계산식은 (테이블을 방문하기 전의)index range scan 비용, 두 조건절의 선택도 결합, 그리고 비트맵과 관련된 80/20 분할 규칙 등 몇 가지를 단순히 모아놓은 것이다. - p.248 참고. 억지로 계산식을 끼워 맞춘듯한.. - 오라클이 비트맵 엔트리와 rowid를 서로 변환할 수 있다면, 실행계획의 어떤 지점에서도 이 변환을 수행할 수 있다. \\ {code:sql} 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 | | | | ------------------------------------------------------------------------------------ {code} \\ - 두개의 비트맵 인덱스로 출발하여, 차례대로 각각의 인덱스에서 약간의 리프 블록 데이터를 얻은 후,그 결과를 메모리내 B-tree 인덱스로 효과적으로 변환한다. - 두개의 B-tree 인덱스 조각을 얻으면, 이들 사이에 인덱스 해시 조인을 수행할 수 있다. \\ h2. 요약 - 비트맵 인덱스는 데이터의 흩어짐에 대한 정보가 없으므로, 옵티마이저는 일부 숫자 값을 만들어 내야 한다. (일부 쿼리에 문제가 발생할 수 밖에 없다) - CPU costing을 활성화 하면 일부 실행계획이 극적으로 달라진다.
"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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