ORACLE 인덱스

1. 인덱스 기본구조

인덱스는 통상적으로 B*Tree 구조를 가진다. 처음에는 루트 노드에 데이터가 싸이는데 점점 데이터 늘어날 수록 루트 노드 -> 브랜치 노드 -> 리프노드로 연결된다.

인덱스 탐색은 수평적 탐색과 수직적 탐색으로 나뉜다 .수평적 탐색은 인덱스 리프블록에 저장된 레코드끼리 연결된 순서에 따라 좌우로 스킨하기 때문에 수평적 탐색이라한다. 반면, 수직적 탐색은 루프에서 리프가지 아래쪽으로 진행하기 때문에 수직적 탐색이라한다.

2. 인덱스 스캔방식 

1) Index Range Scan : 인덱스 루트 블록에서 리프블록까지 수직적으로 탐색한 후에 리프블록을 필요한 범위만 스캔하는 방식이다. ( 가장 일반적이고 정상적인 형태의 액세스 방식이다.)

1-1) Index Range Scan Descending : Index Range Scan과 동일한 스캔방식이지만 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과 집합을 얻는다.

 

2) Index Full Scan : 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식으로 대개 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택한다. 인덱스 선두칼럼이 조건적에 없으면 옵티마이저는 우선적으로 Table Full Scan을 고려한다. 

 

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

 

4) Index Skip Scan : 인덱스 선두 칼럼이 조건절로 사용되지 않으면 옵티마이저는 Table Full Scan을 선택한다. 또는 Table Full Scan 보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있으면 Index Full Scan 방식을 사용한다. Oracle은 인덱스 선두 칼럼이 조건절에 빠졌어도 인덱스를 활용하는 새로운 스캔방식인 Index Skip Scan을 실행한다. 

 

5) Index Fast Full Scan : Index Full Scan 보다 빠른데, 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체  Multiblick Read 방식으로 스캔하기 때문이다.

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

3. 인덱스 생성

테이블에 index를 생성하게 되면 데이터베이스는 index Table을 생성해서 관리한다. 

CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)

칼럼값에 중복을 허용하지 않는 인덱스 생성 

CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 

4. 인덱스 조회

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';

SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'CUSTOMERS';

5. 인덱스 삭제

--문법
DROP INDEX [인덱스 명]

6. 인덱스 리빌드 

인덱스 파일은 생성 후 insert, update, delete 하다보면 성능이 저하된다. 트리 한쪽이 전체적으로 무거워저 한 쪽의 트리 깊이가 깊어지기 때문이다. 이러한 이유로 인덱스의 검색 속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거쳐야 한다. 

ALTER INDEX EX_INDEX REBUILD;

 

7. 인덱스 설계방식

1) 가장 중요한 두가지 선택 기준

 - 조건절에 항상 사용되거나, 자주 등장하는 칼럼들을 선정한다.  -> 인덱스를 조건절에 항상 사용되거나 적어도 자주 사용되는 컬럼들을 선정 

 - '=' 조건으로 자주 조회되는 칼럼들을 앞쪽에 둔다.