오라클 인덱스, Bitmap 조인 인덱스(Bitmap Join Indexes)

 

오라클 인덱스, Bitmap 조인 인덱스(Bitmap Join Indexes)

Bitmap 조인 인덱스bitmap join indexes는 조인 성능 향상을 위해 두 테이블 조인시 조인한 결과 칼럼에 비트맵 인덱스를 생성하는 것 입니다. 

이 인덱스 역시 Bitmap 인덱스로 생성 방법이 조인 컬럼에 대해 생성을 하므로 Bitmap 조인 인덱스라고 합니다. Bitmap 조인 인덱스는 PKprimary key(UKunique key), FKforeign key 관계를 가진 테이블에서만 생성 가능한 인덱스 입니다.

실제 실습을 하면서 이해를 해보겠습니다.

Bitmap 조인 인덱스를 사용하지 않고 기존 B*Tree 인덱스를 이용하여 쿼리를 한 후, Bitmap 조인 인덱스를 만들고 다시 쿼리하여 성능 차이를 확인하고 실행계획도 확인을 해보겠습니다.

실습

MYEMP 테이블의 컬럼에 생성되어 있는 있는 인덱스를 조회한 후 PK 인덱스를 제외하고 삭제하세요.

SELECT A.INDEX_NAME, A.COLUMN_NAME, B.VISIBILITY 

FROM   USER_IND_COLUMNS A, USER_INDEXES B 

WHERE  A.TABLE_NAME = 'MYEMP' 

AND     A.INDEX_NAME = B.INDEX_NAME;

<실행결과>

	INDEX_NAME	COLUMN_NAME	VISIBILITY
1	PK_MYEMP	EMPNO	VISIBLE
2	BIDX_MYEMP_DEPTNO	DEPTNO	VISIBLE

필자의 경우 PK 인덱스가 아닌 인덱스는 BIDX_MYEMP_DEPTNO 입니다.

BIDX_MYEMP_DEPTNO 인덱스 삭제하세요.

DROP INDEX BIDX_MYEMP_DEPTNO;

<실행결과>

Index BIDX_MYEMP_DEPTNO이(가) 삭제되었습니다.

MYEMP 테이블 인덱스에는 현재 PK인덱스(empno 컬럼의 인덱스)만 존재합니다.

MYEMP, MYDEPT 테이블에서  “개발1팀”이 아닌 부서원의 수를 구해 보겠습니다. 

서브쿼리를 이용하여 MYEMP, MYDEPT 테이블에서 “개발1팀”이 아닌 부서원의 수를 구하세요.

SELECT COUNT(*)

FROM    MYEMP E

WHERE E.DEPTNO IN (SELECT DEPTNO FROM MYDEPT 

                                        WHERE DNAME != '개발1팀');

<실행결과>

	COUNT(*)
1	15000000

<실행계획>

실행시간은 필자 노트북 기준으로 11초 이상 걸렸습니다.(실행시간은 시스템 상황에 따라 달라질 수 있습니다.)

실행계획을 읽을 때는 가장 오른쪽으로 들여쓰기 되어 있는 곳부터 상위로 읽어오며, 같은 들여쓰기라면 위 쪽 단계를 먼저 읽습니다. MYDEPT 테이블을 먼저 FULL SCAN 하여 “개발1팀” 이 아닌 데이터를 찾고, MYEMP 테이블 역시 FULL SCAN 후 두 테이블을 deptno 컬럼을 기준으로 해시 조인을 하고 COUNT 값을 구했습니다. 집합함수 COUNT가 사용 되었으므로 최종적으로 한 행에 건수를 출력합니다.

Bitmap 조인 인덱스를 생성한 후 다시 쿼리해 보겠습니다.

실습

MYEMP와 MYDEPT 테이블을 조인하여 추출한 부서명(dname) 컬럼에 대해 IDX_MYEMP_MYDEPT_DNAME이라는 이름으로 Bitmap 조인 인덱스를 생성 합니다.

Bitmap 조인 인덱스를 생성 합니다.

CREATE BITMAP INDEX BIDX_MYEMP_MYDEPT_DNAME

ON MYEMP (MYDEPT.DNAME)

FROM          MYEMP, MYDEPT

WHERE       MYEMP.DEPTNO = MYDEPT.DEPTNO;

<실행결과>

INDEX BIDX_MYEMP_MYDEPT_DNAME(가) 생성되었습니다.

MYDEPT.DNAME 컬럼이 인덱스 생성 시 ON 절에서 참조되므로 MYDEPT 테이블에 조인하여 DNAME 컬럼을 검색하는 MYEMP 테이블에 대한 쿼리는 DEPT 테이블을 참조하지 않고도 수행 할 수 있습니다. 즉  “개발1팀”이 아닌 부서원의 수를 구하기 위해 MYDEPT 테이블에서 “개발1팀”이 아닌  deptno 컬럼을 읽어 이를 MYEMP의 deptno 컬럼과 비교하는 부분에서  MYDEPT 테이블과의 조인이 필요없어지고 “개발1팀”이 아닌 사원들의 수를 생성한 Bitmap 조인 인덱스에서 값을 가져오게 되므로 성능이 향상 됩니다.

앞에서 실행한 쿼리를 다시 실행 합니다.

서브쿼리를 이용하여 MYEMP, MYDEPT 테이블에서 “개발1팀”이 아닌 부서원의 수를 출력하세요.

SELECT  COUNT(*)

FROM     MYEMP

WHERE  DEPTNO IN (SELECT DEPTNO 

                                     FROM    MYDEPT

                                     WHERE  DNAME !=  '개발1팀');

<실행결과>

	COUNT(*)
1	15000000

<실행계획>

생성한 Bitmap 조인 인덱스를 이용했으며 실행시간은 0.15초 정도 걸렸습니다. 

현재  MYEMP 테이블의 데이터가 2,000천만 건정도 밖에 안되지만 쿼리 수행 시간 차이는 상당 합니다. 데이터가 훨씬 많은 대용량 테이블에서는 더 차이가 날것 입니다. 

하지만 무조건 인덱스를 만드는게 좋은것은 아닙니다. 입력/수정이 빈번하다면 역효과가 날수 있으니 주의해야 하며, 특히 Bitmap 인덱스 계열은 컬럼값이 변경 될 때 동일한 값을 가진 다른 행 들도 LOCK이 발생하므로 이 인덱스는 통계성 데이터를 가지는 조회 용도의 테이블에 유리합니다.

생성한 Bitmap 조인 인덱스를 삭제 합니다.

BIDX_MYEMP_MYDEPT_DNAME 인덱스 삭제하세요.

DROP INDEX BIDX_MYEMP_MYDEPT_DNAME;

<실행결과>

Index BIDX_MYEBIDX_MYEMP_MYDEPT_DNAMEMP_DEPTNO이(가) 삭제되었습니다.

 

#비트맵조인인덱스, #인덱스, #오라클인덱스, #비트맵조인, #bitmapjoinindex, #bitmapjoin인덱스, #INDEX, #ORACLE인덱스​

오라클 인덱스, Bitmap 인덱스(Bitmap Indexes)

 

오라클 인덱스, 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​