조인에 따른 사용자 함수의 동작

조인에 따른 사용자 함수의 동작을 살펴보자.

테스트를 위해 아래와 같이 테이블과 함수를 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 10');
CREATE TABLE t2 AS SELECT DECODE (c1, 1, 1, c1 + 10) AS c1 FROM t1;

CREATE OR REPLACE FUNCTION f1 (i_c1 IN NUMBER, i_second IN NUMBER)
    RETURN NUMBER
IS
BEGIN
    DBMS_LOCK.SLEEP (i_second);
    RETURN 1;
END;
/

아래 쿼리는 t2 테이블을 NL 조인하여 99개의 블록 I/O가 발생했다. f1 함수는 조인이 성공한 1회만 수행되어 쿼리 수행에 0.1초가 소요되었다.

-- 2
SELECT /*+ LEADING(A) USE_NL(B) */
       *
  FROM t1 a, t2 b
 WHERE b.c1 = a.c1
   AND f1 (b.c1, 0.1) = 1;

----------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |      1 |00:00:00.11 |      99 |
|   1 |  NESTED LOOPS      |      |      1 |      1 |00:00:00.11 |      99 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |     10 |00:00:00.01 |       9 |
|*  3 |   TABLE ACCESS FULL| T2   |     10 |      1 |00:00:00.11 |      90 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("B"."C1"="A"."C1" AND "F1"("B"."C1",.1)=1))

아래 쿼리는 해시 조인으로 수행되어 블록 I/O가 21개로 감소했지만, f1 함수가 10회 수행되어 쿼리 수행에 1.1초가 소요되었다. 조인 대상을 각각 액세스하는 해시 조인의 특성으로 인한 현상이다.

-- 3
SELECT /*+ LEADING(A) USE_HASH(B) */
       *
  FROM t1 a, t2 b
 WHERE b.c1 = a.c1
   AND f1 (b.c1, 0.1) = 1;

----------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |      1 |00:00:01.10 |      21 |
|*  1 |  HASH JOIN         |      |      1 |      1 |00:00:01.10 |      21 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |     10 |00:00:00.01 |       9 |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |      1 |00:00:01.09 |       9 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."C1"="A"."C1")
   3 - filter("F1"("B"."C1",.1)=1)

아래 쿼리처럼 조인 후 함수를 수행하면 함수가 1회만 수행되어 수행 시간이 0.1초로 감소하는 것을 확인할 수 있다. 조인 부하와 함수 부하의 비율에 따라 적용 여부를 판단해야 한다.

-- 4
SELECT *
  FROM (SELECT /*+ NO_MERGE LEADING(A) USE_HASH(B) */
               a.c1 AS ac1, b.c1 AS bc1
          FROM t1 a, t2 b
         WHERE b.c1 = a.c1)
 WHERE f1 (bc1, 0.1) = 1;

-----------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |      1 |00:00:00.11 |      21 |
|*  1 |  VIEW               |      |      1 |      1 |00:00:00.11 |      21 |
|*  2 |   HASH JOIN         |      |      1 |      1 |00:00:00.01 |      21 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |     10 |00:00:00.01 |       9 |
|   4 |    TABLE ACCESS FULL| T2   |      1 |     10 |00:00:00.01 |       9 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("F1"("BC1",.1)=1)
   2 - access("B"."C1"="A"."C1")

쿼리가 복잡한 경우 아래처럼 함수가 사용된 조건을 조인의 필터 조건으로 변경할 수도 있다.

-- 5
SELECT /*+ LEADING(A) USE_HASH(B) */
       *
  FROM t1 a, t2 b
 WHERE b.c1 = a.c1
   AND f1 (b.c1, 0.1) + a.c1 = 1 + a.c1;

----------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |      1 |00:00:00.11 |      21 |
|*  1 |  HASH JOIN         |      |      1 |      1 |00:00:00.11 |      21 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |     10 |00:00:00.01 |       9 |
|   3 |   TABLE ACCESS FULL| T2   |      1 |     10 |00:00:00.01 |       9 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."C1"="A"."C1")
       filter("F1"("B"."C1",.1)+"A"."C1"=1+"A"."C1")

함수 인수의 NDV가 적다면 캐싱 효과를 위해 서브 쿼리를 사용할 수도 있다. 서브 쿼리는 조인 후 필터 방식으로 수행된다.

-- 6
SELECT /*+ LEADING(A) USE_HASH(B) */
       *
  FROM t1 a, t2 b
 WHERE b.c1 = a.c1
   AND (SELECT f1 (b.c1, 0.1) FROM DUAL) = 1;

-----------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |      1 |00:00:00.11 |      21 |
|*  1 |  FILTER             |      |      1 |      1 |00:00:00.11 |      21 |
|*  2 |   HASH JOIN         |      |      1 |      1 |00:00:00.01 |      21 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |     10 |00:00:00.01 |       9 |
|   4 |    TABLE ACCESS FULL| T2   |      1 |     10 |00:00:00.01 |       9 |
|   5 |   FAST DUAL         |      |      1 |      1 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(=1)
   2 - access("B"."C1"="A"."C1")