사용자 정의 함수의 동작 방식

사용자 정의 함수를 WHERE 절에 사용했을 때의 동작 방식을 살펴보자.

테스트에 사용한 테이블과 함수는 지난 글과 동일하다.

아래 쿼리는 열을 입력하지 않은 함수와 상수를 비교했다. 사용자 정의 함수가 1번 FILTER 오페레이션에서 1회만 수행되는 것을 확인할 수 있다. 자주 사용하는 방식은 아니다.

-- 1
SELECT * FROM t1 WHERE fnc_sleep (1) = 1;

---------------------------------------------------------
| Id  | Operation          | Name | A-Rows |   A-Time   |
---------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     10 |00:00:01.01 |
|*  1 |  FILTER            |      |     10 |00:00:01.01 |
|   2 |   TABLE ACCESS FULL| T1   |     10 |00:00:00.01 |
---------------------------------------------------------

아래 쿼리는 열을 입력한 함수와 상수를 비교했다. 함수가 행수만큼 수행된다. 주로 양방향 복호화 함수를 사용하는 패턴이다.

-- 2-1
SELECT * FROM t1  WHERE fnc_sleep (c1) = 1;

--------------------------------------------------------
| Id  | Operation         | Name | A-Rows |   A-Time   |
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     10 |00:00:10.00 |
|*  1 |  TABLE ACCESS FULL| T1   |     10 |00:00:10.00 |
--------------------------------------------------------

성능 개선을 위해 FBI를 생성할 수 있다.^[각주:1] FBI에 함수의 결과가 저장되기 때문에 함수가 수행되지 않는다.^[각주:2]

-- 2-2
CREATE INDEX t1_x1 ON t1 (fnc_sleep (c1));

SELECT * FROM t1  WHERE fnc_sleep (c1) = 1;

-------------------------------------------------------------------
| Id  | Operation                   | Name  | A-Rows |   A-Time   |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     10 |00:00:00.01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     10 |00:00:00.01 |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |     10 |00:00:00.01 |
-------------------------------------------------------------------

아래 쿼리는 열을 입력하지 않은 함수와 열을 비교했다. 사용자 함수가 행수만큼 수행된다. 주로 단방향 암호화 함수를 사용하는 패턴이다.

-- 3-1
SELECT * FROM t1 WHERE c1 = fnc_sleep (1);

--------------------------------------------------------
| Id  | Operation         | Name | A-Rows |   A-Time   |
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     10 |00:00:10.00 |
|*  1 |  TABLE ACCESS FULL| T1   |     10 |00:00:10.00 |
--------------------------------------------------------

함수에 스칼라 서브 쿼리를 사용하면 쿼리의 성능을 개선할 수 있다. 함수가 1회만 수행된 것을 확인할 수 있다.^[각주:3]

-- 3-2
SELECT * FROM t1 WHERE c1 = (SELECT fnc_sleep (1) FROM DUAL);

--------------------------------------------------------
| Id  | Operation         | Name | A-Rows |   A-Time   |
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     10 |00:00:01.01 |
|*  1 |  TABLE ACCESS FULL| T1   |     10 |00:00:01.01 |
|   2 |   FAST DUAL       |      |     10 |00:00:00.01 |
--------------------------------------------------------

함수를 인덱스 액세스 조건으로 사용해도 쿼리의 성능을 개선할 수 있다.

-- 3-3
CREATE INDEX t1_x2 ON t1 (c1);

SELECT * FROM t1 WHERE c1 = fnc_sleep (1);

-------------------------------------------------------------------
| Id  | Operation                   | Name  | A-Rows |   A-Time   |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     10 |00:00:01.00 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     10 |00:00:01.00 |
|*  2 |   INDEX RANGE SCAN          | T1_X2 |     10 |00:00:01.00 |
-------------------------------------------------------------------

1. 사용자 정의 함수에 DETERMINISTIC 키워드를 기술해야 한다. [본문으로]
2. 복호화 함수인 경우 원본 데이터가 세그먼트에 저장되므로 보안 이슈가 발생할 수 있다. [본문으로]
3. 단일 행 비상관 서브 쿼리는 EXECUTE 단계에서 별도로 수행된다. [본문으로]