함수기반 인덱스(FBI, Fuction-Baseed Index)

함수기반 인덱스

함수 기반 인덱스는 데이터베이스에서 특정 열이나 표현식의 값을 기반으로 인덱스를 생성하는 기술이다.

이러한 인덱스는 데이터베이스 성능을 향상하고 쿼리의 실행 속도를 향상하는 데 도움을 줄 수 있다.

 

함수 기반 인덱스는 다음과 같은 상황에서 유용할 수 있다.

1. 계산된 열 : 데이터베이스 테이블에 저장된 데이터를 기반으로 계산된 열이 있는 경우 이러한 계산된 열을 인덱싱할 수 있다. 주문 날짜와 시간을 합친 값을 계산된 열로 가지고 있는 경우, 이 값을 인덱스화할 수 있다.

2. 문자열 조작 : 문자열 함수를 사용하여 데이터를 가공하고 특정 문자열 패턴을 추출하는 경우 해당 문자열 함수의 결과를 인덱스화할 수 있다.

3. 데이터 변환 : 데이터 형식을 변환하는 함수를 사용하여 데이터를 가공하는 경우 이러한 데이터 변환을 인덱스화할 수 있다.

 

함수 기반 인덱스를 사용하면 쿼리 성능을 향상시키는 동시에 데이터 무결성을 유지할 수 있다. 함수 기반 인덱스를 오용하면 성능 문제를 불러올 수 있으니 주의해야 한다.

 

함수 기반 엔덱스의 개념 및 구조

 함수 기반 인덱스의 원리는 매우 단순하다. 테이블의 칼럼들을 가공한 논리적 칼럼을 인덱스로 생성한 것일 뿐이다.

우리가 일반적으로 쿼리에서 사용하는 컬럼은 실존하는 테이블의 물리적인 칼럼만은 아니다. 

 함수 기반 인덱스는 함수나 수식으로 계산된 결과에 대해 B-Tree인덱스나 비트맵 인덱스를 생성할 수 있다. 여기에서 사용할 수 있는 함수는 우리가 흔히 사용하는 산술식, 사용자 지정 함수, SQL의 제공함수, 패키지 등이 가능하다.

 그러나 SUM, AVG 등 그룹함수는 사용할 수 없다. 그 이유는 이러한 함수들은 테이블의 로우 단위가 아닌 새롭게 생성된 논리적 로우 단위로 적용됙 때문이다. 인덱스란 구체적인 단위 로우를 가리키는 일종의 포인터이므로 논리적으로 생성된 로우에 대해서는 적용할 수 없는 것이 당연하다.

 객체 정의에 사용되는 오브젝트 타입들은 해당 컬럼에 정의된 매소드를 통해서만 함수 기반 인덱스를 적용할 수 있다.

LOB나 REF 타입으로 정의된 컬럼, 'TYPE'오브젝트가 테이블의 칼럼으로 지정되어 있는 네스티드 테이블 칼럼, 또는 이들을 포함하고 있는 오브젝트 타입에서는 사용할 수 없다.

 함수 기반 인덱스는 DBMS마다 약간 다른 명칭을 사용하고 있다. FUNCTION-BASED INDEX라고 부르기도 하지만 'FUNCTIONAL INDEX'라는 이름으로 사용되기도 한다. 약간의 제약요건에 차이가 있지만 유사한 용도로 활용할 수 있다.

 

함수 기반 인덱스의 제약사항

 - 비용 기준 옵티마이져에서만 사용 가능하다.

 - 함수 기반 인덱스를 생성한 후 반드시 통계정보를 생성해야 한다.

 - 사용자 지정 함수는 반드시 'DETERMINISTIC'으로 선언되어야 한다.

 - QUERY_REWRITE_ENABLED 파라메터가 TRUE로 선언되어야 한다.

 - QUERY_REWRITE_INTEGRITY가 TRUSTED로 선언되어야 한다.

 - 다음의 사용자 권한을 가져야 한다.

   *인덱스 생성권한, 쿼리 재생성 권한

 - 함수나 수식의 결과가 NULL인 경우는 이 인덱스를 통해 액세스 할 수 없다.

 - 사용자 지정 함수를 사용한 경우에는 종속성 유지에 주의해야 한다.

 - 옵티마이져가 사용불가 상태가 된 인덱스를 선택하면 SQL의 실행은 실패한다. 이러한 경우에는 새로운 데이터가 입력, 갱신, 삭제도 불가능해지므로 다음과 같은 명령을 사용하여 복구해야 한다.

   * 'ALTER INDEX...ENABLE / ALTER INDEX... REBUILD'를 이용하여 ENABLE 상태로 만들거나 리빌드 하여야 한다.

   * 'ALTER INDEX...UNUSABLE'을 사용하여 미사용 상태로 만든다. 이 상태는 사용불가 상태와는 다르다,

 - 스칼라 서브쿼리로 표현된 컬럼은 함수기반 인덱스를 생성할 수 없다.

 - 값이 상황에 따라 달라질 수 있는 SYSDATE, USER, ROWNUM 등의 가상칼럼이 포함되면 이 인덱스를 생성할 수 없다.

 - 파티션을 적용한 경우에 파티션 키를 함수기반 인덱스에 사용할 수 없다.

 - 숫자 컬럼을 문자연산 하거나 문자 칼럼을 수치연산하는 수식의 경우에는 직접 기술하지 않더라도 내부적으로 TO_CHAR, TO_NUMBER가 추가되어 처리된다.

 - 함수 기반 인덱스에서는 NLS 파라메터를 현재 기준으로 적용하기 때문에 만약 세션 레벨에서 재정의를 한다면 잘못된 결과를 얻을 수도 있으므로 주의해야 한다. NLS_SORT와 NLS_COMP는 세션레벨의 정의에 영향을 받지 않는다.

 - 함수 기반 인덱스는 검색의 효율 향상을 위해서는 효과적이지만 구성 칼럼에 대한 빈번한 입력과 수정이 발생하면 부하가 가중되므로 주의할 필요가 있다.

 

함수 기반 인덱스의 활용 1

1. 검색 성능 향상 : 함수 기반 인덱스는 특정 함수나 표현식의 결과를 기반으로 인덱싱 하기 때문에 해당 함수나 표현식을 사용하는 쿼리의 검색 성능을 향상한다.

2. 정렬 및 그룹화 : 함수 기반 인덱스를 사용하여 데이터를 특정 순서로 정렬하거나 그룹화활 수 있다. ORDER BY, GROUP BY 절과 함께 사용될 때 효과적이다.

3. 데이터 유효성 검사 : 함수 기반 인덱스를 사용하여 데이터의 유형성을 검사하고 제약조건을 적용할 수 있다.

4. 데이터 변환 및 정규화 : 데이터를 특정 형식으로 변환하거나 정규화할 때 함수 기반 인덱스를 사용할 수 있다.

5. 다중 열 인덱싱 : 함수 기반 인덱스는 여러 열의 값을 조합하여 인덱스를 생성할 때 유용하다.

6. 비트맵 인덱스 : 함수 기반 인덱스는 비트맵 인덱스와 함께 사용될 때 효과적이다.

 

함수 기반 인덱스의 활용 2

1. 테이블 설계상의 문제를 해결

 - 칼럼의 중간 부분의 검색

 - 조인 연결고리 칼럼이 대응하지 않을 경우의 해결

 - 일자 컬럼이 분할된 경우의 해결

 - 데이터 타입이 상이한 조인 컬럼

 - 조인 컬럼이 경우에 따라 달라지는 경우의 조인

 - 부모 테이블의 칼럼과 결합한 인덱스 생성

2. 오류 데이터의 검색 문제를 해결

 - 대/소문자나 공백이 혼재된 칼럼의 검색

 - NULL 값을 치환하여 검색

 - 접두사를 채워서 검색

3. 가공처리 결과의 검색

 - 복잡한 계산 결과의 검색

 - 말일, 단가의 검색

 - 기간, 컬럼 길이 검색

4. 오브젝트 타입의 인덱스 검색

5. 배타적 관계의 인덱스 검색

 - 배타적 관계의 유일성 보장

 - 배타적 관계의 결합 인덱스