[Function] 스칼라 서브쿼리에서 수행하도록 변경

SQL 중 최종 추출 결과가 적은 경우에는, Function의 수행위치를 변경하여 과다 수행되는 성능 문제를 해결 할 수 있다.

예를들면,

select z.*

(select

empCnt (e.title) empCnt ,

deptCnt (d.title) deptCnt,

...

from emp e inner join dept d ON e.deptno = d.deptno

where ... ) z

order by rownum <= 3

만약 emp 와 dept 의 inner join rows가 4만건이라면 해당 함수도 4만횟수를 수행하여 쿼리 I/O 상에서는 문제가 없겟지만

함수 I/O 상에서 문제가 크게 발생된다 왜냐하면 함수를 굳이 4만번 수행하지 않고 rownum 3회만 수행하게 할 수 있기 때문이다.

select x.*

, empCnt(x.eTitle) empCnt

, deptCnt(x.dTitle) deptCnt

from (

select z.*

from (

select

e.title eTitle ,

d.title dTitle ,

...

from emp e inner join dept d ON e.deptno = d.deptno

where ... ) z

order by rownum <= 3

) x

위와 같이 쿼리를 수행하면 과다 수행 되지않고 3row를 뽑은뒤 함수가 3번만 수행 되게 되어진다.

하지만 SQL의 최종 추출 결과가 많은 경우에는, Function 의 수행 위치를 바꾸는 것만으로 성능을 개선하기는 힘들다.

Main SQL 에서 추출한 데이터 중 Function을 수행하는 입력값의 종류가 적은 경우 (Distinct rows가 적은경우) 스칼라 서브쿼리에서 Function을 수행하도록 변경하면, Multi Buffer 를 이용해 성능을 개선 시킬 수 있다.

select

jisaname( gr. jisacode1, '00', sysdate ) jisaname1 , 

jisaname( gr.jisacode1, gr.jisacode2, sysdate ) jisaname2,

groupname( gr.parent_groupno ) parent_groupname , 

groupname ( gr.groupno ) groupname ,

...

from t_member mem inner join t_group gr ON gr.groupno = mem.groupno

where ...

해당 쿼리의 함수별 추출 데이터 건수가 4,708 * 4 = 18,832 건이라 하면

select

(SELECT jisaname( gr. jisacode1, '00', sysdate ) FROM DUAL) jisaname1 , 

(SELECT jisaname( gr.jisacode1, gr.jisacode2, sysdate ) FROM DUAL) jisaname2,

(SELECT groupname( gr.parent_groupno ) FROM DUAL) parent_groupname , 

(SELECT groupname ( gr.groupno ) FROM DUAL) groupname ,

...

from t_member mem inner join t_group gr ON gr.groupno = mem.groupno

where ...

jisaname() 은 2번, groupname()은 총 2,213번으로 수행건이 줄어들었다.

gr.jisacode1, 과 gr.jisacode2가 모두 동일한 값을 가지고 있어 스칼라 서브쿼리에서 수행되도록 변경한 경우,

최초 1회씩만 수행하고 나머지는 Cache에서 결과 값을 가져 올 수 있었기 때문이다.

이와같이 입력 값에 대응하는 값의 종류가 다양하지 않아, 같은 입력 값에 대한 결과 값이 항상 동일하다면,

Function 은 스칼라 서브쿼리에서 수행하도록 변경하여 Function 수행에 의한 부하를 감소시켜야 한다.