다중 열 다중 행 스칼라 서브 쿼리

다중 열 다중 행 스칼라 서브 쿼리에 대한 내용을 살펴보자.

예제를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT LEVEL as c1 FROM DUAL CONNECT BY LEVEL <= 3;

CREATE TABLE t2 AS
SELECT     CEIL (LEVEL / 3) AS c1, LEVEL AS c2, 10 - LEVEL AS c3
      FROM DUAL
CONNECT BY LEVEL <= 9;

CREATE INDEX t2_x1 ON t2 (c1, c2);

아래 쿼리는 다중 열 스칼라 서브 쿼리의 예제다. 고정 길이와 결합 연산자를 사용한 전통적 방식이다.

-- 2-1
SELECT a.c1
     , TO_NUMBER (SUBSTR (a.c2,  1, 10)) AS c2_min
     , TO_NUMBER (SUBSTR (a.c2, 11, 10)) AS c2_max
  FROM (SELECT a.c1
             , (SELECT LPAD (MIN (x.c2), 10, '0') || LPAD (MAX (x.c2), 10, '0')
                  FROM t2 x
                 WHERE x.c1 = a.c1) AS c2
          FROM t1 a) a;

C1 C2_MIN C2_MAX
-- ------ ------
1       1      3
2       4      6
3       7      9

3 행이 선택되었습니다.

-----------------------------------
| Id  | Operation         | Name  |
-----------------------------------
|   0 | SELECT STATEMENT  |       |
|   1 |  SORT AGGREGATE   |       |
|*  2 |   INDEX RANGE SCAN| T2_X1 |
|   3 |  TABLE ACCESS FULL| T1    |
-----------------------------------

아래 쿼리는 정규 표현식으로 2-1번 쿼리를 개선한 방식이다. 

-- 2-2
SELECT a.c1
     , TO_NUMBER (REGEXP_SUBSTR (a.c2, '[^,]+', 1, 1)) AS c2_min
     , TO_NUMBER (REGEXP_SUBSTR (a.c2, '[^,]+', 1, 2)) AS c2_max
  FROM (SELECT a.c1
             , (SELECT MIN (x.c2) || ',' || MAX (x.c2)
                  FROM t2 x
                 WHERE x.c1 = a.c1) AS c2
          FROM t1 a) a;

열의 개수가 많은 경우 아래와 같이 레코드 타입을 사용할 수 있다.

-- 2-3
CREATE OR REPLACE TYPE trc1 AS OBJECT (c2_min NUMBER, c2_max NUMBER);
/

SELECT a.c1, a.c2.c2_min, a.c2.c2_max
  FROM (SELECT a.c1
             , (SELECT trc1 (MIN (x.c2), MAX (x.c2))
                  FROM t2 x
                 WHERE x.c1 = a.c1) AS c2
          FROM t1 a) a;

11.2 버전부터는 JPPD(Join Predicate Push Down) 쿼리 변환을 사용할 수 있다.

-- 2-4
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.1') LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) */
       a.c1, b.c2_min, b.c2_max
  FROM t1 a
     , (SELECT   a.c1, MIN (a.c2) AS c2_min, MAX (a.c2) AS c2_max
            FROM t2 a
        GROUP BY a.c1) b
 WHERE b.c1 = a.c1;

-----------------------------------------
| Id  | Operation               | Name  |
-----------------------------------------
|   0 | SELECT STATEMENT        |       |
|   1 |  NESTED LOOPS           |       |
|   2 |   TABLE ACCESS FULL     | T1    |
|   3 |   VIEW PUSHED PREDICATE |       |
|*  4 |    FILTER               |       |
|   5 |     SORT AGGREGATE      |       |
|*  6 |      INDEX RANGE SCAN   | T2_X1 |
-----------------------------------------

아래 쿼리는 다중 열 다중 행 스칼라 서브 쿼리의 예제다. 레코드 타입과 중첩 테이블 타입을 사용했다.

-- 3-1
CREATE OR REPLACE TYPE trc2 AS OBJECT (c2 NUMBER, c3 NUMBER);
/

CREATE OR REPLACE TYPE tnt2 AS TABLE OF trc2;
/

SELECT a.c1, b.c2, b.c3
  FROM (SELECT a.c1
             , CAST (MULTISET ((SELECT y.c2, y.c3
                                  FROM (SELECT   x.*
                                            FROM t2 x
                                        ORDER BY c2 DESC) y
                                 WHERE y.c1 = a.c1
                                   AND ROWNUM <= 2)) AS tnt2) AS t2
          FROM t1 a) a
     , TABLE (t2) b;

C1 C2 C3
-- -- --
 1  3  7
 1  2  8
 2  6  4
 2  5  5
 3  9  1
 3  8  2

6 행이 선택되었습니다.

-----------------------------------------------------
| Id  | Operation                           | Name  |
-----------------------------------------------------
|   0 | SELECT STATEMENT                    |       |
|   1 |  NESTED LOOPS                       |       |
|   2 |   TABLE ACCESS FULL                 | T1    |
|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|       |
|*  4 |    COUNT STOPKEY                    |       |
|   5 |     VIEW                            |       |
|   6 |      TABLE ACCESS BY INDEX ROWID    | T2    |
|*  7 |       INDEX RANGE SCAN DESCENDING   | T2_X1 |
-----------------------------------------------------

아래 쿼리는 JPPD 쿼리 변환을 사용했다.

-- 3-2
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.1') ORDERED USE_NL(B) PUSH_PRED(B) */
       a.c1, b.c2, b.c3
  FROM t1 a
     , (SELECT   a.*
               , ROW_NUMBER () OVER (PARTITION BY a.c1 ORDER BY a.c2 DESC) AS rn
            FROM t2 a
        ORDER BY a.c2 DESC) b
 WHERE b.c1 = a.c1
   AND b.rn <= 2;

-------------------------------------------------
| Id  | Operation                       | Name  |
-------------------------------------------------
|   0 | SELECT STATEMENT                |       |
|   1 |  NESTED LOOPS                   |       |
|   2 |   TABLE ACCESS FULL             | T1    |
|*  3 |   VIEW PUSHED PREDICATE         |       |
|   4 |    WINDOW BUFFER                |       |
|   5 |     TABLE ACCESS BY INDEX ROWID | T2    |
|*  6 |      INDEX RANGE SCAN DESCENDING| T2_X1 |
-------------------------------------------------