인덱스 확장기능 사용법
인덱스 스캔 방식
인덱스 스캔 방식에는 Index Full Scan, Index Unique Scan, Index Skip Scan, Index Fast Full Scan 등이 있다.
2.3.1 Index Range Scan
Index Range Scan는 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 '필요한 범위(Range)만' 스캔한다.
인덱스를 Range Scan 하려면 선두 컬럼을 가공하지 않는 상태로 조건절에 사용해야 한다.
인덱스를 잘 타면 성능도 OK라고 생각할 수도 있지만,
성능은 인덱스 스캔 범위, 테이블 엑세스 횟수를 얼마나 줄일 수 있느냐로 결정된다.
2.3.2 Index Full Scan
Index Full Scan은 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.
Index Full Scan의 효용성
인덱스 스캔단계에서 대부분 레코드를 필터링하고 아주 일부만 테이블을 엑세스하는 상황이라면, 면적이 큰 테이블보다 인덱스를 스캔하는 쪽이 유리하다.
인덱스를 이용한 소트 연산 생략
인덱스를 Full Scan하면 Range Scan과 마찬가지로 결과집합이 인덱스 컬럼 순으로 정렬된다.
따라서 Sort Order By 연산을 생략할 목적으로 사용할 수도 있다.
사용자가 처음 의도(부분범위 처리 활용)와 달리 fetch를 멈추지 않고 데이터를 끝까지 읽는다면 Table Full Scan 보다 훨씬 더 많은 I/O를 일으키고 결과적으로 수행 속도도 훨씬 더 느려진다는 사실이다.
이는 결코 옵티마이저의 잘못이 아니며, first_rows 힌트를 사용한 사용자에게 책임이 있다.
2.3.3 Index Unique Scan
Index Unique Scan은 수직적 탐색만으로 데이터를 찾는 스캔 방식으로서, Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동한다.
Unique 인덱스라고 해도 범위검색 조건(between, 부등호, like)으로 검색할 때는 Index Range Scan으로 처리된다.
Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan이 나타난다. 예를 들어, 주문상품 PK 인덱스를 '주문일자 + 고객ID + 상품ID'로 구성했는데, 주문일자와 고객ID로만 검색하는 경우를 말한다.
2.3.4 Index Skip Scan
Index Skip Scan은 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔방식이며, 오라클 9i 버전부터 사용 가능하다.
Index Skip Scan은 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다.
루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 '리프 블록'만 골라서 엑세스하는 스캔 방식이다.
2.3.5 Index Fast Full Scan
Index Fast Full Scan은 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하는 방식이다.
Index Full Scan은 인덱스의 논리적 구조를 따라 루트 -> 브랜치1 -> 1,2,3,...10 순으로 블록을 읽어들인다.
Index Fast Full Scan은 물리적으로 디스크에 저장된 순서대로 인덱스 리프블록들을 읽어들인다.
Index Fast Full Scan은 Multiblock I/O 방식을 사용하므로 디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과를 발휘한다.
쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때만 사용할 수 있다는 점도 꼭 기억해야 한다.
Index Range Scan 또는 Index Full Scan과 달리, 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능한 것도 중요한 특징 중 하나다.
Index Range Scan
- 인덱스 구조를 따라 스캔
- 결과집합 순서 보장
- Single Block I/O
- (파티션 돼 있지 않다면) 병렬 스캔 불가
- 인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능
Index Fast Full Scan
- 세그먼트 전체를 스캔
- 결과 집합 순서 보장 안됨
- Multi Block I/O
- 병렬스캔 가능
- 인덱스에 포함된 컬럼으로만 조회할 때 사용가능
2.3.6 Index Range Scan Descending
Index Range Scan과 기본적으로 동일한 스캔 방식이지만, 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점이 다르다.
옵티마이저가 인덱스를 거꾸로 읽지 않는다면, index_desc 힌트를 이용해 유도할 수 있다.
친절한 SQL 튜닝(디비안, 조시형 저)
