권순용의 DB 이야기

함수 기반 인덱스의 개념과 생성 이해

오라클의 함수 기반 인덱스는 다양한 상황에 적용 가능하다. 여러 인덱스들처럼 함수 기반 인덱스도 장단을 가지고 있으므로 적용에 앞서 고민할 필요가 있다.

수많은 데이터가 저장된 급여 테이블에 Where 조건의 MOD(GRADE,10)*1.2>10을 만족하는 데이터가 적은 경우 인덱스 스캔을 이용해 데이터를 추출해야 성능을 보장받을 수 있다.

<리스트 1> 컬럼 변형이 일어나는 SQL 문 예SQL> SELECT EMPNO, CEIL(SAL/12)*(GRADE-5) BONUS
FROM EMP
WHERE MOD(GRADE,10)*1.2 > 10

<리스트 1> SQL의 문제는 인덱스 컬럼에 변형이 일어나면 인덱스 스캔을 수행할 수 없다는 점이다. 인덱스에는 실제 인덱스 키 값이 저장돼 있을 뿐 변형된 값은 저장돼 있지 않아서다. <리스트 1>의 GRADE 컬럼에 인덱스가 존재하더라도 무용지물이 되고 만다. 이러한 문제를 해결하기 위해 오라클 8i부터 함수 기반 인덱스를 지원한다. 함수 기반 인덱스를 생성하기 위해서는 다음과 같은 몇 가지 전제조건을 만족해야 한다.

- 해당 테이블에 대한 통계 정보 필요
- Compatible=8.1.0 이상
- QUERY_REWRITE_ENABLED=TRUE
- QUERY_REWRITE_INTEGRITY=TRUSTED
- 해당 데이터베이스(DB) 유저에게 QUERY REWRITE 시스템 권한 필요

● Compatible 매개변수
Compatible 매개변수는 오라클의 버전을 관리한다. 아무리 오라클 8i를 설치했더라도 Compatible 매개변수가 8.0.5면 오라클 8i의 새 기능들을 이용할 수 없다. 오라클 10g, 11g에서도 마찬가지다. 함수 기반 인덱스를 생성하기 위해서는 Compatible 매개변수가 8.1.0 이상으로 설정돼 있어야 한다.

● 매개변수 설정
QUERY_REWRITE_ENABLED 매개변수는 TRUE로, QUERY_REWRITE_ INTEGRITY 매개변수는 TRUSTED로 설정돼 있어야 한다. 이 매개변수는 Init Parameter 파일 또는 SPFILE을 수정하고 데이터베이스(DB)를 다시 시작하거나 다음처럼 Alter Session 명령어로 Session 레벨을 열어 변경할 수도 있다. 이 SQL을 수행하면 QUERY_REWRITE_INTEGRITY 매개변수가 Trusted로 변경된다.

SQL> ALTER SESSI/ON SET QUERY_REWRITE_ENABLED=TRUE;

● 시스템 권한
함수 기반 인덱스를 생성하고자 하는 DB 유저에게 다음의 SQL 쿼리로 QUERY REWRITE 권한을 부여해야 한다.

SQL> GRANT QUERY REWRITE TO USERNAME;

● 통계 정보 필요
함수 기반 인덱스를 생성하고자 하는 테이블에 통계 정보가 생성돼 있어야 한다.

이 모든 조건을 만족하는 경우 <리스트 2> SQL을 실행하면 함수 기반 인덱스가 생성된다. 그러면 계산값을 인덱스 키로 해서 B*TREE 인덱스가 생성될 것이다.

<리스트 2> 함수 기반 인덱스 생성SQL> CREATE INDEX BONUS_IDX ON EMP(MOD(GRADE,10)*1.2)

함수 기반 인덱스의 장단점과 이용

함수 기반 인덱스의 사용은 점점 늘어나고 있다. 이러한 함수 기반 인덱스를 효과적으로 이용하려면 장단점을 명확히 이해해야 한다.

● 함수 기반 인덱스의 장점
함수 기반 인덱스의 가장 큰 장점은 인덱스를 이용할 수 없는 SQL도 인덱스를 이용할 수 있다는 것이다.

- 계산 값에 대한 인덱스 스캔 가능
- 인덱스 컬럼이 Where 절에서 가공 또는 변경돼도 인덱스 이용 가능

함수 기반 인덱스를 이용하면 계산 값을 도출하는 SQL도 인덱스를 이용할 수 있다. 또한 인덱스를 이용할 수 없는 SQL도 Where 절의 컬럼이 변경돼도 인덱스 이용이 가능하다. 요즘은 업무가 복잡해지면서 많은 계산 값을 요구하고 있다. 이처럼 SQL의 계산 값이 중요한 업무라면 함수 기반 인덱스를 통해 성능을 향상시킬 수 있다.

● 함수 기반 인덱스의 단점
함수 기반 인덱스의 단점은 DML 성능 저하다. DML이 매우 많은 테이블의 경우 다른 인덱스를 고려하는 게 좋다.

- DML시 부하 발생
- 인덱스의 유연성 저하

인덱스의 유연성이란 하나의 인덱스로 여러 엑세스 유형을 수용할 수 있는가를 의미한다. 유연성이 뛰어나면 적은 인덱스로 많은 업무를 처리할 수 있다. 그러나 함수 기반 인덱스는 단지 Where 조건에 해당하는 연산자를 그대로 사용할 경우에만 가능하므로 다른 일반 엑세스 유형을 수용하기에 적합하지 않아 유연성이 많이 떨어진다.

● 함수 기반 인덱스의 사용 예
많은 경우 중첩 루프 조인은 조인 순서와 인덱스 이용 두 가지 측면에서 최적화할 수 있다. 최적화가 안 되는 경우도 있는데, 이 경우 함수 기반 인덱스로 SQL을 최적화할 수 있는 경우가 더러 있다. 어떻게 해야 함수 기반 인덱스로 중첩 루프 조인을 최적화할 수 있을까 <리스트 3> 예제를 함께 살펴보자.

<리스트 3> 함수 기반 인덱스로 중첩 루프 조인을 최적화할 수 있는 예SQL> SELECT A.ACOL2, B.BCOL1, B.BCOL2
FROM TAB1 A,
TAB2 B
WHERE A.KEY = B.KEY
AND A.COL1 = ‘123’
AND B.COL3 = ‘AAA’;

<리스트 3>의 TAB1 테이블과 TAB2 테이블에는 각각의 조건을 만족하는 데이터가 매우 많다. 그러나 두 조건의 합에 의해 감소되는 양이 워낙 많기 때문에 최종 결과는 매우 적다고 가정해 보자. 이 경우 두 테이블 모두 각각의 처리 범위가 많기 때문에 어느 테이블을 먼저 엑세스하더라도 성능 저하가 발생한다.

TAB1, TAB2 테이블 중 어느 쪽을 먼저 엑세스하더라도 처리 범위가 많아 조인 횟수가 증가해 결국 성능을 보장받을 수 없는 것이다. 이런 경우는 함수 기반 인덱스로 중첩 루프 조인의 성능을 최적화할 수 있다.

<리스트 4> 함수 기반 인덱스로 최적화 가능한 두 번째 예SQL> CREATE OR REPLACE FUNCTION GET_COL3
(v_key1 VARCHAR2)
RETURN VARCHAR2 IS RES_COL3 VRCHAR2(8);
BEGIN
SELECT COL3 INTO RES_COL3
FROM TAB2
WHERE KEY = v_key1;
RETURN RES_COL3;
END GET_COL3;SQL> CREATE INDEX COL1_COL3_IDX ON TAB1(COL1,GET_COL3(KEY));

<리스트 4>와 같이 함수를 생성하고 해당 함수를 기준으로 함수 기반 인덱스를 생성했다고 가정하자. COL1_COL3_IDX는 TAB1 테이블의 KEY1 컬럼의 값과 TAB2 테이블의 KEY 컬럼의 값이 동일한 데이터에 대해 COL3 컬럼의 값을 이용해 COL1+COL3로 인덱스를 구성한다. 이로 인해 TAB1 테이블의 KEY 값에 대해 TAB2 테이블이 2건 이상의 데이터가 추출되면 안 된다. 따라서 TAB1 테이블과 TAB2 테이블은 M:1의 관계가 돼야 한다. 만약 반대의 관계라면 인덱스를 반대로 생성해야 한다.

결국 이렇게 인덱스를 생성하면 TAB1 테이블의 COL1 컬럼과 TAB2 테이블의 COL3 컬럼을 각 테이블의 KEY1 컬럼으로 연결해 하나의 인덱스로 생성할 수 있다. 인덱스를 생성한 후 앞의 SQL은 <리스트 5>와 같이 변경 가능하다.

<리스트 5> <리스트 4> 최적화SQL> SELECT A.ACOL2, B.BCOL1, B.BCOL2
FROM TAB1 A,
TAB2 B
WHERE A.KEY = B.KEY
AND A.COL1 = ‘123’
AND GET_COL3(A.KEY) = ‘AAA’;

이처럼 SQL을 수행하면 TAB1 테이블은 WHERE 조건에서 조인 조건을 제외하고 2개의 조건으로 처리 범위를 감소시킬 수 있다. 앞서 가정했듯 최종 결과는 적은 양의 데이터가 추출되며 전체 SQL에서 처리 범위를 감소시키는 조건은 단지 2개이므로 전체 데이터는 두 조건에 의해 충분히 처리 범위가 감소하게 되는 것이다. 따라서 TAB1 테이블이 먼저 엑세스되는 테이블로 수행되는 순간 COL1_COL3_IDX 인덱스를 이용해 적은 처리 범위를 엑세스하게 된다. 또 TAB2 테이블에는 KEY 컬럼에만 인덱스가 존재하면 성능을 보장받을 수 있다.

앞서 온라인 조인 SQL의 경우 적은 양의 데이터를 추출하는 경우가 대부분이다. <리스트 4> SQL은 처리 범위를 줄여주는 조건이 서로 다른 테이블에 분리돼 있고, 해당 조건으로 합쳐졌을 때 처리 범위를 최소화할 수 있는 경우다. 이런 경우 2개의 조건을 하나의 테이블에 존재하는 것처럼 함수 기반 인덱스를 구성하면 최종으로 추출하는 결과 데이터 만큼만 엑세스할 수 있도록 처리 범위를 감소시킬 수 있다. 함수 기반 인덱스는 DML이 많은 테이블에 적용 시 인덱스 유지 보수에 의한 비용이 증가할 수 있다. 이는 인덱스에 함수를 사용했기 때문이다. 하지만 최적화가 어려운 조인을 함수 기반 인덱스로 해결할 수 있다는 것은 SQL 최적화의 또 다른 길을 보여준다. 이러한 방식의 SQL 최적화는 해쉬 조인에서도 사용이 가능하다.

출처 : 마이크로소프트웨어 12월호

제공 : 데이터 전문가 지식포털 DBguide.net