index scan

■ index scan 

1. index range scan (index 범위 스캔)
- 인덱스 root block -> branch block -> leaf block (tree 구조)까지 수직으로 탐색한 후 leaf block 에서 필요한 범위만 scan하는 방식 (one plus one scan)

---

<hr sess>

select ix.index_name, ix.uniqueness, ic.column_name
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name = 'EMP';

#) index 생성 

create index hr.emp_idx on hr.emp(employee_id);

#) 생성 확인

select ix.index_name, ix.uniqueness, ic.column_name
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name = 'EMP';

#) 실행계획 확인

set autot traceonly exp
select * from hr.emp where employee_id = 100;

   2 - access("EMPLOYEE_ID"=100) => 해당 값이 어느 블록에 있는지 알고 있다.

--> root block - branch block - leaf block 까지의 스캔

 

#) 실행계획 끄기

set autot off
select * from hr.emp where employee_id = 100;

#) index scan -> full scan으로 보기

select /*+ full(e) */ * from hr.emp e where employee_id = 100;

#) 형변환 -> full scan 

select * from hr.emp where to_number(employee_id) = 100;

#) index 힌트 사용

select /*+ index(e emp_idx) */ * from hr.emp e where employee_id = 100;

#) index range scan 유도 하는 힌트 사용

select /*+ index_rs(e emp_idx) */ * from hr.emp e where employee_id = 100;

 

#) index 삭제 

drop index hr.emp_idx;

#) unique index 생성 

create unique index hr.emp_idx on hr.emp(employee_id);

#) 생성 확인

select ix.index_name, ix.uniqueness, ic.column_name
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name = 'EMP';

#) primary key 추가

alter table hr.emp add constraint emp_id_pk primary key(employee_id) using index hr.emp_idx;

#) 제약 조건 확인

select c.column_name, u.constraint_name, u.constraint_type, u.search_condition, u.index_name
from user_constraints u, user_cons_columns c
where u.constraint_name = c.constraint_name
and u.table_name = 'EMP';

2. index unique scan
- 컬럼에 유일한 값으로 인덱스가 생성된 경우 사용된다.
- 비교연산자는 = 사용할때만 사용된다.
- unique index라고 하더라도 범위스캔을 수행할 경우 index range scan 으로 수행된다.

select * from hr.emp where employee_id = 100;

3. lnlist iterator
#) 실행계획 확인

select * from hr.emp where employee_id in (100, 200);

   3 - access("EMPLOYEE_ID"=100 OR "EMPLOYEE_ID"=200) => select문장을 2개 실행하는 것과 동일

#) 위의 select문에서 where in연산자 사용시 union all 와 같은 나눠서 진행된다. 

select * from hr.emp where employee_id = 100
union all
select * from hr.emp where employee_id = 200;

#) union 사용시 sort unique 실행계획생성되어 비효율적이다

select * from hr.emp where employee_id = 100
union
select * from hr.emp where employee_id = 200;

#) index scan
<hr sess>

create index hr.emp_name_idx on hr.emp(last_name);

 

#) 컬럼 확인 

select ix.index_name, ix.uniqueness, ic.column_name
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name = 'EMP';

#) 실행 계획 확인

set autot traceonly exp
select * from hr.emp where last_name ='King';

#) 컬럼의 name = king & steven 같이 확인.
-> 조합 index를 활용하자.

select * from hr.emp where last_name = 'King' and first_name = 'Steven';

4. 조합 인덱스 
- where '자주 사용'되는 컬럼들을 하나의 인덱스 생성
- 주의) 선행 컬림을 잘 만들어야 한다. 
- 기준 
1) 혼자서도 자주 사용되는 컬럼을 선행 컬럼으로 설정.
2) 범위를 줄일 수 있는 컬럼을 선행 컬럼으로 설정.

#) 조합 인덱스 설정

drop index hr.emp_name_idx;
create index hr.emp_name_idx on hr.emp(last_name, first_name);

 

select ix.index_name, ix.uniqueness, ic.column_name, ic.column_position
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name = 'EMP';

 

#) 실행계획 확인

select * from hr.emp where last_name = 'King' and first_name = 'Steven';

5. index full scan 
- index 만 scan 함. 
- 블록 i/o를 줄이기 위함

#) 실행계획 확인 - scan 확인
--> index full scan 

select last_name, first_name from hr.emp;

#) count 해보기

select count(*) from hr.emp;

=> 전체 count를 할때, 해당 테이블의 pk 찾아서 index full scan 한다.
-> INDEX FULL SCAN : 싱글 블록으로 read 함.

-> SORT AGGREGATE : 그룹 함수

#) 제약조건 확인

select c.column_name, u.constraint_name, u.constraint_type, u.search_condition, u.index_name
from user_constraints u, user_cons_columns c
where u.constraint_name = c.constraint_name
and u.table_name = 'EMP';

6. index fast full scan
- 한꺼번에 여러갯수 read 한다.

#) 멀티블록 index full scan

select /*+ index_ffs(e emp_idx) */ count(*) from hr.emp e;

# index full scan 
- single block i/o
- 정렬보장
- 속도 느림(한개씩 읽어드림)
- db file sequential read 대기 이벤트 발생 (무조건 발생)

# index fast full scan : /*+ index_ffs(e emp_idx) */ 힌트 사용
- mulit block i/o
- 정렬보장이 안된다.
- 속도 빠름
- db file scattered read 대기 이벤트 발생

※  index full scan 보다 index fast full scan이 더 빠른 이유?
- multi block i/o 
show parameter db_file_multiblock_read_count 로 확인
- 병렬 처리가 가능 : /*+  parallel_index(e, emp_idx, 2) */ 힌트 사용
ex) select /*+ index_ffs(e emp_idx) parallel_index(e, emp_idx, 2) */ count(*) from hr.emp e;