스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기.

부분범위 처리 혹은 페이징 처리시 탁월한 힘을 발휘하는것이 스칼라 서브쿼리이다. 여러가지 테스트 결과 스칼라 서브쿼리는 부분범위처리시 가장 좋다는 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 하는것으로 바꾸어 보라. 백견이 불여일행 이다.