Deterministic함수 사용 시 주의사항

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

해결 방법 :

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

• • 추가 설명 : http://www.gurubee.net/article/48713

문서에 대하여

• 최초작성자 : 이창헌
• 최초작성일 : 2010년 5월 13일
• 수정작성일 : 2010년 5월 13일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*