CH01.인덱스 원리와 활용 - 03. 다양한 인덱스 스캔 방식

(1) Index Range Scan
인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식입니다. B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 인덱스 방식이라고 할 수 있습니다.
대개 인덱스가 사용되는 실행계획을 보면 SQL에 문제가 없다고 판단할 수 있지만 실행계획 상에 Index Range Scan이 나타난다고 해서 항상 빠른 속도를 보장하는 것은 아닙니다. 인덱스를 스캔하는 범위(Range)를 얼마만큼 줄일 수 있느냐, 그리고 테이블로 액세스하는 횟수를 얼마만큼 줄일 수 있느냐가 관건이며, 이는 인덱스 설계와 SQL튜닝의 핵심 원리 중 하나입니다.

Index Range Scan이 가능하게 하려면 인덱스를 구성하는 선두 컬럼이 조건절에 사용되어야 합니다. 그렇지 못한 상황에서 인덱스를 사용하도록 힌트로 강제한다면 Index Full Scan방식으로 처리됩니다. Index Range Scan과정을 거쳐 생성된 결과집합은 인덱스 컬럼 순으로 정렬된 상태가 되기 때문에 이런 특징을 잘 이용하면 sort order by 연산을 생략하거나 min/max값을 빠르게 추출할 수 있습니다.

(2) Index Full Scan
수직적 탐색후 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식으로서, 대개는 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택됩니다. 루트블록과 브랜치 블록을 거쳐 가장 왼쪽에 위치한 첫 번째 리프 블록으로 이동 후 인덱스 전체를 수평적 탐색하는 방식입니다.

Index Full Scan의 효율성
인덱스 선두 컬럼이 조건절에 없으면 옵티마이저는 우선적으로 Table Full Scan을 고려합니다. 그런데 대용량 테이블이어서 Table Full Scan의 부담이 크다면 옵티마이저는 인덱스를 활용하는 방법을 생각해 보지 않을 수 없습니다. 인덱스가 차지하는 면적은 테이블보다 훨씬 적기 때문에, 만약 테이블 전체를 스캔하기보다 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 일부에 대해서만 테이블 액세스가 발생하도록 할 수 있다면 전체적인 I/O 효율 측면에서 이 방식이 유리합니다. 이때 옵티마이저는 Index Full Scan을 선택할 수 있습니다.

위 그림 처럼 반환되는 row가 전체 중 극히 일부라면 Table Full Scan보다는 Index Full Scan을 통한 필터링이 클 효과를 가져다 줍니다. 하지만 이런 방식은 적절한 인덱스가 없어 Index Range Scan의 차선책으로 선택된 것이므로 할 수 있다면 인덱스 구성을 조정해 주는 것이 좋습니다.

인덱스를 이용한 소트 연산 대체
Index Full Scan은 Index Range Scan과 마찬가지로 그 결과집합이 인덱스 컬럼 순으로 정렬되므로 sort order by 연산을 생략할 목적으로 사용될 수 도 있습니다. 이는 옵티마이저가 전략적으로 선택한 경우에 해당합니다.

select /*+first_rows */ * from emp
where sal > 1000
order by ename;
위의 쿼리를 수행한다고 했을 때 위의 그림처럼 대부분 사원이 연봉이 1,000을 초과하므로 Index Full Scan을 하면 거의 모든 레코드에 대해 테이블 액세스가 발생해 Table Full Scan보다 오히려 불리합니다. (index range scan도 마찬가지입니다.) 그럼에도 여기서 인덱스가 사용된 것은 first_rows 힌트를 사용해 옵티마이저 모드를 바꾸었기 때문입니다. 즉, 옵티마이저는 소트 연산을 생략함으로써 전체 집합 중 처음 일부만을 빠르게 리턴할 목적으로 Index Full Scan을 선택한 것입니다.

(3) Index Unique Scan
수직적 탐색만으로 테이터를 찾는 스캔 방식으로서, Unique 인덱스를 통해 ‘=‘ 조건으로 탐색하는 경우에 작동합니다.
Unique인덱스가 존재하는 컬럼은 중복 값이 발생하지 않도록 DBMS가 데이터 정합성을 관리해 줍니다. 따라서 해당 인덱스 키 컬럼 모두 ‘=‘ 조건으로 검색할 때는 데이터를 한건 찾는 순간 더이상 찾을 필요가 없기 때문에 수직적 탐색만을 사용합니다.
Unique 인덱스라도 범위검색 조건(between,부등호,like 검색)으로 검색할 때는 Index Range Scan으로 처리됩니다.
또한 Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan이 나타납니다. 예를 들어 주문 상품 PK 인덱스가[주문일자 + 고객ID + 상품 ID]로 구성했는데, 주문일자와 고객 ID로만 검색하는 경우를 말합니다.

(4) Index Skip Scan
인덱스 선두 컬럼이 조건절로 사용되지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택합니다. 또는 Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면 Index Full Scan방식을 사용합니다.
오라클은 인덱스 선두 컬럼이 조건절에 빠졌어도 인덱스를 활용하는 새로운 스캔방식을 9i부터 사용할 수 있는데 Index Skip Scan이 그것입니다. 이 스캔방식은 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용합니다.
Index Skip Scan은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 ‘가능성이 있는’ 리프 블록만 골라서 액세스하는 방식입니다. 이 방식을 사용하면 가장 좌측에 있는 리프 블록과 가장 마지막 리프블록도 항상 방문합니다.

버퍼 Pinning을 이용한 Skip 원리

그림에서는 첫 번째 리프 블록을 방문한 후에 다른 리프 블록으로 곧바로 점프해 나가는 것처럼 묘사했지만 리프 블록에 있는 정보만으로 다음에 방문해야 할 블록을 찾는 방법은 없습니다. 항상 그 위쪽에 있는 브랜치 블록을 재방문해서 다음 방문할 리프 블록에 대한 주소 정보를 얻어야 합니다.
그런데 오라클 리프 블록에는 자신의 상위 브랜치 또는 루트 블록을 가리키는 주소 정보를 갖고 있지 않습니다. 이때 버퍼 Pinning 기법이 활용되어 브랜치 블록 버퍼를 Pinning한 채로 리프 블록을 방문했자다 다시 브랜치 블록으로 되돌아와 다음 방문할 리프 블록을 찾는 과정을 반복하는 것입니다.
그리고 브랜치 블록들 간에도 서로 연결할 수 있는 주소정보를 갖지 않기 때문에 하나의 브랜치 블록을 모두 처리하고 나면 다시 그 상위 노드를 재방문하는 식으로 진행됩니다. 루트 또는 브랜치 블록을 재방문하더라도 Pinning 한 상태이기 때문에 추가적인 블록 I/O는 발생하지 않습니다.

Index Skip Scan이 작동하기 위한 조건
Index Skip Scan은 Distinct Value 개수가 적은 선두 컬럼이 조건절에서 누락됐고 후행 컬럼의 Distinct Value 개수가 많을 때 효과적이라고 했습니다. 하지만 인덱스 선두 컬럼이 누락됐을 때만 Index Skip Scan이 작동하는 것은 아닙니다.
일별업종거래_PK : 업종유형코드 + 업종코드 + 기준일자
인덱스 구성이 다음과 같을 때 선두 컬럼을 입력하고 중간 컬럼에 대한 조건절이 누락된 경우에도 Skip Scan이 사용될 수 있습니다.
예)
where 업종유형코드 = ‘01’ and 기준일자 between ‘020101’ and ‘030101’
또한 아래와 같이 Distinct Value가 적은 두 개의 선두컬럼이 모두 누락된 경우에도 유용하게 사용할 수 있습니다.
예)
where 기준일자 between ‘020101’ and ‘030101’
선두컬럼이 부등호, between, like와 같은 범위검색 조건일 때도 Index Skip Scan이 사용될 수 있습니다.
일별업종별거래_x01: 기준일자 + 업종유형코드
위와 같은 인덱스 구성일 때 기준일자가 02년 1월 1일부터 03년 1월 1일 구간에서 업종유형코드가 ‘01’인 레코드만 선택하고자 할때도 사용가능합니다.
예)
where 기준일자 between ‘020101’ and ‘030101’ and 업종유형코드 = ‘01’
인덱스 선두 컬럼이 범위검색 조건일 때 인덱스 스캔에서 비효율이 발생하는데 그럴 때 Index Skip Scan이 유용합니다. 힌트에 index_ss, no_index_ss를 통해 사용유도/사용방지 할 수 있습니다.

In-List iterator와의 비교
인덱스 구성 : 성별 + 연봉
쿼리)
select * from 사원
where 연봉 between 2000 and 4000 and 성별 in (‘남’,’여’);

위의 쿼리를 실행하면 실행계획에 INLIST ITERATOR로 표시된 부분이 나오는데 이는 조건절 in-list에 제공된 값의 종류만큼 인덱스 탐색을 반복 수행함을 뜻합니다. 즉, 남자 사원에 대한 연봉을 검색하기 위해 루트 블록을 읽어 리프블록까지 탐색 한 후 , 여자 사원에 대한 연봉을 다시 검색하기 위해 루트 블록을 다시 읽어 리프블록을 스캔합니다.
이렇게 쿼리 작성자가 직접 성별에 대한 조건식을 추가해주면 Index Skip Scan에 의존하지 않고 빠르게 결과집합을 얻을 수 있습니다. 위의 예처럼 in-list를 명시하려면 성별 값의 종류가 더 이상 늘지 않음이 보장되어야 합니다. 그리고 이 기법이 효과를 발휘하려면 in-list로 제공하는 distinct value가 적어야 합니다.

(5)Index Fast Full Scan
Index Full Scan보다 빠른 스캔방식입니다. 인덱스 트리구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read방식으로 스캔하기 때문에 Index Full Scan보다 빠릅니다.

위의 그림의 보면 인덱스의 논리적인 연결구조를 표시한 것이고 리프 블록간에 실제로는 양방향 연결 리스트(Double Linked List)구조를 갖지만 일방향으로 표시되어 있습니다.

위의 논리적인 구조를 물리적순서에 따라 재배치한 것입니다.
Index Full Scan에서는 인덱스의 논리적 구조를 따라 루트 -> 1번 브랜치 -> 1 -> 2 -> 3 -> 4 ... -> 10순으로 블록을 읽어 들입니다.
반면, Index Fast Full Scan은 물리적으로 디스크에 저장된 순서대로 인덱스 블록을 읽어들입니다. 처음에 Multiblock Read방식으로 1->2 ->10 ->3 -> 9번 순으로 읽고 그 다음엔 8 -> 7 -> 4 -> 5 ->6순으로 읽습니다. 루트와 두 개의 브랜치 블록도 읽지만 필요 없는 블록이므로 버립니다.

오라클 10g부터는 Index Full Scan 일 때도 Multiblock I/O 방식으로 읽는 경우가 있는데 테이블 액세스 없이 인덱스만 읽고 처리할 때가 그렇습니다. 인덱스를 스캔하면서 테이블을 Random 액세스 할 때는 9i이전 버전과 동일하게 테이블과 인덱스 블록 모두 Single Block I/O 방식으로 읽습니다.

Index Fast Full Scan의 특징
디스크로부터 대량의 인덱스 블록을 읽어야 하는 상황에서 큰 효과를 발휘합니다. 대신 인덱스 리프 노드가 갖는 연결 리스트 구조를 이용하지 않기 때문에 얻어진 결과집합이 인덱스 키 순서대로 정렬되지 않습니다.
관련 힌트는 index_ffs, no_index_ffs이고 쿼리에 사용되는 모든 컬럼이 인덱스 컬럼에 포함돼 있을 때만 사용 가능합니다.
Index Range Scan, Index Full Scan과 달리 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능한 것도 중요한 특징입니다. 병렬 쿼리 시에는 Direct Path Read 방식을 사용하기 때문에 I/O 속도가 더 빨라집니다.

(6) Index Range Scan Descending
Index Range Scan과 동일한 스캔방식이고 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다릅니다.
내림차순 정렬을 하고자할때 where 절 컬럼에 인덱스가 있으면 옵티마이저가 알아서 인덱스를 거꾸로 읽는 실행계획을 수립합니다.
만약 옵티마이저가 다른 선택을 한다면 index_desc 힌트를 사용해 그렇게 유도할 수 있습니다. 또한 max 값을 구하고자 할 때도 해당 컬럼에 인덱스가 있으면 인덱스를 뒤에서부터 한 건만 읽고 멈추는 실행계획이 자동으로 수립됩니다.

(7) And-Equal, Index Combine, Index Join
인덱스가 아무리 많아도 테이블당 하나만 사용하는 것이 일반적이지만 오라클은 두 개 이상 인덱스를 함께 사용하는 방법도 제공하는데 지금 설명하는 것이 그것입니다.

And-Equal
10g부터는 폐기된 기능이며, 단일 컬럼의 Non-Unique 인덱스여야 함과 동시에 인덱스 컬럼에 대한 조건절이 ‘=‘ 상태여야 합니다.

Index Combine
동작원리는 아래와 같습니다.
1.일반 B*Tree 인덱스를 스캔하면서 각 조건을 만족하는 레코드의 rowid 목록을 얻습니다.
2. 1단계에서 얻은 rowid 목록을 가지고 비트맵 인덱스 구조를 하나씩 만듭니다. 이미 비트맵 인덱스가 있는 테이블에 대해서는 1,2번과정을 생략합니다.
3. 비트맵 인덱스에 대한 Bit-Wise 오퍼레이션을 수행합니다.
4. Bit-Wise 오퍼레이션 수행 결과가 참(true)인 비트 값들을 rowid로 환산해 최종적으로 방문할 테이블 rowid목록을 얻습니다.
5.rowid를 이용해 테이블을 액세스 합니다.
데이터 분포도가 좋지 않은 두 개 이상의 인덱스를 결합해 테이블 Random 액세스량을 줄이는데 목적이 있습니다. 조건절이 ‘=‘이어야 할 필요가 없고, Non-Unique인덱스일 필요도 없습니다. 비트맵 인덱스를 이요하므로 조건절이 or로 결합된 경우에도 유용하게 사용가능합니다.

Index Join
한 테이블에 속한 여러 인덱스를 이용해 테이블 액세스 없이 결과집합을 만들 때 사용하는 인덱스 스캔 방식입니다.
1.크기가 비교적 적은 쪽 인덱스에서 키 값과 rowid를 읽어 PGA메모리에 해시맵을 생성합니다. 해시 키로는 rowid가 사용됩니다.
2.다른 쪽 인덱스를 스캔하면서 앞서 생성한 해시 맵에 같은 rowid값을 갖는 레코드가 있는지 탐색합니다.
3.rowid 끼리 조인에 성공한 레코드만 결과집합에 포함시킵니다. 어떤 rowid 값이 양쪽 인덱스 집합 모두에 속한다면 그 rowid가 가리키는 테이블은 각 인덱스 컬럼에 대한 검색조건을 모두 만족한다는 것을 의미합니다.
Index Join은 쿼리에 사용된 컬럼들이 인덱스에 모두 포함될 때만 작동합니다. 양쪽 모두에 포함될 필요는 없고, 한쪽에 모두 포함되기만 하면 됩니다.