2.3 인덱스 확장기능

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

 

인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 '필요한 범위(Range)만' 스캔한다.

* 수직 탐색 : 조건을 만족하는 첫번째 레코드를 찾음

* 수평 탐색 : 찾고자 하는 데이터가 더 안나타날 때 까지 찾음

 

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

 

* 의 경우는 인덱스 구성 칼럼의 일부에만 조건이 주어진 경우나
  Like Between 또는 Equal 조건이 아닌 범위 조건 (>, =>, <, <=) 에서 주로 발생한다.

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

 

대개 데이터 검색을 위한 최적의 인덱스가 없을 때 차선책으로 사용됨

인덱스 선두 컬럼이 조건절에 없으면 옵티마이저는 우선적으로 Table Full Scan을 고려하는데, 대용량 테이블이어서 Table Full Scan이 부담되면
차선책을 선택한다.

 

SQL> select * from emp where sal > 2000 order by ename;

 

이라는 쿼리가 있을 때, sal > 9000 이면 (즉 , sal>9000인 사원이 전체 중 극히 일부) Index Full Scan 방식이 효과적이다.

 

차선책일 뿐이지만 웬만하면 sal이 선두컬럼인 인덱스를 생성해주는 것이 좋다.  

인덱스를 Full Scan하면 Range Scan과 마찬가지로 인덱스 컬럼 순으로 정렬된다.

 

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

 

 

SAL > 9000 일 때는 Index Ragne Scan을 선택했지만, 지금은 INDEX FULL SCAN을 선택했다.

이상황에서는 모든 레코드에 대해 테이블 액세스가 발생하므로 Table Full Scan 보다 오히려 불리할 수 있다.

 

그런데도 옵티마이저가 인덱스를 선택한 이유는 사용자가 FIRST_ROWS 힌트로 옵티마이저 모드를 바꿨기 때문이다.

 

소트연산을 생략함으로서 전체 집합중 처음 일부를 빠르게 출력할 목적으로 옵티마이저가 Index Full Scan 방식을 선택한 것

 

 

 

 

2.3.3 Index Unique Scan

 수직적 탐색만으로 데이터를 찾는 스캔 방식이다. unique 인덱스를 '=' 조건으로 탐색하는 경우 작동함
 

 

* Primary 키가 컬려있는 컬럼엔 자동으로 인덱스가 생성된다.

 Unique 인덱스로 생성하면 인덱스로 지정된 컬럼은 단일 값만 들어와야 한다.

 

Index Unique Scan은 쿼리가 Unique Index에서 하나의 유일한 행을 검색해야 할 때 사용된다.

이 스캔은 Key 또는 Unique Index로 생성된 인덱스에서 발생되는 액세스 방식이다.

 

인덱스 구성 칼럼에 대한 Where절이 '='로 모두 주어졌을 경우에 발생한다. 인덱스 트리 구조에 대해 수직적 탐색을 수행하여
Single Block I/O

 

 

2.3.4 Index Skip Scan (index_ss)

 

인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan을 또는 Index Full Scan을 선택하는데

인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식이 Index Skip Scan 이다.

 

 

 

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

 

 

 

-- 스캔방식 유도를 위한 index_ss 힌트 사용.
select /*+ index_ss(사원 사원_IDX) */ *
from 사원
where 연봉 between 2000 and 4000
 

성별 '남'보다 작은 값이 있을까봐 일단 첫번때 리프 블록 액세스

연봉 >= 800인 2번째 리프 블록은 Skip

연봉 >= 1500인 3번째 리프 블록 다음이 연봉 >= 5000이기 때문에 조건을 만족하는 값이 있을 가능성이 있어 3번째 리프 블록 액세스

연봉 >= 5000인 4번째 리프 블록은 애초에 조건에 만족하지 않기 때문에 Skip

5번째 리프 블록 또한 같은 이유로 Skip

6번째 리프 블록은 연봉 >= 10000이라 skip 될것 같지만, 다음 블록에서 성별 조건이 바뀌어서 일단 액세스

7번 블록은 성별 상관없이 연봉 >= 3000 이고, 다음 레코드는 연봉 >= 5000이기 때문에 액세스

8번 ~ 9번 블록은 연봉 >= 5000, 연봉 >= 7000 이기 때문에 조건에 만족하지 않아 Skip

10번 블록 또한 연봉 >= 10000으로 조건에 만족하지 않지만, 다음 성별이 있을수 있으니 일단 액세스

 

인덱스는 Index Range Scan을 우선으로 (최선으로) 설계해야 함.  index_ss는 차선책임

 

2.3.4 Index Fast Full Scan (index_ffs / no_index_ffs)
 인덱스 세그먼트 전체를 MultiBlock I/O 방식으로 스캔한다. 
  Index Full Scan을 인덱스의 논리적인 구조를 따라 루트 > 브랜치1 > 1 > 2 > 3 > 4 > 5 > .. 이런 순으로 블록을 읽어 들이는데
  Index Fast Full Scan은 디스크에 저장된 물리적인 순서로 Multiblock I/O로 읽어들인다.

 

 디스크로부터 대량의 인덱스를 읽어들여야  할 때 큰 효과를 발휘한다.

 * 속도는 빠르다
 * 인덱스 리프노드가 갖는 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 정렬되지 않는다.

 

Index Full Scan	Index Fast Full Scan
1. 인덱스 구조를 따라 스캔 2. 결과집합 순서 보장 3. Single Block I/O 4. (파티션 돼 있지 않다면) 병렬스캔 불가 5. 인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능	1. 세그먼트 전체를 스캔 2. 결과집합 순서 보장 안 됨 3. Multiblock I/O 4. 병렬스캔 가능 5. 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능

 

 

 

2.3.5 Index Range Scan Descending (index_desc)

인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다.

 

ex) select deptno, dname, loc
    ,(select max(sal) from emp where deptno = d.deptno)

   from dept d

 

위와 같이 MAX값을 구하고자 할 때 해당 컬럼에 인덱스가 있으면 인덱스를 뒤에서 부터 한건만 읽고 멈추는 실행계획이 자동으로 생성.