제3장 인라인뷰의 활용

사용자지정 저장형 함수란?

• 공통적인 업무 규칙들을 반복적으로 작성하지 않고 쉽게 사용할 수 있게 하는것

{section}
{column:width=10}

-- 함수 3회 수행
SELECT 
       사번, 
       AVG_AMT_FUNC(사번), 
       AVG_AMT_FUNC(사번) * 100,
       기본급 / AVG_AMT_FUNC(사번)
FROM   사원 
WHERE  부서코드 = '1110' ;

{column}
{section}

{section}
{column:width=10}

-- 비록 인라인뷰에서 한번만 사용했더라도 함수는 3번 수행됨
SELECT 사번, 
       AVG_AMT, 
       AVG_AMT * 100,
       기본급 / AVG_AMT
FROM 
      ( SELECT 사번, 
               AVG_AMT_FUNC(사번) AVG_AMT
               FROM 사원 
         WHERE  부서코드 = '1110' ) ;

{column}
{section}

{section}
{column:width=10}

-- 함수 1번 수행
SELECT 사번, 
       AVG_AMT, 
       AVG_AMT * 100,
       기본급 / AVG_AMT
FROM 
      ( SELECT 사번, 
               AVG_AMT_FUNC(사번) AVG_AMT
               FROM 사원 
         WHERE  부서코드 = '1110' 
        GROUP BY 사번) ;

{column}
{section}

결론 : GROUP BY를 하면 내부벅으로 치리결과가 저장되고 이것을 여러 번 사용하는것은 관계가 없습니다.

• 부분범위 처리 방법

{section}
{column:width=10}

-- 100의 테이타를 가져와서 GROUP BY 실행됨 사번은 유니크하지않타다면 100개의 이하의 테이타가 나올수있음
SELECT 사번, 
       AVG_AMT, 
       AVG_AMT * 100,
       기본급 / AVG_AMT
FROM 
      ( SELECT 사번, 
               AVG_AMT_FUNC(사번) AVG_AMT
               FROM 사원 
         WHERE  부서코드 = '1110' 
        GROUP BY 사번) ;
WHERE ROWNUM < 100

{column}
{section}

• TEST

{section}
{column:width=10}

FUNCTION Z_TEST_JH_STUDY
         (IN_AR_WARHS_CREATE_NO        in varchar2)       
RETURN   varchar2 is
    RET_VAL           VARCHAR2(14); 
BEGIN
 	RET_VAL := '';
	
	SELECT  SUBSTR(IN_AR_WARHS_CREATE_NO,15,3) INTO   RET_VAL
	FROM   DUAL;
	
    RETURN(RET_VAL);
END;

{column}
{section}

{section}
{column:width=10}

-- 5.234 Secs		
SELECT T
	 , T
	 , T
	 , T
  FROM ( SELECT Z_TEST_JH_STUDY(AR_WARHS_CREATE_NO) AS T
           FROM TB_IEM120
	  WHERE PLANT_CD = 'A021'
--        GROUP BY AR_WARHS_CREATE_NO
       )
 WHERE ROWNUM < 10000

EXECUTION PLAN

ID	PID	Operation	                  Name	       Rows	Bytes	Cost	IN-OUT	PQ Dist	PStart	PStop
0		SELECT STATEMENT		                9K	234K	1994 	 	 	  	  
1	0	  COUNT STOPKEY		  	 	 	 	 	  	  
2	1	    INDEX FAST FULL SCAN	TB_IEM120_PK	3M	91M	1994 	 	 

{column}
{section}

{section}
{column:width=10}

-- 1.79 Secs
SELECT T
	 , T
	 , T
	 , T
  FROM ( SELECT Z_TEST_JH_STUDY(AR_WARHS_CREATE_NO) AS T
           FROM TB_IEM120
	  WHERE PLANT_CD = 'A021'
          GROUP BY AR_WARHS_CREATE_NO
       )
 WHERE ROWNUM < 10000

EXECUTION PLAN

ID	PID	Operation	                  Name	       Rows	Bytes	Cost	IN-OUT	PQ Dist	PStart	PStop
0		SELECT STATEMENT		                9K	19M	20714 	 	 	  	  
1	0	  COUNT STOPKEY		  	 	 	 	 	  	  
2	1	    VIEW		                        3M	7G	20714 	 	 	  	  
3	2	      SORT GROUP BY NOSORT		        3M	91M	20714 	 	 	  	  
4	3	        INDEX RANGE SCAN	TB_IEM120_PK	3M	91M	20714 	 	 

{column}
{section}

• 저장형 함수의 활용시 주의사항

{section}
{column:width=10}

-- 우리가 흔하게 쓰는 방식
CREATE or  REPLACE 
   FUNCTION  AVG_MAX_AMT_FUNC 
      (v_empno    varchar2)
       RETURN   varchar2 IS
       V_avg_amt varchar2(30);
BEGIN
       SELECT RPAD(avg(급여총액),15)
                      ||RPAD(max(급여총액),15) 
                     into v_avg_amt 
       FROM    급여
       WHERE 사원번호 = v_empno
              and  년월 between  '199801' 
                                and         '199803' ;
       RETURN v_avg_amt;
END AVG_MAX_AMT_FUNC;

{column}
{section}

{section}
{column:width=10}

-- 함수 2번 실행 
SELECT 사번,성명,substr(AMT,1,15), substr(AMT,16,15) 
   FROM  (SELECT 사번,성명,
                                   AVG_MAX_AMT_FUNC(사번) AMT 
                     FROM  사원 
                  WHERE  부서코드 = '1120') ;

{column}
{section}

{section}
{column:width=10}

-- 함수 1번 실행 
 SELECT 사번,성명,substr(AMT,1,15), substr(AMT,16,15) 
   FROM  (SELECT 사번, MIN(성명) 성명,
                                   AVG_MAX_AMT_FUNC(사번) AMT
                     FROM  사원 
                  WHERE  부서코드 = '1120'
                  GROUP BY 사번 );

{column}
{section}

• 인라인 뷰를 안쓰고 해결하는 방법

{section}
{column:width=10}

SELECT 직무,
       AVG_MAX_AMT_FUNC(사번),
       COUNT(*),
       MAX(기본급),
       MAX(기본급) / AVG_AMT_FUNC(사번) * 100
  FROM 사원
 WHERE 부서코드 = '1120'
GROUP BY 직무, AVG_AMT_FUNC(사번);

{column}
{section}

결론 : 인라인뷰를 사용하는 것이 훨씬 간략해 보이고, MAX(기본급)과 같은 처리가 반복적으로 일어 나지 않아 유리함

출처 : 대용량 테이터베이스 솔류션II