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

오라클 인덱스, 비트리 인덱스(B*Tree Indexes)

 

오라클 인덱스, 비트리 인덱스(B*Tree Indexes)

B*Tree 인덱스는 오라클의 대표적인 형태의 인덱스로 CREATE INDEX로 별다른 옵션없이 생성되는 인덱스는 B*Tree 인덱스 입니다. 인덱스 구조가 자료구조에서 배운 TREE와 비슷해서 균형잡힌 트리 B*TREE(Balanced Tree)라고 합니다. 

다음은 EMP 테이블에서 사원이름 컬럼을 내림차순(Z -> A 순서)으로 정렬했을 때의 B*Tree 인덱스의 내부 구조 입니다.

[그림 13.1 B*Tree 인덱스 내부구조]

B*Tree 인덱스의 리프 블록leaf blocks에는 실제 인덱스 데이터와 원본 테이블 행의 ROWID가 있는데 각 항목은 인덱스 데이터 + ROWID로 정렬되어 있고, 브랜치 블록branch blocks은 리프 블록을 찾아가기 위한 인덱스 데이터를 가지고 있습니다.  모든 리프 블록이 동일한 깊이에 있기 때문에 B-Tree 인덱스는 균형을 이루고 각각의 행들을 검색하는데는 거의 동일한 시간이 소요됩니다.

리프 블록의 ROWID는 인덱스 경유 후 원본 테이블의 데이터에 접근해서 값을 읽어야 하는 경우 이용되며, ROWID에는 object #, file #, block # 및 row #이 포함됩니다. 여기서 file #은 물리적 데이터베이스 파일, block #은 데이터가있는 블록, row #은 블록 내의 테이블 행에 대한 포인터입니다.

리프 블록은 적절한 다음 및 이전 리프 블록을 가리 킵니다. 이렇게하면 리프 블록 자체가 디스크 전체에 저장 되더라도 인덱스에 논리적 순서가 부여되고 이를 통해 범위 스캔, 내림/오름차순 검색 등을 수행 할 수 있습니다.

테이블의 기본키primary key와 같이 고유 인덱스unique indexes로 생성되면 데이터 값당 하나의 ROWID를 가지고 있고 값이 유일하므로 값에 따라서 정렬됩니다. 하지만 고유 인덱스가 아닌 경우에는 ROWID가 정렬 된 순서로 키에 포함되므로 인덱스 키값 및 ROWID를 기준으로 정렬됩니다. 사원테이블에서 이름이 동일한 사원이 있을 경우 ROWID를 기준으로 정렬됩니다.

만약 ename 컬럼에 NULL인 데이터가 있다면 ename 오름차순 정렬시 맨 뒤에 위치하며 NULL을 포함하는 값은 인덱싱 되지 않습니다. 그래서 WHERE ename IS NULL을 사용하면 ename 인덱스를 경유하지 않으니 주의해야 합니다.

B*Tree 인덱스는 데이터의 일부 작은 하위집합만 선택하는 경우에 성능이 좋습니다. 일반적으로 최대 5~10% 정도인데,  테이블 데이터의 10% 이상을 인덱스를 경유하여 추출하는 경우 원본 테이블 FULL SCAN 보다 성능이 안좋을수 있습니다. 

고유한 값이 많을 수록, 컬럼의 값이 다양할 수록 성능상 좋습니다. 예를들어 성별 컬럼(M or N)이 있다면 이값의 분포도는 대략 50% 일것 입니다. 이런 경우 B*Tree 인덱스는 성능상 큰 도움이 못될 수도 있으며 이때는 Bitmap 인덱스bitmap indexes를 사용하는 것이 효율적 입니다. 테이블의 크기가 작은 경우 기본키primary key를 제외하고는 굳이 별도의 인덱스를 구성할 필요는 없습니다.

만약 여러개의 컬럼으로 B*Tree 인덱스를 구성한다면 첫번째 열, 선행 컬럼은 가장 자주 액세스 되며 유일한 값을 가지는 컬럼을 사용하는 것이 좋습니다.

생성된 B*Tree 인덱스는 오라클에서 자동으로 유지 관리하는데 인덱싱 된 테이블에 값을 삽입/수정 할 때 약간의 오버헤드가 있습니다. 입력 또는 수정이 빈번한 테이블에 많은 인덱스가 있는 경우 문제가 될 수 있으니 주의해야 합니다.

 

또한 B*Tree 인덱스는 실제 컬럼 값을 인덱스 영역에 정렬해서 보관 하므로 데이터의 중복이 생기고 칼럼 값의 선택도/분포도(전체 데이터중 5~10% 정도 선택, 값이 다양하고 유일할 수 수록)가 좋아야 최적의 성능을 내는 구조 입니다. OR 처럼 범위를 넓히면서 검색하는 경우 인덱스를 사용하지만 제 성능을 내기 어려우며 특히 NOT을 사용하는 부정연산에는 취약 합니다.

 

#비트리인덱스, #비트리인덱스란, #btree, #B*Tree, #B*Tree인덱스, #인덱스, #오라클인덱스, #INDEX, #ORACLE

오라클 INDEX, 오름차순/내림차순 인덱스(Ascending/Descending Index)

 

오라클 INDEX, 오름차순/내림차순 인덱스(Ascending/Descending Index)

인덱스index를 생성 할 때 칼럼명 다음에 ASC 또는 DESC를 기술하지 않으면 기본적으로 오름차순(ASCENDING)으로 인덱스가 생성되는데, 컬럼명 다음에 ASC 라고 기술하면 오름차순, DESC 라고 기술하면 내림차순(DESCENDING)으로 인덱스가 생성됩니다.

오름차순, 내림차순 인덱스 구분은 데이터 조회시 정렬(SORT) 이라는 중요한 기능을 수행 합니다.  인덱스가 내림차순으로 생성되어 있다면 인덱스를 경유하여 데이터를 조회하면 ORDER BY를 기술하지 않더라도 데이터는 내림차순 정렬되어 있을 것 입니다.

EMP 테이블의 데이터를 조회할 때 SELECT * FROM EMP 이런식으로 인덱스를 경유하지 않고 원본 테이블에서 조회 한다면 출력되는 데이터는 원본 데이터에서 입력된 순서대로 조회됩니다.

만약 전체 사원목록을 조회할 때 사원명으로 내림차순 조회하고 싶다면 대부분 다음처럼 쿼리 할 것 입니다.

SELECT * FROM EMP ORDER BY ENAME;

이때 EMP 테이블의 ename  컬럼에 인덱스가 있고 이를 경유 한다면 원본데이터를 메모리에 올려서 정렬하는 과정을 거치지 않아 다행이지만, 인덱스가 없다면 위 쿼리 구문의 실행을 위해 오라클은 해당 컬럼 값들을 메모리에 올려 정렬sort해야 하므로 대용량 테이블에서는 최악의 쿼리가 될 것 입니다. 실무에서 대용량 테이블인 경우 인덱스가 없는 컬럼에 대해서 절대 이런식으로 사용하시면 안됩니다. 

만약 인덱스가 생성되어 있다면 EMP 테이블의 데이터를 추출할 때 인덱스를 경유하도록 하면 데이터는 저절로 정렬되어 있다는 사실을 잘 생각해야 합니다. 이런 경우 일부러 ORDER BY를 사용하지 않아도 인덱스를 경유하도록 하면 데이터는 정렬되어 있을 것 입니다. 이장의 뒷부분에서 실습을 해보겠습니다.

실습에서 오름차순, 내림차순 인덱스를 생성하고 ORDER BY 구문을 사용하지 않아도 인덱스 영역을 경유 하도록 쿼리를 잘 사용하면 데이터는 정렬된다는 것을 실습해 보겠습니다.

실습

오름차순 인덱스를 생성해 보겠습니다.

EMP 테이블의 sal 컬럼에 비고유, 오름차순 인덱스를 생성하세요.

CREATE INDEX IDX_EMP_SAL ON EMP(SAL ASC);

<실행결과>

Index IDX_EMP_SAL이(가) 생성되었습니다.

컬럼명 다음에 ASC, DESC를 기술하지 않으면 ASC가 기본값 입니다. 위 구문은 다음 구문과 동일 합니다. CREATE INDEX IDX_EMP_SAL ON EMP(SAL)

실습

복합 컬럼에 오름차순, 내림차순 인덱스를 생성해 보겠습니다.

EMP 테이블에서 job 오름차순, sa 내림차순으로 인덱스를 생성 하세요.

CREATE INDEX IDX_EMP_JOB_SAL ON EMP(JOB, SAL DESC);

<실행결과>

Index IDX_EMP_JOB_SAL이(가) 생성되었습니다.

실습

테이블에 입력되는 순서대로 SELECT 시 조회된다는 사실을 실습을 통해 알아 보겠습니다.

TEST 테이블을 생성 후 3건의 데이터를 입력하고 조회해 보세요.

CREATE TABLE TEST (

A VARCHAR2(100) PRIMARY KEY,

b VARCHAR2(10));

INSERT INTO TEST VALUES ('1','111');

INSERT INTO TEST VALUES ('3','333');

INSERT INTO TEST VALUES ('2','222');

SELECT * FROM TEST;

<실행결과>

	A	B
1	1	111
2	3	333
3	2	222

INSERT 문에 의해 입력되는 순서대로 데이터가 조회 됩니다.

실습

ORDER BY 구문을 사용하지 않고도 정렬된 데이터를 추출해 보겠습니다.

현재  EMP 테이블의 ename 컬럼은 인덱스가 생성되어 있습니다.

EMP 테이블의 모든 데이터를 조회하세요.

SELECT EMPNO, ENAME FROM EMP;

<실행결과>

	EMPNO	ENAME
1	7369	SMITH
...	...	...
13	7902	FORD
14	7934	MILLER

EMP 테이블에 입력된 순서대로 조회 됩니다.

EMP 테이블에서 모든 사원의 empno, ename 컬럼 데이터를 조회하세요.(단 ename 오름차순으로 정렬)

SELECT EMPNO, ENAME FROM EMP ORDER BY ENAME;

<실행결과>

	EMPNO	ENAME
1	7876	ADAMS
...	...	...
13	7844	TURNER
14	7521	WARD

ename 컬럼을 기준으로 오름차순 정렬되어 조회 됩니다.

이번에는 ORDER BY 구문을 이용하지 않고 인덱스를 경유하도록 해서 데이터를 조회해 보겠습니다.

EMP 테이블에서 모든 사원의 empno, ename 컬럼 데이터를 조회하세요.(단 ename 오름차순으로 정렬)

SELECT EMPNO, ENAME FROM EMP WHERE ENAME > 'A';

<실행결과>

	EMPNO	ENAME
1	7876	ADAMS
...	...	...
13	7844	TURNER
14	7521	WARD

ename 컬럼을 기준으로 오름차순 정렬되어 조회 됩니다. 

ORDER BY를 사용하지 않았지만 오라클을 감동시켜 ename 인덱스를 경유 하도록 하기 위해 WHERE 절에 ename 컬럼을 일부러 출현시켰더니 오라클이 ename 인덱스를 경유하여 오름차순으로 데이터가 조회 되었습니다. 여기서 우리는 WHERE 절에 인덱스 컬럼을 출현시키면 오라클이 해당 컬럼의 인덱스 영역에서 데이터를 조회한다는 사실을 알았습니다.

사실 중요한 것은 ORDER BY를 사용했냐 안했냐가 아니라 내가 작성한 쿼리문이 적절한 인덱스를 경유하여 검색 속도에 문제가 없는지 입니다. 

ORDER BY는 인덱스가 없는 컬럼에 대해 사용하게 되면 100% 메모리에 올려 정렬이라는  과정을 거치게 되어 있으므로 대용량 테이블인 경우 조회 속도는 최악 입니다. 정말 주의 해야 합니다. 

물론 ORDER BY를 사용하더라도 컬럼에 인덱스가 있다면 원본 데이터를 정렬하는 것보다 대체로 인덱스 영역에서 데이터를 가지고 오니 큰 무리는 없습니다. 그러므로 ORDER BY를 사용했다면 오라클의 실행계획execution plan(SQL Developer에서 F12)을 반드시 확인하여 ORDER BY에 사용한 컬럼의 인덱스를 경유하는 지를 반드시 확인해야 합니다.

오라클에서 쿼리 실행전 중요한 구성요소중 하나는 오라클 옵티마이저oracle optimizer 입니다. 옵티마이저는 다양한 실행 경로를 생성하고 비용(COST)이 가장 적게드는 최적의 실행경로를 선택합니다.

실행계획은 오라클 옵티마이저(Oracle Optimizer)가 SQL문장의 실행을 위해 테이블의 데이터를 원본에서 하나씩 읽어서 접근할 것인지, 어떤 인덱스에서 랜덤 액세스를 통해 접근할 것인지, 어떤 SQL연산을 사용할 것인지, 어떤 조인순서로 조인을 하고, 어떤 조인을 사용할 것인지 등에 대해 계획을 수립한 것 입니다.

실습

이번에는 ename 컬럼의 인덱스를  내림차순으로 생성한 후 ename 내림차순으로 데이터를 조회해 보겠습니다.

ename 컬럼에 생성되어 있는 IDX_EMP_ENAME 인덱스를 삭제하고 내림차순 인덱스를 다시  생성하세요.

DROP INDEX IDX_EMP_NAME;

CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME DESC);

EMP 테이블에서 모든 사원의 empno, ename 컬럼 데이터를 조회하세요.(단 ename 내림차순으로 정렬)

SELECT EMPNO, ENAME FROM EMP ORDER BY ENAME DESC;

<실행결과>

	EMPNO	ENAME
1	7521	WARD
...	...	...
13	7499	ALLEN
14	7876	ADAMS

ename 컬럼을 기준으로 내림차순 정렬되어 있습니다.

이번에는 ORDER BY 구문을 이용하지 않고 인덱스를 경유하도록 해서 데이터를 조회해 보겠습니다.

EMP 테이블에서 모든 사원의 empno, ename 컬럼 데이터를 조회하세요.(단 ename 내림차순으로 정렬)

SELECT EMPNO, ENAME FROM EMP WHERE ENAME > 'A';

<실행결과>

	EMPNO	ENAME
1	7521	WARD
...	...	...
13	7499	ALLEN
14	7876	ADAMS

ORDER BY를 사용하지 않았지만 WHERE절에 ename 컬럼을 출현시켜 오라클에게 ename 컬럼 인덱스를 경유하여 데이터를 추출하도록 했습니다. WHERE절에 컬럼을 출현시키는 방법이외에 오라클의 힌트 구문을 이용하여 원하는 인덱스를 경유하여 데이터를 추출하도록 할 수 있습니다.

 

#인덱스, #오름차순인덱스, #INDEX, #오라클인덱스, #오라클, #오라클강좌, #오라클교육, #ORACLE​