비트맵 인덱스 (by darkturtle) [2012.11.09]
h2.비트맵 인덱스
- 오라클 7.3 버전에서 추가됌
- Enterprise 및 Personal Edition 에서 사용가능 ( Standard Edition 은 불가 )
- DW 나 ad hoc 쿼리 환경에서 적합
- OLTP 또는 동시에 여러 세션에 의해 빈번하게 UPDATE되는 환경에는 적합하지 않다.
- 비트맵 인덱스
: 하나의 인덱스 키 엔트리에 많은 로우에 대한 포인터를 저정하는 구조로,
하나의 엔트리는 많은 수의 로우를 가리킴 - B*Tree 인덱스
: 인덱스키와 테이블 로우가 한 쌍 구조,
하나의 인덱스 엔트리는 하나의 로우를 가리킴
create BITMAP index JOB_IDX ON emp ( job ) ;
[표 11-6] 오라클 JOB_IDX 비트냅 인덱스를 저장하는 방식
-. 비트맵 인덱스는 NULL 엔트리도 저장한다.(엔트리에 없다면, NULL 값이 존재 하지 않음 )
[표 11-7] 비트아이즈 OR 연산의 표현
SQL> SELECT COUNT(*) FROM EMP WHERE job ='CLERK' or job='MANAGER';
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 0 (0)|
| 1 | SORT AGGREGATE | | 1 | 6 | |
| 2 | BITMAP CONVERSION COUNT | | 1 | 6 | |
|* 3 | BITMAP INDEX FAST FULL SCAN| JOB_IDX | | | |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("JOB"='CLERK' OR "JOB"='MANAGER')
==> 비트냅 인덱스에서 Count 만 하면 됌
SQL> select * from emp where job='CLERK' or job='MANAGER';
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)|
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)|
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='CLERK' OR "JOB"='MANAGER')
SQL>
==> * 로 테이블 접근 필요가 있음
언제 비트냅 인덱스를 사용해야 하는가 ?
- 낮은 카디널리티(low distinct cardinarlity, 적은 수의 구별된 카디널리티) 데이터에 적합
- 낮은 카디널리티는 로우의 숫자를 구별된 값의 숫자로 나누게 되면 거의 0 에 가까운 작은 값이 된다.
==> 칼럼 distinct 가 낮은것 - 일반적으로 선택도가 높아야 함. 반면에 B*Tree는 일반적으로 선택도가 낮아함
- 특히 여러 칼럼을 ad hot 방식으로 참조하거나 COUNT(테이블에 접근할 필요가 없는)
같은 집계 처리를 해야 하는 경우에 유리
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_GOUPR 으로 구성된 인덱스
- LOCATION, AGE_GROUP 으로 구성된 인덱스
- AGE_GROUP, GENDER 으로 구성된 인덱스
- 비트냅 인덱스 세 개를 이용하여 해당 칼럼을 참조하는 어떤 조합의 조건이라도
결과를 처리하기 위해 AND, OR과 NOT 의 연산으로 간단하게 처리 - 병합된 비트냅의 결과를 취해서, ( 카운트가 )필요하다면, rowid로 변환해서 테이블을 액세스
SQL> create table t
( gender not null,
location not null,
age_group not null,
data
)
as
select decode(ceil(dbms_random.value(1,2)),1,'M',2,'F') gender,
ceil(dbms_random.value(1,50)) location,
decode(ceil(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 big_table
where rownum <= 100000
/
Table created.
SQL> create bitmap index gender_idx on t (gender );
Index created.
SQL> create bitmap index location_idx on t (location );
Index created.
SQL> create bitmap index age_group_idx on t ( age_group);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly
SQL> select count(*)
2 from T
3 where gender='M'
4 and location in ( 1,10,30 )
5 and age_group ='41 and over' ;
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=73 us)
0 BITMAP CONVERSION COUNT (cr=2 pr=0 pw=0 time=65 us)
0 BITMAP AND (cr=2 pr=0 pw=0 time=58 us)
0 BITMAP INDEX SINGLE VALUE GENDER_IDX (cr=2 pr=0 pw=0 time=33 us)
0 BITMAP INDEX SINGLE VALUE AGE_GROUP_IDX (cr=0 pr=0 pw=0 time=2 us)
0 BITMAP OR (cr=0 pr=0 pw=0 time=14 us)
0 BITMAP INDEX SINGLE VALUE LOCATION_IDX (cr=0 pr=0 pw=0 time=2 us)
0 BITMAP INDEX SINGLE VALUE LOCATION_IDX (cr=0 pr=0 pw=0 time=1 us)
0 BITMAP INDEX SINGLE VALUE LOCATION_IDX (cr=0 pr=0 pw=0 time=1 us)
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("GENDER"='M' AND "AGE_GROUP"='41 and over' AND ("LOCATION"=1 OR
"LOCATION"=10 OR "LOCATION"=30))
3 - access(ROWID=ROWID)
4 - access(ROWID=ROWID)
6 - access("GENDER"='M')
8 - access("AGE_GROUP"='41 and over')
11 - access("LOCATION"=1 OR "LOCATION"=10 OR "LOCATION"=30)
- 비트맵 인덱스의 강력한 장점을 보여주는 예제
location 이 1, 10, 30인 3개의 인덱스를 각각 읽어서 '비트'의 OR 연산
그리고 AGE_GROUP='41 AND OVER' 인 비트맵과 GENDER='M'인 비트맵과 AND 연상 수행
간단하게 비트냅이 1인 개수를 카운트 하여 결과
select *
from T
where ( ( GENDER ='M' AND LOCATION = 20 )
OR ( GENDER ='F' AND LOCATION = 22 ))
AND age_group ='18 and under';
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
0 TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=69 us)
0 BITMAP CONVERSION TO ROWIDS (cr=2 pr=0 pw=0 time=69 us)
0 BITMAP AND (cr=2 pr=0 pw=0 time=63 us)
0 BITMAP INDEX SINGLE VALUE AGE_GROUP_IDX (cr=2 pr=0 pw=0 time=38 us)
0 BITMAP OR (cr=0 pr=0 pw=0 time=19 us)
0 BITMAP AND (cr=0 pr=0 pw=0 time=10 us)
0 BITMAP INDEX SINGLE VALUE LOCATION_IDX (cr=0 pr=0 pw=0 time=2 us)
0 BITMAP INDEX SINGLE VALUE GENDER_IDX (cr=0 pr=0 pw=0 time=2 us)
0 BITMAP AND (cr=0 pr=0 pw=0 time=5 us)
0 BITMAP INDEX SINGLE VALUE GENDER_IDX (cr=0 pr=0 pw=0 time=2 us)
0 BITMAP INDEX SINGLE VALUE LOCATION_IDX (cr=0 pr=0 pw=0 time=2 us)
- 이번에는 실제 테이블 로우를 액세스해야 하기 때문에
오라클은 각 0과 1을 ROWID로 바꿔어서 테이블에 액세스 하게 된다.
- 비트냅 인덱스는 읽기 중심의 환경에서는 적합 인덱스
- 쓰기를 많이 하는 환경에서는 결코 부적합
- 하나의 비트냅 인데스 크기 많은 로우를 가리키기 때문에, 하나의 세션에서 인덱스 엔트리를 수정하게 되면
그 인덱스 포인터하고 있는 모든 로우를 락킹 - 오라클은 비트맵 인덱스에서 수정되는 한 비트만 락을 걸지 않고,
비트냅 인덱스 엔트리 전체를 락킹하기 때문에 같은 비트냅 인덱스 엔트리를 수정하는 모든 작업은
이전의 락킹이 풀릴 때까지 기다려야 한다. - 동시성을 심각하게 제한
비트맵 조인 인덱스
*. 9i에서 소개된 새로운 인덱스 유형
- 비트냅 조인 인덱스는 다른 테이블의 컬럼을 이용한 인덱스
- 데이터는 정규화 상태로 유지하고 동시에 반정규화이 효과를 얻음
- 전제 조건
다른 테이블의 기본 키 또는 유니크 키 조인을 포함해야 한다는 것이다.
SQL> CREATE BITMAP INDEX EMP_BM_IDX
ON EMP ( D.DNAME)
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
Statement Processed.
SQL> BEGIN
DBMS_STATS.SET_TABLE_STATS(USER,'EMP', NUMROWS =>1000000, NUMBLKS => 300000);
DBMS_STATS.SET_TABLE_STATS(USER,'DEPT', NUMROWS =>100000, NUMBLKS => 300000);
DBMS_STATS.DELETE_INDEX_STATS(USER,'EMP_BM_IDX');
END;
/
Statement Processed.
==> 통계정보를 업데이트 하여, EMP,DEPT 가 대량의 데이터처럼 조정
CREATE INDEX 로 인한 COMPUTE STATISTICS 수행 정보를 삭제 하기 위해서 DELETE_INDEX_STATS 수행
SELECT COUNT(*)
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND DEPT.DNAME ='SALES'
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: OPS$ORACLE (ID=59)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=1 pr=0 pw=0 time=46 us)
0 BITMAP CONVERSION COUNT (cr=1 pr=0 pw=0 time=41 us)
0 BITMAP INDEX SINGLE VALUE EMP_BM_IDX (cr=1 pr=0 pw=0 time=29 us)
SQL> set autotrace traceonly explain
SQL> select count(*)
2 from emp, dept
3 where emp.deptno = dept.deptno
4 and dept.dname ='SALES';
Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)|
| 1 | SORT AGGREGATE | | 1 | 13 | |
| 2 | BITMAP CONVERSION COUNT | | 10 | 130 | 1 (0)|
|* 3 | BITMAP INDEX SINGLE VALUE| EMP_BM_IDX | | | |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."SYS_NC00009$"='SALES')
SQL> CREATE BITMAP INDEX EMP_BM_IDX
ON DEPT ( E.ENAME )
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORA-25954: missing primary key or unique constraint on dimension
비트맵 인덱스 요약
- OLTP 가 아니라면 비트냅 인덱스를 시도해보라
- 일반적으로 대형 테이블이고, 대부분이 ad-hoc 쿼리의 읽기 전용 시스템인 경우에는
비트냅 인덱스가 해결책이 될 수 있음
- Ad hoc은 "이것을 위해" 즉 "특별한 목적을 위해서."라는 뜻의 라틴어로, 일반적으로 다음을 나타낸다.
일반화할 수 없는 해결책 ( 정형화 안됨 날쿼리 ㅇ.ㅇ)