대용량 데이터베이스 솔루션 - 함수 기반 인덱스

개념

CREATE INDEX prod_idx ON prod ( cnt * price );

SELECT * FROM prod WHERE cnt * price = 300

위엣 cnt * price 를 연산이라고 생각하지 말고 일종의 컬럼명칭이라고 생각해보자.

PROD 테이블의 cnt, price 컬럼을 곱한 결과를 인덱스로 생성한 것이다.

함수기반 인덱스는 함수(Function)나 수식(Expression)으로 계산된 결과에 대해 B-Tree 인덱스나 비트맵 인덱스를 생성할 수 있다.

여기에서 사용하는 함수는 흔히 사용하는 산술식, 사용자 지정함수, SQL 제공함수, 패키지 등이 가능하다.

그러나 SUM, AVG 와 같은 그룹함수는 사용할 수 없다.

인덱스란 구체적인 단위 로우를 가리키는 일종의 포인터이므로 논리적으로 생성된 로우에 대해서는 적용할 수 없기 때문에 이는 당연하다.

제약사항/준수사항

함수 기반 인덱스에서 사용자 지정함수 사용 시 해당 함수에서는 거의 모든 절차형 처리뿐만 아니라 다른 테이블의 정보를 참조하여 가공할 수가 있기

때문에 제약이 불가능하다. 다시 말해 논리적으로 가능한 모든 표현을 할 수 있기 때문에 아무런 제약이 없다면 심각한 문제가 발생할 수도 있다.

그렇기 때문에 몇 가지의 제약사항을 가지고 있을 수 밖에 없다.

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

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

- 다른 테이블을 참조하는 함수를 이용하여 함수 기반 인덱스를 생성하는 것은 권장할 것이 못된다.

  이유인 즉 이미 계산된 내용을 논리적 뷰 처럼 가지고 있는 함수 기반 인덱스 값이 존재하므로 해당 테이블의 내용을 바꾸고자 할때 

  그 테이블을 못바꾸게 하거나 (예전 오라클 버전) 논리적 함수기반 인덱스를 바꾸려면 많은 비용이 들기 때문이다.

  그래서 함수기반 인덱스에 이미 저장된 과거의 데이터는 그대로 인정하고 새로 발생하는 데이터에 대해서만 계산이 새로 되는 것도 하나의 방법이다.

  이것을 원하지 않는다면 'disabled' 을 시키거나 재구축을 해서 처리 한다.

.. 등등 생략 (책 84p ~ 85p)

함수기반 인덱스의 활용

실전에서 생각보다 많이 활용되지 않고 있다. 그러나 정확한 개념의 이해를 바탕으로 적절한 적용형태를 찾아보면 아주 훌륭한 해결책이 되는 경우도 많을 

것이다.

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

   √ 컬럼의 중간 부분의 검색 : 잘못된 설계로 인해 특정 컬럼을 조회하고자 할때 필연적으로 SUBSTR 함수를 이용하여 데이터를 처리하게 될 경우

      CREATE INDEX from_loc_index ON orders (SUBSTR (ship_id, 5, 3)); 과 같은 함수기반 인덱스를 생성하여 문제를 어느정도 해소시킬 수 있다.

   √ 조인 연결고리 컬럼이 대응하지 않는 경우의 해결 : 이 경우도 정상적인 데이터 모델링을 수행했다면 나타날 수 없는 형태이지만 현실에서는 가끔 

      등장하는 형태이다. 

      ex) 품목 분류 테이블에 컬럼을  '대분류(class1)', '중분류(class2)', '소분류(class3)' 로 나누었으나 외부키를 받는 하위 테이블에서는 

           컬럼이 늘어나는 것을 염려하여 '그룹코드' 라는 하나의 컬름으로 생성한 사례.. 

          SELECT ... FROM item_group g, items i

          WHERE g.class1 = SUBSTR(i.group_code, 1, 2)

              AND  g.class2 = SUBSTR(i.group_code, 3, 2)

              AND  g.class3 = SUBSTR(i.group_code, 5, 3) 

         이런 경우에는 데이터가 적은 상위 테이블에 함수기반 인덱스를 생성하여 해결할 수 있다.

         CREATE INDEX group_cd_idx ON item_group (class1 || class2 || class3 );

   √ 날짜 컬럼이 분활된 경우의 해결 : 년 월 일 을 각각 컬럼으로 분할하여 관리하는 경우 범위 스캔하고자 하면 컬럼 값을 매번 더해서 계산하고 인덱스를 

     사용할 수 없게 되어 심각한 문제가 발생 한다. 이런 경우 CREATE INDEX sal_date_idx ON sales (sal_yyyy || sal_mm || sal_dd);  로 쉽게 해결 

     할 수 있다.

   √ 데이터 타입이 상이한 조인 컬럼 : 데이터 타입이 서로 다른 컬럼을 조인하면 컬럼의 내부적 변형이 발생하여 어느 한쪽의 인덱스가 사용될 수 없는 

     상태가 될 수 있다. 이로 인해 조인의 방향이 잘못되어 심각한 수행속도 저하가 발생한다.

     ex) DEPT 테이블의 dept_no 는 NUMBER 인데 EMP 의 dept_no 는 VARCHAR2 로 정의 되어 있을때 아래와 같이 해결 가능 하다.

          CREATE INDEX dept_no_idx ON emp (TO_NUMBER(dept_no));

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

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

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

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