오라클 인덱스에 대해 알아보자 (데이터베이스 실무에서 효율적으로 사용 할 수 있는 Oracle Index)

데이터베이스 실무에서 인덱스는 데이터 검색과 조인 등의 작업을 효율적으로 수행하기 위해 중요한 도구입니다. 특히, WHERE 절이나 조인 조건절에서 자주 사용되는 칼럼, 전체 데이터 중 일부를 검색하는 경우, 여러 칼럼이 자주 사용되는 경우, 그리고 테이블의 데이터 변경이 드문 경우에 인덱스를 생성하는 것이 좋은데요, 이번 글에서는 오라클 데이터베이스에서 인덱스의 종류와 생성 방법, 그리고 관리에 대해 알아보겠습니다.

인덱스의 종류 및 생성 방법

1.1. 단일 인덱스

단일 인덱스는 하나의 칼럼으로 구성되는 인덱스로, WHERE 절이나 조인에서 자주 사용되는 칼럼에 대해 생성합니다.

사용법

CREATE INDEX index_name ON table_name (column_name [ASC|DESC]);

1.2. 결합 인덱스

결합 인덱스는 두 개 이상의 칼럼을 결합하여 생성하는 인덱스입니다. 예시로, 학생 테이블의 deptno와 grade 칼럼을 결합 인덱스로 생성하는 방법을 알아보겠습니다.

사용법

CREATE INDEX idx_stud_dno_grade ON student (deptno, grade);

1.3. DESCENDING INDEX

결합 인덱스에서 칼럼의 정렬 순서는 기본적으로 오름차순으로 고정되어 있습니다. DESCENDING INDEX는 칼럼별로 정렬 순서를 별도로 지정하여 결합 인덱스를 생성하는 방법입니다. 예를 들어, 학생 테이블에서 deptno 칼럼은 내림차순으로, name 칼럼은 오름차순으로 생성하는 경우를 보겠습니다.

사용법

CREATE INDEX fidx_stud_no_name ON student (deptno DESC, name ASC);

인덱스 관리

2.1. 인덱스 정보 조회

인덱스에 대한 정보는 데이터 딕셔너리 뷰인 user_indexes나 user_ind_columns을 통해 확인할 수 있습니다. user_indexes에서는 인덱스 이름과 유일성 여부 등을, user_ind_columns에서는 인덱스 이름, 인덱스가 생성된 테이블 이름과 칼럼 이름 등을 조회할 수 있습니다.

사용법

인덱스 정보 조회

SELECT index_name, uniqueness FROM user_indexes WHERE table_name = ‘STUDENT’;

인덱스 칼럼 정보 조회

SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘STUDENT’;

2.2. 인덱스 삭제

인덱스를 삭제하기 위해서는 DROP INDEX 명령문을 사용합니다. 단, 기본키나 고유키 제약 조건을 생성할 때 오라클에서 자동으로 생성되는 인덱스는 DROP INDEX 명령문으로 삭제할 수 없습니다.

사용법

인덱스 삭제

DROP INDEX index_name;

인덱스는 데이터베이스 실무에서 데이터 검색과 조인 작업을 효율적으로 수행하는 데 중요한 도구인데요, WHERE 절이나 조인 조건절에서 자주 사용되는 칼럼에 대해 인덱스를 생성하고, 전체 데이터 중 일부를 검색하는 경우에도 인덱스를 활용하면 성능 향상이 될 수 있습니다. 또한, 복합 조건으로 검색되는 경우에는 결합 인덱스를 생성하여 작업 효율을 높일 수 있습니다. 이렇듯 인덱스를 효과적으로 활용하고 관리함으로써 데이터베이스의 성능을 최적화하는 것이 중요하며, 데이터베이스 운영에 필수적인 요소라고 할 수 있습니다.

이전 글 – 오라클 뷰(VIEW) 조회 및 사용방법 (개념과 특징,종류, 장단점, 인라인뷰)

이전 글 – 오라클 사용자 권한 제어 (4가지 – 시스템 권한, 객체 권한, 롤, 동의어)

이전 글 – 오라클(데이터 웨어하우스) 분석 함수(효과적인 데이터 다차원 분석을 위한 강력한 도구)

이전 글 – 오라클 스케줄러와 JOB 관리 방법

이전 글 – 오라클 테이블 복구(테이블 데이터 복원하기)