꿈꾸는 개발자, DBA 커뮤니티 구루비

3. 저장형 함수를 이용한 데이터 연결

3. 저장형 함수를 이용한 데이터 연결

3.1 개념 및 특징

3.1.1 절차형 처리

① 데이터베이스가 제공하는 절차형 SQL을 이용해 생성한다.
② 하나 이상의 SQL이 존재할 수 있다.
③ 다양한 연산이나 조건처리 가능하다.
④ 외부에서 받은 값이나 고정된 상수값을 이용하여 필요한 처리를 하여 단하나의 결과값만 리턴한다.

제공받은 값을 이용해 가공 후 리턴할 값을 만들 때까지만 의미.
단순히 단 하나의 값을 만드는 처리
사용자가 마음대로 작성할 수 있다는 것 외에는 데이터베이스가 제공한 SUM, SUBSTR 등과 같은 함수
사용자가 자신의 의지대로 필요한 처리를 할 수 있다는 장점

3.1.2 독립적인 오브젝트

테이블이나 뷰와 같이 독자적인 기능을 가지는 독립적인 오브젝트이다.
다른 오브젝트의 도움 없이도 결과를 낼수 있다.
하나의 공통 모듈로서 여러 곳에서 공유 할수 있다.
저장형 함수는 매우 가벼워야 한다.

3.1.3 단일값을 리턴

단한개의 컬럼만 리턴
추출한 컬럼값들을 결합하여 하나의 상수값으로 만들어 리턴
이것을 SQL에서 SUBSTR으로 다시 문할하여 최종 출력

3.1.4 로우단위별 실행

자신이 속한 집합의 로우 단위별로 실행

조인과의 비교

특정한 경우 조인에 비해 매우 효과적일 수 있다.
저장형 함수가 조인과 다른점은 절차형 처리를 할 수 있다는 것과 단일값을 리턴한다는 특성.
어떤 집합의 로우들이 그 값에 따라 서로 다른 집합과 연결하는 경우도 쉽게 처리할 수 있다.

유형별 활용

3.3.1 1:M 조인을 1:1 조인

복잡한 query 를 저장항 함수 이용함.
1개의 값을 리턴한다.

조인을 하므로써 전체범위처리를 하므로 이의도적으로 부분범위처리로 변경 시키고자 할 때 사용한다.

3.3.2 M:M 조인의 해결

M:M 관계를 억지로 조인으로 풀려고 해서는 안된다.
앞에서본 UNION, GROUP BY 이용 할수도 있다.
서브쿼리를 활용할수 있다.
M:M조인을 쓰는 이유는 모든집합의 처리를 완료한 후에 출력하는 것이 아니라 소량의 집합만 전체를 처리하고 다량의 집합은 일부씩 처리되므로 온라인 화면 처리에서느 효과를 불수 있다.

3.3.3 부분범위처리로의 유도

① 자신과 1:M 관계를 갖는 집합에서 조건으로 체크하고자 하거나 조건 체크와 그 결과값도 같이 출력하고자 할 때 사용하는 방법.
② EXISTS로 인해 전체범위처리로 실행되는 필터 처리를 부분범위처리 방식으로 바꾸는 경우에 사용하는 방법.
③ 부분범위처리로 유도할 수 없는 상태일 때 그 원인 부분만 전체범위처리하고 나머지 처리를 부분범위처리로 바꾸는 방법.

가. M집합 체크시의 부분범위처리

 

 SELECT 고객번호, 고객명, 연락처.......
  FROM ( SELECT x.고객번호, max(x.고객명) 고객명, max(x.연락처) 연락처,....
         FROM 고객 x, 청구 y
         WHERE x.고객번호 = y.고객번호
           AND   x.고객상태 = '연체'
         AND   y.납입구분 = 'N'
         GROUP BY x.고객번호
           HAVING sum(y.미납금) between :VAL1  and  :VAL2)
   WHERE ROWNUM  <= 2000

고객 테이블에 index = 고객상태
청구 테이블에 index = 고객번호 + 납입구분

50만 고객중에 2000명만 선택하여 추출하는것이다.
조인을 하면 GROUP BY 를 하기 전에 먼저 모든 처리범위를 처리하는 '전체범위처리' 방식으로 수행 되므로 2000명을 골라내기전에 50만명에 대한 전체범위를 처리된다는게 문제이다.

해결방법 : EXISTS를 이용하여 해결

 
 SELECT 고객번호, 고객명, 연락처.......
  FROM 고객 x
  WHERE 고객상태 = '연체'
    AND  EXISTS ( SELECT ' ' 
                    FROM 청구 y
                   WHERE y.고객번호 = x.고객번호
                        AND y.납입구분 = 'N'
                   GROUP BY y.고객번호
                      HAVINGsum(y.미납금)  between   :VAL1  and  :VAL2 )
  and ROWNUM <=2000

전체범위처리 방식으로 수행 했던 원인 group by가 체크하는걸 서브쿼리로 넣는다.
그러므로 고객 테이블은 부분범위 처리를 하게된다.
단점이 있다.
- 서브쿼리의 수행결과를 메인쿼리에서 추출 할수 없다.
- 미납액 합계 추출 안된다.

EXISTS를 사용했을때 '미납액' 을 추출할수 있으며 부분범위 처리 수행된다.
위에 EXISTS의 부분을 FUNCTION 를 이용했다.

나. 전체범위처리로 수행되는 필터 처리 해결

필터처리는 NESTED LOOPS 조인과 유사한 방법으로 수행되거나 경우에따라서 SORT MERGE 조인과 유사한 방법으로 수행된다.

다. 득정 부분만 부분범위 처리 유도

GROUP BY 당년금액*-1, 상품
전체범위 처리로 실행되었지만, inlineview로 생성하였기 때문에 저장형 함수로 처리한 나머지 연도의 데이터는 부분범위처리가 가능하다.

3.3.4 베타적 논리합(Exclusive OR) 관계의 조인

어떤 엔터티의 틀징 관계가 두개 이상 엔터티의 합집학과 절대적 관계를 가지는것 이다.
참조 엔터티가 그 중 하나의 참조되는 엔터티와 반드시 관계를 가져야 한다.

OR 조인시 주의사항
A * (B + C) = (A * B) + (A * C) 가 되듯이 A and (B + C) = (A and B) or (A and C) = (A and B) union (A and C)