오라클 index(인덱스) 종류 사용법[대용량 데이터 베이스 튜닝 및 설계 2일차]

오라클 index(인덱스) 종류 사용법[대용량 데이터 베이스 튜닝 및 설계 2일차]

 

 

 

 

 

데이터 베이스는 연속된 빈 블럭을 기준으로 용량을 산정한다

아무리 데이터 용량이 남아있다 한들 그만큼의 트랜잭션을 발생해도 안되는 경우가 있다

이건 연속된 빈 블럭의 값이 그만큼 남아 있지 않아서 이다.

 

 

INDEX란?

 

전체 내용물 중에서 특정한 부분을 바로 찾을 수 있는 목차나 색인.

Index는 사용자가 임의적으로 생성, 변경, 삭제할 수 있는 데이터베이스 내에 실질적으로 저장되는 물리적인 구조체.

기본적으로 Index-key값과 ROWID값으로 구성.

 

 

INDEX 사용이 불가한 경우

 

Index column에 대한 가공
~ where substr(ename, 1, 2) = ‘PK’;  

 

부정형 비교
~ where ename != ‘PK’; 

 

 IS NOT NULL 비교
~ where ename IS NOT NULL;

 

 

INDEX 생성시 고려 사항

 

조건을 만족한는 데이터의 분포도가 10~15% 내외일 때 인덱스를 생성한다.

분포도 = ( 조건을 만족하는 행수 / 테이블 전체 행수 ) * 100

 

대용량 데이터를 가진 테이블에 적용한다.

 DB_BLOCK_SIZE가 8K인 경우 테이블 사이즈가 6 Block(48K) 이상인 경우

 

비록 분포도가 나쁘더라도 FAST INDEX SCAN이 가능한 경우라면 인덱스를 생성한다

 

적합한 유형의 인덱스를 선택한다.

 

테이블과 I/O 분리를 고려 한다.

 

 

Index Range

 

루트 블럭에서 리프 블럭까지 수직적으로 탐색한 후에 리프 블럭을 필요한 부분만 스캔하는 방식
가장 일반적으로 정상적인 형태의 액세스 방식 

 

실행계획에 Index Range이 나타난다고 항상 빠른 속도를 보장하는 것은 아님

 

 

스캔하는 범위(Range)를 얼마만큼 줄일 수 있는냐가 관건임

 

 

인덱스 설계와 SQL 튜닝의 핵심원리 중 하나임

 

 

인덱스의 선두 컬럼이 사용되어야 하며 그렇지 못한 상황에서 인덱스를 강제로 사용하기 위해

 

힌트를 사용한다면 Index Full Scan 방식으로 처리됨

 

 

Index Full Scan

 

수직적 탐색 없이 인덱스 리프 블럭을 처음부터 끝까지 수평적으로 탐색하는 방식
데이터 검색을 위한 최적의 인덱스가 없을 경우 차선으로 선택됨

 

sql> select * from emp where depno = 20;

 

Index Unique Scan

 

Unique index를 통해 ‘=‘ 조건으로 탐색할 경우 동작
Unique index column이라도 범위 검색(between, 부등호, like)할 경우 Index Range Scan이 동작

 

sql> select * from emp where sal > 2000;

 

 

Index Skip Scan

 

인덱스 선두 컬럼이 조건절로 사용되지 않으면 옵티마이저는 기본적으로 Table Full scan을 선택한다.
인덱스 선두 컬럼이 조건절에 빠졌어도 인덱스를 활용하는 스캔 방식 (9i 이후)

 

군대군대 뭉쳐서 퍼져있는 값을 찾을때 유용하게 사용할 수 있다

 

sql> select empno,ename from emp where empno = 7788;

 

 

[index skip scan]

 

 

 

 Index Fast Full Scan

 

Index Fast Full Scan은 Index Full Scan 보다 빠르다.
인덱스 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔

 

 

[index fast full scan]

 

 

 

Index Full Scan VS Index Fast Full Scan

 

index full scan	index fast full scan
인덱스 구조를 따라 스캔	세그먼트 전체를 스캔
결과 집합 순서 보장	결과집합 순서보장 안됨
single block i/o	MultiBlock i/o
병렬스캔 불가 (파티션이 되어있지 않으면)	병렬스캔 가능
인덱스에 포함되지 않은 컬럼 조회시 사용 가능	인덱스에 포함된 컬럼으로만 조회 할 때 사용