Oracle 데이터베이스에서 함수 기반 인덱스는 SQL 쿼리 성능을 획기적으로 개선할 수 있는 강력한 기능입니다. 이 글에서는 함수 기반 인덱스가 무엇인지, 언제 사용하는 것이 효과적인지, 그리고 실제 예제와 함께 자세히 알아보겠습니다.
함수 기반 인덱스란 무엇인가요?
기존의 인덱스는 테이블 컬럼의 값 자체를 기반으로 생성됩니다. 반면, 함수 기반 인덱스는 컬럼의 값을 특정 함수를 적용한 결과값을 기반으로 생성됩니다. 예를 들어, UPPER(column_name)과 같이 컬럼 값을 대문자로 변환하는 함수를 적용한 인덱스를 만들 수 있습니다.
함수 기반 인덱스, 왜 필요할까요?
다음과 같은 상황에서 함수 기반 인덱스는 매우 유용합니다.
- 함수를 사용하는 쿼리 성능 향상: 쿼리 조건절(
WHERE절)에서 함수를 사용하여 컬럼을 필터링하는 경우, 함수 기반 인덱스를 사용하면 인덱스를 활용하여 검색 속도를 높일 수 있습니다. - 데이터 변환 후 비교: 컬럼 값을 특정 형식으로 변환한 후 비교하는 쿼리에서 유용합니다. 예를 들어, 날짜 컬럼에서 특정 월(MONTH)만 추출하여 비교하는 경우 함수 기반 인덱스를 활용할 수 있습니다.
- 대소문자 구분 없이 검색: 대소문자를 구분하지 않고 데이터를 검색해야 하는 경우, 컬럼 값을 대문자 또는 소문자로 변환하는 함수 기반 인덱스를 생성하여 검색 효율을 높일 수 있습니다.
함수 기반 인덱스 사용 예시 및 실행 결과
1. 대소문자 구분 없는 검색
EMPLOYEES 테이블의 LAST_NAME 컬럼에서 대소문자를 구분하지 않고 ‘smith’를 검색하는 쿼리를 생각해 보겠습니다.
일반 인덱스 사용 시
CREATE INDEX emp_last_name_idx ON EMPLOYEES (LAST_NAME);
SELECT * FROM EMPLOYEES
WHERE UPPER(LAST_NAME) = 'SMITH';
이 경우, 일반적인 인덱스는 UPPER(LAST_NAME)에 의해 변환된 값을 저장하지 않으므로, 인덱스 스캔이 아닌 테이블 전체 스캔이 발생할 수 있습니다.
함수 기반 인덱스 사용 시
CREATE INDEX emp_last_name_upper_idx ON EMPLOYEES (UPPER(LAST_NAME));
SELECT * FROM EMPLOYEES
WHERE UPPER(LAST_NAME) = 'SMITH';
함수 기반 인덱스는 UPPER(LAST_NAME)의 결과값을 저장하므로, 쿼리 옵티마이저가 인덱스를 사용하여 훨씬 빠르게 검색할 수 있습니다.
2. 날짜 컬럼에서 특정 월(MONTH) 검색
ORDERS 테이블의 ORDER_DATE 컬럼에서 2023년 12월 주문을 검색하는 쿼리를 생각해 보겠습니다.
일반 인덱스 사용 시
CREATE INDEX order_date_idx ON ORDERS (ORDER_DATE);
SELECT * FROM ORDERS
WHERE EXTRACT(MONTH FROM ORDER_DATE) = 12 AND EXTRACT(YEAR FROM ORDER_DATE) = 2023;
이 경우도 일반 인덱스는 EXTRACT 함수를 적용한 값을 저장하지 않으므로, 인덱스 활용이 어려울 수 있습니다.
함수 기반 인덱스 사용 시
CREATE INDEX order_month_idx ON ORDERS (EXTRACT(MONTH FROM ORDER_DATE), EXTRACT(YEAR FROM ORDER_DATE));
SELECT * FROM ORDERS
WHERE EXTRACT(MONTH FROM ORDER_DATE) = 12 AND EXTRACT(YEAR FROM ORDER_DATE) = 2023;
함수 기반 인덱스는 EXTRACT(MONTH FROM ORDER_DATE)와 EXTRACT(YEAR FROM ORDER_DATE)의 결과값을 저장하므로, 쿼리 성능이 크게 향상됩니다.
3. 수치 연산을 포함한 검색
물품 가격에 세금을 포함한 가격을 기반으로 검색하는 경우를 예로 들어보겠습니다.
CREATE INDEX item_price_tax_idx ON ITEMS (PRICE * 1.1);
SELECT * FROM ITEMS
WHERE PRICE * 1.1 > 100;
이러한 함수 기반 인덱스를 사용하면, 오라클은 인덱스를 사용하여 PRICE * 1.1 > 100 조건을 만족하는 레코드를 빠르게 찾을 수 있습니다.
함수 기반 인덱스 사용 시 주의사항
- 성능 저하 가능성: 함수가 복잡하거나 수행 시간이 오래 걸리는 경우, 인덱스 생성 및 유지 관리에 오버헤드가 발생하여 오히려 성능이 저하될 수 있습니다.
- 인덱스 크기 증가: 함수 결과값을 저장하므로 일반 인덱스보다 크기가 커질 수 있습니다.
- DML 작업 성능 저하: 테이블 데이터가 변경될 때마다 함수 연산을 수행하여 인덱스를 업데이트해야 하므로,
INSERT,UPDATE,DELETE작업 성능이 저하될 수 있습니다. - 결정적 함수 사용: 함수 기반 인덱스를 생성할 때 사용되는 함수는 항상 동일한 입력에 대해 동일한 결과를 반환하는 결정적(deterministic) 함수여야 합니다.
결론
함수 기반 인덱스는 특정 상황에서 쿼리 성능을 극적으로 향상시킬 수 있습니다. 하지만 무분별하게 사용하면 오히려 성능 저하를 초래할 수 있으므로, 사용 목적과 주의사항을 충분히 고려하여 신중하게 적용해야 합니다.