CS/DataBase
(ORACLE) 인덱스 구조
주누
2019. 11. 12. 01:08
인덱스란?
대용량 테이블에서 필요한 데이터만을 빠르게 찾아서 실행할 목적으로 사용하는 객체이다.
키값으로 정렬 되어있는 것이 특징.
범위스캔 : 인덱스의 이러한 특징을 이용하여 검색 조건에 일치하지 않는 값을 만났을때 멈출 수있는 특징
(단, 키값이 같다면 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, 루트, 블랜치 블록은 읽지만 필요 없으므로 버린다.)
 |
|