여러가지 인덱스 스캔 방식

 

 

1. Index Range Scan 

 

Index Range Scan은 B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 엑세스 방식

친절한 SQL 튜닝

 

인덱스의 수직+수평 탐색을 통해 필요한 범위만 스캔한다. 

인덱스 Range Scan 하려면 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 한다. 

 

2. Index Full Scan

 

Index Full Scan은 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.

친절한 SQL 튜닝

Index Full Scan은 대게 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.

 

인덱스 선두 컬럼이 조건절에 없으면, 옵티마이저는 먼저 Table Full Scan을 고려한다. 

하지만, 대용량 테이블이어서 Table Full Scan에 부담이 크다면 옵티마이저는 인덱스 활용을 다시 고려하지 않을 수 없다. 

 

데이터 저장공간은 가로X세로 즉 컬럼 길이 X 레코드 수에 의해 결정된다. 인덱스가 차지하는 면적은 테이블보다 훨씬 적다. 

인덱스 스캔 단계에서 대부분의 레코드를 필터링하고, 아주 일부만 테이블을 엑세스 하는 상황이라면, 테이블을 풀로 스캔하는 것보다, Index를 Full Scan하는 것이 좋다. 

 

다만, 이 방식은 적절한 인덱스가 없어 Index Range Scan의 차선책으로 선택한 것이다. 

수행빈도가 낮은 SQL이라면 상관 없지만, 그렇지 않다면 SAL 컬럼이 선두인 인덱스를 생성해 주는 것이 좋다.

 

* 인덱스를 Full Scan하면 Range Scan과 마찬가지로 결과집합이 인덱스 칼럼 순으로 정렬된다.

따라서 Sort Order by 연산을 생략할 목적으로 사용할 수도 있다. 이는 차선책이 아니라, 옵티마이저가 전략적으로 선택한 경우에 해당한다. 

 

---

3. Index Unique Scan

 

Index Unique Scan은 수직적 탐색만으로 데이터를 찾는 방식이다. Unique 인덱스를  = 조건으로 탐색하는 경우 작동한다.

친절한 SQL 튜닝

Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성을 관리해 준다. 

따라서 해당 인덱스 키 컬럼을 모두 = 조건으로 검색할 때는 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없다.

Unique 인덱스라고 해도 범위 검색 조건으로 검색할 때는 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을 사용하기도 한다.

 

Index Skip Scan은 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value가 많을 때 유용하다. (고객 테이블에서 Distinct Value가 가장 적은 컬럼은 성별이고, Distinct Value가 가장 많은 컬럼은 고객번호다)

 

친절한 SQL 튜닝

select * from 사원 where 성별 = '남' AND 연봉 between 2000 and 4000을 하면, 

성별이 남이면서 연봉이 >= 2000인 첫 레코드를 찾아야하므로, 4번째 리프블록이 5000>=이므로, 3번째 블록부터 >4000까지 스캔을 시작한다. 

 

이때 성별 조건을 빼보자

 

Index Skip Scan이 실행되는데 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스 하는 방식이다. 

 

첫 레코드가 남&800이므로, 2000과 4000사이가 아니므로 skip, 두번째도 1500보다 작거나 같은 연봉의 모임이므로 skip

세번째 블록은 접근하고, 나머지는 스킵

남 & 10000인 마지막 블록은 skip하지 않는다. 이는 다음 블록 <여 & 3000>이 연봉 2000과 4000사이를 만족하기 때문이다. 성별은 모르지만, 연봉이 조건에 포함되고, 남&10000에 일부 포함될 수 있음으로 읽어야 한다. 

마지막으로 여 & 10000인 리프 블록도 엑세스하는데, 여보다 값이 큰 미지의 성별이 존재할 수 있기 때문이다. 

친절한 SQL 튜닝

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

• Distinct Value 개수가 적은 선두 컬럼이 조건절에 없고, 후행컬럼의 Distinct Value 개수가 많을 때 
• 선두컬럼에 대한 조건절은 있고, 중간 컬럼에 대한 조건절이 없는 경우 [3개일 때 1번과 3번만 있는 경우일 듯]
• 선두 컬럼이 부등호, BERWEEN,LIKE 같은 범위검색 조건일 때도 사용 가능 

Index Range Scan이 불가능하거나 효율적이지 못한 상황에서 Index Skip Scan이 종종 빛을 발한다.

부분

---

5. Index Fast Full Scan

 

Index Full Scan보다 빠른 방식이다. 논리적인 인덱스 트리구조를 무시하고 인덱스 세그먼트 전체를 Mutiblock I/O 방식으로 스캔하기 때문이다. 

(관련 힌트는 index_ffs와 no_index_ffs이다)

 

친절한 SQL 튜닝

화살표는 인덱스의 논리적 구조를 표시한 것이다. 리프 블록 간에 양방향 연결 리스트 구조를 갖는다.

친절한 SQL 튜닝

실제 저장된 물리적 구조는 위와 같다. Index Full Scan은 인덱스의 논리적 구조를 따라 루트->브랜치1 ->1->2->3->4.. 순으로 블록을 읽어들인다.

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

물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 읽어들인다. Mutiblock I/O 방식으로 

왼쪽 익스텐트 -> 오른쪽 익스텐트 순으로 읽는다. 

• 디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과를 발휘한다.
• 속도는 빠르지만, 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키 순서대로 정렬되지 않는다.
• 쿼리에 사용한 칼럼이 모두 인덱스에 포함돼 있을 때만 사용할 수 있다. 
• 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능한 것도 중요한 특징 중 하나이다. (병렬 쿼리 시에는 Direct Path I/O 방식을 사용하기 때문에 I/O 속도가 더 빨라진다.)

친절한 SQL 튜닝

---

6. Index Range Scan Descending

 

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

 

친절한 SQL 튜닝

INDEX RANGE SCAN에서 ORDER BY를 선두컬럼에 DESC로 주거나, INDEX_DESC 힌트를 사용해서 유도할 수 있다.

 

---

정리 

 

1. INDEX RANGE SCAN : 인덱스의 수직+수평 탐색을 통해 필요한 범위만 스캔

 

2. INDEX FULL SCAN : 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식

인덱스 선두 컬럼이 조건절에 없으면, 옵티마이저는 먼저 Table Full Scan을 고려 >  이게 너무 부담스러울 때 선택 

+ 선두는 없지만 인덱스 스캔 단계에서 대부분의 레코드를 필터링하고, 아주 일부만 테이블을 엑세스 하는 상황이라면

 

3. INDEX UNIQUE SCAN: 수직적 탐색만으로 데이터를 찾는 방식이다. Unique 인덱스를  = 조건으로 탐색하는 경우

 + Unique 인덱스라고 해도 범위 검색 조건으로 검색할 때는 Index Range Scan으로 처리된다. 

 + Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan

 

4. INDEX SKIP SCAN : 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스 하는 방식

작동 조건 

• Distinct Value 개수가 적은 선두 컬럼이 조건절에 없고, 후행컬럼의 Distinct Value 개수가 많을 때 
• 선두컬럼에 대한 조건절은 있고, 중간 컬럼에 대한 조건절이 없는 경우
• 선두 컬럼이 부등호, BERWEEN,LIKE 같은 범위검색 조건일 때도 사용 가능

* Distinct Value란 특정 컬럼에서 중복되지 않고 유일한 값

 

5. INDEX FAST FULL SCAN: Mutiblock I/O 방식으로 스캔 관련 힌트는 index_ffs와 no_index_ffs (세그먼트 전체 스캔)

  + 쿼리에 사용한 칼럼이 모두 인덱스에 포함돼 있을 때만 사용할 수 있다

  + 속도는 빠르지만, 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키 순서대로 정렬되지 않는다

  + 인덱스 파티션 x여도 병렬 쿼리가 가능하다는데 이건 무슨말..?

 

6. INDEX RANGE SCAN DESCENDING : INDEX RANGE SCAN과 동일하지만, 내림차순으로 정렬된 결과를 얻음 

  + ORDER BY를 선두컬럼에 DESC로 주거나, INDEX_DESC 힌트를 사용해서 유도

 

---

참고자료 

https://www.yes24.com/Product/Goods/61254539

친절한 SQL 튜닝 - 예스24