오라클 인덱스, Bitmap 인덱스(Bitmap Indexes)
테이블의 컬러 컬럼이 BLUE, GREEN, RED, BLACK 4가지의 값을 가진다고 했을 때, 컬럼에 유일한 값이 몇개 되지 않으면 컬럼의 카디널리티cardinality가 낮다고 합니다. 이러한 컬럼에 적합한 인덱스가 Bitmap 인덱스bitmap indexes 입니다.
정보저장의 최소단위인 비트를 이용하여 칼럼 값을 간결하게 저장하고 이를 이용하여 자동으로 ROWID를 생성하는 구조를 가지며 성별 컬럼처럼 분포도가 나쁜 칼럼, NOT, OR를 사용하는 경우 탁월한 성능을 냅니다.
[그림 13.2 Bitmap 인덱스 내부구조]
CREATE BITMAP INDEX 명령으로 Bitmap 인덱스를 생성하면 비트리 인덱스처럼 트리구조를 만들고 리프블럭에 값들을 비트로 변환하여 저장 합니다. B*Tree 인덱스의 리프 블록(Leaf Block)은 INDEX KEY VALUE와 ROWID 로 구성이 되어 있지만 Bitmap 인덱스는 START ROWID ~ END ROWID로 압축해서 저장하고 컬럼값 역시 ‘1’ 이라는 비트로 저장해서 원본 데이터의 ROWID를 계산합니다.
Bitmap 인덱스를 생성하고자 하는 테이블 스캔을 한 후 Bitmap Index Generator에 의해 칼럼 값(비트형태의 ‘1’로 저장), 시작 ROWID, 끝 ROWID , Bitmap을 갖는 인덱스 엔트리를 생성 합니다. 생성된 Bitmap들을 B-tree구조에 넣기 쉽도록 KEY값과 START ROWID 순으로 정렬하며 마지막 단계에서는 정렬된 인덱스 엔트리들을 단순히 B*Tree 구조로 삽입 합니다.
.
인덱스를 데이터의 존재 여부를 0 or 1로 표시하는 비트 단위로 저장하고 B*Tree 인덱스 한계를 극복하여 대량의 자료 조회에 적합한 구조이지만 잦은 DML이 발생되는 곳은 리프 블록의 갱신으로 인해 부적합 합니다.
하나의 인덱스 값을 수정하면 그 인덱스 값을 가지는 모든 행(로우, ROW)에 락lock을 겁니다. 즉 하나의 인덱스 값으로 테이블상의 여러 개의 행을 표현하기 때문에 INSERT, UPDATE, DELETE 등을 사용하는 경우 오라클 LOCK 메커니즘인 행 단위 락(ROW LEVEL LOCKING)을 지원할 수 없습니다.
B*Tree 인덱스가 NULL값을 보관하지 않는 것과는 달리 Bitmap 인덱스는 NULL값에 대한 BIT값을 저장하여 비트리 인덱스의 NULL문제를 해결했으며 AND, OR 연산시 비트연산을 빠르게 수행하여 탁월한 성능을 보입니다.
[기본형식]
CREATE BITMAP INDEX index_name ON table_name (Column|Expr[,Column|Expr]...) |
실습에서 사용되는 MYEMP, MYDEPT 테이블은 0.환경설정의 0.4 실습 데이터 설치편을 참조하여 생성 바랍니다.
MYEMP 테이블의 deptno 컬럼에 대해 인덱스를 생성한 후 일단 안보이도록 하고 WHERE절에 deptno 컬럼을 사용하여 조회해 봅니다. 다시 deptno 컬럼의 인덱스를 보이도록 한 후 조회해 봐서 성능 차이가 나는지를 확인해 봅니다.
실습
MYEMP 테이블의 deptno 컬럼에 오름차순 인덱스를 생성하고 안보이도록 숨깁니다.
숨기게 되면 인덱스는 존재하지만 오라클에서 해당 인덱스를 사용하지 않습니다.
deptno 컬럼의 인덱스를 생성한 후 INVISIBLE 상태로 변경 합니다. 컬럼에 대한 인덱스가 이미 있다면 DROP INDEX로 삭제 후 다시 생성하세요. |
CREATE INDEX IDX_MYEMP_DEPTNO ON MYEMP(DEPTNO);
<실행결과>
Index IDX_MYEMP_DEPTNO이(가) 생성되었습니다.
ALTER INDEX IDX_MYEMP_DEPTNO INVISIBLE;
<실행결과>
Index IDX_MYEMP_DEPTNO이(가) 변경되었습니다.
현재 deptno 컬럼의 인덱스는 생성되어 있지만 보이지 않으므로 오라클에서 사용하지 않습니다.
WHERE절에 deptno 컬럼을 사용하여 조회해 보겠습니다.
MYEMP 테이블에서 deptno 값이 1 또는 3인 데이터가 몇 건 있는지 확인하세요. |
SELECT COUNT(*) FROM MYEMP WHERE DEPTNO IN (1,3);
<실행결과>
| COUNT(*) |
1 | 10000000 |
<실행계획>
실행시간은 필자의 노트북 기준으로 대략1 2초 정도 걸렸으며, deptno 컬럼 인덱스를 경유하지 않고 MYEMP 테이블 전체를 FULL SCAN하여 deptno 값이 1 인것과 3인 것을 필터링 하여 찾았고 집합함수인 COUNT문 때문에 AGGREGATE 옵션이 실행되어 결과를 한건으로 보였습니다. AGGREGATE 옵션은 주로 GROUP BY와 같이 쓰이는 SUM, COUNT등이 출현하는 경우 한건으로 추출되도록 하는 옵션 입니다.
deptno 컬럼의 인덱스를 보이도록 한 후 다시 쿼리해 보겠습니다.
deptno 컬럼에 생성되어 있는 인덱스를 보이도록 하고 다시 SELECT문을 실행하세요. |
ALTER INDEX IDX_MYEMP_DEPTNO VISIBLE;
SELECT COUNT(*) FROM MYEMP WHERE DEPTNO IN (1,3);
<실행결과>
| COUNT(*) |
1 | 10000000 |
<실행계획>
IDX_MYEMP_DEPTNO 인덱스를 빠르게 멀티 블럭으로 읽어 들이면서 전체 인덱스를 읽는 FAST FULL SCAN을 하면서 deptno 컬럼 값이 1 또는 3인 데이터를 찾고 AGGREGATE 옵션에 의해 한건으로 COUNT의 결과를 보입니다. 비용cost은 이전에 인덱스를 숨기고 실행한 것보다 적게 들었지만 여전히 1.7초 내외의 수행시간이 걸렸습니다. 인덱스를 경유 했지만 여전히 쿼리 성능이 만족스럽지 않은 상황 입니다. 비트리 인덱스는 값이 고유할수록, 값의 분포도가 좋을수록 좋은 성능을 내는 인덱스인데 deptno 컬럼은 중복되는 값이 많아서 인덱스를 경유해도 성능이 급진적으로 개선이 되지는 않습니다.
CARDINALITY 항목은 해당 오퍼레이션에서 추출되는 예상되는 건수를 표시 하는데 테이블에 대한 통계정보가 부정확하면 실제 건수와 차이가 나니 정확한 실행 계획의 생성을 위해 주기적으로 ANALYZE TABLE 명령으로 테이블 통계정보를 생성하는 것이 좋습니다. 이 값이 부정확하면 오라클 옵티마이저oracle optimizer가 실제 인덱스를 경유하는 것보다 FULL TABLE SCAN 하는 것이 효율적인데도 인덱스를 경유하게 하고, 해시조인이 효율적인데도 중첩루프 조인을 하도록 해서 쿼리 성능을 나쁘게 하는 실행계획을 만들어 낼 수 있습니다.
이번에는 먼저 생성한 deptno 컬럼의 인덱스를 삭제하고 Bitmap 인덱스를 생성하여 동일한 쿼리를 실행하고 실행시간 및 실행계획을 확인해 보겠습니다.
실습
IDX_MYEMP_DEPTNO 인덱스를 삭제하고 Bitmap 인덱스를 생성합니다.
deptno 컬럼의 인덱스(IDX_MYEMP_DEPTNO)를 삭제한 후 BIDX_MYEMP_DEPTNO 라는 이름으로 Bitmap 인덱스를 생성하세요. |
DROP INDEX IDX_MYEMP_DEPTNO;
CREATE BITMAP INDEX BIDX_MYEMP_DEPTNO ON MYEMP(DEPTNO);
앞에서 작성한 SELECT 쿼리를 다시 실행하고 실행계획 및 성능을 확인하겠습니다.
deptno 값이 1 또는 3인 데이터가 몇 건인지 확인하세요. |
SELECT COUNT(*) FROM MYEMP WHERE DEPTNO IN (1,3);
<실행결과>
| COUNT(*) |
1 | 10000000 |
<실행계획>
실행시간은 약 0.022초 정도 소요되었으며 생성한 Bitmap 인덱스를 경유하였고 비용도 많이 줄어 성능도 향상 되었음을 확인할 수 있습니다. 이렇게 Bitmap 인덱스는 deptno 컬럼처럼 값의 분포도가 좋지 않은 컬럼의 조회 용도로 사용하기에 적합한 인덱스 입니다.
#비트맵인덱스, #bitmap인덱스, #오라클인덱스, #인덱스, #INDEX
