[펌]deterministic

[펌]deterministic 함수

 

deterministic 함수는 입력값이 같으면 리턴되는 결과 값도 항상 같음을 보장하는 함수이다.

 

함수 기반 인덱스를 생성할 때 사용자 정의함수를 사용하려면 해당 함수는

반드시 deterministic 함수로 생성되어야 한다.

 

아래 함수는 deterministic 키워드를 사용했지만 실제로는 deterministic 함수로 보기 어렵다.

왜냐하면 하나의 쿼리에서 이 함수가 반복 실행된다면 도중에 다른 값이 리턴될 수 있기 때문이다.

 

create or replace function uf_name
  (p_empno number)
  return varchar2
  deterministic
is

  v_ename emp.ename%type;
begin
  select ename into v_ename
  from emp
  where empno=p_empno;

  return v_ename;
end;
/

 

반면 다음 함수는 입력값이 같으면 항상 결과 값이 같으므로 deterministic 함수의

적절한 예제로 볼 수 있을 것이다.

 

create or replace function uf_tax
  (p_sal number)
  return number
  deterministic
is
begin

  return p_sal*0.01;
end;
/

 

다음은 여러 매뉴얼에서 발췌한 내용들이다.

 

- Any user-defined function referenced in Function Based Index must be DETERMINISTIC.

- If you subsequently change the semantics of the function, then you must manually

    rebuild  all dependent function-based indexes and materialized views.

 

- Specify DETERMINISTIC to indicate that the function returns the same result value
   whenever it is called with the same values for its arguments.

 

- You must specify this keyword if you intend to call the function in the ｅxpression

    of a function-based index or from the query of a materialized view that is marked

    REFRESH FAST or ENABLE QUERY REWRITE. When Oracle Database encounters a deterministic

    function in one of these contexts, it attempts to use previously calculated results when

    possible rather than re-executing the function.

 

- Do not specify this clause to define a function that uses package variables or that

   accesses the database in any way that might affect the return result of the function.

   The results of doing so will not be captured if Oracle Database chooses not to reexecute

    the function.

 

 

 

[출처] http://blog.naver.com/orapybubu?Redirect=Log&logNo=40092439547

 

---

 

 

Deterministic Function

참고 자료 1

http://forums.oracle.com/forums/message.jspa?messageID=2460975

Deterministic Function

In addition to what the others have said:

You are never required to declare a function as DETERMINISTIC,
but you should (when applicable), because Oracle can cache the results of the function calls,
and only call the function when necessary.

For example:

SELECT f (cd), ...

FROM table_x;

Suppose table_x has 1,000,000 rows, and 50 distinct values for cd.
If function f is declared as DETERMINISTIC, then Oracle can do this query with only 50 function calls.
If f is not declared as DETERMINISTIC, Oracle has no choice:
it must call the function 1,000,000 times.
No matter how trivial the function is, the mere act of calling PL/SQL that many times can slow down your query.

참고 자료 2

http://adap.tistory.com/entry/Deterministic-%EC%9D%98-%EC%A7%84%EC%8B%A4Multi-buffer

Deterministic 의 진실(Multi buffer)

목적 : Scalar, Function(Deterministic 사용 여부) 사용시 각각의 차이점을 확인하기 위함
       1 : M 테이블에서 1의 내용을 scalar, funion 처리함
테스트 환경 : 10gR2

1) Scalar
    - 가장 성능이 좋은것으로 나타났다.

2) Function (Deterministic사용)
    - Deterministic사용시 Scalar Query보다는 효율은 좋지 않으나 미사용시보다 성능이 많이 개선된다.
      즉 Deterministic의 기능은 하는 것으로 보이나 Scalar와 같이 정확히 구현된건 아닌거 같다.
      (모호한 점은 Multi block i/o를 하기는 하는데.. Scalar처럼 정확하게 하지는 않는거 같음.)

    참고내용
    - Manual : 오라클이 함수의 현재 호출이 이전 호출과 같은 입력 값을 사용했다는 것을 확인할 수 있다면
                    이전에 리턴했던 결과 값을 사용함으로써 함수의 반복 호출을 피할 수 있다는 것이다.
    - Cost Base Oracle - Fundamental(조나단 루이스) : 그런 특징은 지금까지 구현되지 않았다.(틀림)
                    (사용하고 안하고의 차이가 많다)
    - Oracle PL/SQL Tuning Expert secrets ~(Donald K. Burleson,.) : 오라클이 주장하는 어떤한 증거도
                    찾을 수 없음(테스트를 Pipelined table에 대해서만 하였음)
             
3) Function (Deterministic 미사용)
    - Deterministic 미사용시는 (SQL존재) recursive call이 결과 건수만큼 증가한다.

4) Function (Deterministic 사용 with dual) - Scalar형태로 처리가 곤란한 경우 Function사용시

5) Function (Deterministic 미사용 with dual)

성능면에서는
    Scalar > Deterministic > NonDeterministic
  ->Scalar >Deterministic with dual > Non Deterministic with dual
               > Deterministic without dual > Non Deterministic without dual
  -> 즉 Scalar처리가 가능하지 않은 환경이라면 (Function 사용시 deterministic 과 dual사용이 유리)
  -> 만약 Loop 내에서라면 값이 변경된다면 일관성에 문제가 발생될지도...??

1) select a.*,(select b.mgr from tb_mgr b where b.mgr = a.mgr) from tb_dbio_mgr a
-- consistent gets : 118 (recursive calls : 0)

2) select a.*,sf_deterministic_test(a.mgr) from tb_dbio_mgr a
-- consistent gets : 657 (recursive calls : 992)

3)select a.*,sf_no_deterministic_test(a.mgr) from tb_dbio_mgr a
-- consistent gets : 9806 (recursive calls : 10823)

4) select a.*,(select sf_deterministic_test(a.mgr) from dual) from tb_dbio_mgr a
-- consisent gets  :  132 (recursive calls :  31)

5) select a.*,(select sf_no_deterministic_test(a.mgr) from dual) from tb_dbio_mgr a
-- consistent gets :  148 (recursive calls : 64)

- 소량의 자료를 처리하거나 부분범위처리시는 Scalar를 사용하여 처리하면 성능에 많은 이점이 있다.
- 단순한 Scalar처리를 할 수 없다면 Deterministic을 사용한 Function을 사용할 수 있다. 그러나 엔진간의
   Switch를 고려해야한다.(Recursive call)

 

[출처] Deterministic Function|작성자 타락천사

http://blog.naver.com/darkturtle?Redirect=Log&logNo=50070277805

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

..