'SQL 변형'에 해당되는 글 2건

Function 수행횟수 확인 TEST

1.TEST Function 생성

create or replace function

Scott.F1(p_num IN number) return number is v_num number;

begin

select max(e.empno)

into v_num

from emp E

where e.deptno = p_num;

return v_num;

end;

/

2.원본 SQL문 Trace 수행

- 원본 Trace 아래에 Funtion 내부적으로 수행된 SQL문에 대하여 Trace가 남는다.

select D.DEPTNO,scott.F1(D.DEPTNO) f_deptno

from scott.DEPT D

********************************************************************************

• Funtion 내부 쿼리 내역

SQL ID: dxa9c0qzbrxrt Plan Hash: 2083865914

SELECT SUM(E.EMPNO)

FROM

EMP E WHERE E.DEPTNO = :B1

• Function 내부의 Sum하는 SQL문이 바인드변수(:B1)을 바꾸어 가면서 4회 반복하여 Function을 호출한다.
• Function Call의 호출 수를 최소화하거나 Function 내부의 SQL문을 개선하는 것을 생각해 볼 수 있다.

1. Decode Function TEST

• Decode Funtion에 대하여 TEST를 진행

1-1) 개선 전

Compile Time : 2013/07/12 13:22:54

Trace File : C:\oracle\product\10.2.0\admin\Gbpr_tes\udump\gbpr_ora_4752.trc

Trace Version : 10.2.0.4.0

********************************************************************************

select empno, sal1,sal2,sal3

from (

select empno,

sum(sal) as sal1,

0 as sal2,

0 as sal3

from scott.emp_TEST a

where a.empno='7844'

group by empno

union all

select empno,

0 as sal1,

sum(sal) as sal2,

0 as sal3

from scott.emp_TEST b

where b.empno='7900'

group by empno

union all

select empno,

0 as sal1,

0 as sal2,

sum(sal) as sal3

from scott.emp_TEST b

where b.empno='7782'

group by empno

)

order by EMPNO

********************************************************************************

1-2) 개선 후

Compile Time : 2013/07/12 13:23:30

Trace File : C:\oracle\product\10.2.0\admin\Gbpr_tes\udump\gbpr_ora_4752.trc

Trace Version : 10.2.0.4.0

********************************************************************************

select empno,

NVL(sum(decode(empno,'7844', sal)),0) as sal1,

NVL(sum(decode(empno,'7900',sal)),0) as sal2,

NVL(sum(decode(empno,'7782',sal)),0) as sal3

from scott.emp_TEST a

where a.empno in ('7844','7900','7782')

group by empno

order by EMPNO

********************************************************************************