SQL Query Tuning [3] 적용(Function Index)

Query Tuning

앞서 설명한 실행 계획을 통해 얻을 수 있는 데이터는 절대적인 것이 아니다. cost 값이 높음에도 불구하고 응답 속도가 더 빠른 Query 구문이 있는가하면, 그 반대 역시 존재하기 때문이다. 따라서 우리는 다음과 같이 시도해볼 수 있다.

1. Index 설정( + Hint 설정)
2. Function Index 설정( + Hint 설정)
3. Hash Join 설정 ( + Hint 설정)

 

---

 

Function Index 설정( + Hint 설정)

1. Function 설명

인덱스 조회 및 상세 조회는 이전 게시물( SQL Query Tuning [2] 적용(Index) )을 참고하도록 하자. 바로 함수 인덱스에 대한 간단한 설명과 생성, 적용 과정을 살펴보도록 하겠다.

 

SQL Query 구문을 조금 만져보았다면, 그리고 현재 진행중인 프로젝트가 있다면 한 번 쯤 들어보거나 실제로 겪어보았을 것이 바로 Function(함수)이다. java programing에서 생각해보자면 공통 모듈이라고 볼 수 있다. 우리는 여러 질의문을 실행하며 다양한 조건의, 다양한 형태로 데이터를 조회하게 되는데. 이러한 과정에서 공통적으로 쓰이는 영역을 함수로 미리 선언하여 Query 구문을 조금 더 쉽게 사용할 수 있게 한다.

 

---

 

2. Function Index 사용 이유

필자의 경우 함수 인덱스를 사용할 수 밖에 없는 조건이었다. SELECT 절에 여러 컬럼의 데이터를 조합하여 사용자 정의된 별도 컬럼이 생성되고 있었다. 그리고 해당 컬럼값이 WHERE 절의 조건문으로 잡혀있었기 때문이다. 일반적인 컬럼에 대하여 조건문이 걸렸을 경우, 평범한 인덱스 선언으로 충분히 해결할 수 있었을 것이다. 그러나 사전에 정의되지 못하는 컬럼에 대해서는 인덱스만으로 해결할 수 없는 문제였다. 그렇기에 사용자 정의된 컬럼을 작성하는 식에 대하여 함수화 후 해당 함수를 인덱스로 추가하여 문제를 해결하였다.

 

---

 

3. Function Index 생성

당연한 이야기지만 함수 인덱스를 생성하기 위해서는 함수가 존재해야한다. 이후 인덱스를 생성할 수 있다. 함수가 미리 선언되었다고 가정하에 진행하도록 하겠다. 다음은 선언해놓은 함수를 인덱스로 잡는 과정이다.

CREATE INDEX test_function_index
ON table_name(function_name(param1, param2, param3));

일반적인 인덱스 생성과 동일하다. 인덱스를 잡을 컬럼명 대신 함수명과 해당 함수를 호출함에 있어 필요한 매개변수를 작성해주면 끝난다. 해당 테이블에 대한 인덱스를 조회해보면 다음과 같이 정상적으로 나오는 것을 확인할 수 있다.

 

---

 

4. Function Index 적용

필자가 테스트한 결과로는, 아쉽게도 인덱스를 단순히 생성한다고 해서 바로 적용되지는 않았다. 다음을 보도록 하자.

실행 계획을 살펴보면 그 어디에도 인덱스가 실행된 내역은 찾아볼 수 없다. 옵티마이저가 판단하기에 함수 인덱스를 실행하지 않아도 된다고 생각했나보다. 하지만 이는 오판이라는 것을 필자는 알고 있다. 이미 해당 Query 구문에 대해서는 함수 인덱스를 강제 적용했을 때의 퍼포먼스가 훨씬 훌륭하다는 것을 인지하고 있기 때문이다. 함수 인덱스를 강제 적용하는 과정을 아래에서 살펴보도록 하자.

 

---

 

5. Function Index Hint 적용

SELECT DISTINCT inner.result
FROM
    (
        SELECT /*+ index(a test_function_index) */
            a.text_a,
            b.text_b,
            c.text_c
            function_name(param1, param2, param3) AS result
        FROM table_name a
        LEFT JOIN temp_table_b b on a.text_a = b.text_a
        LEFT JOIN temp_table_c c on a.text_a = c.text_a
    ) inner
WHERE 1=1
AND inner.result LIKE '%' || '테스트' || '%';

일반적인 인덱스 힌트 적용과 동일하다. 함수 인덱스명을 명시해주면 된다. 함수 인덱스 힌트가 적용된 결과물을 살펴보도록 하자.

 

아름답게 인덱스가 설정된 것을 볼 수 있다. 질의문을 실행하는데 필요한 예상 자원 소모량(cost) 값은 대폭 증가하였음을 알 수 있다. 여기까지만 보았을 때 옵티마이저가 왜 함수 인덱스를 적용하지 않았는지 이해될 것이다. 그러나 지금의 우리에게 필요한 것은 자원의 소모를 최적화하기 보다는 Query 조회 속도를 올리는 것이다.

 

아래의 실제 Query 실행 결과를 확인해보자.

보시다시피 소요 시간이 10배 가까이 차이나는 것을 알 수 있다. 무조건적으로 실행 계획 내역만을 보고 판단하지는 말자. 필자 역시 실행 내역을 참고는 하나, 어디까지나 참고만 할 뿐... 실제 실행 결과를 비교해봐야하는 것을 알 수 있다.

 

---

 

[ Function 생성 안될 때 ]

간혹 Function 생성이 안되는 경우가 존재한다. 그럴 때 도움이 되는 Query 구문을 준비했으니, 참고해서 알아서 해보자.

-- 모든 계정 권한 확인
SELECT *
FROM dba_sys_privs
WHERE 1=1
AND privilege LIKE '%' || 'PROCEDURE' ||'%'
ORDER BY grantee, privilege;

-- 함수 생성 권한 확인
SELECT grantee, privilege
FROM dba_sys_privs
WHERE 1=1
AND grantee = 'user_name';

-- 함수 생성 권한 부여 및 제거(자신의 스키마)
GRANT CREATE PROCEDURE TO user_name;
REVOKE CREATE PROCEDURE FROM user_name;

-- 함수 생성 권한 부여 및 제거(모든 스키마)
GRANT CREATE ANY PROCEDURE TO user_name;
GRANT DROP ANY PROCEDURE TO user_name;

 

---

 

[ 연관 게시글 ]

SQL Query Tuning [1] 개요 및 설명

SQL Query Tuning [2] 적용(Index)

SQL Query Tuning [3] 적용(Function Index)

SQL Query Tuning [4] 적용(Hash Join)