Notice
Recent Posts
Recent Comments
Link
BGP's Blog
오라클 인덱스 본문
# 인덱스의 유형과 특징
우리는 일반적으로 인덱스를 키(Key)라는 생각으로 접근을 하고 있다. 엄밀한 의미에서는 이들의 개념은 확실히 다르다. 인덱스는 사용자가 임의적으로 생성하고, 변경하며, 삭제할 수 있는 데이터베이스 내에 실질적으로 저장되는 물리적 구조체를 말한다.
그러나 키는 주로 데이터의 업무규칙을 강화시키기 위해 부여하는 각종 무결성(Integrity)에 부합되는 논리적인 개념이다.
인덱스는 단순하게 색인이라는 개념에 앞서 옵티마이저가 실행계획을 수립할 때 최적의 경로를 찾도록 하는 '전략적 요소'라는 시각에서 접근하여야 한다.
일반적인 테이블에서 행들은 관계 데이터베이스 이론의 핵심적인 개념을 만족시키기 위해 특정한 순서에 따르지 않고 저장된다.
행이 처음 삽입될 때 사용자는 행이 보관될 물리적인 위치를 선택할 수 없다. 이것은 테이블에서 특정한 행을 찾기 위해서는 찾고자 하는 행을 만날 때까지 순차적으로 검색해야 함을 의미한다. 또한, 지정한 조건에 일치하는 행을 찾았다고 해도 테이블의 논리적인 끝에 도달하기 전까지는 검색을 멈출 수 없다. 이것은 일치하는 행을 찾았다고 해도 조건에 맞는 행이 더 이상 없다는 확신이 없기 때문이다.
이와 같은 정보의 검색 방식을 완전 테이블 검색(full table scan)이라 부르며, 완전 테이블 검색을 수행하기 위해서는 하이 워터마크(high water mark)라 불리는 테이블의 논리적 끝에 이를 때까지 테이블의 모든 블록을 로드하고 읽어야 한다.
CREATE INDEX EMP_NAME_IDX
ON EMP(NAME) ;
이 SQL 구문은 테이블에 대한 완전 검색을 다시 수행하여, 각 레코드의 name 필드를 얻고, 이를 알파벳 오름차순으로 정렬한 인덱스로 만든다.
오라클은 얻은 각각의 이름을 행의 rowid와 연결하며, 프로세스가 완료되면 새 인덱스를 얻게된다.
*rowin - rowid는 테이블 내의 행의 물리적 주소로서, 객체가 어디에 어떤 파일에 있으며, 어떤 블록에 포함되어 있는지를 알려준다.
# 인덱스의 효율
테이블에 새로운 행을 삽입하기 위해서는 인덱스에도 대응되는 항목을 추가해야 한다. 즉, 한 번이 아닌 두번의 삽입 작업이 필요하다는 것이며, 결과적으로 인덱스의 존재 때문에 삽입 작업의 속도가 떨어진다.
게다가 삽입 작업 뿐 아니라 인덱스에 영향을 주는 갱신이나 삭제 작업에도 역시 인덱스를 최신의 것으로 유지하는 부가적인 처리가 필요하다. 즉, 일반적으로 인덱스는 DML(INSERT, UPDATE, DELETE, 쿼리)을 느리게 만든다.(인덱스를 과용하지 말아야 하는 이유이다.)
# 행 삽입이 인덱스에 미치는 영향
테이블로의 삽입은 인덱스 구조에 상당한 변화를 필요로 하며, 때로는 트리 계층의 위쪽으로도 구조를 변화시켜 인덱스의 높이를 증가시키고 성능을 감소시키기도 한다. 한편, 오라클의 인덱스 자동 재-밸런싱에 의해 3 이상의 높이를 가지는 인덱스도 큰 문제가 없다는 것에 주의.
일반적으로 인덱스의 삽입 작업의 효율을 떨어뜨리는 것은 인덱스의 높이가 아닌 인덱스 내의 변화 자체와 부가적인 블록을 얻는 과정이다.
# 행 갱신과 삭제가 인덱스에 미치는 영향
테이블 갱신은 인덱스 갱신으로 이어지지 않으므로 잎 노드 항목의 위치는 잘못된 상태로 남겨진다. 따라서 원래의 잎 항목을 사용되지 않도록 표시하고, 새로운 항목을 적절한 위치에 삽입해야 한다.
잎 노드의 잘못된 항목을 완전히 제거하지 않고 지워진 것으로 표시한 이유는 항목을 삭제하는데 더욱 많은 시간이 걸리기 때문이다. 인덱스를 이용하는 것은 DML 성능을 향상시키기 위한 것이지 악화시키기 위한 것이 아니다.
기반 테이블에서의 행 삭제 역시 비슷한 방법으로 처리된다. 행의 인덱스 항목은 삭제된 것으로 표시되지만, 실제 잎 노드가 점유하고 있던 공간을 회수하지는 않는다.
*DML의 대상이 되는 테이블의 특정한 열에 인덱스를 이용해야 하는지를 결정해야 하는 개발자들은 인덱싱의 단점(공간과 이용과 낭비, 부가적인 I/O, 테이블과 인덱스 모두를 관리하기 위해 느려지는 DML)과 이점(데이터 검색의 속도 증가)을 저울질 해야 한다.
인덱스는 여러 가지 단점을 가지고 있으며, 충분한 생각없이 이를 만드는 것은 좋은 생각은 아니다. 테이블에 만들어지는 인덱스의 수를 최소화하기 위해 노력해야 한다.
# 인덱스와 제약조건
테이블에 어떠한 제약조건을 선언한다는 것은 제약조건을 가진 열에 암시적으로 인덱스를 만들어야 한다는 것을 의미한다.
유일 인덱스를 요구하던 제약조건이 비활성화되면 인덱스는 경고없이 제거된다. 반면, 비-유일 인덱스를 요구하던 제약조건이라면 제약조건 으로 하던 일에 관계없이 인덱스는 유지된다.
제약조건을 다시 활성화시키면 앞에서 제거되었던 인덱스를 처음부터 다시 만들어야 한다는 것을 고려해야 한다. 그러나 인덱스를 만드는 데는 많은 I/O작업이 필요하며, 만들어지는 동안 테이블 잠금이 필요하기 때문에 다른 DML 실행을 막는 문제점이 있다.
다행히도 제약조건을 제어하여 제거되지 않는 비-유일 인덱스를 이용하도록 할 수 있다. 여기에서 이용되는 기법은 오라클8.0에서 추가된 개념인 연기가능 제약조건(deferrable constraint)을 이용하는 것이다.
연기된 제약조건은 트랜잭션이 승인을 시도하기 전까지는 확인이 되지 않는다. 즉, 오라클은 승인되기 전까지는 테이블에 적용될 제약조건들을 위반할 수도 있는 DML을 허용해야 한다.
예를들면, 기본키를 위반하는 10,000개의 행을 추가한다고 해도 삽입 작업을 승이하기 전까지는 테이블에 이들 레코드를 허용해야 한다는 것이다.
# 역 키 인덱스 (Reverse Key Inddex)
행 데이터가 삽입될 때 테이블의 기본키를 자동 증가 일련 번호로 지정하는 경우를 상당히 자주 볼 수 있다. (EMP 테이블의 EMPNO 필드)이 열은 단순 증가 일련 번호라 불리며, 이와 같은 필드에 만들어지는 인덱스는 다중 사용자 애플리케이션을 느려지게 하는 것으로 악명이 높다.
일반적인 B-Tree 인덱스에서의 경우를 살펴보면, 새로운 직원을 테이블에 추가하더라도 앞부분의 잎 노드에 다시 방문할 필요가 없음을 알 수 있다.
기존의 항목들 사이에 새로운 항목을 추가할 필요가 없으므로 블록 분할이 일어나지 않는다. 이 경우 PCTFREE에 0이외에 다른 값을 지정할 필요가 없음을 알 수 있을 것이다. 정리하자면, 단순 증가 일련 번호의 인덱스는 잎 노드의 공간을 최대한 효율적으로 이용한다.
그러나 새로운 레코드를 테이블에 삽입하려 한다면, 이들은 모두 같은 잎 노드에 접근하려는 경쟁에 참여해야 한다.
단순 증가 일련 번호 인덱스의 마지막 잎 노드에 대한 경쟁은 노드 접근에 심각할 정도의 긴 대기 시간을 발생시킬 수 있으며, 이것은 곧 간단한 DML 실행을 위해서도 상당히 오랜 시간을 기다려야 함을 의미한다.
이러한 심각한 성능상의 장애를 막기 위해 필요한 것은 삽입될 항목을 인덱스에 분산시키는 메커니즘이며, 오라클 8.0에 추가된 역 키 인덱스(reverse key index)가 바로 이러한 기능이다.
역 키 인덱스의 근본 원리는 매우 간단하다. 구조와 형식에 있어 이 인덱스는 일반적인 B-Tree 인덱스와 동일하지만, 일련 번호의 레코드를 테이블에 추가할 때는 이를 역으로 취급하는 것이다. 예를 들어, 7891은 1987로, 7892는 2987로 취급하여 인덱스에 추가한다.
위와 같이 인덱스에 추가되는 항목들이 마지막 잎 노드에 집중되지 않고 모든 잎 노드에 분산될 수 있다.
이것은 잎 노드 내의 기존에 존재하는 항목 사이 새 항목이 추가 될 수 있음을 의미하는 것이며, 이는 곧 성능 하락과 공간 낭비의 문제를 발생시키는 블록 분할로 이어질 수 있다. 결과적으로 역 키 인덱스는 일반 인덱스에 비해서는 좀 더 크고 많은 빈 공간을 포함하며, 지정된 인덱스 항목을 얻기 위해 좀 더 많은 I/O 작업을 필요로 한다.
# 비트맵 인덱스
이 테이블에 일반 B-Tree 인덱스를 만들었다고 가정하고, GENDER 열을 이용한다면..
이 열에 대한 선택은 전체 행의 거의 절반을 리턴할 것이므로 이는 그다지 좋은 방법의 인덱스라고 볼 수 없다. 전체 행의 2%~5% 가량을 선택하는 쿼리를 실행할 때는 오라클이 인덱스를 이용하려하지 않는다. 따라서 전혀 활용성이 없는 이 인덱스는 아무런 이유없이 공간을 소비하고 DML의 효율을 낮추고 있는것이다. 이와 같이 뚜렷하게 다른 특성을 가지고 있지 않은 데이터는 낮은 집중도(cardinality)를 가지고 있다고 말할 수 있다.
(*집중도는 '다른 값을 가질 수 있는 경향'으로 정의할 수 있다.)
요점은 낮은 집중도와 B-Tree 인덱스는 잘 어울리지 못한다는 점이다. 그렇다면 낮은 집중도의 열을 선택하는 쿼리에는 항상 전체 테이블 검색을 수행할 수 밖에 없는 것인가? 다행스럽게도 그렇지는 않다. 이와 같은 열에는 비트맵 인덱스를 이용할 수 있기 때문이다.
create bitmap index emp_mgr_bmp
on emp(manager) ;
이 명령이 실행되면 테이블 스캔으로 MANAGER 열에 포함된 값들에 대한 실제 테이블이 만들어진다.(QS, RW)
이 실제 테이블은 일반적인 B-Tree 형식으로 보관되며, 각 잎 노드는 발견된 한 가지 값을 위한 전체 비트맵을 보관하는데 이용된다.
동일한 과정을 테이블의 나머지 두 열에 반복하여 DEPARTMENT와 GENDER 열의 비트맵 인덱스를 만들면 세 개의 인덱스를 한곳에서 얻을 수가 있으므로 쿼리를 매우 빠르게(인덱스를 통해) 처리할 수 있다.
옵티마이저는 이와 같은 쿼리를 실행하기 위해 GENDER 비트맵 인덱스에서 FEMALE 비트맵을 얻고, MANAGER 비트맵 인덱스에서 RW 비트맵을, 그리고 DEPARTMENT 비트맵 인덱스에서 30 비트맵을 얻는다. 그리고 얻은 비트맵을 상대로 불린(boolean) AND 연산을 수행한다.
요약하면, 비트맵 인덱스는 낮은 집중도의 열에 만들어질 수 있는 컴팩트한 객체이며(비트의 연속을 저장하므로 수백만 개의 행을 처리하더라도 그리 많은 공간이 필요치 않다.), AND연산과 동일하게 "OR" 연산을 수행할 수 있어 많은 행에 대한 다수 조건을 테스트하는데 매우 유리하다.
그리고 비트맵 인덱스의 중요한 특성은 하나의 잎 노드에 수백 개의 행을 포함하는 항목이 포함될 수 있다는 것이다.
테이블의 인덱스에 비트맵 인덱스가 있는 경우에는 동시 DML을 처리하기가 매우 힘들다. OLTP 애플리케이션은 항상 동시 DML을 수행해야 하므로 비트맵 인덱스와 OLTP 시스템은 절대 어울릴 수 없는 조합이다.
반면, 데이터 웨어하우징과 비트맵 인덱스는 매우 잘 어울리는 한 쌍이다. 데이터 웨어하우징에 필요한 데이터의 양은 엄청난 대규모이며, 낮은 집중도 속성에 대한 쿼리의 필요성이 높고, 동시 DML이 필요한 경우는 거의 없기 때문이다.
*요점 정리 두 가지
.비트맵 인덱스는 낮은 집중도의 데이터에만 유용하다고 했지만, 낮은 집중도라는 말을 절대적인 의미로 해석할 수 없다는 것이다. 우편번호 데이터를 비트맵 인덱스로 만드는 경우를 예로 들면, 이는 비교적 높은 집중도를 가지고 있지만, 수백만 가지의 주소 수에 비하면 비교적 낮은 집중도이다.
비트맵 인덱스가 유용하게 적용될 수 있는 것은 '비교적' 낮은 집중도의 데이터이다.
.테이블에 하나의 비트맵 인덱스를 만드는 것은 의미가 없다. 비트맵 인덱스가 의미를 가질 수 있으려면 여러 개의 다른 비트맵 인덱스와 결합하고 부울린 AND나 OR연산을 통한 결과가 있어야 하기 때문이다. 다른 말로하면, 다수의 열이 인덱스에 포함 되어야만 비트맵 인덱스를 효과적으로 이용할 수 있다는 것이다.
테이블에 다수의 인덱스를 만드는 것은 성능에 치명적인 영향을 주겠지만, 이는 비트맵 인덱스에는 해당 사항이 없는 이야기이다.
# 관계형 데이터베이스에서 적용하고 있는 인덱스 유형은
- B-Tree 인덱스
- 비트맵(Bitmap) 인덱스
- B-Tree 클러스터 인덱스
- 해쉬(Hash) 클러스터 인덱스
- 리버스 키(Reverse Key) 인덱스
- 비트맵 조인 인덱스(Bitmap join index)
- 함수기반(Function-based) 인덱스
등이 있다.
.B-Tree 인덱스
관계형 데이터베이스에서 가장 일반적으로 사용되는 인덱스라 할 수 있다.
구조는 나뭇가지의 모습과 유사하다. 줄기에서 차례로 가지가 뻗어 나가고 마지막 가지에 잎사귀가 달려있는 유형과 아주 흡사하다.
여기에 사용된 'B'에도 여러 가지 학설이 있으나 가장 보편적으로 공감하는 것은 바로 'Balanced'에서 비롯되었다는 주장이다.
이 주장에는 중요한 의미가 담겨있는데 균형이 잡혔다는 것은 바로 줄기에서 잎사귀에 이르는 깊이가 어떤 인덱스 로우에 대해서도 동일하다는 것이다.
테이블의 로우가 어떤 위치에 있든 동일한 처리방법과 속도로 접근할 수 있다는 것은 이 인덱스가 가지는 가장 큰 특징이다.
나무로 볼 때 이러한 개념은 나무의 크기에 거의 영향을 받지 않는다. 가지의 깊이가 조금만 더 들어가면 엄청난 가지를 가질 수 있고, 이는 기하급수의 원리로 증가하는 것이기 때문에 가지 깊이가 약간 늘어나는 것만으로도 천문학적인 잎사귀를 관리할 수 있다는 것이다.
이 인덱스 구조에도 나무처럼 가지에 해당하는 브랜치 블록(Branch Block)과 잎사귀에 해당하는 리프 블록(Leaf Block)이 있다.
이 그림에서 알 수 있듯이 B-Tree 인덱스는 루트 블록과 브랜치 블록, 리프 블록으로 구성되어 있다. 리프 블록이 증가하면서 분할이 발생하면 브랜치 블록이 추가된다.
우리가 인덱스를 경유하여 데이터를 액세스할 때 루트 블록에서 출발해서 브랜치 블록을 추적하여 리프 블록을 찾는다.
유일스캔(Unique Scan)이면 바로 테이블을 액세스하고 멈추지만 범위스캔일 때는 리프 블록을 계속해서 스캔한다.
우리는 일반적으로 인덱스를 키(Key)라는 생각으로 접근을 하고 있다. 엄밀한 의미에서는 이들의 개념은 확실히 다르다. 인덱스는 사용자가 임의적으로 생성하고, 변경하며, 삭제할 수 있는 데이터베이스 내에 실질적으로 저장되는 물리적 구조체를 말한다.
그러나 키는 주로 데이터의 업무규칙을 강화시키기 위해 부여하는 각종 무결성(Integrity)에 부합되는 논리적인 개념이다.
인덱스는 단순하게 색인이라는 개념에 앞서 옵티마이저가 실행계획을 수립할 때 최적의 경로를 찾도록 하는 '전략적 요소'라는 시각에서 접근하여야 한다.
일반적인 테이블에서 행들은 관계 데이터베이스 이론의 핵심적인 개념을 만족시키기 위해 특정한 순서에 따르지 않고 저장된다.
행이 처음 삽입될 때 사용자는 행이 보관될 물리적인 위치를 선택할 수 없다. 이것은 테이블에서 특정한 행을 찾기 위해서는 찾고자 하는 행을 만날 때까지 순차적으로 검색해야 함을 의미한다. 또한, 지정한 조건에 일치하는 행을 찾았다고 해도 테이블의 논리적인 끝에 도달하기 전까지는 검색을 멈출 수 없다. 이것은 일치하는 행을 찾았다고 해도 조건에 맞는 행이 더 이상 없다는 확신이 없기 때문이다.
이와 같은 정보의 검색 방식을 완전 테이블 검색(full table scan)이라 부르며, 완전 테이블 검색을 수행하기 위해서는 하이 워터마크(high water mark)라 불리는 테이블의 논리적 끝에 이를 때까지 테이블의 모든 블록을 로드하고 읽어야 한다.
CREATE INDEX EMP_NAME_IDX
ON EMP(NAME) ;
이 SQL 구문은 테이블에 대한 완전 검색을 다시 수행하여, 각 레코드의 name 필드를 얻고, 이를 알파벳 오름차순으로 정렬한 인덱스로 만든다.
오라클은 얻은 각각의 이름을 행의 rowid와 연결하며, 프로세스가 완료되면 새 인덱스를 얻게된다.
*rowin - rowid는 테이블 내의 행의 물리적 주소로서, 객체가 어디에 어떤 파일에 있으며, 어떤 블록에 포함되어 있는지를 알려준다.
# 인덱스의 효율
테이블에 새로운 행을 삽입하기 위해서는 인덱스에도 대응되는 항목을 추가해야 한다. 즉, 한 번이 아닌 두번의 삽입 작업이 필요하다는 것이며, 결과적으로 인덱스의 존재 때문에 삽입 작업의 속도가 떨어진다.
게다가 삽입 작업 뿐 아니라 인덱스에 영향을 주는 갱신이나 삭제 작업에도 역시 인덱스를 최신의 것으로 유지하는 부가적인 처리가 필요하다. 즉, 일반적으로 인덱스는 DML(INSERT, UPDATE, DELETE, 쿼리)을 느리게 만든다.(인덱스를 과용하지 말아야 하는 이유이다.)
# 행 삽입이 인덱스에 미치는 영향
테이블로의 삽입은 인덱스 구조에 상당한 변화를 필요로 하며, 때로는 트리 계층의 위쪽으로도 구조를 변화시켜 인덱스의 높이를 증가시키고 성능을 감소시키기도 한다. 한편, 오라클의 인덱스 자동 재-밸런싱에 의해 3 이상의 높이를 가지는 인덱스도 큰 문제가 없다는 것에 주의.
일반적으로 인덱스의 삽입 작업의 효율을 떨어뜨리는 것은 인덱스의 높이가 아닌 인덱스 내의 변화 자체와 부가적인 블록을 얻는 과정이다.
# 행 갱신과 삭제가 인덱스에 미치는 영향
테이블 갱신은 인덱스 갱신으로 이어지지 않으므로 잎 노드 항목의 위치는 잘못된 상태로 남겨진다. 따라서 원래의 잎 항목을 사용되지 않도록 표시하고, 새로운 항목을 적절한 위치에 삽입해야 한다.
잎 노드의 잘못된 항목을 완전히 제거하지 않고 지워진 것으로 표시한 이유는 항목을 삭제하는데 더욱 많은 시간이 걸리기 때문이다. 인덱스를 이용하는 것은 DML 성능을 향상시키기 위한 것이지 악화시키기 위한 것이 아니다.
기반 테이블에서의 행 삭제 역시 비슷한 방법으로 처리된다. 행의 인덱스 항목은 삭제된 것으로 표시되지만, 실제 잎 노드가 점유하고 있던 공간을 회수하지는 않는다.
*DML의 대상이 되는 테이블의 특정한 열에 인덱스를 이용해야 하는지를 결정해야 하는 개발자들은 인덱싱의 단점(공간과 이용과 낭비, 부가적인 I/O, 테이블과 인덱스 모두를 관리하기 위해 느려지는 DML)과 이점(데이터 검색의 속도 증가)을 저울질 해야 한다.
인덱스는 여러 가지 단점을 가지고 있으며, 충분한 생각없이 이를 만드는 것은 좋은 생각은 아니다. 테이블에 만들어지는 인덱스의 수를 최소화하기 위해 노력해야 한다.
# 인덱스와 제약조건
테이블에 어떠한 제약조건을 선언한다는 것은 제약조건을 가진 열에 암시적으로 인덱스를 만들어야 한다는 것을 의미한다.
유일 인덱스를 요구하던 제약조건이 비활성화되면 인덱스는 경고없이 제거된다. 반면, 비-유일 인덱스를 요구하던 제약조건이라면 제약조건 으로 하던 일에 관계없이 인덱스는 유지된다.
제약조건을 다시 활성화시키면 앞에서 제거되었던 인덱스를 처음부터 다시 만들어야 한다는 것을 고려해야 한다. 그러나 인덱스를 만드는 데는 많은 I/O작업이 필요하며, 만들어지는 동안 테이블 잠금이 필요하기 때문에 다른 DML 실행을 막는 문제점이 있다.
다행히도 제약조건을 제어하여 제거되지 않는 비-유일 인덱스를 이용하도록 할 수 있다. 여기에서 이용되는 기법은 오라클8.0에서 추가된 개념인 연기가능 제약조건(deferrable constraint)을 이용하는 것이다.
연기된 제약조건은 트랜잭션이 승인을 시도하기 전까지는 확인이 되지 않는다. 즉, 오라클은 승인되기 전까지는 테이블에 적용될 제약조건들을 위반할 수도 있는 DML을 허용해야 한다.
예를들면, 기본키를 위반하는 10,000개의 행을 추가한다고 해도 삽입 작업을 승이하기 전까지는 테이블에 이들 레코드를 허용해야 한다는 것이다.
# 역 키 인덱스 (Reverse Key Inddex)
행 데이터가 삽입될 때 테이블의 기본키를 자동 증가 일련 번호로 지정하는 경우를 상당히 자주 볼 수 있다. (EMP 테이블의 EMPNO 필드)이 열은 단순 증가 일련 번호라 불리며, 이와 같은 필드에 만들어지는 인덱스는 다중 사용자 애플리케이션을 느려지게 하는 것으로 악명이 높다.
일반적인 B-Tree 인덱스에서의 경우를 살펴보면, 새로운 직원을 테이블에 추가하더라도 앞부분의 잎 노드에 다시 방문할 필요가 없음을 알 수 있다.
기존의 항목들 사이에 새로운 항목을 추가할 필요가 없으므로 블록 분할이 일어나지 않는다. 이 경우 PCTFREE에 0이외에 다른 값을 지정할 필요가 없음을 알 수 있을 것이다. 정리하자면, 단순 증가 일련 번호의 인덱스는 잎 노드의 공간을 최대한 효율적으로 이용한다.
그러나 새로운 레코드를 테이블에 삽입하려 한다면, 이들은 모두 같은 잎 노드에 접근하려는 경쟁에 참여해야 한다.
단순 증가 일련 번호 인덱스의 마지막 잎 노드에 대한 경쟁은 노드 접근에 심각할 정도의 긴 대기 시간을 발생시킬 수 있으며, 이것은 곧 간단한 DML 실행을 위해서도 상당히 오랜 시간을 기다려야 함을 의미한다.
이러한 심각한 성능상의 장애를 막기 위해 필요한 것은 삽입될 항목을 인덱스에 분산시키는 메커니즘이며, 오라클 8.0에 추가된 역 키 인덱스(reverse key index)가 바로 이러한 기능이다.
역 키 인덱스의 근본 원리는 매우 간단하다. 구조와 형식에 있어 이 인덱스는 일반적인 B-Tree 인덱스와 동일하지만, 일련 번호의 레코드를 테이블에 추가할 때는 이를 역으로 취급하는 것이다. 예를 들어, 7891은 1987로, 7892는 2987로 취급하여 인덱스에 추가한다.
위와 같이 인덱스에 추가되는 항목들이 마지막 잎 노드에 집중되지 않고 모든 잎 노드에 분산될 수 있다.
이것은 잎 노드 내의 기존에 존재하는 항목 사이 새 항목이 추가 될 수 있음을 의미하는 것이며, 이는 곧 성능 하락과 공간 낭비의 문제를 발생시키는 블록 분할로 이어질 수 있다. 결과적으로 역 키 인덱스는 일반 인덱스에 비해서는 좀 더 크고 많은 빈 공간을 포함하며, 지정된 인덱스 항목을 얻기 위해 좀 더 많은 I/O 작업을 필요로 한다.
# 비트맵 인덱스
| ID | MANAGER | DEPT | GENDER | ETC |
| 70 | QS | 10 | M | … |
| 10 | RW | 10 | M | … |
| 60 | RW | 30 | F | … |
| 20 | QS | 20 | F | … |
| 40 | QS | 10 | M | … |
| 30 | RW | 30 | M | … |
| 50 | RW | 20 | F | … |
| … | … | … | … | … |
이 열에 대한 선택은 전체 행의 거의 절반을 리턴할 것이므로 이는 그다지 좋은 방법의 인덱스라고 볼 수 없다. 전체 행의 2%~5% 가량을 선택하는 쿼리를 실행할 때는 오라클이 인덱스를 이용하려하지 않는다. 따라서 전혀 활용성이 없는 이 인덱스는 아무런 이유없이 공간을 소비하고 DML의 효율을 낮추고 있는것이다. 이와 같이 뚜렷하게 다른 특성을 가지고 있지 않은 데이터는 낮은 집중도(cardinality)를 가지고 있다고 말할 수 있다.
(*집중도는 '다른 값을 가질 수 있는 경향'으로 정의할 수 있다.)
요점은 낮은 집중도와 B-Tree 인덱스는 잘 어울리지 못한다는 점이다. 그렇다면 낮은 집중도의 열을 선택하는 쿼리에는 항상 전체 테이블 검색을 수행할 수 밖에 없는 것인가? 다행스럽게도 그렇지는 않다. 이와 같은 열에는 비트맵 인덱스를 이용할 수 있기 때문이다.
create bitmap index emp_mgr_bmp
on emp(manager) ;
| QS | RW |
| 1 | 0 |
| 0 | 1 |
| 0 | 1 |
| 1 | 0 |
| 1 | 0 |
| 0 | 1 |
| 0 | 1 |
이 명령이 실행되면 테이블 스캔으로 MANAGER 열에 포함된 값들에 대한 실제 테이블이 만들어진다.(QS, RW)
이 실제 테이블은 일반적인 B-Tree 형식으로 보관되며, 각 잎 노드는 발견된 한 가지 값을 위한 전체 비트맵을 보관하는데 이용된다.
동일한 과정을 테이블의 나머지 두 열에 반복하여 DEPARTMENT와 GENDER 열의 비트맵 인덱스를 만들면 세 개의 인덱스를 한곳에서 얻을 수가 있으므로 쿼리를 매우 빠르게(인덱스를 통해) 처리할 수 있다.
| F | 0 | 0 | 1 | 1 | 0 | 0 | 1 |
| RW | 0 | 1 | 1 | 0 | 0 | 1 | 1 |
| 30 | 0 | 0 | 1 | 0 | 0 | 1 | 0 |
| AND | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
옵티마이저는 이와 같은 쿼리를 실행하기 위해 GENDER 비트맵 인덱스에서 FEMALE 비트맵을 얻고, MANAGER 비트맵 인덱스에서 RW 비트맵을, 그리고 DEPARTMENT 비트맵 인덱스에서 30 비트맵을 얻는다. 그리고 얻은 비트맵을 상대로 불린(boolean) AND 연산을 수행한다.
요약하면, 비트맵 인덱스는 낮은 집중도의 열에 만들어질 수 있는 컴팩트한 객체이며(비트의 연속을 저장하므로 수백만 개의 행을 처리하더라도 그리 많은 공간이 필요치 않다.), AND연산과 동일하게 "OR" 연산을 수행할 수 있어 많은 행에 대한 다수 조건을 테스트하는데 매우 유리하다.
그리고 비트맵 인덱스의 중요한 특성은 하나의 잎 노드에 수백 개의 행을 포함하는 항목이 포함될 수 있다는 것이다.
테이블의 인덱스에 비트맵 인덱스가 있는 경우에는 동시 DML을 처리하기가 매우 힘들다. OLTP 애플리케이션은 항상 동시 DML을 수행해야 하므로 비트맵 인덱스와 OLTP 시스템은 절대 어울릴 수 없는 조합이다.
반면, 데이터 웨어하우징과 비트맵 인덱스는 매우 잘 어울리는 한 쌍이다. 데이터 웨어하우징에 필요한 데이터의 양은 엄청난 대규모이며, 낮은 집중도 속성에 대한 쿼리의 필요성이 높고, 동시 DML이 필요한 경우는 거의 없기 때문이다.
*요점 정리 두 가지
.비트맵 인덱스는 낮은 집중도의 데이터에만 유용하다고 했지만, 낮은 집중도라는 말을 절대적인 의미로 해석할 수 없다는 것이다. 우편번호 데이터를 비트맵 인덱스로 만드는 경우를 예로 들면, 이는 비교적 높은 집중도를 가지고 있지만, 수백만 가지의 주소 수에 비하면 비교적 낮은 집중도이다.
비트맵 인덱스가 유용하게 적용될 수 있는 것은 '비교적' 낮은 집중도의 데이터이다.
.테이블에 하나의 비트맵 인덱스를 만드는 것은 의미가 없다. 비트맵 인덱스가 의미를 가질 수 있으려면 여러 개의 다른 비트맵 인덱스와 결합하고 부울린 AND나 OR연산을 통한 결과가 있어야 하기 때문이다. 다른 말로하면, 다수의 열이 인덱스에 포함 되어야만 비트맵 인덱스를 효과적으로 이용할 수 있다는 것이다.
테이블에 다수의 인덱스를 만드는 것은 성능에 치명적인 영향을 주겠지만, 이는 비트맵 인덱스에는 해당 사항이 없는 이야기이다.
# 관계형 데이터베이스에서 적용하고 있는 인덱스 유형은
- B-Tree 인덱스
- 비트맵(Bitmap) 인덱스
- B-Tree 클러스터 인덱스
- 해쉬(Hash) 클러스터 인덱스
- 리버스 키(Reverse Key) 인덱스
- 비트맵 조인 인덱스(Bitmap join index)
- 함수기반(Function-based) 인덱스
등이 있다.
.B-Tree 인덱스
관계형 데이터베이스에서 가장 일반적으로 사용되는 인덱스라 할 수 있다.
구조는 나뭇가지의 모습과 유사하다. 줄기에서 차례로 가지가 뻗어 나가고 마지막 가지에 잎사귀가 달려있는 유형과 아주 흡사하다.
여기에 사용된 'B'에도 여러 가지 학설이 있으나 가장 보편적으로 공감하는 것은 바로 'Balanced'에서 비롯되었다는 주장이다.
이 주장에는 중요한 의미가 담겨있는데 균형이 잡혔다는 것은 바로 줄기에서 잎사귀에 이르는 깊이가 어떤 인덱스 로우에 대해서도 동일하다는 것이다.
테이블의 로우가 어떤 위치에 있든 동일한 처리방법과 속도로 접근할 수 있다는 것은 이 인덱스가 가지는 가장 큰 특징이다.
나무로 볼 때 이러한 개념은 나무의 크기에 거의 영향을 받지 않는다. 가지의 깊이가 조금만 더 들어가면 엄청난 가지를 가질 수 있고, 이는 기하급수의 원리로 증가하는 것이기 때문에 가지 깊이가 약간 늘어나는 것만으로도 천문학적인 잎사귀를 관리할 수 있다는 것이다.
이 인덱스 구조에도 나무처럼 가지에 해당하는 브랜치 블록(Branch Block)과 잎사귀에 해당하는 리프 블록(Leaf Block)이 있다.
이 그림에서 알 수 있듯이 B-Tree 인덱스는 루트 블록과 브랜치 블록, 리프 블록으로 구성되어 있다. 리프 블록이 증가하면서 분할이 발생하면 브랜치 블록이 추가된다.
우리가 인덱스를 경유하여 데이터를 액세스할 때 루트 블록에서 출발해서 브랜치 블록을 추적하여 리프 블록을 찾는다.
유일스캔(Unique Scan)이면 바로 테이블을 액세스하고 멈추지만 범위스캔일 때는 리프 블록을 계속해서 스캔한다.
'노트' 카테고리의 다른 글
| UML Diagram의 종류 (0) | 2010.02.05 |
|---|---|
| [UML] UML 소개 (0) | 2008.09.30 |
'노트' Related Articles
more
