[Oracle] 오라클 인덱스(Index) 란?

[Oracle] 오라클 인덱스(Index) 란?

인덱스(Index)는 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체다.

 

인덱스 형태와 속성에 따른분류 

- 인덱스 구성 컬럼 개수에 따른 분류: 단일 인덱스와 결합 인덱스

- 유일성 여부에 따른 분류: UNIQUE 인덱스, NON-UNIQUE 인덱스

 

구조와 내부 알고리즘에 따른 분류

- 인덱스 내부 구조에 따른 분류: B-tree 인덱스, 비트맵 인덱스, 함수 기반 인덱스 

 

이 외에도 해시 클러스터 인덱스, 리버스 키 인덱스, 비트맵 조인 인덱스, 도메인 인덱스, 파티션 인덱스 가 있다. 

 

---

 

인덱스 생성

 

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

 

입력

CREATE[UNIQUE] INDEX EX02_01_IX01
ON EX02_01 (H001);

 

출력

UNIQUE INDEX EX02_01_IX01 가 생성되었씁니다.

 

입력 

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME = 'EX02_01';

 

출력

INDEX_NAME 	INDEX_TYPE 	TABLE_NAME	 UNIQUENESS
----------	 ---------- 	---------- 	-----------
EX02_01_IX01	 NORMAL		 EX02_01	 UNIQUE

중복 값을 허용하지 않는다는 측면에서 이 제약조건과 UNIQUE 인덱스는 같은 역활을 한다.

따라서 별도로 UNIQUE 인덱스를 생성하지 않아도

UNIQUE 제역조건을 만들면 오라클은 자동으로 UNIQUE 인덱스를 생성해 준다.

더불어 기본키를 생성해도 오라클은 자동으로 UNIQUE 인덱스를 생성해 준다.

이때 생성되는 인덱스명은 UNIQUE나 기본키 객체명과 동일하게 생성된다.

 

 

 

입력 

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, UNIQUENESS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'JOB_HISTORY';

 

출력

CONSTRAINT_NAME 	CONSTRAINT_TYPE 	TABLE_NAME 	INDEX_NAME
---------------		---------------		------------	----------
SYS_C0011070		c			JOB_HISTORY
SYS_C0011071		c			JOB_HISTORY
PK_JOB_HISTORY		P			JOB_HISTORY	PK_JOB_HISTORY

 

입력 

SELECT INDEX_NAME 	INDEX_TYPE 	TABLE_NAME	 UNIQUENESS
FORM USER_INDEXES
WHERE TABLE_NAME = 'JOB_HISTORY';

출력

INDEX_NAME 	INDEX_TYPE 	TABLE_NAME	 UNIQUENESS
----------	 ---------- 	---------- 	-----------
PK_JOB_HISTORY	 NORMAL		 JOB_HISTORY	 UNIQUE

또한, 한개 이상의 컬럼으로 인덱스를 만들 수 있는데, 이를 결합 인덱스라고 한다.

 

입력

CREATE INDEX EX02_01_IX02
ON EX02_01 (H001, H02);

출력

INDEX EX02_01_IX02이 생성되었습니다.

입력

SELECT INDEX_NAME 	INDEX_TYPE 	TABLE_NAME	 UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME = 'EX02_01';

출력

INDEX_NAME 	INDEX_TYPE 	TABLE_NAME	 UNIQUENESS
----------	 ---------- 	---------- 	-----------
EX02_01_IX02	 NORMAL		 EX02_01	 NONUNIQUE
EX02_01_IX01	 NORMAL		 EX02_01	 UNIQUE

 

 

인덱스를 너무 많이 만들면 SELECT 외에 INSERT, DELETE, UPDATE 시 성능에 부하가 뒤 따른다.

- 일반적으로 테이블 전체 로우 수의 15%이하의 데이터를 조회할 때 인덱스를 생성한다.

- 테이블 건수가 적다면 (코드성 테이블) 굳이 인덱스를 만들 필요가 없다.

- 데이터의 유일성 정도가 좋거나 범위가 넓은 값을 가진 컬럼을 인덱스로 만드는 것이 좋다.

- NULL이 많이 포함된 컬럼은 인덱스 컬럼으로 만들기 적당치 않다.

- 결합 인덱스를 만들 때는, 컬럼의 순서가 중요하다.

(보통, 자주 사용되는 컬럼을 순서상 앞에 두는 것이 좋다.)

- 테이블에 만들 수 있는 인덱스 수의 제한은 없으나, 너무 많이 만들면 오히려 성능 부하가 발생한다.

---

 

 

인덱스 삭제

DROP INDEX [스키마명.]인덱스명;

입력 

DROP INDEX EX02_01_IX01;

출력

INDEX EX02_01_IX01 가 삭제되었습니다.