부분범위 처리 혹은 페이징 처리시 탁월한 힘을 발휘하는것이 스칼라 서브쿼리이다. 여러가지 테스트 결과 스칼라 서브쿼리는 부분범위처리시 가장 좋다는 Nested Loop 조인보다 성능상 좀더 유리하다.  과다호출로 몸살을 앟고 있는 사용자 함수를 스칼라 서브쿼리로만 바꾸어도 튜닝이 된다. 또한 View Merging 이나 JPPD(Join Push Predicate Down) 등의 성공여부를 고민할 필요도 없으므로 특히 개발자 들에게 인기가 많은것 또한 사실이다.

모든 기능은 용도에 맞게 사용해야 한다.
  이렇게 많이 활용되고 있는 스칼라 서브쿼리도 한계는 있다. Multi Column, Multi Row 을 RETURN 하는 것은 만들수가 없다는 것이다. 물론 Pipilined Funtion 으로 해결이 가능하지만 모든경우에 이것을 만들기란 여간 힘든것이 아니다. 바로 이럴때 사용가능한것이 Cast + MultiSet 함수를 이용한 스칼라 서브쿼리이다. MultiSet 함수는 Oracle 8.1.7  부터 사용이 가능하다. 필자가 이기능을 소개하는 이유는 실무에서 이런기능을 꼭 써야하는 경우임에도 Pipelined 함수등을 사용하는것을 많이 보아서이다. Pipelined 등의 함수를 사용해야 할때는 자주사용하는 SQL 이 아주 길어서 Network 의 부하가 생길때 혹은 모듈로서 공유를 해야될때 이다.

그럼 한번 만들어보자.
아래의 스크립트는 customers 테이블과 sales 테이블을 이용하여 MultiSet 스칼라 서브쿼리를 구현한것이다.
Cast + MultiSet 스칼라 서브쿼리를 사용하려면 먼저 type 2개를 만들어야 한다.


CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT
(
 PROD_COUNT NUMBER(5),
 CHANNEL_COUNT NUMBER(2),       --> 스칼라 서브쿼리가 RETURN 할 TYPE 선언
 AMOUNT_TOT NUMBER
)
;
  
CREATE OR REPLACE TYPE ARRAY_SALES_CUST  --> 위에서 선언한 TYPE 을 배열로 선언
AS VARRAY(1) OF SALES_CUST_TYPE ;       --> 고객별로 여러건이 RETURN 될 경우 값을 넉넉히 줄것.
         --> 아래의 예제는 고객당 1건만 RETURN 하므로 VARRAY 에 1을 준것이다.

이렇게 2개의 type 을 선언하면 모든 준비가 끝난다. Pipilined Funtion 의 코딩량에 비하면 사용하기가 훨신 간편함을 알수 있다. 그럼 이제 SQL 을 만들어 보자.

SELECT C.CUST_ID, C.CUST_YEAR_OF_BIRTH
             ,S.PROD_COUNT, S.CHANNEL_COUNT, S.AMOUNT_TOT
  FROM (
               SELECT /*+ FULL(C) */
                            C.CUST_ID, C.CUST_YEAR_OF_BIRTH,
                            CAST( MULTISET ( SELECT count(distinct s.PROD_ID)        AS PROD_COUNT
                                                                       ,count(distinct s.CHANNEL_ID)  AS CHANNEL_COUNT
                                                                       ,sum(s.AMOUNT_SOLD)           AS AMOUNT_TOT
                                                            FROM sh.sales s
                                                           WHERE s.cust_id = c.cust_id
                                                         ) AS  ARRAY_SALES_CUST --> 위에서 선언한 배열 TYPE 이용.
                                     ) AS SALES_CUST --> MultiSet 함수의 Alias 선언
                FROM sh.customers c
            )  C,
            TABLE(SALES_CUST) S                     --> 위에서 선언한 MultiSet 함수의 Alias 를 테이블로 이용함.
 WHERE S.PROD_COUNT  <> 0 ;    --> 스칼라 서브쿼리내의 특정컬럼을 조건절에 이용한 예제임.

SQL 의 결과 :

사용자 삽입 이미지















결과는 성공적이다. 성능면에서도 스칼라서브쿼리와 동일하다. 이것은 마치 스칼라서브쿼리의 장점과 PIPELINED TABLE 함수의 장점을 취한것과 같다. 하지만 대용량 배치 SQL 에서는 Hash 조인이나 Sort Merge 조인보다 성능이 좋지 못하므로 주의 해야한다.

결론 :
Cast + MultiSet 스칼라 서브쿼리는 부분범위 처리시 혹은 페이징 처리시에 탁월한 성능을 발휘하며 스칼라 서브쿼리의 단점인  Multi Column, Multi Row 을 RETURN 할수 있다. 뿐만아니라 View Merging 이나 JPPD (Join Predicate Push Down) 이 불가능할 경우의 훌륭한 해결책이 될수 있다.

P.S :
위의 예제는 Multi Column을 RETURN 하지만 Multi Row 를 RETURN 하지 않는다. 독자 여러분이 위의  Cast + MultiSet 스칼라 서브쿼리를 Multi Column, Multi Row 를 RETURN 하는것으로 바꾸어 보라. 백견이 불여일행 이다.

'Oracle > SQL Pattern' 카테고리의 다른 글

오라클 Regular Expressions 완전정복  (22) 2009.10.07
간단한 집합개념 Test  (7) 2009.07.06
스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기.  (7) 2009.06.29
Model 절에 대하여  (10) 2009.05.28
SQL 포기하지마라.  (7) 2009.05.28
Upgrade Select For Update  (3) 2009.05.19
Posted by extremedb

댓글을 달아 주세요

  1. 불량토끼.. 2009.07.20 14:53  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 읽고 보고...정보도 많이 얻고 있습니다...
    바쁜 와중에서도 블러그를 운영하는 열정 참 좋습니다..

    "스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기."를 읽다가...제가 아둔하여서...왜? 이렇게 복잡하게
    쿼리를 짜야하는지?....이렇게 짜면 뭤이 더 좋은지 잘 납득이 가질 않아서..이렇게 질문을 드립니다...

    뭐...워낙이 초보라서 잘 이해가 가지 않지만...이렇게 짜는 쿼리가 실무에 어떠한 도움이 되는지 좀더 이해하기
    쉽게 설명을 해 주시면 감사하겠습니다...

    참고로 이 블러그는 참 좋은 정보가 많은데...전문가에게는 많은 도움이 될것같습니다.. 좀 아쉬운 점이 있다면..
    일반적으로 프로젝트 실무에서 좀더 도움이 될 만한 보다 쉬운 지면을 할애해 주시면...불쌍한 민초(?)들이 보다 더
    많은 도움이 될 것 같습니다...감사합니다...수고하시고...늘 건강 하십시요...

    거북이를 싫어하는 불량토끼....

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.07.20 20:30 신고  댓글주소  수정/삭제

      스칼라 서브쿼리를 쓰지않으면 쿼리가 아래처럼 됩니다.

      select C.CUST_ID, C.CUST_YEAR_OF_BIRTH
      ,S.PROD_COUNT, S.CHANNEL_COUNT, S.AMOUNT_TOT
      from sh.customers c,
      (SELECT s.cust_id
      ,count(distinct s.PROD_ID) AS PROD_COUNT
      ,count(distinct s.CHANNEL_ID) AS CHANNEL_COUNT
      ,sum(s.AMOUNT_SOLD) AS AMOUNT_TOT
      FROM sh.sales s
      group by s.cust_id
      ) s
      where s.cust_id(+) = c.cust_id
      and S.PROD_COUNT(+) <> 0 ;

      건수가 많은 판매 테이블을 Full Scan 하여 Group BY 후에 조인을 하게 되므로 매우 느려질수 있습니다.
      또한 View Merging(뷰 해체) 이 발생해서 조건이 파고 든다고 해도 결국은 Group BY 를 해야하니 부분범위 처리시 매우 않좋아 집니다. 반면에 스칼라서브쿼리를 사용하면 전체 Group BY 는 피할수 있습니다. 하지만 이런 단점을 피하려고 스칼라 서브쿼리를 쓰게되면 Multi Column 을 처리할수도 없고 Multi Row 를 처리할수도 없기 때문에 대안이 MutiSet 을 이용한 스칼라 서브쿼리 인것입니다.

  2. 불량토끼.. 2009.07.22 09:33  댓글주소  수정/삭제  댓글쓰기

    답변감사합니다...하지만 여전히 풀리지 않는 의문이 하나 있는데....

    스칼라 서브쿼리를 쓰게되면...일반적으로 메인 쿼리의 레코드 수만큼 서브쿼리가 실행되는 것으로 알고 있습니다..
    제가 잘못 알고 있는 것인지...

    예를 들어서...메인쿼리의 고객수가 1000건이면...스칼라서브쿼리는 1000번이 실행되나요?? 아니면 한번만 실행이
    되나요??

    또한 일반적으로 사용할 때...스칼라서브쿼리는 1000번이 실행 되는데...예제처럼 멀티 로우를 사용하는 방식으로
    쓰게되면 1번만 실행되는 건지...궁금합니다..

    또한 일반적으로 쿼리에 펑션을 사용하면...레코드 수만큼 펑션이 콜 되므로 가능하면 일반적인 쿼리에는 펑션을
    사용하지 말도록 요구하는 것이 전문가들의 의견인데..어떻게 사용하는 것이 바람직 한지 궁금합니다..

    제 생각에는 인라인 뷰를 보다 효율적으로 작성을 하면 될것 같은데..지식이 짧아서...잘 모를겠습니다..ㅎㅎㅎ

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.07.22 13:51 신고  댓글주소  수정/삭제

      스칼라서브쿼리는 메인쿼리의 레코드 수만큼 실행되지 않습니다. 그것은 subquery caching 기능이 있기 때문입니다. 즉 메인쿼리가 1000 건이지만 스칼라서브쿼리의 결과는 10종류 밖에 되지 않을경우 정확히 10번만 수행합니다. 하지만 최악의 경우 스칼라서브쿼리의 결과가 메인쿼리의 결과 row 수인 1000 종류가 나온다면 1000 번 수행됩니다.

      Function 은 무조건 스칼라 서브쿼리보다 느립니다.
      Determinstic 을 명시적으로 지정한 경우는 이론적으로 스칼라 서브쿼리의 효과를 누릴수 있지만 대개의 경우 Funtion 내에서 select 를 하고 있으므로 Determinstic으로 지정하는것은 위험합니다.

      그리고 인라인뷰를 효율적으로 작성하신 SQL 을 저에게 보내주시기 바랍니다. 어떤 것인지 궁금하네요.^^

  3. 불량토끼.. 2009.07.22 13:20  댓글주소  수정/삭제  댓글쓰기

    ^^답변 감사합니다....

  4. Favicon of http://blog.naver.com/dooba202 BlogIcon 두바리 2010.05.19 08:14  댓글주소  수정/삭제  댓글쓰기

    평소 방법이 없을까 했던 차에 이렇게 좋은 방법을 알게 되었네요.
    내용좀 제 블로그로 가져갑니다~
    잘활용하겠습니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.05.20 10:28 신고  댓글주소  수정/삭제

      오라클 10g 등을 사용하고 계신다면 위의 방법보다 더좋은 것이 있습니다.
      아래의 링크를 참조하세요.
      http://scidb.tistory.com/100