[SQLP] 2-3. 인덱스 스캔 방식

🧷 인덱스 스캔 방식

🖇️ Index Range Scan

인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후, 리프 블록에서 필요한 범위만 스캔하는 방식

 

B * Tree 인덱스에서 가장 일반적이고 정상적인 형태의 액세스 방식이다. 인덱스를 수직적으로 탐색한 후 리프 블록에서 필요한 범위만 스캔하는데, 이것은 범위 스캔을 의미한다.

 

 

Index Range Scan이 동작하려면 인덱스를 구성하는 선두 컬럼이 WHERE 조건절에 포함되어야 한다. 그렇지 않은 상태에서 힌트를 사용해 인덱스를 강제로 적용하면 Index Full Scan 방식으로 처리될 수 있다.

 

반대로, 선두 컬럼을 가공하지 않으면 무조건 Index Range Scan이 발생하기 때문에 인덱스를 사용한다고 해서 항상 성능이 좋은 것은 아니다.
결국 인덱스 스캔 범위를 줄이고 테이블 액세스를 최소화하는 것이 핵심이며, 이는 인덱스 설계와 SQL 튜닝의 중요한 원칙 중 하나다.

 

🖇️ Index Full Scan

인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이며, 일반적으로 최적의 인덱스가 없을 때 차선책으로 선택

 

 

인덱스 루트 블록과 브랜치 블록을 통한 수직적 탐색 없이 리프 블록 전체를 처음부터 끝까지 수평적으로 스캔한다. 그러나, 위 그림처럼 완전히 수직적 탐색 없이 진행된다고 이해하면 안 된다.

 

실제로는 루트 블록과 브랜치 블록을 거치지 않고는 가장 왼쪽에 위치한 첫 번째 리프 블록을 찾아갈 방법이 없기 때문에 수직적 탐색은 발생한다. 루트 블록과 브랜치 블록을 통해 가장 왼쪽에 위치한 첫 번째 리프 블록을 찾는 과정이 선행된 후에 수평적 탐색이 진행된다.

 

✔️ Index Full Scan이 발생하는 경우

 

아래 쿼리에서는 Index Full Scan이 발생했다. 이는 인덱스 선두 컬럼(STATUS)이 WHERE 절에 포함되지 않아 Index Range Scan이 불가능하지만, 후순위 인덱스 컬럼(CUSTOMER_ID)이 조건절에 포함되어 있기 때문이다.

 

 

즉, Index Range Scan을 사용할 수 없는 경우에도 옵티마이저가 Table Full Scan보다 Index Full Scan을 선택할 수 있다.

 

(1) Index Full Scan의 효용성

인덱스 선두 컬럼이 WHERE 절에 없으면 옵티마이저는 먼저 Table Full Scan을 고려하지만, 대용량 테이블의 경우 테이블 전체를 읽는 부담이 크기 때문에 인덱스 활용이 다시 검토될 수 있다.

 

이는 인덱스가 차지하는 면적이 테이블보다 훨씬 작기 때문이다. 데이터 저장 공간은 컬럼 길이 × 레코드 수 에 의해 결정되므로, 테이블 전체를 스캔하는 것보다 작은 크기의 인덱스를 활용하는 것이 I/O 효율 측면에서 유리할 수 있다.

 

따라서, 인덱스 스캔 단계에서 대부분의 레코드를 필터링하고 일부만 테이블 액세스할 수 있다면
옵티마이저는 Table Full Scan 대신 Index Full Scan을 선택할 가능성이 높다.

(2) Index Full Scan을 활용한 정렬 연산 생략

① 정렬(ORDER BY) 연산 최적화

 

Index Full Scan은 Index Range Scan과 마찬가지로 정렬된 인덱스를 스캔하기 때문에 ORDER BY를 포함한 쿼리에서 별도의 정렬 연산(SORT)을 생략할 수 있다. 따라서 정렬을 최적화하는 목적으로도 Index Full Scan이 사용될 수 있다.

 

② FIRST_ROWS 힌트

 

Index Full Scan은 특정 상황에서 Table Full Scan보다 성능이 더 불리할 수 있다. 특히, 인덱스 스캔 단계에서 대부분의 레코드를 필터링할 수 없고, 거의 모든 레코드에 대해 테이블 액세스를 수행해야 하는 경우 Index Full Scan보다 Table Full Scan이 더 효율적일 수 있다. (Index Range Scan도 마찬가지)

 

이러한 경우에도 옵티마이저는 FIRST_ROWS 힌트의 영향을 받아 인덱스 스캔 방식을 선택할 수 있다. 정렬 연산을 생략하여 전체 집합 중 처음 일부 데이터를 빠르게 출력하려는 목적 때문이다. 이 방식은 부분범위 처리가 가능한 상황에서 극적인 성능 개선효과를 가져다준다.

 

주의할 점은 사용자가 처음 의도한 부분범위 처리 활용과 달리 fetch를 멈추지 않고 전체 데이터를 읽어야 하는 경우, Table Full Scan보다 훨씬 더 많은 I/O를 발생시켜 성능이 저하될 수 있다.
이 경우, 성능 저하의 원인은 옵티마이저가 아니라, FIRST_ROWS 힌트를 사용한 사용자에게 있다.

 

🖇️ Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식

 

 

Index Unique Scan은 Unique 인덱스를 '=' 조건으로 탐색하는 경우에만 작동하는 스캔 방식이다.

Unique 인덱스가 존재하는 컬럼은 중복값이 입력되지 않도록 DBMS가 데이터 정합성을 관리하므로, 인덱스 키 컬럼을 모두 '='조건으로 검색하면 단 하나의 데이터만 찾으면 되며, 이후 추가적인 탐색이 필요 없다.

 

즉, Index Unique Scan은 '수직적 탐색'만으로 데이터를 빠르게 찾아내는 방식이다.

 

🖇️ Index Skip Scan

(1) Index Skip Scan 이란

일반적으로 인덱스의 선두 컬럼이 WHERE 절에 포함되지 않으면 옵티마이저는 Table Full Scan을 수행한다. 혹은, I/O를 줄이거나 정렬된 결과를 얻을 수 있는 경우 Index Full Scan을 선택하기도 한다.

 

그러나, Oracle 9i 버전부터 선두 컬럼이 빠진 경우에도 인덱스를 활용할 수 있도록 도입된 방식이 Index Skip Scan이다.
이 방식은 선두 컬럼의 Distinct Value 개수가 적고, 후행 컬럼의 Distinct Value 개수가 많을 때 효과적이다.

 

Index Skip Scan으로 유도하거나 방지하고자 할 때, index_ss, no_index_ss 힌트를 사용한다.

 

(2) Index Skip Scan 동작 원리

Index Skip Scan은 루트 블록이나 브랜치 블록에서 읽은 컬럼 값 정보를 기반으로, 조건절에 부합하는 가능성이 있는 리프 블록만 선택하여 액세스 하는 방식이다.

 

즉, 불필요한 블록은 건너뛰고(Skip) 필요한 블록만 액세스 하여 성능을 최적화한다. 이 과정에서 첫 번째 리프 블록과 마지막 리프 블록은 항상 방문하며, 그 중간에서는 필요한 블록만 골라서 탐색한다.

 

 

Index Skip Scan은 Index Range Scan이 불가능하거나 효율적이지 못한 상황에서 효과적으로 사용할 수 있다. 그러나, 이 방식이 항상 최선책은 아니며, 기본적으로 인덱스는 Index Range Scan을 목표로 설계하는 것이 바람직하다.

 

즉, 자주 실행되지 않는 SQL을 위해 인덱스를 추가하는 것이 비효율적일 때, 차선책으로 활용하는 전략이 바람직하다.

 

(3) 버퍼 Pinning을 이용한 Skip 원리

Index Skip Scan은 리프 블록에서 다음에 방문할 블록을 바로 찾을 수 없기 때문에, 상위 브랜치 블록을 재방문하여 다음 방문할 리프 블록의 주소 정보를 얻는 과정이 필요하다.

 

그러나, Oracle의 인덱스 구조상 리프 블록에는 상위 브랜치 블록(혹은 루트 블록)의 주소 정보가 포함되지 않는다. 이 문제를 해결하기 위해 버퍼 Pinning(Buffer Pinning) 기법이 활용된다.

 

❗버퍼 Pinning

특정 블록을 메모리에 유지(Pin)한 상태에서 필요한 리프 블록을 방문한 후, 다시 브랜치 블록으로 되돌아가는 방식

 

브랜치 블록을 Pinning 한 상태에서 리프 블록을 탐색하고, 필요할 때 다시 브랜치 블록으로 되돌아와 다음 리프 블록을 찾는 과정을 반복한다.

이 과정에서 루트 또는 브랜치 블록을 재방문하더라도, Pinning 상태이므로 추가적인 블록 I/O는 발생하지 않는다.

 

(4) 작동 조건

Index Skip Scan은 선두 컬럼의 Distinct Value 개수가 적고, 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다. 그러나 반드시 선두 컬럼이 빠져야만 작동하는 것은 아니다.

 

예를 들어, 인덱스 구성이 STATE - CUSTOMER_ID - ORDER_DATE로 구성되어 있다.

 

① 인덱스 선두 컬럼이 WHERE 절에서 누락되거나 범위검색 조건인 경우

 

Index Skip Scan은 WHERE 조건에서 선두 컬럼이 누락된 경우 외에도 범위 검색(BETWEEN, LIKE)을 사용할 때도 동작할 수 있다.

 

 

위의 쿼리에서 일반적인 Index Range Scan을 사용하면 STATUS가 'P%'를 만족하는 모든 인덱스 구간을 전부 스캔해야 한다.

 

그러나 Index Skip Scan을 사용하면, STATUS가 'P%'를 만족하는 인덱스 구간에서 CUSTOMER_ID = '2'를 포함할 가능성이 있는 리프 블록만 골라서 액세스 할 수 있다.

 

이처럼, 선두 컬럼이 누락된 경우말고도 범위 검색(LIKE, BETWEEN, <, >)을 사용할 때도 Index Skip Scan이 유용할 수 있다.

 

② 중간 컬럼이 WHERE 절에서 누락된 경우

 

인덱스 선두 컬럼이 없을 때만 작동하는 것은 아니다. 테이블에 선두 컬럼이 조건절에 포함되었지만, **중간 컬럼이 빠진 경우에도 Index Skip Scan을 사용할 수 있다.

 

 

③ Distinct Value 개수가 적은 선두 컬럼이 여러 개 누락된 경우

 

Index Skip Scan은 Distinct Value 개수가 적은 두 개 이상의 선두 컬럼이 누락된 경우에도 사용될 수 있다. 즉, 인덱스의 여러 개 선두 컬럼이 조건절에 포함되지 않더라도 Index Skip Scan이 가능할 수 있다.

 

🖇️ Index Fast Full Scan

(1) Index Fast Full Scan 이란?

Index Fast Full Scan은 인덱스의 논리적 트리 구조를 무시하고, 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하는 검색 방식이다. 이는 Index Full Scan보다 빠르며, 특정 상황에서 Table Full Scan을 대체할 수 있다.

 

Index Full Scan은 인덱스의 논리적 구조를 따라 루트 블록 → 브랜치 블록 → 리프 블록 순으로 순차적으로 탐색한다.

 

반면, Index Fast Full Scan은 논리적 인덱스 구조를 무시하고, 디스크에 저장된 물리적 순서대로 리프 블록을 읽는 방식이다.
이 과정에서 Multiblock I/O 기법이 사용되며, 한 번에 여러 개의 블록을 읽어 성능을 향상한다. Multiblock I/O 크기는 db_file_multiblock_read_count 파라미터에 의해 결정된다.

 

(2) Index Fast Full Scan의 특징

• Hint : index_ffs, no_index_ffs
• Multiblock I/O 방식을 사용하여 대량의 블록을 한 번에 읽는다.
• 속도는 빠르지만, 인덱스 키 순서대로 정렬되지 않는다.
• 인덱스에 포함된 컬럼만 조회하는 경우에만 사용할 수 있다.
• Index Full Scan 또는 Index Range Scan과 달리, 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능하다.
• 병렬 쿼리 시에는 Direct Path I/O 방식을 사용하기 때문에 I/O 속도가 더 빨라진다.

✔️ Index Full Scan vs. Index Fast Full Scan

Index Full Scan	Index Fast Full Scan
인덱스 구조를 따라 Scan	인덱스 세그먼트 전체를 Scan
결과 집합 정렬 ⭕	결과 집합 정렬 ❌
Single Block I/O	Multiblock I/O
병렬 Scan 불가 (파티션도 불가)	병렬 Scan 가능
선두 컬럼 조건만 포함되면 OK	인덱스에 포함된 컬럼으로만 조회 가능

(3) Index Fast Full Scan을 활용한 튜닝 사례

보통 LIKE '%업체명%' 같은 Index Range Scan이 불가능한 쿼리는 Table Full Scan을 수행해야 하므로 속도가 저하될 수 있다. 하지만, 결과 건수가 적고, 테이블 전체를 읽어야 하는 경우 Index Fast Full Scan이 유용할 수 있다. 또한, 쿼리에서 사용되는 인덱스가 자주 조회되지 않아, 디스크 I/O가 많아지는 경우에도 효과적이다.

 

Index Fast Full Scan의 특징을 이해하면, 테이블의 BCHR(버퍼 캐시 히트율)가 낮아 디스크 I/O가 많이 발생하는 경우 특히 유리하다는 점을 알 수 있다.
그러나, Index Fast Full Scan은 인덱스에 포함된 컬럼으로만 조회할 수 있다는 제약이 있다. 즉, 쿼리가 테이블의 추가 컬럼을 필요로 하면 비효율적일 수 있다.

 

이러한 경우, 다음과 같은 방식으로 Index Fast Full Scan을 활용하면 성능을 최적화할 수 있다.

SELCT /*+ ORDERED USE_NL(B) NO_MERGE ROWID(B)*/ B.*
FROM (
    SELECT /*+INDEX_FFS(IDX_X)*/ ROWID RID
    FROM TABLE
    WHERE INSTR(업체명, '업체명') > 0
) A, TABLE B
WHERE B.ROWID = A.ROWID

1. IDX_X 인덱스를 이용하여 Fast Full Scan을 수행한다.
   • LIKE 연산보다 빠른 INSTR 연산을 사용하여 검색 속도를 높인다.
2. 찾은 ROWID를 이용해 테이블을 다시 액세스 한다.
   • 불필요한 블록 I/O를 최소화하여 성능을 개선한다.

 

이러한 아이디어는 Index Fast Full Scan 을 통해 스캔해야 할 건수가 많더라도 조건절에 의해 필터링되고 나서의 최종 결과건수는 적다는데서 착안한 것이다. 그러나, 검색된 결과 건수가 많으면, 테이블에 대한 Random Access가 많아져 성능이 저하될 수 있다.

 

하지만, 부분 범위 처리가 가능한 애플리케이션 환경이라면 오히려 효율적일 수 다. (11g 이상이라면, 결과 건수가 많더라도 인라인뷰에 ORDER BY ROWID를 추가하면 성능을 더욱 최적화할 수 있다.)

 

🖇️ Index Range Scan Descending

Index Range Scan Descending은 기본적인 Index Range Scan과 동일하지만, 인덱스를 뒤에서부터 앞쪽으로 스캔하여 내림차순(DESC) 정렬된 결과를 얻는 방식이다. 옵티마이저가 자동으로 실행 계획을 수립하지만, 필요할 경우 index_desc 힌트를 사용해 유도할 수 있다.