사용자 정의 함수는
- 소량의 데이터 조회 시
- 대용량 데이터를 조회할 때는 부분범위처리가 가능한 상황에서 제한적으로 사용
- 조인 또는 스칼라 서브쿼리 형태로 변환
- 어쩔 수 없을 경우 함수를 쓰되 호출 횟수를 최소화
함수 호출 부하 해소 방안
- 페이지 처리 또는 부분범위처리 활용
- Decode 함수 또는 Case문으로 변환
- 뷰 머지 방지를 통한 함수 호출 최소화
- 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화
- Deterministic 함수의 캐싱 효과 활용
- 복잡한 함수 로직을 풀어 SQL로 구현
(1) 페이지 처리 또는 부분범위처리 활용
함수를 인라인 뷰 안쪽에 두면 조건에 맞는 전체 레코드 수만큼 호출되고 정렬까지 발생한다.
해법은 맨 바깥 select-list로 끌어올린다. 그러면 order by + rownum 페이지 필터를 통과한 최종 10건에만 함수가 호출된다!
-- 함수를 맨 바깥으로 → 페이지된 최종 10건에만 호출
select memb_nm(매도회원번호) 매도회원명, ... -- 여기서만!
from (
select rownum no, a.* from (
select 매도회원번호, ... -- 안쪽은 원본 컬럼만, 함수 없음
from 체결 where ... order by 체결시각 desc
) a where rownum <= 30
) where no between 21 and 30;(2) Decode 함수 또는 Case문으로 변환
함수 로직을 풀어서 decode, case문으로 전환하거나 조인문으로 구현할 수 있는지 확인해보자!
(3) 뷰 머지(View Merge) 방지를 통한 함수 호출 최소화
단계 A: 함수를 그냥 3번 호출
select sum(decode(SF_상품분류(시장코드, 증권그룹코드), '1. 주식 현물', 체결수량)) 주식현물수량
, sum(decode(SF_상품분류(시장코드, 증권그룹코드), '2. 주식외 현물', 체결수량)) 주식외수량
, sum(decode(SF_상품분류(시장코드, 증권그룹코드), '3. 파생', 체결수량)) 파생수량
from 체결
where 체결일자 = '20090315'단계 B: 인라인 뷰로 묶기 (1번만 호출) -> 옵티마이저에 의해 A와 동일
select sum(decode(상품분류, '1. 주식 현물', 체결수량)) 주식현물수량
, sum(decode(상품분류, '2. 주식외 현물', 체결수량)) 주식외수량
, sum(decode(상품분류, '3. 파생', 체결수량)) 파생수량
from ( select SF_상품분류(시장코드, 증권그룹코드) 상품분류 -- 여기서 1번만 계산하려는 의도
, 체결수량
from 체결
where 체결일자 = '20090315' )단계 C: no_merge 힌트 사용
select sum(decode(상품분류, '1. 주식 현물', 체결수량)) 주식현물수량
, sum(decode(상품분류, '2. 주식외 현물', 체결수량)) 주식외수량
, sum(decode(상품분류, '3. 파생', 체결수량)) 파생수량
from ( select /*+ NO_MERGE */ SF_상품분류(시장코드, 증권그룹코드) 상품분류
, 체결수량
from 체결
where 체결일자 = '20090315' )- rownum : 옵티마이저가 rownum을 확인하면 해당 뷰를 머지하지 않는다!
(4) 스칼라 서브쿼리의 캐싱효과를 이용한 함수 호출 최소화
select ( select d.dname -- 출력값: d.dname
from dept d
where d.deptno = e.deptno ) -- 입력값: e.deptno
from emp e서브쿼리가 수행될 때마다 입력 값을 캐시에서 찾아보고 거기 있으면 저장된 출력 값을 리턴하고, 없으면 쿼리를 수행한 후 입력 값과 출력 값을 캐시에 저장한다.
함수를 Dual 테이블을 이용해 스칼라 서브쿼리로 한번 감싸준다!
특히, 함수 입력 값의 종류가 적을 때 이 기법을 활용하면 함수 호출 횟수를 획기적으로 줄일 수 있다!
select ...
from ( select /*+ NO_MERGE */
(select SF_상품분류(시장코드, 증권그룹코드) from dual) 상품분류 -- 감쌈
, 체결수량
from 체결 where 체결일자 = '20090315' )입력값의 종류가 많으면 해시 충돌 때문에 함수가 그대로 호출된다. (충돌이 나면 오라클은 기존 캐시 엔트리를 그대로 둔 채 그냥 스칼라 서브쿼리를 한 번 더 실행한다)
(5) Deterministic 함수의 캐싱 효과 활용
10gR2에서 함수를 선언할 때 Deterministic 키워드를 넣어 주면 스칼라 서브쿼리를 덧입히지 않아도 캐싱 효과가 나타난다.
함수의 입력 값과 출력 값은 CGA(Call Global Area)에 캐싱된다.
CGA에 할당된 값은 데이터베이스 Call 내에서만 유효하므로 Fetch Call이 완료되면 그 값들은 모두 해제된다. 따라서 Deterministic 함수의 캐싱 효과는 데이터베이스 Call 내에서만 유효하다.
무엇보다 Deterministic은 오라클의 보장이 아니라 개발자의 선언일 뿐이어서 내부에 SELECT가 있는 함수에 캐싱 목적으로 붙이면 일관성 없는 결과를 초래하므로 순수 연산 함수에만 사용해야 한다.
