INDEX (인덱스)

INDEX 란?

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

index 사용해야할 경우
- 테이블에 행의 수가 많을 경우
- 조건절인 where문에 해당 컬럼이 많이 사용 될 경우
- 검색 결과가 데이터의 2% ~ 4% 정도일 경우(적은 양의 컬럼을 가져올 경우)

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

index 사용하지 말아야 하는 경우
- 테이블에 행의 수가 적을 경우
- where 문에 해당 컬럼이 자주 사용되지 않을 경우

- 검색 결과가 전체 데이터의 10% ~ 15% 이상 높을 경우(많은 양의 컬럼을 가져 올 경우)
- 테이블에 DML 작업이 많은 경우(입력,수정,삭제 등이 자주 발생 할 경우)

INDEX의 종류

고유 인덱스 : 유일한 값을 가지는 컬럼에 대해 생성하는 인덱스

CREATE UNIQUE INDEX idx_name ON table_name(column_name);

비고유 인덱스 : 중복간 값을 가지는 컬럼에 대해 생성하는 인덱스

CREATE INDEX idx_name ON table_name(column_name);

단일 인덱스 : 하나의 컬럼으로만 구성된 인덱스

결합 인덱스 : 두 개 이상의 컬럼을 결합하여 생성되는 인덱스, WHERE 절의 조건 비교에서 두 개 이상의 컬럼이 AND로 연결되어 자주 사용되는 경우에 주로 생성

CREATE INDEX index_name ON table_name(column_name1, column_name2);

Descending 인덱스 : 인덱스에서 정렬을 DESC, ASC할 수 있다.

함수기반 인덱스(FBI) : 오라클 8i부터 지원, 컬럼에 대한 연산이나 함수의 계산 결과를 인덱스로 생성 가능

CREATE INDEX index_name ON table_name(column1 - column2);

ORACLE INDEX 생성 및 삭제

index Dictionary (인덱스 사전)

- 인덱스가 어떤 테이블에 대해 생성되어 있고, 어떤 컬럼으로 구성되어 있는지에 대한 정보를 보고자 할 때 사용

USER_INDEXES :인덱스 이름 및 고유성을 포함, 테이블이 소유하고 있는 인덱스가 어떤 것이 있는지 확인

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'TEMP';

USER_IND_COLUMNS : 인덱스 이름, 테이블 이름, 열 이름 포함, 인덱스가 어떤 컬럼으로 구성되어 있는가 확인

SELECT INDEX_NAME, COLUMN_NAME FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'TEMP';

CREATE INDEX (생성)

CREATE INDEX index명 ON table명(INDEX걸어줄 TABLE의 COLUMN명);

CREATE INDEX index_name ON table_name (column_name);

CREATE INDEX index_name ON table_name (column_name1,column_name2,column_name3);

복합 인덱스로 지정해준 테이블에서 복합 인덱스를 타게 하려면 복합 인덱스로 설정한 컬럼을 조회 쿼리에서 모두 조회 조건에 사용해야 인덱스를 탈 확률이 높아진다.

CREATE INDEX IDX_NAME ON TABLE_NAME(ROUND(PRICE1-PRICE2));

오라클의 경우 가공시킨 컬럼에도 적용이 가능하다. 하지만 조회 쿼리에서 인덱스를 타게 하려면 동일하게 조건을 주어야 한다.

ex) SELECT * FROM TABLE_NAME WHERE ROUND(PRICE1 - PRICE2) > 0;

DELETE INDEX (삭제)

기본적으로 INDEX의 소유자이거나 DROP ANY INDEX의 권한이 있어야 ㅎ

DROP INDEX index명;

DROP INDEX index_name;

ALTER INDEX (수정)

ALTER INDEX index명 RENAME TO 새로운 index명;

ALTER INDEX index_name RENAME TO new_index_name;

오라클 9i부터 사용 가능!

INDEX REBUILD

ALTER INDEX index명 REBUILD;

ALTER INDEX idx_name REBUILD;

INDEX 조회

SELECT object_name, object_type FROM user_objects WHERE object_type ='INDEX';

효율성이 떨어지면 인덱스를 재 생성해 주어야 한다.
컬럼의 데이터가 입력,수정,삭제될 경우 해당 컬럼에 의해 생성된 인덱스에 대해서 재구성 해야 된다.

원본 : http://hyun0412.tistory.com/?page=3