권순용의 DB 이야기

B*TREE 인덱스의 이해

오라클 데이터베이스(DB)는 네 가지 인덱스를 제공하나 가장 광범위하게 쓰이는 인덱스는 하나다. 바로 B*TREE가 그것이다. 이 글에서는 대부분의 업무에 최적의 성능을 제공하는 B*TREE 인덱스를 좀 더 깊이 들여다본다.

테이블의 데이터가 수정되면 해당 테이블의 B*TREE 인덱스에서도 추가(Insert), 갱신(Update), 삭제(Delete) 등이 수행된다. 이러한 작업은 시스템 성능 저하의 원인이 될 수 있다.



● Insert Operation
테이블에 데이터가 추가되면 해당 테이블에 존재하는 인덱스에도 추가된 데이터의 인덱스 엔트리가 추가된다. 테이블에 추가되는 데이터의 인덱스 키(Key) 값의 저장 위치를 찾기 위해 인덱스 리프 블록을 확인해 찾는다. 그리고는 인덱스 블록의 여유 공간에 해당 인덱스 엔트리를 추가한다. 해당 인덱스 블록에 여유 공간이 없을 경우 해당 블록은 2개의 인덱스 리프 블록으로 분기돼 인덱스 키들은 다시 정렬되고 분기된 2개의 인덱스 리프 블록을 통해 추가된다. 이 때 리프 블록을 연결하고 있는 브랜치 블록과 루트 블록의 정보가 갱신될 수 있다.

● Delete Operation
앞서 설명한 Insert Operation과 정반대의 작업을 수행한다. 데이터가 삭제되면 해당 인덱스 엔트리의 연결이 끊어진다. 그렇게 함으로써 루트 블록으로부터 해당 인덱스 엔트리를 인식하지 못하게 한다. 삭제된 인덱스 엔트리의 공간은 추가를 위해 공간 해제를 수행하게 되며, 리프 블록에 하나의 인덱스 엔트리라도 남게 되면 인덱스 리프 블록은 유지되게 된다. 많은 인덱스 엔트리가 삭제되면 삭제된 공간은 반납된다. 그러나 해당 리프 블록의 전체 데이터가 삭제되지 않는 한 해당 리프 블록은 반납되지 않는다. 그러므로 해당 리프 블록에 새로운 인덱스 엔트리가 추가로 저장되지 않는다면 공간이 낭비되게 된다.

● Update Operation
삭제와 추가 작업이 동시에 수행되는 작업이다. 테이블에 데이터가 순차적으로 증가해 추가되는 경우 인덱스의 우측으로 인덱스 엔트리가 집중돼 B*TREE의 가장 중요한 특징인 균형(Balance)이 무너지게 된다. 또한 죄측 리프 블록으로 인덱스 엔트리가 추가되지 않기 때문에 좌측 리프 블록은 블록 사용률이 낮아지게 된다. 삭제가 많거나 또는 인덱스 키가 순차적으로 증가하며 추가되는 테이블은 주기적인 인덱스 재구성(Rebuild)을 통해 인덱스 균형(Balance)을 유지시켜줘야 한다.

그렇다면 이와 같은 인덱스는 어떻게 생성해야 하는가? 인덱스 생성에 대해 확인해 보자.

<리스트 1> B*TREE 인덱스 생성

SQL> CREATE (UNIQUE) INDEX DEPT_IDX ON DEPT(DNAME)

<리스트 1>의 SQL을 수행하면 B*TREE 인덱스가 생성된다. B*TREE 인덱스를 생성하기 위해서는 인덱스 키 컬럼을 정렬해야 하는데, 만약 많은 양을 정렬할 경우 임시 테이블스페이스에서 정렬을 수행하게 되므로 임시 테이블스페이스에 대한 I/O를 확인해야 한다. 인덱스 생성 중 대용량의 테이블에 대해 병렬 프로세싱(Parallel Processing)을 적용해 인덱스를 생성할 경우 속도 향상을 꾀할 수 있다. 또한 인덱스 생성 시 발생하는 로그(Log)는 전체 시스템 부하를 발생시키므로 Nologging 옵션을 설정하면 인덱스 생성 속도를 좀 더 높일 수 있다.

<리스트 2> 인덱스 생성 예

SQL> CREATE (Unique) INDEX DEPT_IDX 
     ON DEPT(DNAME)
     NOLOGGING 
     PARALLEL 8

<리스트 2>처럼 인덱스를 생성할 때에는 주의해야 한다. PARALLEL 8 옵션을 설정하고 인덱스를 생성하면 해당 인덱스의 속성 중 DEGREE 값이 8이 되므로 해당 인덱스를 이용하는 SQL은 원하지 않아도 PARALLEL이 수행될 수 있다. 그러므로 PARALLEL 옵션을 설정해 생성한 인덱스는 DEGREE 값을 NOPARALLEL로 변경해야 한다.

지금부터는 B*TREE 인덱스의 장단점을 살펴보자. B*TREE 인덱스의 장단을 명확히 이해해야만 인덱스를 보다 효과적으로 사용할 수 있을 것이다.

● B*TREE 인덱스의 장점
B*TREE 인덱스는 가장 많이 사용되는 인덱스로, 다음과 같은 장점을 가지고 있다.

· 인덱스를 이용해 데이터에 엑세스할 때 모든 인덱스 엔트리에 대해 균형(Balance)을 맞춤
· 인덱스를 이용한 범위(Range) 스캔 시 Double Linked List를 사용하기 때문에 다른 인덱스보다 더 유리하며 ACESENDING과 DESCENDING도 가능함
· OLTP로 적은 데이터에 엑세스하는 데 유리함



● B*TREE 인덱스의 단점
B*TREE 인덱스의 단점은 다음과 같다. 이들 단점들은 B*TREE 인덱스의 아키텍처에 기인하고 있다.

· 분포도(Cardinality)가 낮은 컬럼의 경우 B*TREE 인덱스가 불리함
· OR 연산자에 대해 테이블 전체를 스캔(Full Scan)하는 것은 위험함
· 인덱스 연산 불가
· 인덱스 확장 시 부하 발생

출처 : 마이크로소프트웨어 8월호

제공 : 데이터 전문가 지식포털 DBguide.net