oracle 인덱스 구조와 사용법 및 확장 기능

인덱스 구조

이번 게시글에서는 기본적인 인덱스 구조와 탐색 원리를 확인한다. 인덱스의 기본 구조와 탐색원리는 가장 기본적인 내용이다. 인덱스에 대한 정확한 이해는 sql을 개발하거나 튜닝 시 가장 필요한 전제조건이다. 인덱스에 대한 기본적인 이해가 끝나면 인덱스 사용법과 확장기능을 확인한다.

oracle 인덱스 구조

oracle 인덱스 구조에 대한 이해는 랜덤 액세스 최소화를 위해 아주 중요한 부분이다. 그 이유는 랜덤 액세스를 하면 디스크 I/O가 많이 발생하고 데이터베이스 성능이 느려진다. 우선 인덱스의 정의에 대해서 알아보자. 인덱스는 사용자의 요청한 데이터를 빠르고 효율적으로 액세스 하기 위한 오브젝트이다. 쉽게 말해 책의 목차와 같은 기능을 하는데 목차가 없다면 내가 원하는 정보를 찾기 위해 책 한 권을 전부 읽어야 한다는 불편함이 있다. 데이터베이스에서는 책의 목차와 같은 역할을 하는 것이 인덱스이며 인덱스가 없다면 모든 테이블을 처음부터 끝까지 읽어야 한다. 인덱스의 구조는 나무를 거꾸로 뒤집은 모양이며 루트, 브랜치, 리프로 이루어져 있다. 루트와 브랜치 블록은 각각 하위 블록에 대한 키값을 가지고 있으며 하위 블록에 저장된 키값의 범위를 나타낸다. 루트와 브랜치 블록의 가장 왼쪽 첫 번째 레코드를 LMC라 하는데 키값을 가진 첫 번째 레코드의 위치를 나타낸다. 테이블 레코드를 가리키는 값을 rowid라 하며, rowid는 데이터블록주소(DBA)와 로우 번호로 구성되어 있다. 인덱스 탐색 방법은 수직적 탐색과 수평적 탐색으로 나뉜다. 수직적 탐색은 인덱스 스캔 시작지점을 찾는 과정이다. 수직적 탐색은 루트, 브랜치 블록에서 이용되며 찾고자 하는 값과 같거나 큰 값이 보이면 바로 전 레코드가 가지고 있는 정보를 가지고 하위 블록으로 이동한다. 하위 블록으로 이동 후 조건절을 만족하는 레코드를 모두 찾고 rowid값을 얻기 위해 스캔하기 시작하는데 이를 수평적 탐색이라 한다.

사용법

인덱스는 찾고자 하는 단어들이 순서대로 모여있다. 그렇기 때문에 찾고자하는 값을 가공하게 되면 인덱스를 정상적으로 사용하지 못한다. 인덱스를 사용하여 스캔 시작점을 찾고 스캔을 하다가 중간에 멈추는 것을 index range scan이라 하며 이 경우가 인덱스를 정상적으로 사용한다고 할 수 있다. 이와 반대인 스캔 시작점을 찾을 수 없고 어디까지 찾아야 할지 모르는 경우를 인덱스 전체를 스캔하는 index full scan이라 한다. 인덱스 튜닝은 index range scan을 하지 않는 sql을 찾아 index range scan을 하게 하는 것이다. 그 이유는 스캔을 위한 시작점과 끝점을 알지 못해 ndex full scan을 하게 되면 비용이 많이 들기 때문이다. index range scan을 하는 조건은 다음과 같다. 인덱스의 선두 칼럼이 sql 조건절에 있어야 한다. 인덱스의 또 하나의 특징은 이미 인덱스의 값들이 정렬이 되어 있기 때문에 소트 연산을 따로 하지 않는다는 점이다. 하지만 order by에 들어가는 칼럼을 가공한다면 정렬 연산을 생략할 수 없다. 자동 형 변환이 편리하지만 성능에 문제가 생기는 경우도 있다. 실행에러가 아닌 결과 자체에 오류가 생기는 경우도 있다. 그렇기 때문에 자동 형변환 기능에 의존하지 않고 정확하게 형변환을 진행해야 한다.

 

확장 기능

위에서 살펴본 index range scan과 index full scan이 외에 여러 가지 방법의 인덱스 스캔 방식이 있다. index range scan은 가장 일반적인 액세스 방식이며, 루트 블록에서 리프 블록까지 수직 탐색 후 일정 범위만 스캔하는 방식이다. 인덱스 선두 칼럼을 가공하면  index range scan을 하지 않는다. index full scan은 말 그대로 인덱스의 모든 리프 블록을 수평적으로 스캔하는 것이다. 옵티마이저는 제대로 된 인덱스가 없다면 full table scan을 하는데 이때 비교적 크기가 작은 인덱스를 full scan 하는 것이 오히려 효과적이다. index unique scan은 수직적 탐색만으로 스캔을 하는 방식이다. 이 경우는 unique 인덱스가 존재해야 하며 '=' 조건으로 검색할 때 사용된다. index skip scan은 인덱스의 선두 칼럼의 값이 적고 후행 컬럼의 값이 많을 때 효과 적인 스캔 방법이며 필요 없는 블록을 건너뛰어 full table scan 보다 성능이 좋다는 장점이 있다. index fast full scan은 index full scan보다 속도가 빠르다. 그 이유는 인덱스 트리 구조를 무시하고 인덱스의 세그먼트 단위로 여러 블록을 한 번에 스캔을 하기 때문이다(multiblock I/O).