<오라클 성능 고도화 원리와 해법2> Ch01-03 다양한 인덱스 스캔 방식

오라클 성능 고도화 원리와 해법2 - Ch01-03 다양한 인덱스 스캔 방식

(1) Index Range Scan

Index Range Scan은 그림1-4처럼 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식이다. B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식이라고 할 수 있고, 실행 계획에는 아래와 같이 표시된다.

인덱스를 수직적으로 탐색한 후에 리프 블록에서 “필요한 범위만” 스캔한다고 했는데, 이는 범위 스캔(Range Scan)이 의미하는바를 잘 설명해주고 있다. 데이터베이스 프로그래밍에 경험이 많지 않은 초급 개발자는 대개 인덱스가 사용되는 실행 계획을 보면 자신이 작성한 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은 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식으로서, 대개는 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다. 아래는 Index Full Scan할 때의 실행 계획이다.

수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로만 탐색한다고 했는데, 이는 개념적으로 설명하기 위한 것일 뿐 실제로는 그림1-5처럼 수직적 탐색이 먼저 일어난다. 루트 블록과 브랜치 블록을 거치지 않고는 가장 왼쪽에 위치한 첫번째 리프 블록으로 찾아갈 방법이 없기 때문이다. 그래서 이 과정을 그림1-5에 점선으로 표시한 것이다.

Index Full Scan의 효용성

위 SQL처럼 인덱스 선두 컬럼(ename)이 조건절에 없으면 옵티마이저는 우선적으로 Table Full Scan을 고려한다. 그런데 대용량 테이블이어서 Table Full Scan의 부담이 크다면 옵티마이저는 인덱스를 활용하는 방법을 다시 생각해보지 않을 수 없다.

데이터 저장공간은 ‘가로 X 세로’ 즉, ‘컬럼길이 X 레코드수’에 의해 결정되므로 대개 인덱스가 차지하는 면적은 테이블보다 훨씬 적게 마련이다. 만약 테이블 전체를 스캔하기보다 인덱스 스캔 단계에서 대부분의 레코드를 필터링하고 일부에 대해서만 테이블 액세스가 발생하도록 할 수 있다면 전체적인 I/O 효율 측면에서 이 방식이 유리하다. 이럴 때 옵티마이저는 Index Full Scan 방식을 선택할 수 있다.

아래는 Index Full Scan이 효과를 발휘하는 전형적인 케이스다.

그림1-6처럼 연봉이 5,000을 초과하는 사원이 전체 중 극히 일부라면 Table Full Scan보다는 Index Full Scan을 통한 필터링이 큰 효과를 가져다준다. 하지만 이런 방식은 적절한 인덱스가 없어 Index Range Scan의 차선책으로 선택된 것이므로, 할 수 있다면 인덱스 구성을 조정해 주는 것이 좋다.

인덱스를 이용한 소트 연산 대체

Index Full Scan은 Index Range Scan과 마찬가지로 결과집합이 인덱스 컬럼 순으로 정렬되므로 Sort Order By 연산을 생략할 목적으로 사용될 수도 있는데, 이는 차선책으로 선택됐다기보다 옵티마이저가 전략적으로 선택한 경우에 해당한다.

그림1-7에서 대부분 사원의 연봉이 1,000을 초과하므로 Index Full Scan을 하면 거의 모든 레코드에 대해 테이블 액세스가 발생해 Table Full Scan보다 오히려 불리하다. 만약 SAL이 인덱스 선두 컬럼이어서 Index Range Scan하더라도 마찬가지다. 그럼에도 여기서 인덱스가 사용된 것은 사용자가 first_rows 힌트를 이용해 옵티마이저 모드를 바꿨기 때문이다. 즉, 옵티마이저는 소트 연산을 생략함으로써 전체 집합 중 처음 일부만을 빠르게 리턴할 목적으로 Index Full Scan 방식을 선택한 것이다.

사용자가 그러나 처음 의도와 다르게 데이터를 읽기를 멈추지 않고 끝까지 fetch 한다면 Full Table Scan한 것보다 훨씬 더 많은 I/O를 일으키면서 서버 자원을 낭비하는 결과를 초래할 텐데, 이는 옵티마이저의 잘못이 결코 아니며 first_rows 힌트를 사용한 사용자에게 책임이 있다.

(3) Index Unique Scan

Index Unique Scan은 그림1-8처럼 수직적 탐색만으로 데이터를 찾는 스캔 방식으로서, Unique 인덱스를 통해 ‘=’ 조건으로 탐색하는 경우에 작동한다.

Unique 인덱스가 존재하는 컬럼은 중복값이 발생하지 않도록 DBMS가 데이터 정합성을 관리해준다. 따라서 해당 인덱스 키 컬럼을 모두 ‘=’ 조건으로 검색할 때는 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없다.

Unique 인덱스라도 범위 검색 조건(between, 부등호, like)으로 검색할 때는 Index Range Scan으로 처리된다. 예를 들어, empno >= 7788 조건으로 검색하는 경우 수직적 탐색만으로는 조건에 해당하는 레코드를 모두 찾을 수 없기 때문이다.

또한, Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan이 나타난다. 예를 들어, 주문 상품 PK 인덱스가 [주문일자 + 고객ID + 상품ID]로 구성됐는데, 주문일자와 고객ID로만 검색하는 경우를 말한다.

(4) Index Skip Scan

일단 테이블 전체를 스캔할 필요 없이 조건에 해당하는 레코드를 찾을 수 있으면 인덱스 스킵 스캔 방식을 사용한다고 생각해. 특히, 선두 컬럼이 누락된 경우에도 오라클은 새로운 스캔 방식을 도입했어. 그게 바로 인덱스 스킵 스캔이야. 이 방식은 선두 컬럼의 Distinct Value가 적고, 뒤따르는 컬럼의 Distinct Value가 많을 때 효과적이야.

인덱스 스킵 스캔은 리프 블록을 선택적으로 액세스하는 방식이야. 이때 루트나 브랜치 블록에서 읽은 정보를 기반으로 조건에 부합할 가능성이 있는 리프 블록만 선택하는 거야.

버퍼 Pinning을 이용한 Skip 원리

스킵 스캔 과정을 그림으로 보면, 첫 번째 리프 블록을 방문한 후에 다른 리프 블록으로 점프해 나가는 것처럼 생각할 수 있어. 그림에서는 점프하는 것처럼 표현했지만 실제로는 그렇지 않아. 항상 상위 블록을 재방문해서 다음 방문할 리프 블록에 대한 주소 정보를 얻어야 해.

오라클 리프 블록에는 자신의 상위 브랜치나 루트 블록을 가리키는 주소 정보를 갖고 있지 않아. 그래서 상위 노드를 다시 방문할 때 버퍼 Pinning 기법을 사용해야 해. 브랜치 블록 버퍼를 Pinning한 채로 리프 블록을 방문하고, 다시 브랜치 블록으로 돌아와 다음 방문할 리프 블록을 찾는 과정을 반복하는 거야. 상위 노드를 기준으로 하위 노드를 NL 조인하는 그림을 상상하면 이해하기 쉬울 거야.

그리고 브랜치 블록들 사이에는 서로 연결할 수 있는 주소 정보를 갖고 있지 않기 때문에 하나의 브랜치 블록을 모두 처리한 후에야 다시 상위 노드를 재방문하는 식으로 진행돼.

Index Skip Scan이 작동하기 위한 조건

인덱스 스킵 스캔은 Distinct Value 개수가 적은 선두 컬럼이 조건에서 빠진 경우와 뒤따르는 컬럼의 Distinct Value 개수가 많을 때 효과적이라고 했어. 하지만 인덱스의 선두 컬럼이 빠진 경우에만 작동하는 건 아냐.

In-List Iterator와의 비교

쿼리 작성자가 직접 성별에 대한 조건식을 추가해주면 인덱스 스킵 스캔에 의존하지 않고도 빠르게 결과 집합을 얻을 수 있어. 다만, 이렇게 In-List를 명시하려면 성별값의 종류가 더 이상 늘지 않음이 보장되어야 해. 그리고 이 튜닝 기법이 효과를 발휘하려면 인덱스 스킵 스캔과 마찬가지로 In-List로 제공하는 값의 종류가 적어야 해.

(5) Index Fast Full

말 그대로 Index Fast Full Scan은 Index Full Scan보다 빠르다. Index Fast Full Scan이 Index Full Scan보다 빠른 이유는, 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하기 때문이다.

반면, Index Fast Full Scan은 물리적으로 디스크에 저장된 순서대로 인덱스 블록을 읽어 들인다.

Index Fast Full Scan의 특징

Index Fast Full Scan은 Multiblock Read 방식을 사용하므로 디스크로부터 대량의 인덱스 블록을 읽어야 하는 상황에서 큰 효과를 발휘한다. 대신 인덱스 리프 노드가 갖는 연결 리스트 구조를 이용하지 않기 때문에 얻어진 결과 집합이 인덱스 키 순서대로 정렬되지 않는다.

관련 힌트는 indexffs와 no.indexfs이고, 쿼리에 사용되는 모든 컬럼이 인덱스 컬럼에 포함되어 있을 때만 사용 가능하다는 점도 염두에 두어야 한다.

Index Range Scan 또는 Index Full Scan과 달리, 인덱스가 파티션되어 있지 않더라도 병렬 쿼리가 가능한 것도 중요한 특징 중 하나다. 병렬 쿼리 시에는 Direct Path Read 방식을 사용하기 때문에 I/O 속도가 더 빨라진다.

Index Full Scan과 Index Fast Full Scan의 특징을 표로 요약해보면 다음과 같다.

Index Fast Full Scan을 활용한 튜닝 사항

거래 건 수가 21개 뿐이므로 Index Full Scan이 도움이 될 수 있는 상황이다. 공급 업체의 업체 명 컬럼에 생성한 인덱스는 사용 빈도가 높지 않아 가끔 조회될 때마다 디스크 I/O가 많이 발생한다.

그럴 때 Index Fast Full Scan이 매우 유용하게 사용될 수 있다. Index Fast Full Scan의 특징을 이해했다면 버퍼 캐시 히트율이 낮아 디스크 I/O가 많이 발생할 때 유리하다는 사실을 이해할 것이다!

다만, 이 스캔 방식은 필요한 모든 컬럼이 인덱스에 포함되어 있을 때만 사용 가능하다는 제약이 있다.

(6) Index Range Scan Descending

Index Range Scan과 기본적으로 동일한 스캔 방식이다. 그림 1-13처럼 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과 집합을 얻는다는 점만 다르다.

(7) And-Equal, Index Combine, Index Join

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

And-Equal

And-Equal은 바로 이야기 할 Index Combine 방식이 8i에서도 입증되었으나, 10g부터는 아예 폐기(deprecated)된 기능이다.

Index Combine

Index Combine은 And-Equal과 마찬가지로 데이터 분포도가 좋지 않은 두 개 이상의 인덱스를 결합해 테이블 Random 액세스량을 줄이는 데에 목적이 있다.

조건절이 ‘=’ 이어야 할 필요가 없고, Non-Unique 인덱스일 필요도 없다. 게다가, 비트맵 인덱스를 이용하므로 아래와 같이 조건절이 OR로 결합된 경우에도 유용하다.

Index Join

Index Join은 이름만 보면 두 테이블 간 조인에 사용되는 스캔 방식 같지만 그것과는 무관해. 오히려 한 테이블에 속한 여러 인덱스를 이용해 테이블 액세스 없이 결과 집합을 만들 때 사용하는 인덱스 스캔 방식이다.

Index Join은 쿼리에 사용된 컬럼들이 인덱스에 모두 포함될 때만 작동해. 양쪽 모두에 포함될 필요는 없고, 둘 중 어느 한 쪽에 포함되기만 하면 된다.