DECODE Function Test

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

   

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