메뉴 건너뛰기

bysql.net

o. Deterministic의 목적

- 함수의 입력 값이 같다면 출력 값도 항상(현재 뿐만 아니라 미래에도) 같음을 선언하는 의미 (10gR2의 신 기능인 캐싱효과 제외)


o. 캐싱효과를 위해 함부로 사용하면 안된다?

- 성능도 중요하지만, 잘못 사용했을때 데이터 일관성을 해칠 수 있다. 이 키워드가 함수의 일관된 결과를 보장해주지는 않는다.


o. 아래 함수를 Deterministic으로 선언하면 어떻게 될까? 함수의 입력값이 같을 때 출력 값이 같을까?



SQL> CREATE OR REPLACE FUNCTION LOOKUP(L_INPUT NUMBER) RETURN VARCHAR2 DETERMINISTIC

AS

L_OUTPUT LOOKUPTABLE.VALUE%TYPE;

BEGIN

SELECT VALUE INTO L_OUTPUT FROM LOOKUPTABLE WHERE KEY=L_INPUT;

RETURN L_OUTPUT;

END;

/



 - 위 함수를 Deterministic으로 선언하면 안된다. 동일 입력 값에 대해 동일 출력 값을 보장할 수 없다.

 - LookupTable에서 value 값이 갱신될 수 있기 때문이며 Deterministic으로 선언했는데 실제 내용이 그렇지 않을 경우 문제가 발생 할 수 있다.

 - LookupTable의 value 컬럼 값이 바뀐다면 위 함수를 사용하는 쿼리들이 시점에 따라 서로 다른 값을 출력하게 되며 쿼리 결과를 Fetch하는 도중에도 다른 갑슬 출력하게 된다.

 - 이것은 함수를 사용하는 한, Deterministic과 무관하며 Deterministic을 쓴다면 캐싱 효과는 Fetch Call 내에서만 유효하므로 새로운 Fetch Call 시점에 다시 쿼리하면서 이런 현상이 발생하게 된다.

 - 함수를 스칼라 서브쿼리에 덧입히더라도 읽기 일관성을 완전히 보장받을 수는 없다.

 - 스칼라 서브쿼리의 캐싱 효과는 Fecth Call을 넘어서 커서가 닫힐 때까지 유효하지만,

첫 번째 호출이 일어나 캐싱될 때까지의 시간차 때문에 완벽한 문장수준 읽기 일관성을 보장하기 어려운 것이다.



o. LookupTable에 아래와 같이 2개 레코드를 입력한다면

lookup함수에 입력 값으로 1을 사용하든 2를 사용하든 YAMAHA 가 출력된다.


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');



o. big_table

    no=1 레코드가 1000개,   no=2 레코드가 1000개 존재한다고 가정.

함수 호출이 no값 순서대로 일어나게 no 컬럼이 선두에 위치한 인덱스 사용 -> 캐시에 최초 (1,YAMAHA)가 캐싱


o. lookup 함수를 호출하는 쿼리는 아래와 같다.


SQL> SELECT /*+ INDEX(T T_NO_IDX) */ (SELECT LOOKUP(T.NO) FROM DUAL)

FROM BIG_TABLE T

WHERE T.NO >0;



o. 아직 no=2인 첫 번째 레코드에 도달하지 않은 상태에서 다른 세션에서 아래 쿼리를 수행하면

이 순간부터 lookup 함수의 출력 값은 YAMAHA2로 바뀐다.


SQL> UPDATE LOOKUPTABLE SET VALUE='YAMAHA'; COMMIT;


2012-05-29_162419.png


- 이 쿼리가 no=2 인 첫 번째 레코드에 도달하는 순간 lookup 함수가 수행되면서 캐시에는 아래 두 개 엔트리가 저장된다.

{(1,YAMAHA),(2,YAMAHA2)}

 - 이제 Query Execution Cache는 일관성 없는 상태가 된다. 쿼리 시작 시점을 기준으로 한다면 아래와 같아야 한다.

{(1,YAMAHA),(2,YAMAHA)}

 - 만약 쿼리가 진행 중인 Current 시점을 기준으로 한다면 아래와 같아야 한다.

{(1,YAMAHA2),(2,YAMAHA2)}


 - 그런데 실제 캐시에는 어느쪽도 아닌 일관성 없는 값을 가지게 된다. 이러한 일관성 없는 읽기는 사용자 정의 함수를 쓰는 한

완전히 피할 수 없는 현상이며 아래 Join문을 써야 해결된다.



SQL> SELECT L.VALUE

FROM BIG_TABLE T, LOOKUPTABLE L

WHERE L.KEY(+)=T.NO;


SQL> SELECT (SELECT VALUE FROM LOOKUPTABLE WHERE KEY=T.NO)

FROM BIG_TABLE T;



o. 스칼라 서브쿼리는 조인의 또 다른 형태일 뿐이므로 메인 쿼리가 시작되는 시점을 기준으로 블록을 읽기 때문에 읽기 일관성을 보장한다.


o. Deterministic과 FBI(Function-Based Index)의 관계

 - FBI는 인덱스가 처음 만들어지거나 엔트리가 추가되는 시점의 함수 출력 값을 저장해두는 원리

 - Deterministic으로 선언하지 않은 함수에 대해서는 FBI생성을 거부한다.(강제성)



결론: 캐싱 효과를 얻으려고 함부로 Deterministic으로 선언하는 것은 위험하다.

번호 제목 글쓴이 날짜 조회 수
66 Front Page file 운영자 2012.02.21 131194
65 6._문장수준_읽기_일관성 file 정찬호 2012.03.12 57264
64 3._SQL트레이스 sapius 2012.04.04 26843
63 5._Undo dasini 2012.03.11 25064
62 2._SQL_처리과정 dasini 2012.04.06 21655
61 6._RAC_캐시_퓨전 file 남송휘 2012.05.21 17511
60 4._커서_공유 file 남송휘 2012.04.27 16075
59 5._오라클_Lock file 시와처 2012.03.26 12573
58 8._블록_클린아웃 시와처 2012.03.19 11995
57 4._동시성_구현_사례 [1] dasini 2012.03.27 11683
56 8._Statspack_AWR 시와처 2012.04.01 11474
55 2._DB_버퍼_캐시 file 시와처 2012.03.04 10421
54 10._대기_이벤트 박영창 2012.03.19 10365
53 9._Snapshot_too_old 박영창 2012.03.19 9727
52 1._기본_아키텍처 file AskZZang 2012.03.02 8338
51 7._세션_커서_캐싱 박영창 2012.04.22 7778
50 5._Direct_Path_IO 남송휘 2012.05.21 7503
49 3._버퍼_Lock 박영창 2012.02.24 7226
48 7._PLSQL_함수의_특징과_성능_부하 file 남송휘 2012.05.15 6624
47 10._V$SQL file 정찬호 2012.04.09 6539