오라클 인덱스, 비트리 인덱스(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
