(ORACLE) 인덱스 구조

인덱스란?

대용량 테이블에서 필요한 데이터만을 빠르게 찾아서 실행할 목적으로 사용하는 객체이다.

키값으로 정렬 되어있는 것이 특징.

 

범위스캔 : 인덱스의 이러한 특징을 이용하여 검색 조건에 일치하지 않는 값을 만났을때 멈출 수있는 특징

(단, 키값이 같다면 rowid로 우선순위를 판별한다.)

---

인덱스의 기본구조

1. 브랜치 블록 (root포함) : 하위 노드 블록을 찾아가기위한 data block address를 갖는다

2. 리프블록 : 인덱스 key column, rowid 포함

※lmc(LeftMostChild) : 키 값을 가진 첫 번째 엔트리보다 작은 값

 

오라클은 인덱스 구성 컬럼이 모두 null인 레코드는 저장하지 않는다.

 

 

1) 리프 노드상의 인덱스 레코드와 테이블 레코드 간에는 1:1관계

2) 리프 노드상의 키 값과 테이블 레코드 키 값은 서로 일치

3) 브랜치 노드상의 레코드 개수는 하위 레벨 블록 개수와 일치

4) 브랜치 노드상의 키 값은 하위 노드가 갖는 값의 범위를 의미

---

인덱스의 탐색 

1. 수직적 탐색 : 범위 스캔을 위한 시작 지점을 찾는 탐색

2. 수평적 탐색 : 범위 스캔

인덱스를 이용한 튜닝

일별지수업종별거래및시세_PK : 지수구분코드 + 지수업종코드 + 거래일자
일별지수업종별거래및시세_X01 : 거래일자

거래일자 between :startDd and :endDd
and 지수구분코드 \|\| 지수업종코드 in ('1001', '2003');  => 거래일자 인데스 사용 혹은 Full Table Scan
=>
거래일자 between :startDd and :endDd
and (지수구분코드, 지수업종코드) in (('1', '001'), ('2', '003')); => PK 인덱스 사용

 

접수정보파일_PK : 수신번호
접수정보파일_X01 : 정정대상접수번호 + 금감원접수번호

decode(정정대상접수번호, lpad(' ', 14), 금감원접수번호, 정정대상접수번호) = :접수번호 => Full Table Scan
=>
정정대상접수번호 in (:접수번호, lpad(' ', 14))
and 금감원접수번호 = decode(정정대상접수번호, lpad(' ', 14), :접수번호, 금감원접수번호) 
=> 접수정보파일_X01 Index Range Scan

 

다양한 인덱스 스캔

(1) Index Rang Scan

• 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식.(p.40 그림 1-4)
• INDEX (RANGE SCAN) OF 'TBL_IDX' (INDEX)
• 인덱스를 스캔하는 범위를 얼마만큼 줄일 수 있느냐, 테이블로 액세스하는 횟수를 얼마만큼 줄일 수 있느냐가 인덱스 설계와
  sql 튜닝의 핵심 원리.
• 인덱스를 구성한는 선두 컬럼이 조건절에 사용되어야 한다.
• 생성된 결과집합은 인덱스 컬럼 순으로 정렬된 상태이므로 sort order by 연산을 생략하거나 min/max 값을 빠르게 추출할 수 있다.

(2) Index Full Scan

• 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식. (p.41 그림 1-5)
  (실제로는 수직적 탐색이 먼저 일어난다. 가장 왼쪽의 리프 블록을 찾아가기 위해)
• 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.
• INDEX (FULL SCAN) OF 'TBL_IDX' (INDEX)

Index Full Scan의 효율성

• 인덱스 선두 컬럼이 조건절에 없다면 옵티마이저는 우선적으로 Table Full Scan 고려
• 테이블 전체를 스캔하기보다 인덱스 스캔 단계에서 대부분 레코드를 필터링 하고 일부에 대해서만 테이블 액세스가 발생하도록
  할 수 있다면 옵티마이저는 Index Full Scan 방식을 선택.(p.42 그림 1-6)
• 적절한 인덱스가 없어 차선책으로 선택한 것이므로, 인덱스 구성을 조정해 주는 것이 좋다.

(3) Index Unique Scan

• 수직적 탐색만으로 데이터를 찾는 스캔방식. 
• Unique 인덱스를 통해 인덱스 키 컬럼 모두 '=' 조건으로 탐색하는 경우에 동작
• INDEX (UNIQUE SCAN) OF 'TBL_IX' (UNIQUE)
• 범위검색 조건, 일부 컬럼만으로 검색시에는 Index Range Scan으로 처리

(4) Index Skip Scan

• 9i 버전부터 가능
• 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용
• 루트 또는 브랜치 블록에서 얽은 컬럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 "가능성이 있는" 리프 블록만
  골라서 액세스하는 방식
• 첫번째 리프 블록, 마지막 리프블록은 항상 방문.

(5) Index Fast Full Scan

• 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔.
• p.51 그림 1-12는 그림 1-11을 물리적 순서에 따라 재배치 한 것.
• Index Full Scan에서는 인덱스의 논리적이 구조를 따라 블록을 읽는다
  (루트 -> 브랜치1 -> 1 -> 2 -> 3 -> 4 -> 5 -> 6 -> 7 -> 8 -> 9 -> 10)
• Index Fast Full Scan은 물리적으로 디스크에 저장된 순서대로 인덱스 블록을 읽는다.
  (Multiblock Read 방식 : 1 -> 2 -> 10 -> 3 -> 9, 8 -> 7 -> 4 ->. 5 -> 6, 루트, 블랜치 블록은 읽지만 필요 없으므로 버린다.)

 

---

오라클 성능 고도화 원리와 해법 2 국내도서 저자 : 조시형 출판 : (주)비투엔컨설팅 2010.01.09 상세보기