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