TunA :: TunA

Oracle/Tuning2020. 3. 21. 09:54

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

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

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

        
              -- 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")

            

        
      

저작자표시 비영리 변경금지

'Oracle > Tuning' 카테고리의 다른 글

여부 속성으로 인한 성능 저하 사례 #1 (0)	2020.07.08
뷰 병합이 동작하지 않는 사례 (0)	2020.04.07
월 기준 이력 조회 (0)	2020.03.16
INDEX 힌트의 세 가지 방식 (0)	2020.02.23
NOT EXISTS 서브 쿼리가 UNNEST되지 않는 사례 (0)	2020.02.21

Posted by 정희락_

«이전 1 ··· 3 4 5 6 7 8 9 ··· 256 다음»

일	월	화	수	목	금	토
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

TunA

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

'Oracle > Tuning' 카테고리의 다른 글

카테고리

최근에 올라온 글

글 보관함

달력

링크

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역