인덱스(INDEX)

인덱스

빠른 검색을 위해서 사용. SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 생성하는 오라클 객체

인덱스의 장점

- 검색 속도가 빨라진다.

- 시스템에 부하를 줄여서 시스템의 전체 성능을 향상시킨다.

인덱스의 단점

- 인덱스를 위한 추가 공간이 필요하다.

- 인덱스를 생성하는데 시간이 걸린다.

- 데이터의 변경 작업(INSERT, UPDATE, DELETE)이 자주 일어날 때는 오히려 성능이 저하된다.

1. 인덱스 생성과 삭제

1) 인덱스 생성

CREATE INDEX index_name

ON table_name (column_name);

2) 인덱스 삭제

DROP INDEX index_name;

예제) 

CREATE INDEX INDEX_EMP_COPY_ENAME

ON EMP_COPY(ENAME);

-- EMPCOPY 테이블의 ENAME에 대한 인덱스 생성

SELECT INDEX_NAME, TABLE_NAME

FROM USER_INDEXES

WHERE TABLE_NAME IN('EMP_COPY');

-- 생성된 인덱스 확인

SELECT DISTINCT EMPNO, ENAME FROM EMP_COPY

WHERE ENAME = 'JONES';

-- 이름(ENAME) JONES를 검색. 빠르게 검색된다.

DROP INDEX INDEX_EMP_COPY_ENAME;

-- 인덱스 삭제

2. 인덱스를 사용해야 하는 경우

1) 인덱스를 사용해야 하는 경우

- 테이블에 행의 수가 많을 때

- WHERE문에 해당 컬럼이 많이 사용될 때

- 검색 결과가 전체 데이터의 2%~4% 정도일 때

- JOIN에 자주 사용되는 컬럼이나 NULL을 포함하는 컬럼이 많은 경우

2) 인덱스를 사용하면 안되는 경우

- 테이블에 행의 수가 적을 때

- WHERE문에 해당 컬럼이 자주 사용되지 않을 때

- 검색 결과가 전체 데이터의 10%~15% 이상일 때

- 테이블에 DML 작업이 많은 경우(입력 수정 삭제가 자주 일어날 때)

3. 인덱스의 물리적인 구조와 재생성

1) B-트리 인덱스 재생성

인덱스가 생성된 후 새로운 행을 추가하거나 삭제할 때 인덱스로 사용된 값이 변경될 수 있다. 이럴 경우 기본 테이블에서 추가, 삭제, 갱신 작업이 일어날 때 해당 테이블에 걸린 인덱스 내용도 수정해야 한다.

ALTER INDEX index_name REBUILD;

-- 인덱스를 재생성한다.

4. 인덱스의 종류

1) 고유 인덱스

유일 인덱스. 유일한 값을 갖는 컬럼에 설정. 

CREATE UNIQUE INDEX index_name

ON table_name (column_name);

-- 기존의 인덱스는 비고유 인덱스로, 고유 인덱스에는 UNIQUE 옵션을 추가해서 인덱스를 생성해야 한다.

2) 비고유 인덱스

유일하지 값을 갖는 컬럼에 설정. 기본 인덱스 형식이다.

CREATE INDEX index_name

ON table_name (column_name);

예제) 고유 인덱스 생성

CREATE UNIQUE INDEX IDX_EMP_COPY_EMPNO

ON EMP_COPY(EMPNO);

-- 사원번호 EMPNO는 중복되는 값 없이 유일하므로 고유 인덱스로 설정한다.

예제) 비고유 인덱스 생성

CREATE INDEX IDX_EMP_COPY_DEPTNO

ON EMP_COPY(DEPTNO);

-- 부서번호 DEPT는 중복되는 경우가 있으므로 비고유 인덱스로 설정한다.

3) 결합 인덱스

두 개 이상의 컬럼으로 인덱스를 구성

예제) 단일 인덱스 생성

CREATE UNIQUE INDEX IDX_EMP_COPY_EMPNO

ON EMP_COPY(EMPNO); -- 위 예제에서 만든 단일 인덱스

예제) 결합 인덱스 생성

CREATE INDEX IDX_EMP_COPY_DEPTNO_ENAME

ON EMP_COPY(DEPTNO, ENAME); -- 2개 이상의 컬럼을 합친 결합 인덱스

4) 함수 기반 인덱스 정의

함수 기반 인덱스는 수식이나 함수를 적용하여 만든 인덱스

CREATE INDEX INDEX_EMP_COPY_ANNSAL

ON EMP_COPY(SAL*12)