[Oracle] 인덱스 생성 및 삭제

인덱스

• SQL명령문의 처리 속도 향상을 위해 칼럼에 대해 생성하는 객체이다.
• 인덱스는 포인트를 이용하여 테이블에 저장된 데이터를 랜덤 액세스 하기 위한 목적으로 사용한다.

 

인덱스를 언제 써야 효율적일까?

• WHERE절이나 조인 조건절에서 자주 사용되는 칼럼에 쓴다.
• 전체 데이터중에서 10~15% 이내의 데이터를 검색하는 경우
• 테이블에 저장된 데이터의 변경이 드문 경우

 

인덱스 생성 방법

CREATE [UNIQUE] INDEX index_name
ON table_name (column1 [ASC|DESC] [, column2 [ASC|DESC]...]);

• UNIQUE : 고유 인덱스 지정
• ASC | DESC : 인덱스 키를 오름차순 또는 내림차순으로 정렬

 

고유 인덱스

유일한 값을 가지는 칼럼에 대해 생성하는 인덱스이다.

모든 인덱스 키는 테이블의 하나의 행과 연결된다.

 

EX) 테이블에서 dname 칼럼을 고유 인덱스로 생성.

(인덱스의 이름을 idx_dept_name으로 정의)

-- test 테이블에 dname 칼럼 추가
ALTER TABLE test
ADD dname VARCHAR2(100);


-- 고유 인덱스 생성
CREATE INDEX idx_dept_name
ON test(dname);

 

비고유 인덱스

중복된 값을 가지는 칼럼에 대해 생성하는 인덱스이다.

하나의 인덱스 키는 테이블의 여러 행과 연결될 수 있다.

 

EX) test 테이블에 birthdate 칼럼을 비고유 인덱스로 생성하라.

( 비고유 인덱스의 이름은 idx_stud_birthdate로 정의 )

CREATE INDEX idx_stud_birthdate
ON test(birthdate);

 

단일 인덱스 / 결합 인덱스

• 단일 인덱스 : 하나의 칼럼으로 구성된 인덱스
• 결합 인덱스 : 두 개 이상의 칼럼을 결합하여 생성된 인덱스

 

EX) test 테이블에 deptno, grade 칼럼을 결합 인덱스로 생성하라.

( 결합 인덱스의 이름은 idx_test_dno_grade로 정의. )

CREATE INDEX idx_test_dno_grade
ON test(deptno, grade);

 

DESCENDING INDEX

칼럼 별로 정렬 순서를 지정하여 결합 인덱스를 생성하기 위한 방법이다.

 

EX) test 테이블의 deptno와 name 칼럼으로 결합 인덱스 생성.

( deptno 칼럼은 내림차순, name 칼럼은 오름차순으로 생성 )

CREATE INDEX test_no_name
ON test(deptno DESC, name ASC);

 

함수 기반 인덱스

칼럼에 대한 연산이나 함수의 계산 결과를 인덱스로 생성한다.

 

EX) 표준 체중에 대한 함수 기반 인덱스 생성

( 표준 체중을 구하는 공식은 '신장-100*0.9'이다. )

CREATE INDEX idx_test_weight
ON test( (height-100)*0.9 );

 

인덱스 정보 조회

1) USER_INDEXES에서 인덱스 이름과 유일성 여부를 검색한다.

SELECT index_name, uniqueness
FROM user_indexes;

 

2) USER_IND_COLUMNS에서 인덱스 이름, 인덱스가 생성된 테이블 이름과 칼럼 이름 등 확인한다.

SELECT index_name, column_name
FROM user_ind_columns;

 

인덱스 삭제

DROP INDEX문을 사용하자.

DROP INDEX index_name;

 

EX) test 테이블에 생성한 idx_test_dno_grade 인덱스를 삭제하자.

DROP INDEX idx_test_dno_grade;

 

인덱스 재구성

인덱스를 정의한 테이블의 칼럼 값에 대해 변경 작업이 자주 발생하여,

불필요하게 생성된 인덱스 내부 노드를 정리하는 작업

ALTER INDEX index_name REBUILD
[TABLESPACE tablespace];

 

EX) test_pk 인덱스를 재구성

ALTER INDEX test_pk REBUILD;