[DB] ORACLE INDEX

정의

조회 속도를 향상시키기 위한 데이터베이스 검색 기술. 해당 테이블의 조회결과를 빠르게 하기 위해 사용. ('색인' 이라 칭함)

인덱스를 생성해 줌으로 써 조회 속도를 빠르게 할 수 있기 때문에 인덱스가 필요.

 

 

종류

자동인덱스 : Primary Key 또는 Unique 제한 규칙에 의해 자동으로 생성되는 인덱스

수동인덱스 : CREATE INDEX 명령을 실행해서 만들어지는 인덱스

 

 

원리

 

기본

1. 인덱스 설정 시 INDEX TABLE 이 생성

2. 인덱스 컬럼의 로우값과 ROWID 값이 저장

3. 로우값을 트리 구조로 저장

 

1~3 과정이 발생하기 때문에 검색 시 좀더 빠른 데이터 조회가 가능.

 

* ROWID : 데이터 삽입 시 하나의 ROW 가 생성되며 이 ROW 는 절대적인 주소를 가지게 되는데 이를 ROWID 라고 한다.

 

 

- 인덱스 선두 컬럼이 조건절에서 사용되지 않으면 범위 스캔을 위한 시작점을 찾을 수 없어 옵티마이저는 인덱스 전체를 스캔하거나 테이블 전체를 스캔하는 방식을 선택한다.

 

- 결합 인덱스일 때는 인덱스 구성 컬럼중 하나라도 값이 null이 아닌 레코드는 인덱스에 포함되므로, 필터링을 통해 '인덱스 구성 컬럼중 하나 is not null' 조건에 해당하는 레코드를 찾을 수 있다.

 

- 옵티마이저는 where pk컬럼 is null 조건은 인덱스 스캔을 통해 공집합을 리턴하는 방식으로 table full scan을 피한다

 

- 결합 인덱스일 때는 인덱스 구성 컬럼중 하나라도 값이 is null 조건을 사용하더라도 인덱스 선두컬럼이 조건절에서 누락되지 않으면 Index Range Scan이 가능하다.

 

- 결합 인덱스일 때는 인덱스 구성 컬럼중 어느 하나에 Not Null 제약이 설정돼 있다면 그 컬럼에대한 조건식 유무에 상관없이 항상 Index Range Scan이 가능하다.

 

- 숫자형 컬럼과 문자형 컬럼을 비교하면 문자형 컬럼이 숫자형으로 변환

- like로 비교할 때는 숫자형이 문자형으로 변환

- decode(a, b, c, d)는 출력된 값의 데이터 타입은 세번째 인자 c에 의해 결정된다. c인가자 null값이면 varchar2로 취급한다

- 문자형과 날짜형이 만나면 날짜형으로 변환

- 성능을 위해서라면 인덱스 컬럼과 비교되는 반대쪽을 인덱스 컬럼 데이터 타입에 맞춘다.

 

 

단점

UPDATE, INSERT, DELETE 등 DML 명령 사용 시 INDEX TABLE 까지 데이터 갱신이 이루어지기 때문에 속도가 느려짐.

 

 

인덱스가 필요한 경우

- 데이터가 많이 쌓일 거라고 예상되는 경우

- 데이터가 많이 쌓여 있어 조회 속도가 너무 느릴 경우

- 조회 결과가 전체 데이터수의 3~5% 미만일 경우 인덱스 스캔이 효율적이며 적은 COST 로 빠르게 데이터 검색 가능

- 조회 결과가 전체 데이터수의 3~5% 이상일 경우 풀스캔이 유리

 

 

인덱스가 필요없는 경우

- 데이터가 적은 경우

- 조회보다 삽입, 수정, 삭제 처리가 많은 테이블

- 조회결과가 전체행의 15% 이상 읽어들일 것으로 예상 될 때.

 

 

생성방법

- 단일 인덱스

CREATE INDEX [인덱스명] ON TABLE_NAME [컬럼명]

 

- 다중(복합) 인덱스

CREATE INDEX [인덱스명] ON TABLE_NAME [컬럼명1, 컬럼명2, 컬럼명3]

 

* 복합인덱스를 타게 하려면 조회 조건에서 복합인덱스에서 정해준 모든 컬럼을 조회 조건으로 사용해야 탈 확률이 높아진다.

 

 

유의할 점

- 인덱스는 NOT NULL 값에 대해서만 생성되기 때문에 NULL 여부를 체크하는 비교문은 인덱스를 사용하지 못함.

 

 

인덱스를 사용해야 할 컬럼

1. WHERE 절이나 조인 조건에서 자주 사용되는 열

2. 열이 광범위한 값을 포함할 때

 

 

인덱스를 사용하지 않아야 할 컬럼

1. 테이블에 데이터가 적은 경우

2. where 절에 자주 사용되지 않는 열

3. 테이블이 자주 갱신 될 때