Oracle (index)

 인덱스는 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체다, 책을 볼 때 특정 내용을 빨리 찾기 위해 맨 뒤에 있는 찾아보기 항목을 사용해 본 적이 있을텐데, 이 찾아보기 부분이 인덱스라고 생각하면된다. 그 특성에 따라 대표적인 인덱스를 분류하면 대략 다음과 같다.

 

인덱스 구성 컬럼 개수에 따른 분류 : 단일 인덱스와 결합 인덱스

유일성 여부에 따른 분류 UNIQUE 인덱스, NON-UNIQUE 인덱스

인덱스 내부 구조에 따른 분류: B-tree 인덱스, 비트맵 인덱스, 함수 기반 인덱스

 

1과 2는 인덱스 형태와 속성에 따른 분류이며 , 3은 구조와 내부 알고리즘에 따른 분류로 보면 된다. 

인덱스는 테이블에 있는 한 개 이상의 컬럼으로 만들수 있다. 표준인 B-tree  인덱스 키 (인덱스 만들 테이블의 컬럼 값)와 이키에 해당하는 컬럼 값을 가진 테이블의 로우가 저장된 주소 값으로 구성된다.

 

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

CREATE UNIQUE INDEX DEPARTMENT_IX01
ON DEPARTMENTS(MANAGER_ID);

SELECT INDEX_NAME,INDEX_TYPE,INDEX_NAME,UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME = 'DEPARTMENTS';

중복 값을 허용하지 않는다는 측면에서 이 제약조건과 UNIQUE 인덱스는 같은 역할을 한다. 따라서 별도로 UNIQUE인덱스를 생성하지 않아도 UNIQUE 제약조건을 만들면 오라클은 자동으로 UNIQUE 인덱스를 생성해준다. 더불어 기본키를 생성해도 오라클은 자동으로 UNIQUE 인덱스를 생성해준다. 이때 생성되는 인덱스명은 UNIQUE나 기본키 객체명과 동일하게 생성된다.

 

SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'JOB_HISTORY';

 

또한 한개 이상의 컬럼으로 인덱스를 만들 수 있는데, 이를 결합 인덱스라고 한다.

CREATE INDEX EMPLOYEES_IX01
ON EMPLOYEES(MANAGER_ID,DEPARTMENT_ID);


//결과
SELECT INDEX_NAME , INDEX_TYPE, TABLE_NAME, UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME = 'EMPLOYEES';

MANAGER_ID 과 DEPARTMENT_ID 컬럼으로 NON-UNIQUE 인덱스가 만들어졌음을 확인할 수 있다. 인덱스는 테이블에 있는 데이터를 빨리 찾기 위한, 즉 조회 성능을 높이려는 목적에서 만들어 졌고 인덱스 자체에 키와 매핑주소 값을 별도로 저장한다. 따라서 테이블에 데이터를 입력하거나 삭제, 수정할때 인덱스에 저장된 정보도 이에 따라 생성, 수정이 이루어진다. 그러므로 인덱스를 너무 많이 만들면 SELECT 외의 INSERT ,DELETE, UPDATE 시 성능에 부하가 뒤따른다. 인덱스를 생성할때 고려해야 할 사항을 정리하면 다음과 같다.

 

 

일반적으로 테이블 전체 로우 수의 15%이하의 데이터를 조회할때 인덱스를 생성한다. 

    물론 15%는 정해진것은 아니며 테이블건수 데이터 분포 정도에 따라 달라진다.

 

테이블 건수가 적다면(코드성 테이블) 굳이 인덱스를 만들 필요가 없다.

데이터 추출을 위해 테이블이나 인덱스를 탐색하는 것을 스캔 이라고 하는데, 테이블 건수가 적으면 인덱스를 경유하기보다 테이블 전체를 스캔하는 것이 빠르다.

 

데이터의 유일성 정도가 좋거나 범위가 넓은 값을 가진 컬럼을 인덱스로 만드는것이 좋다

NULL 이 많이 포함된 컬럼은 인덱스 컬럼으로 만들기 적당치 않다

결합 인덱스를 만들 때는, 컬럼의 순서가 중요하다

테이블에 만들 수 있는 인덱스 수의 제한은 없으나, 너무 많이 만들면 오히려 성능 부하가 발생한다.