oracle SQL - 인덱스

인덱스란?

쿼리 성능을 향상시켜주는 스키마 객체 중 하나입니다.

 - 스키마 객체 중 하나로 테이블의 행에 대해 직접적이고 빠른 접근방식을 제공합니다.

 - 단일 열 또는 조합 열에 정의할 수 있습니다.

 - 고유 인덱스와 비 고유 인덱스가 있습니다.

 - 명령문을 통해 수동으로 생성하거나 오라클 서버에 의해 자동으로 생성됩니다.

 - 자동으로 생성되는 경우는 특정 열의 제약조건이 Primary Key 또는 Unique일 경우 고유 인덱스가 자동으로 생성됩니다.

 - 자동으로 생성된 인덱스는 수동으로 삭제할 수 없습니다.

 - 테이블 DROP시 테이블과 관련된 모든 인덱스도 같이 삭제됩니다.

 

 

수동으로 인덱스 생성 문법 

CREATE [UNIQUE] INDEX 인덱스이름
ON 테이블이름(COLUMN [, COLUMN2...]);

1행 UNIQUE 키워드를 생략하면 디폴트로 비 고유 인덱스를 생성합니다.

고유 인덱스를 생성하기 위해선 UNIQUE 키워드를 사용해야 합니다.

 

주의

※ 하나의 열에 인덱스를 여러 번 정의할 수 있지만 인덱스가 많은 테이블은 DML 성능이 저하될 가능성이 있습니다.

 

 

인덱스는 언제 생성해야 할까?

1) 열에 광범위한 값이 포함된 경우

2) 열에 NULL값이 많이 포함된 경우

3) WHERE 절 또는 조인 조건에 자주 사용되는 경우

4) 쿼리에 의해 검색되는 행이 적은 경우 (2% ~ 4%)

 - 반환되는 행이 많은 경우 Full table scan이 더 효율적일 수 있습니다.

 

 

인덱스를 권장하지 않는 경우

1) 테이블이 작은 경우

2) 열이 쿼리의 조건으로 자주 사용되지 않는 경우

3) 테이블이 자주 갱신되는 경우

4) 인덱스화된 열이 표현식의 일부로 참조되는 경우

 - 예를 들어 월 급여 열이 인덱스와 되어있지만 (급여 * 12) 즉 급여를 통해 연봉으로 반환한다면 함수기반 인덱스를 권장합니다.

 

 

인덱스 유형

1. 고유 인덱스 (Unique Index)

 - Primary Key 또는 Unique 제약조건을 걸면 자동으로 생성되는 인덱스입니다.

 - 고유 인덱스화된 열은 중복된 값을 가질 수 없습니다.

 - 수동으로 생성 시 "CREATE UNIQUE INDEX"처럼 UNIQUE를 입력해주어야 합니다.

 

2. 비 고유 인덱스 (Non-Unique Index)

 - NULL값이 많은 열이지만 자주 참조하는 열이거나 FOREIGN KEY가 정의된 열에 주로 사용합니다.

 

3. 결합 인덱스 (Composite Index)

 - 인덱스 생성 시 ON절에 열을 여러 개 지정하면 조합 열에 대한 단일 인덱스가 생성됩니다.

 - 특정 두 열을 조건으로 자주 사용한다면 두 열을 결합하여 결합 인덱스를 생성합니다.

 

4. 함수기반 인덱스 (Function Based Index)

 - 인덱스화된 열에 함수나 표현식을 사용하면 Full Table Scan이 발생합니다. (인덱스 무용지물)

 - 사원의 급여보다 연봉을 더 자주 검색한다면 다음과 같이 함수 기반 인덱스 생성

CREATE INDEX emp_annsal_idx
ON y_emp(salary * 12);

 - 쿼리문에 salary * 12 명령문을 실행하는 동안 계산하지 않고 인덱스를 참조하므로 검색 속도 향상

 

 

인덱스 정보 (Data Dictionary)

USER_INDEXS

 - 인덱스 이름, 고유성, 관련 테이블 등 확인

 - 열의 이름은 확인할 수 없습니다.

 

USER_IND_COLUMNS

 - 인덱스와 관련된 열 확인