Deterministic함수 사용 시 주의사항

• Deterministic키워드는, 함수의 입력 값이 같다면 출력 값도 항상 같음을 선언 (10gR2에서 캐싱 효과 추가)
  • 잘못 사용했을 때 데이터 일관성을 해칠 수 있음(사용자 정의 함수를 사용하는 한, Deterministic키워드를 쓰든 안 쓰든 상관없이 나타나는 현상)

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  /

함수가 생성되었습니다


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;
	

• 주의사항 : 오라클만의 독특한 읽기 일관성 모델을 이해하는 것이 무엇보다 중요( Consistent 모드 , Current 모드, 사용자 정의 함수 사용시 읽기 일관성 문제)

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


FBI를 만드는 경우 외에도, update문으로 반정규화 컬럼을 갱신하거나 insert문 가공 테이블에 레코드를 삽입할때도 중간에 값이 변경되면 일관성 없는 상태로 값들이 영구 저장될 수 있다.