[Oracle] 오라클 인덱스(Index) 생성 및 삭제 방법 (Create, Drop)

오라클에서는 다양한 종류의 인덱스를 생성해서 사용할 수 있다. B-트리 인덱스, 비트맵 인덱스, 파티션 인덱스, 함수 인덱스, 도메인 인덱스를 생성할 수 있으며, 아래는 가장 많이 사용하는 B-트리 인덱스(일반 인덱스)를 생성하는 방법이다.

 

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

 

대괄호([]) 항목은 생략이 가능하다. 인덱스는 한 개 이상의 컬럼으로 만들 수 있으며, 하나의 테이블에 여러 개의 인덱스를 생성할 수 있다. 테이블에 인덱스가 많이 생성되어 있으면, SELECT는 빠를 수 있지만 데이터 변경(INSERT, UPDATE, DELETE)은 느려질 수 있으므로 주의해야 한다.

 

인덱스 생성

단일 인덱스 생성 (1개의 컬럼으로 구성)

CREATE INDEX emp_ix01 ON emp(hiredate)

 

위의 예제는 hiredate 단일 컬럼으로 생성된 인덱스이다. 인덱스를 생성하면 데이터의 ROWID와 인덱스로 지정된 컬럼이 정렬되어서 별도의 공간에 저장된다. 인덱스 영역은 데이터가 이미 정렬되어 있어서 아주 빠르게 검색이 가능하며, 인덱스의 ROWID와 테이블의 ROWID를 연결하여 데이터를 가져온다.

 

복한 인덱스 생성 (2개 이상의 컬럼으로 구성)

CREATE INDEX emp_ix02 ON emp(job, deptno)

 

인덱스는 1개 이상의 컬럼으로 생성할 수 있으며, 최대 32개의 컬럼까지 지정하여 생성할 수 있다. 2개 이상의 컬럼으로 생성된 인데스를 복합 인덱스라고 부른다.

 

유니크(UNIQUE) 인덱스 생성

CREATE UNIQUE INDEX emp_uk ON emp(empno, mgr)

 

UNIQUE 인덱스를 생성하면 인덱스로 지정된 컬럼은 해당 테이블에서 유일한 값이어야 한다. 해당 컬럼에 중복된 값이 입력되면 무결성 오류가 발생하게 된다. UNIQUE 인덱스는 데이터 변경이 발생할 때마다 중복체크를 하기 때문에 일반 인덱스에 비하여 데이터 변경이 느리며, 유일 값을 꼭 체크해야 하는 특별한 경우가 아니면 많이 사용하지 않는다.

 

인덱스 삭제

DROP INDEX emp_idx01

 

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

 

인덱스 정렬

인덱스 컬럼에 정렬 기준을 부여할 수 있으며, 기본값은 오름차순(ASC)이다. 해당 인덱스로 쿼리문이 실행되면 정렬(ORDER BY)을 하지 않아도 인덱스 컬럼 정렬 기준으로 데이터가 조회된다. 인덱스 정렬을 잘 활용하면 ORDER BY를 사용하지 않고 정렬을 할 수 있기 때문에 쿼리의 속도를 향상시킬 수 있다.

 

오름차순(ASC)

CREATE INDEX emp_ix01 ON emp(hiredate)

 

입사일자(hiredate) 오름차순(ASC)으로 조회된다.

 

내림차순(DESC)

CREATE INDEX emp_ix01 ON emp(hiredate DESC)

 

 

입사일자(hiredate) 인덱스 생성 시 내림차순(DESC)으로 지정했기 때문에, 입사일자 기준 내림차순으로 조회된다.

 

[Oracle] 인덱스(Index) 수정 방법 (컬럼 추가, 변경)

[오라클] 인덱스 조회 방법 (Index)

[Oracle] 오라클 인덱스(Index) 힌트 사용방법 (주석, 튜닝)

[Oracle] 함수 기반 인덱스 생성 방법 (Funtion-Based Index)