사용자 정의 함수

     1.  소량의 데이터 조회시에만 사용

     2.  대용량 데이터를 조회할 때는 부분범위처리가 가능한 상황에서 제한적 사용

     3.  조인 또는 스칼라 서브 쿼리 형태로 변환  (메모리에 캐싱됨)

     4.  함수를 쓰되 호출 횟수를 최소화함

            .  페이지 처리 또는 부분범위 처리 활용

            .  Decode 함수 또는 Case 문으로 변환

            .  뷰 머지 방지를 통한 함수 호출 최소화

            .  스칼라 서브 쿼리 캐싱 효과를 이용한 함수 호출 최소화

            .  Deterministic 함수의 캐싱 효과 활용

            .  복잡한 함수 로직을 풀어 SQL로 구현

 

 

      (1)  페이지 처리 또는 부분 범위 처리 활용

 select rownum no, a.*
   from (
       select memb_nm(매수회원번호) 매도 회원명 
             ,memb_nm(매수회원번호)  매수회원명
             ,code_nm('446' , 매도 투자자 구분코드) 매도투자자구분명
             ,code_nm('446' , 매수 투자자 구분코드) 매수투자자구분명
             ,code_nm('418' , 체결 유형코드) 체결 유형명
             ,매도계좌번호
             ,매수계좌번호
      from 체결
      where 종목코드 = : 종목코드
       and   체결일자 = : 체결일자 
       and   체결시간 between sysdate-10/21/60 and sysdate
       order by 체결시각 desc
   ) a
   where rownum <= 30 
)
where no between 21 and 30

 

--> 체결 테이블에서 종목 코드와 체결 일자를 받아서

memb_mn으로 회원 번호를 받고, code_mn으로 투자자를 받아 뿌림 , 체결 시각으로 30건을 읽어서 그 중에 21에서 30건만 화면으로

보여줌 

 

 

 --> 전체 레코드 건수만큼 함수 호출을 일으킴

         --> 그 결과 집합을 Sort Area 또는 Temp 테이블 스페이스에 저장

                --> 최종 결과 집합 10건만을 사용자에게 전송

 

>>>  order by 와 rownum에 의한 필터 처리 후 사용자에게 전송하는 최종 결과 집합에 대해서만 함수 호출이 일어남

 select memb_nm(매수회원번호) 매도 회원명        

          , memb_nm(매수회원번호)  매수회원명       

          , code_nm('446' , 매도투자자구분코드) 매도투자자구분명       

          , code_nm('446' , 매수투자자구분코드) 매수투자자구분명

          , code_mn('418', 체결유형코드) 체결 유형명

          , 체결 시각

          , 체결수량

          , 체결가

          , 체결수량*체결가 체결 금액

 from (    select rownum no, a.*  

                from (  

                             select 매수회원번호              

                                     , 매수회원번호             

                                     , 매도투자자구분코드            

                                     , 매수투자자구분코드             

                                     , 매도계좌번호            

                                     , 매수계좌번호      

                             from 체결       

                             where 종목코드 = : 종목코드       

                             and   체결일자 = : 체결일자        

                             and   체결시간 between sysdate-10/21/60

                             and sysdate       

                             order by 체결시각 desc    ) a  

                where rownum <= 30  )

 where no between 21 and 30

--> rownum으로 먼저 30건만 읽어서 최종 쿼리에서 함수 호출을 함
 

 

 

 (2) Decode 함수 또는 Case 문으로 변환

        인라인 뷰에서 order by 절에 사용, 전체 결과 집합을 모두 출력,  insert .. select 문에서 사용

       ---> 함수를 로직으로 풀어서 decode, case 문으로 전환하거나 조인문으로 구현

 

 >> 체결 테이블 생성

 

CREATE TABLE 체결(체결일자, 체결번호, 시장코드, 증권그룹코드, 체결수량, 체결금액)

NOLOGGING

AS

SELECT '20090315'     

              , ROWNUM      

              , DECODE(SIGN(ROWNUM-100000), 1, 'ST', 'KQ')        -- 유가증권, 코스닥      

              , DECODE(MOD(ROWNUM, 8), 0, 'SS', 1, 'EF', 2, 'EW'  -- 주식, ETF, ELW                            

                                                                                 , 3, 'DR', 4, 'SW', 5, 'RT'  -- DR, 신주인수권, 리츠                            

                                                                                 , 6, 'BC', 7, 'MF')          -- 수익증권, 투자회사     

              , ROUND(DBMS_RANDOM.VALUE(10, 1000), -1)      

              , ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2) 

FROM   DUAL CONNECT BY LEVEL <= 500000

UNION ALL

SELECT '20090315'     

              , ROWNUM + 300000      

              , (CASE WHEN MOD(ROWNUM, 4) < 2 THEN 'SD' ELSE 'GD' END)     

              , (CASE WHEN MOD(ROWNUM, 4) IN (0, 2) THEN 'FU' ELSE 'OP' END)     

              , ROUND(DBMS_RANDOM.VALUE(10, 1000), -1)      

              , ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2) 

 FROM   DUAL CONNECT BY LEVEL <= 500000;

-->  주식 가상 테이블

 

 >>  업무에 따라서 주식 상품을 다르게 분류하고 집계 하기도함

       상품 분류라는 분류 기준 함수를 정의 

 

CREATE OR REPLACE FUNCTION SF_상품분류(시장코드 VARCHAR2, 증권그룹코드 VARCHAR2)  RETURN VARCHAR2

IS  

       L_분류 VARCHAR2(20);

BEGIN  

       IF 시장코드 IN ('ST', 'KQ') THEN  -- 유가증권, 코스닥    

             IF 증권그룹코드 = 'SS'     THEN    

                   L_분류 := '주식 현물';    

             ELSIF 증권그룹코드 IN ('EF', 'EW')   THEN  -- ETF, ELW      

                   L_분류 := '파생';    

             ELSE       

                   L_분류 := '주식외 현물';    

             END IF;  

       ELSE      

             L_분류 := '파생';  

       END IF; 

 

    --SELECT 순서 || '. ' || L_분류 INTO L_분류   

    --FROM   분류순서  

    --WHERE  분류명 = L_분류;     

   RETURN L_분류;

 END;

 -->  시장 코드에 따라서 분류를 return 하는 함수

 

SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류     

              , COUNT(*) 체결건수     

              , SUM(체결수량) 체결수량     

              , SUM(체결금액) 체결금액

FROM   체결

WHERE  체결일자 = '20090315'

GROUP BY SF_상품분류(시장코드, 증권그룹코드) 

ORDER BY 1 ;

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY     |      |      1 |    235K|      3 |00:00:02.77 |     454 | 18432 | 18432 |     1/0/0|
|*  2 |   TABLE ACCESS FULL| 체결 |      1 |    235K|    200K|00:00:00.20 |     454 |       |       |          |
----------------------------------------------------------------------------------------------------------------

 

 

 

 
 

SELECT CASE
       WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드  = 'SS' THEN '주식 현물'
       WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 NOT IN ('SS', 'EF', 'EW') THEN '주식외 현물'
       WHEN 시장코드 IN ('SD', 'GD') OR 증권그룹코드 IN ('EF', 'EW') THEN '파생'
       END 상품분류
     , COUNT(*) 체결건수
     , SUM(체결수량) 체결수량
     , SUM(체결금액) 체결금액
FROM   체결
WHERE  체결일자 = '20090315'
GROUP BY

ORDER BY 1 ;

 

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY     |      |      1 |    235K|      3 |00:00:00.57 |     454 | 18432 | 18432 |     1/0/0|
|*  2 |   TABLE ACCESS FULL| 체결 |      1 |    235K|    200K|00:00:00.01 |     454 |       |       |          |
----------------------------------------------------------------------------------------------------------------

 

>>> DECODE와 CASE를 이용하면 훨씬 빠르게 수행됨

SELECT    

              DECODE( 시장코드||증권그룹코드            
                         , 'STSS',  '1. 주식 현물'        
                        , 'KQSS', '1. 주식 현물'    
                        , 'SDFU', '3.  파생'            
                        , 'SDOP', '3.  파생'          
                        , 'GDFU', '3.  파생'         
                        , 'GDOP', '3.  파생'        
                        , 'STEF', '3.  파생'        
                        , 'STEW', '3.  파생'           
                        , 'KQEF', '3.  파생'           
                        , 'KQEW', '3.  파생'     
                        , '2.  주식외 현물' ) 상품분류 
               , COUNT(*) 체결건수 
               , SUM(체결수량) 체결수량   
               , SUM(체결금액) 체결금액
   FROM   체결 WHERE  체결일자 = '20090315'
   GROUP BY    DECODE( 시장코드||증권그룹코드            
                         , 'STSS',  '1. 주식 현물'        
                        , 'KQSS', '1. 주식 현물'    
                        , 'SDFU', '3.  파생'            
                        , 'SDOP', '3.  파생'          
                        , 'GDFU', '3.  파생'         
                        , 'GDOP', '3.  파생'        
                        , 'STEF', '3.  파생'        
                        , 'STEW', '3.  파생'           
                        , 'KQEF', '3.  파생'           
                        , 'KQEW', '3.  파생'     
                        , '2.  주식외 현물' )
   ORDER BY 1 ;

 

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY     |      |      1 |    235K|      3 |00:00:00.50 |     454 | 18432 | 18432 |     1/0/0|
|*  2 |   TABLE ACCESS FULL| 체결 |      1 |    235K|    200K|00:00:00.01 |     454 |       |       |          |
----------------------------------------------------------------------------------------------------------------

 


 

 >> Recursive Call을 포함하도록 테스트

 

 

EATE OR REPLACE FUNCTION SF_상품분류(시장코드 VARCHAR2, 증권그룹코드 VARCHAR2) 
RETURN VARCHAR2
IS
  L_분류 VARCHAR2(20);
BEGIN
  IF 시장코드 IN ('ST', 'KQ') THEN  -- 유가증권, 코스닥
    IF 증권그룹코드 = 'SS' THEN 
      L_분류 := '주식 현물';
    ELSIF 증권그룹코드 IN ('EF', 'EW') THEN  -- ETF, ELW
      L_분류 := '파생';
    ELSE 
      L_분류 := '주식외 현물';
    END IF;
  ELSE  
     L_분류 := '파생';
  END IF;

  SELECT 순서 || '. ' || L_분류 INTO L_분류 
  FROM   분류순서
  WHERE  분류명 = L_분류;
  
  RETURN L_분류;
END;


SELECT  SF_상품분류(시장코드, 증권그룹코드) 상품분류     
            , COUNT(*) 체결건수     
            , SUM(체결수량) 체결수량    
            , SUM(체결금액) 체결금액
             FROM   체결
WHERE  체결일자 = '20090315'
GROUP BY SF_상품분류(시장코드, 증권그룹코드)ORDER BY 1 ;

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY     |      |      1 |     22 |      2 |00:00:47.87 |     600K| 18432 | 18432 |     1/0/0|
|*  2 |   TABLE ACCESS FULL| 체결 |      1 |    200K|    200K|00:00:00.40 |     454 |       |       |          |
----------------------------------------------------------------------------------------------------------------
 

 

 

>>> 시장코드와 증권그룹코드별 분류명을 메타정보로써 관리하는 테이블을 만들어 사용

SELECT C.순서 ||','|| B.분류명 상품분류
            , SUM(체결건수)
            , SUM(체결수량)
            , SUM(체결금액)
FROM (SELECT 시장코드, 증권그룹코드
                        , COUNT(*)체결건수
                        , SUM(체결수량)체결수량
                        , SUM(체결금액)체결금액
            FROM 체결
            WHERE 체결일자 = '20090315'
            GROUP BY   시장코드, 증권그룹코드) A, 상품분류 B, 분류순서 C
WHERE A.시장코드 = B.시장코드
AND A.증권그룹코드 = B.증권그룹코드
AND C.분류명 = B.분류명
GROUP BY        C.순서 ||','|| B.분류명
ORDER BY 1;
           


-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
-------------------------------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY        |      |      1 |    235K|      2 |00:00:00.51 |     460 | 18432 | 18432 |     1/0/0|
|*  2 |   HASH JOIN           |      |      1 |    235K|      7 |00:00:00.51 |     460 |   899K|   899K|     1/0/0|
|*  3 |    HASH JOIN          |      |      1 |     20 |     10 |00:00:00.01 |       6 |   972K|   972K|     1/0/0|
|   4 |     TABLE ACCESS FULL | 분류 |      1 |      3 |      3 |00:00:00.01 |       3 |       |       |          |
|   5 |     TABLE ACCESS FULL | 상품 |      1 |     20 |     20 |00:00:00.01 |       3 |       |       |          |
|   6 |    VIEW               |      |      1 |    235K|     12 |00:00:00.51 |     454 |       |       |          |
|   7 |     HASH GROUP BY     |      |      1 |    235K|     12 |00:00:00.51 |     454 |       |       |          |
|*  8 |      TABLE ACCESS FULL| 체결 |      1 |    235K|    200K|00:00:00.01 |     454 |       |       |          |
-------------------------------------------------------------------------------------------------------------------


 
 

  

 

(3) 뷰 머지 방지를 위한 함수 호출 최소화

 

. 함수를 풀어 조인문으로 변경하기 곤란한경우, 함수를 그대로 둔채 함수 호출 횟수를 줄이려는 노력이 필요

 

 SELECT SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '1. 주식 현물', 체결수량)) "주식현물_체결수량"   
     , SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '2. 주식외 현물', 체결수량)) "주식외현물_체결수량" 
     , SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '3. 파생', 체결수량)) "파생_체결수량"       
FROM   체결
WHERE  체결일자 = '20090315' ;


-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:02:26.36 |    1800K|
|*  2 |   TABLE ACCESS FULL| 체결 |      1 |    235K|    200K|00:00:00.40 |     454 |
-------------------------------------------------------------------------------------

 

>>>> 아래와 같이 변환했지만 속도가 전혀 줄지 않음

 

SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"    
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량" 
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량"       
FROM (
  SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류
       , 체결수량 
  FROM   체결
  WHERE  체결일자 = '20090315'
) ;

 

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:02:25.06 |    1800K|
|*  2 |   TABLE ACCESS FULL| 체결 |      1 |    235K|    200K|00:00:00.40 |     454 |
-------------------------------------------------------------------------------------

 ----> 옵티마이저의 QUERY TRANSFORMER에 의해 뷰 머지가 발생했기 때문

           -> 인라인뷰로 묶기 이전 상태로 되돌아감

 

 >> 아래와 같은 방법으로 해결

 

 

(1)  NO_MERGE HINT 사용

 SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"    
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량" 
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량"       
FROM (
  SELECT /*+ NO_MERGE */ SF_상품분류(시장코드, 증권그룹코드) 상품분류
       , 체결수량 
  FROM   체결
  WHERE  체결일자 = '20090315'
) ;

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:49.16 |     600K|
|   2 |   VIEW              |      |      1 |    235K|    200K|00:00:49.60 |     600K|
|*  3 |    TABLE ACCESS FULL| 체결 |      1 |    235K|    200K|00:00:00.20 |     454 |
--------------------------------------------------------------------------------------

(2)  ROWNUM 사용 

SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"    
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량" 
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량"       
FROM (
  SELECT ROWNUM, SF_상품분류(시장코드, 증권그룹코드) 상품분류
       , 체결수량 
  FROM   체결
  WHERE  체결일자 = '20090315'

 

---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |00:00:49.03 |     600K|
|   2 |   VIEW               |      |      1 |    235K|    200K|00:00:49.00 |     600K|
|   3 |    COUNT             |      |      1 |        |    200K|00:00:00.40 |     454 |
|*  4 |     TABLE ACCESS FULL| 체결 |      1 |    235K|    200K|00:00:00.20 |     454 |
---------------------------------------------------------------------------------------

 

  

 

 

 

(4) 스칼라서브쿼리의 캐싱효과를 이용, 함수호출 최소화

 

      서브쿼리가 수행될 때마다 입력값을 캐시에서찾아보고 거기 있으면 저장된 출력값을 리턴 함

   , 없다면  쿼리를 수행한 후 입력값과 출력 값을 캐시에 저장

 

SELECT SUM(DECODE(상품분류, '1. 주식 현물', 체결수량)) "주식현물_체결수량"    
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량" 
     , SUM(DECODE(상품분류, '3. 파생', 체결수량)) "파생_체결수량"       
FROM (
  SELECT /*+ NO_MERGE */ 
        (SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류
       , 체결수량 
  FROM   체결
  WHERE  체결일자 = '20090315'
) ;


SELECT 순서||'.'||:B1 
FROM
분류순서 WHERE 분류명 = :B1 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 725010    11.37      10.39          0          0          0           0
Fetch   725010     17.57      17.63          0    2175030          0      725010
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   1450021     28.95      28.03          0    2175030          0      725010

 

>> 함수 호출을 20번으로 예상했지만 많이 수행됨

     --> 해시 충돌이 발생 >> 기존 엔트리를 그대로 둔채 스칼라 서브쿼리만 한번더 수행

      >> 스칼라 서브 쿼리를 사용하기 이전 쿼리가 반복 수행됨

 

 

 

 .  캐쉬 사이즈 조절

ALTER SESSION SET "_query_execution_cache_max_size" = 2097152;

 

 

 

 -> 아래와 같은 개선 효과 가능


SELECT SUM(DECODE(상품분류, '1. 주식 현물', 체결수량)) "주식현물_체결수량" 
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
     , SUM(DECODE(상품분류, '3. 파생', 체결수량)) "파생_체결수량"
FROM (
  SELECT /*+ NO_MERGE */ 
        (SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류
       , 체결수량 
  FROM   체결 
  WHERE  체결일자 = '20090315'
) ;

경   과: 00:00:00.79
 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     20     0.00       0.00          0          0          0           0
Fetch       20      0.00       0.00          6         60          0          20
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       41      0.00       0.00          6         60          0          20

 

 

>> 1번에 나온것을 아래와 같이 변환 가능

 
       select( SELECT memb_nm(매수회원번호)  FROM DUAL)매도 회원명 
               ,( SELECT memb_nm(매수회원번호)    FROM DUAL)매수회원명
               ,( SELECT code_nm('446' , 매도 투자자 구분코드)  FROM DUAL) 매도투자자구분명
               ,( SELECT code_nm('446' , 매수 투자자 구분코드)   FROM DUAL)매수투자자구분명
               ,( SELECT code_nm('418' , 체결 유형코드)   FROM DUAL)체결 유형명
               ,매도계좌번호
               ,매수계좌번호
      from 체결
      where 종목코드 = : 종목코드
       and   체결일자 = : 체결일자 
       and   체결시간 between sysdate-10/21/60 and sysdate

 

 

 

 

 

(5) Deterministic 함수의 캐싱효과활용

  ->  Deterministic  키워드를 넣어주면 스칼라 서브쿼리를 덧입히지 않아도 캐싱 효과가 나타남

  ->함수의 입출력 값은 CGA(Call Global Area)에 캐싱, 할당된 값은 데이터베이스 CALL 내에서만 유효하므로 Fetch Call이 완료 되면 모두 해제

  -> 스칼라 서브쿼리에서의 입출력 값은 UGA(Call Global Area)에 캐싱, Fetch Call에 상관없이 그 효과가 캐싱되는 순간부터 끝까지 유지

 

 

 create or replace function ACCUM (p_input number) return number
DETERMINISTIC
as
  rValue number := 0 ;
  call_cnt number := 0;
begin
  dbms_application_info.read_client_info(call_cnt);
  if call_cnt is null then
    call_cnt := 0;
  end if;
 
  dbms_application_info.set_client_info(call_cnt + 1);
 
  for i in 1..p_input loop
    rValue := rValue + i ;
  end loop;
  return rValue ;
end;

 

select sum(accum_num) from (   select accum(mod(rownum, 50)) accum_num   from dual   connect by level <= 100000 ) ;

select sys_context('userenv', 'client_info') from dual;

 

 

 

create or replace function ACCUM (p_input number) return number
as
  rValue number := 0 ;
  call_cnt number := 0;
begin
  dbms_application_info.read_client_info(call_cnt);
  if call_cnt is null then
    call_cnt := 0;
  end if;
 
  dbms_application_info.set_client_info(call_cnt + 1);
 
  for i in 1..p_input loop
    rValue := rValue + i ;
  end loop;
  return rValue ;
end;

select sum(accum_num) from (   select accum(mod(rownum, 50)) accum_num   from dual   connect by level <= 100000 ) ;

select sys_context('userenv', 'client_info') from dual;

 

 100000

 >> 시점과는 무관하게 항상 일관성있는 결과를 출력하므로 캐싱효괄르 위한 Deterministic 함수의 올바른 활용 사례지만

     함수가 쿼리문을 포함할때는 선언해선 안됨, 일관성 측면에서 뜻하지 않은 결과 초래 --.current 모드로 읽기 수행

 

 

 

 

 

 SQL> create or replace function ACCUM (p_input number) return number

  2  DETERMINISTIC

  3  as

  4    rValue number := 0 ;

  5    call_cnt number := 0;

begin

  dbms_application_info.read_client_info(call_cnt);

  6    7    8    if call_cnt is null then

  9      call_cnt := 0;

  end if;

 10   11

 12    dbms_application_info.set_client_info(call_cnt + 1);

 13

  for i in 1..p_input loop

 14   15      rValue := rValue + i ;

 16    end loop;

 17    return rValue ;

 18  end;

 19  /


Function created.


SQL> select sum(accum_num) from (   select accum(mod(rownum, 50)) accum_num   from dual   connect by level <= 100000 ) ;


SUM(ACCUM_NUM)

--------------

      41650000


SQL> select sys_context('userenv', 'client_info') from dual;


SYS_CONTEXT('USERENV','CLIENT_INFO')

--------------------------------------------------------------------------------

50


SQL>