Index 2) 인덱스 스캔

1. Index Range Scan

- 인덱스를 수직적으로 탐색 후에 리프 블록에서 필요한 범위만 스캔하는 것이다.

- B*Tree 형태의 인덱스에서 가장 일반적인 형태의 엑세스 방식이다. 

- SQL Server에서는 Index Seek이라고 표현한다.

- Index Range Scan이 가능하게 하려면 인덱스를 구성하는 선두 컬럼이 조건절에 사용되어야 한다.

- 결과집합은 인덱스 컬럼 순으로 정렬된 상태가 되기 때문에 이런 특징을 잘 이용하면 sort order by 연산을 생략하거나

   min/max 값을 빠르게 추출할 수 있다.

 

Point

인덱스를 스캔하는 범위를 얼만큼 줄일 것인지, 테이블로 액세스하는 횟수를 얼마만큼 줄일 것인지가 관건이며, 이는 인덱스 설계와 SQL 튜닝의 핵심 원리 중 하나이다.

SQL> create index soju_sojuno_idx on soju(sojuno); 
SQL> set autotrace traceonly explain SQL> select * from soju where sojuno = 7; 
Execution Plan ------------------------------------------------------ 0 
SELECT STATEMENT Optimizer=ALL_ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) 
OF 'SOJU' (TABLE) 2 1 INDEX (RANGE SCAN) OF 'SOJU_SOJUNO_IDX' (INDEX)

---

2. Index Full Scan

- 수직적 탐색 후 Leaf 블록을 처음부터 마지막까지 수평적으로 탐색

- 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.

 

Point

데이터 저장공간은 '컬럼길이 × 레코드수’에 의해 결정되므로 인덱스가 차지하는 면적은 테이블보다 적은 것은 사실이다. 인덱스 스캔 단계에서 필터를 걸치고, 일부만 테이블 엑세스가 발생할 경우 테이블 전체를 스캔하는 것 보다 낫다.

SQL> create index soju_idx on soju (sojuname, price); 
SQL> set autotrace traceonly exp SQL> select * from soju 2 where price > 5000 5 
order by sojuname; Execution Plan ---------------------------------------------------------- 0 
SELECT STATEMENT Optimizer=ALL_ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) 
OF 'SOJU' (TABLE) 2 1 INDEX (FULL SCAN) OF 'SOJU_IDX' (INDEX)

---

3. Index Unique Scan

- 수직적 탐색만으로 데이터를 찾는 스캔으로, Unique 인덱스를  = 조건으로 탐색할 경우에 작동

SQL> create unique index pk_soju on soju(sojuno); 
SQL> alter table soju add 2 constraint pk_soju primary key(sojuno) using index pk_soju; 
SQL> set autotrace traceonly explain SQL> select sojuno, sojuname from soju where sojuno = 1924; 
Execution Plan ----------------------------------------------- 0 
SELECT STATEMENT Optimizer=ALL_ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) 
OF 'SOJU' 2 1 INDEX (UNIQUE SCAN) OF 'PK_SOJU' (UNIQUE)

 

*TMI : 1924년도는 진로가 처음 출시된 연도이다.

---

4. Index Skip Scan

- 인덱스 선두 컬럼이 조건절에 빠졌어도 인덱스를 활용

- Root or Branch 블록에서 읽은 컬럼값 정보를 이용해 조건에 부합하는 레코드를 '포함 가능성'있는 하위블록만 선택해    액세스하는 방식이다.

 

Point

조건절에 빠진 인덱스 선두 컬럼의 중복값의 개수가 적고 후행 칼럼의 중복값의 개수가 많을 때 유용하다.

In-List를 사용하면 Index Skip Scan을 타지 않고도 빠른 결과값을 얻는데, In-List로 제공하는 값의 종류가 적어야 한다.

SQL> select * from 소주판매원 where 연봉 between 1500 and 2000; 
Execution Plan -------------------------------------------------- 0 
SELECT STATEMENT Optimizer=ALL_ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) 
OF '소주판매원' (TABLE) 2 1 INDEX (SKIP SCAN) OF '소주판매원 _IDX' (INDEX)

---

5. Index Fast Full Scan

- 인덱스 트리구조를 무시, 세그먼트 전체를 Multiblock Read 방식으로 스캔해서 Index Full Scan보다 빠른 인덱스다.

 

하기는 Index Full Scan과 Index Fast Full Scan의 비교이다.

---

6. Index Range Scan Descending

- Index Range Scan과 방식은 동일한 스캔 방식이지만, 뒤에서 앞으로 스캔하기 때문에 내림차순으로 정렬된다.

 

Point

max/min 등 집계함수를 사용하여 값을 도출할 때도 해당 컬럼에 인덱스가 있으면 뒤에서 부터 읽는다.

SQL> select * from soju 2 where sojuno is not null 3 order by sojuno desc; 
Execution Plan ------------------------------------------------------------- 0 
SELECT STATEMENT Optimizer=ALL_ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) 
OF 'SOJU' (TABLE) 2 1 INDEX (RANGE SCAN DESCENDING) OF 'PK_SOJU' (INDEX (UNIQUE))