함수 기반 인덱스 장점 및 단점

함수 기반 인덱스란 무엇인가?

오라클 데이터베이스에서 함수 기반 인덱스(Function-Based Index, FBI)는 테이블의 특정 컬럼에 적용된 함수나 표현식의 결과 값을 기반으로 생성되는 인덱스입니다. 일반적인 인덱스가 컬럼의 원본 값 자체를 기반으로 하는 것과는 달리, 함수 기반 인덱스는 데이터에 대한 특정 연산이나 변환을 미리 수행한 결과로 인덱스를 생성하여, 해당 연산을 포함하는 쿼리의 성능을 획기적으로 개선할 수 있습니다.

함수 기반 인덱스가 필요한 이유

일반적인 인덱스는 컬럼의 값 자체를 검색하는 쿼리에는 매우 효과적이지만, 컬럼에 함수가 적용된 조건으로 검색하는 경우에는 성능이 저하될 수 있습니다. 예를 들어, ‘UPPER(ename) = ‘SMITH”와 같은 쿼리에서 ‘ename’ 컬럼에 일반 인덱스가 있더라도, 데이터베이스는 테이블의 모든 행에 대해 UPPER 함수를 적용한 후 비교해야 하므로 인덱스를 제대로 활용하지 못합니다.

이러한 경우, 함수 기반 인덱스를 사용하면 ‘UPPER(ename)’의 결과 값을 미리 인덱스에 저장해두기 때문에, 쿼리 실행 시 테이블 전체를 스캔하지 않고 인덱스만 검색하여 빠르게 결과를 찾을 수 있습니다.

함수 기반 인덱스의 장점

  • 성능 향상: 함수를 사용하는 쿼리의 응답 시간을 획기적으로 줄일 수 있습니다.
  • 쿼리 최적화: 옵티마이저가 함수 기반 인덱스를 활용하여 더 효율적인 실행 계획을 수립할 수 있도록 돕습니다.
  • 유연성: 다양한 함수 및 표현식에 대한 인덱스 생성을 지원하므로, 다양한 유형의 쿼리에 적용 가능합니다.

함수 기반 인덱스의 단점

  • 인덱스 유지 비용: 컬럼의 데이터가 변경될 때마다 함수를 계산하고 인덱스를 업데이트해야 하므로, DML 작업(INSERT, UPDATE, DELETE)의 성능에 영향을 줄 수 있습니다.
  • 저장 공간: 함수 결과 값을 저장하기 위한 추가적인 저장 공간이 필요합니다.
  • 복잡성 증가: 인덱스 생성 및 관리가 일반 인덱스보다 복잡하며, 잘못 설계된 함수 기반 인덱스는 오히려 성능 저하를 초래할 수 있습니다.

실전 예제: 함수 기반 인덱스 생성 및 활용

예제 1: 대소문자 구분 없이 이름 검색하기

다음은 ’employees’ 테이블에서 대소문자 구분 없이 이름을 검색하는 쿼리입니다.

SELECT emp_id, ename FROM employees WHERE UPPER(ename) = 'SMITH';

이 쿼리의 성능을 향상시키기 위해 ‘ename’ 컬럼에 UPPER 함수를 적용한 함수 기반 인덱스를 생성할 수 있습니다.

CREATE INDEX idx_employees_ename_upper ON employees (UPPER(ename));

이제 위 쿼리를 실행하면, 데이터베이스는 ‘idx_employees_ename_upper’ 인덱스를 사용하여 테이블 전체를 스캔하지 않고도 빠르게 결과를 찾을 수 있습니다.

예제 2: 날짜 형식 변환 후 검색하기

다음은 ‘orders’ 테이블에서 특정 날짜 형식으로 저장된 주문 날짜를 검색하는 쿼리입니다.

SELECT order_id, order_date FROM orders WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = '2023-10-27';

이 쿼리의 성능을 향상시키기 위해 ‘order_date’ 컬럼에 TO_CHAR 함수를 적용한 함수 기반 인덱스를 생성할 수 있습니다.

CREATE INDEX idx_orders_order_date_char ON orders (TO_CHAR(order_date, 'YYYY-MM-DD'));

이 경우에도 쿼리 실행 시 인덱스가 활용되어 테이블 전체 스캔을 피할 수 있습니다.

실행 계획 확인하기

쿼리가 실제로 함수 기반 인덱스를 사용하는지 확인하려면 ‘EXPLAIN PLAN’ 명령을 사용하여 쿼리의 실행 계획을 분석할 수 있습니다.

EXPLAIN PLAN FOR SELECT emp_id, ename FROM employees WHERE UPPER(ename) = 'SMITH'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

실행 계획 결과에 ‘INDEX RANGE SCAN’ 또는 ‘INDEX FULL SCAN’과 함께 생성한 인덱스 이름(‘idx_employees_ename_upper’)이 나타나면, 쿼리가 함수 기반 인덱스를 성공적으로 사용하고 있다는 것을 의미합니다.

함수 기반 인덱스 설계 시 고려 사항

  • 쿼리 패턴 분석: 어떤 함수가 자주 사용되는지, 어떤 컬럼에 적용되는지 등을 분석하여 인덱스 생성 대상을 결정해야 합니다.
  • 유지 비용과 성능 trade-off: DML 작업의 빈도와 성능 향상 효과를 고려하여 인덱스 생성 여부를 결정해야 합니다.
  • 적절한 함수 선택: 인덱스 생성에 사용할 함수는 결정적(Deterministic)이어야 합니다. 즉, 동일한 입력 값에 대해 항상 동일한 결과 값을 반환해야 합니다.

결론

함수 기반 인덱스는 특정 유형의 쿼리 성능을 획기적으로 개선할 수 있는 강력한 도구입니다. 하지만 인덱스 유지 비용, 저장 공간, 복잡성 등을 고려하여 신중하게 설계해야 합니다. 실전 예제를 통해 얻은 지식을 바탕으로, 데이터베이스 환경에 최적화된 함수 기반 인덱스를 구축하여 오라클 데이터베이스의 성능을 극대화하십시오.

위로 스크롤