전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
비트맵 인덱스
- 비트맵 인덱스는 오라클 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 단독 인덱스
BITMAP 인덱스 구성
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')
"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4044
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.