h1.Deterministic 함수 사용 시 주의사항

Deterministic 키워드는 입력값이 같다면 출력값도 항상 같다라는 목적을 가지고 있다.
본래 의미를 무시하고 캐싱 효과를 얻을 목적으로 함부로 Deterministic 함수로 선언하면 안 된다.
성능도 중요 하지만 잘못 사용했을 때 데이터 일관성을 해칠 수 있기 때문이다.

h3.예시1) 데이터 일관성을 해치게 되는 현상

SQL>  create table LookupTable( key number, value varchar2(100) );
SQL> insert into LookupTable( key, value ) values ( 1, 'YAMAHA' );
SQL> insert into LookupTable( key, value ) values ( 2, 'YAMAHA' );
SQL> commit;


SQL> create or replace function lookup( l_input number ) return varchar2
  2  deterministic
  3  as
  4    l_output LookupTable.value%TYPE;
  5  begin
  6  	select value into l_output from LookupTable where key = l_input;
  7  	return l_output;
  8  end;
  9  /

함수가 생성되었습니다

-위의 함수는 deterministic를 사용하면 안되지만 deterministic를 사용하였다.
 그 이유는 테이블에서 데이터를 읽어 오게 되는데 LookupTable에서 value 값이 갱신될 수 있기 때문이다.

SQL> create table big_table nologging
  2  as
  3  (select 1 no FROM DUAL CONNECT BY LEVEL < 1001
  4   union all
  5   select 2 FROM DUAL CONNECT BY LEVEL < 1001)
  6  /

SQL> create index t_no_idx on big_table(no);

select /*+ index(t t_no_idx) */ (select lookup(t.no) from dual )
from big_table t
where t.no > 0

위 쿼리 실행 후 다른 세션에서 update LookupTable set value='YAMAHA2'; commit;

결과값 {1,'YAMAHA'},{2,'YAMAHA2'}
쿼리 시작 시점 {1,'YAMAHA'},{2,'YAMAHA'}
Current시점 {1,'YAMAHA2'},{2,'YAMAHA2'}

실제 캐시에는 어느 쪽도 아닌, 일관성 없는 값을 갖게 됩니다. 


이러한 경우를 위해 일관성을 유지하기 위해서는 아래와 같이 사용하여야 한다.

 SQL> select l.value
  2    from big_table t, LookupTable l
  3  where l.key(+) = t.no


SQL> select (select value from LookupTable where key = t.no )
  2    from big_table t;

예시2) 함부로 Deterministic으로 선언했을 때 생기는 부작용

SQL> delete from LookupTable;

1 행이 삭제되었습니다.

SQL> insert into LookupTable values( 1, 'YAMAHA' );

1 개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> create table t
  2  as
  3  select 1 no from dual;

테이블이 생성되었습니다.


SQL> select no, lookup(no) value from t;

        NO VALUE                       
---------- -----------                                                                                                  
         1    YAMAHA 

SQL> create index t_idx on t( lookup(no) );

인덱스가 생성되었습니다.

SQL> select no, lookup(no) value, lookup(1) value2 from t
  2  where lookup(no) = 'YAMAHA';

        NO      VALUE        VALUE2                   
---------- ----------- -------------                                                                                    
         1     YAMAHA        YAMAHA  

    
SQL> update LookupTable set value = 'YAMAHA2' where key = 1;

1 행이 갱신되었습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> select no, lookup(no) value, lookup(1) value2 from t
  2   where lookup(no) = 'YAMAHA';

        NO       VALUE      VALUE2 
------------ --------- ------------
         1     YAMAHA      YAMAHA2


SQL> alter index t_idx rebuild
  2  /

인덱스가 변경되었습니다.

SQL> select no, looKup(no) value, lookup(1) value2 from t
  2   where lookup(no) = 'YAMAHA'
SQL> /

선택된 레코드가 없습니다.

 SQL> select no, lookup(no) value, lookup(1) value2 from t
  2   where lookup(no) = 'YAMAHA2'
SQL> /

        NO   VALUE     VALUE2     
----------  ---------  --------------------------------------------
         1    YAMAHA2  YAMAHA2

함수의 실제 내용이 Deterministic 이 아닌데 Deterministic으로 선언했을 때 일관성이 깨지는 사례를 보았다.