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

14S_비트맵 인덱스




비트맵 인덱스

  • 비트맵 인덱스는 오라클 7.3 버전에서 추가되었다.
  • Enterprise 및 Personal Edition에서는 사용 가능하지만 Standard Edition에서는 사용할 수 없다.
  • 비트맵 인덱스는 시스템 구축 시에는 요청될 쿼리를모두 알 수 없는 데이터 웨어하우스 또는 ad hoc 쿼리 환경(쿼리가 재사용되지않고 CPU 높이거나 성능저하를 요하는것)에 적합하도록 설계되었다.
  • OLTP (on-line transaction processing 데이터베이스의 데이터를 수시로 갱신하는 프로세싱을 의미)또는 동시에 여러 세션에 의해 빈번하게 UPDATE되는 환경에는 적합하지 않다.
  • 비트맵 인덱스는 하나의 인텍스 키 엔트리에 많은 로우에 대한 포인터를 저장하고 있는 구조로, 인텍스키와 테이블 로우가 한 쌍을 이루는 B*Tree 인텍스와는 다르다.
  • 비트맵 인덱스는  매우 적은 수의 인덱스 엔트리를 가지게 되고,각 엔트리는 많은 수의 로우를 가리키지만, 일반적인 B*Tree 인덱스는 하나의 인덱스 엔트리가 하나의 로우를 가리키고 있다
      비트맵 인덱스 생성
create BITMAP index job_idx on emp(job);

  • 표 11 -6에서 로우8,10,13은 ANALYST고, 로우4,6,7은 MANAGER고 NULL 인 로우는 없다(비트맵 인덱스는 null 엔트리도 저장한다 비트랩 인텍스에 null 엔트리가 없다는 것은 null 인 로우가 없다는 것을 의미한다).
  • 만약 MANAGER 값을 가진 로우의 수를 찾는다면 비트맵 인덱스를 이용하여 빠르게 셀 수있다 JOB 이 CLERK이거나 MANAGER 인 로우를 찾고자 한다면 표 11-7에서 보여주듯이 인덱스의 비트맵을 조합하여 쉽게 찾을수 있다.

select count(*) from emp where job = 'CLERK' or job = 'MANGER';

Plan hash value: 3446200557

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |         |     1 |     8 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |         |     6 |    48 |     1   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX FAST FULL SCAN| JOB_IDX |       |       |            |          |
-----------------------------------------------------------------------------------------
  • 비트맵 인덱스로부터 직접 결과를 추출할 수 있다.
select * from emp where job = 'CLERK' or job = 'MANGER'
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |   228 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     6 |   228 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
  • 위 쿼리는 테이블에 액세스할 필요가 있다.
  • 오라클은 비트맵의 1번째 비트를 실제 테이블의 rowid로 바꾸는 함수 를 적용해서 테이블에 액세스할 수 있도록 한다.

언제 비트맵 인덱스를 사용해야 하는가?

  • 비트맵 인덱스는  낮은 카디널리티 (low distind cardinality , 적은수의 구별된 카디널리티 ex) 색상 , 성별) 데이터에 적당하다.
  • 낮은카디널리티는 결과 집합의 크기에 상대적이다. ex) 수억개중 만개는 낮은 카디널티가 될수있다. 
  • 컬럼의 갯수에 비례하여 작은값의 경우에 사용된다.
  • 여러 컬럼을 ad hoc 방식으로참조하거나 COUNT와 같은 집계 처리를 해야 하는 경우
  • 예를 들어 GENDER, LOCATION, 그리고 AGE_GROUP 3개 컬럼을 가진 대형 테이블이 있다고 하자.
  • 이 테이블에서 GENDER는 M과 F,LOCATION은 1부터 50까지의 값 AGE_GROUP은 18 and under, 19-25, 26-30, 31-40,41 and over 값으로 되어있다.
     Select count(*)
     from T
     where gender = 'M'
     and location in ( 1, 10, 30 )
     and age_group = '41 and over';
    
    select *
    from t
    where ( (gender = 'M ' and location = 20 )
    or ( gender = 'F' and location = 22 ))
    and age_group = '18 and under';
    
    select count(*) from t where location in (11 ,20,30);
    
    select count(*) from t where age_group = '41 and over' and gender = 'F';
    
           B*Tree 인텍스 구성
  • GENDER, LOCATION, AGE_GROUP: 3 개 컬럼이 모두 사용되기 때문에 GENDER와 LOCATION 결합 인덱스 또는 GENDER 단독 인덱스
  • LOCATION, AGE_GROUP: LOCATION과 AGE_GROUP 결합 인덱스 또는 LOCATION 단독인덱스
  • AGE_GROUP, GENDER: AGE_GROUP와 GENDER 또는 AGE_GROUP 단독 인덱스
         BIT MAP 인덱스 구성
drop table t ;

create table t
(gender not null,
location not null,
age_group not null,
data
)
as

select decode( round(dbms_random.value(1,2)) ,
                1, 'M' ,
                2, 'F' ) gender,
 round(dbms_random.value(1 ,50)) location,
 decode( round(dbms_random.value(1, 5)),
         1, '18 and under' ,
         2, '19-25' ,
         3, '26-30' ,
         4, '31-40' ,
         5, '41 and over') ,
 rpad( '*' ,20,'*')
 from dual connect by level <= 100000 ;



 create bitmap index gender_idx on t(gender);
 create bitmap index location_idx on t(location);
 create bitmap index age_group_idx on t(age_group);
 exec dbms_stats.gather_table_stats( user, 'T' )

 Select count(*)
 from T
 where gender = 'M'
 and location in ( 1, 10, 30 )
 and age_group = '41 and over';


Plan hash value: 2919078855

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |    13 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |               |     1 |    13 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |               |   308 |  4004 |     7   (0)| 00:00:01 |
|   3 |    BITMAP AND                 |               |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | AGE_GROUP_IDX |       |       |            |          |
|   5 |     BITMAP OR                 |               |       |       |            |          |
|*  6 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          |
|*  7 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          |
|*  8 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          |
|*  9 |     BITMAP INDEX SINGLE VALUE | GENDER_IDX    |       |       |            |          |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("AGE_GROUP"='41 and over')
   6 - access("LOCATION"=1)
   7 - access("LOCATION"=10)
   8 - access("LOCATION"=30)
   9 - access("GENDER"='M')
  • 이 예제는 비트맵 인덱스의 강력한 장점을 보여주고 있다 location이 1,10,30 인 3개의 인덱스를 각각 읽어서 비트맵으로 '비트' 의 OR 연산을 할 것 이다. 그리고 AGE_GROUP='41 AND OVER'인 비트맵과 GENDER='M'인 비트맵과 AND 연산을 수행한다.
select *
from t
where ( (gender = 'M' and location = 20 )
   or ( gender = 'F' and location = 22 ))
and age_group = '18 and under';


Plan hash value: 1064409070

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |   276 |  9384 |    52   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | T             |   276 |  9384 |    52   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS  |               |       |       |            |          |
|   3 |    BITMAP AND                  |               |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE  | AGE_GROUP_IDX |       |       |            |          |
|   5 |     BITMAP OR                  |               |       |       |            |          |
|   6 |      BITMAP AND                |               |       |       |            |          |
|*  7 |       BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          |
|*  8 |       BITMAP INDEX SINGLE VALUE| GENDER_IDX    |       |       |            |          |
|   9 |      BITMAP AND                |               |       |       |            |          |
|* 10 |       BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          |
|* 11 |       BITMAP INDEX SINGLE VALUE| GENDER_IDX    |       |       |            |          |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("AGE_GROUP"='18 and under')
   7 - access("LOCATION"=22)
   8 - access("GENDER"='F')
  10 - access("LOCATION"=20)
  11 - access("GENDER"='M')
  • 이 경우도 비슷한 로직으로 처리한다 OR 조건은 AND 조건에 맞는 비트맵을 찾고,각각의 AND 결과를 OR 조건으로 연산을 수행한다. 그리고 AGE_GROUP='18 AND UNDER' AND 를 만족하는 조건인 비트맵을 찾고, 최종 결과를 산출하게 된다.
단점
  • 비트맵 인덱스는 읽기 중심의 환경에서는 적합한 인덱스이나 쓰기를 많이 하는 환경에서는 결코 적절하지 않다.
  • 하나의 비트맵 인덱스 키가 많은 로우를 가리키기때문에, 하나의 세션에서 인텍스 엔트리를 수정하게 되면 그 인텍스가 포인터하고 있는 모든 로우를 락킹하기 때문이다.
  • 오라클은 비트맵 인덱스에서 수정되는 한 비트만 락을 걸지 않고, 비트맵 인덱스 엔트리 전체를 락킹하기 때문에 같은 비트맵 인덱스 엔트리를 수정 하는 모든 작업은 이전의 락킹이 풀릴때까지 기다려야 한다.

비트맵 조인 인덱스

  • 일반적으로 인텍스는 하나의 테이블에 속한 컬럼을 대상으로 생성되는 반면 비트맵 조인 인텍스는 이러한 규칙을 깨고 다른 테이블의 컬럼을 이용한 인텍성이 가능하도록 한다.
-- 일반적인 쿼리
select count(*)
from emp, dept
where emp.deptno = dept.deptno
and dept.dname = 'SALES'
/
select emp.*
from emp, dept
where emp.deptno = dept.deptno
and dept.dname = 'SALES'
/
create bitmap index emp_bm_idx
 on emp( d.dname )
 from emp e, dept d
 where e.deptno = d.deptno

begin
 dbms_stats.set_table_stats( user, 'EMP' , numrows => 1000000, numblks => 300000 ) ;
 dbms_stats.set_table_stats( user, 'DEPT' , numrows => 100000, numblks => 30000 );
 dbms_stats.delete_index_stats( user, 'EMP_BM_IDX');
end;



select count (*)
  from emp, dept
 where emp.deptno = dept.deptno
  and dept.dname = 'SALES'

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |            |     1 |     3 |            |          |
|   2 |   BITMAP CONVERSION COUNT   |            |   333K|   976K|     1   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX SINGLE VALUE| EMP_BM_IDX |       |       |            |          |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMP"."SYS_NC00010$"='SALES')
  • 위의 예제를 보면, 결과를 생성하기 위해 EMP 테이블이나 DEPT 테이블에 실제로 액세스할 필요가 없다.
    최종 결과는 인덱스 자체만으로 생성된다.SQL의 결과를 만드는 데 필요한 모든 정보는 인덱스구조에 모두 포함되어 있기 때문이다.
  • 비트맵 조인 인덱스는 전제 조건이 있다. 조인 조건은다른테이블의 기본 키 또는유니크 키 조인을 포함해야 한다는 것이다.
    -- 아래 예제는 bitmap index를 타야하는데 해쉬조인로 되버림 
    select emp.*
      from emp, dept
     where emp.deptno = dept.deptno
      and dept.dname = 'SALES'
    
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |  6250M|   296G|   133K (34)| 00:26:43 |
    |*  1 |  HASH JOIN         |      |  6250M|   296G|   133K (34)| 00:26:43 |
    |*  2 |   TABLE ACCESS FULL| DEPT | 25000 |   317K|  8143   (1)| 00:01:38 |
    |   3 |   TABLE ACCESS FULL| EMP  |  1000K|    36M| 81422   (1)| 00:16:18 |
    ---------------------------------------------------------------------------
    
       1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       2 - filter("DEPT"."DNAME"='SALES')
    
    
    

문서정보

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