다중 행 다중 열 스칼라 서브 쿼리의 부분 범위 처리

예전 글에 이어 다중 열, 다중 행 스칼라 서브 쿼리 부분 범위 처리에 대한 내용을 살펴보자.

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

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM as c1 FROM XMLTABLE ('1 to 3');
CREATE TABLE t2 AS SELECT CEIL (ROWNUM / 3) AS c1 , ROWNUM AS c2 , 10 - ROWNUM AS c3 FROM XMLTABLE ('1 to 9');
CREATE INDEX t2_x1 ON t2 (c1, c2);

아래 쿼리는 컬렉션 타입을 사용했다. 부분 범위 처리가 가능한 것을 확인할 수 있다.(A-Rows = 6)

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

유형이 생성되었습니다.

CREATE OR REPLACE TYPE tnt1 IS TABLE OF trc1;
/

유형이 생성되었습니다.

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 tnt1) AS t2_m
          FROM t1 a) a
     , TABLE (t2_m) 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  | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |      1 |      6 |      18 |
|   1 |  NESTED LOOPS                       |       |      1 |      6 |      18 |
|   2 |   TABLE ACCESS FULL                 | T1    |      1 |      3 |      10 |
|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|       |      3 |      6 |       8 |
|*  4 |    COUNT STOPKEY                    |       |      3 |      6 |       8 |
|   5 |     VIEW                            |       |      3 |      6 |       8 |
|   6 |      TABLE ACCESS BY INDEX ROWID    | T2    |      3 |      6 |       8 | -- !
|*  7 |       INDEX RANGE SCAN DESCENDING   | T2_X1 |      3 |      6 |       4 |
---------------------------------------------------------------------------------

JPPD는 부분 범위 처리가 불가능하다.(A-Rows = 8)

-- 3-2
SELECT /*+ 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  | Starts | A-Rows | Buffers | Used-Mem |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |      1 |      6 |      14 |          |
|   1 |  NESTED LOOPS                   |       |      1 |      6 |      14 |          |
|   2 |   TABLE ACCESS FULL             | T1    |      1 |      3 |      10 |          |
|*  3 |   VIEW PUSHED PREDICATE         |       |      3 |      6 |       4 |          |
|   4 |    WINDOW BUFFER                |       |      3 |      9 |       4 | 2048  (0)|
|   5 |     TABLE ACCESS BY INDEX ROWID | T2    |      3 |      9 |       4 |          | -- !
|*  6 |      INDEX RANGE SCAN DESCENDING| T2_X1 |      3 |      9 |       2 |          |
----------------------------------------------------------------------------------------

12.1 버전부터 사용할 수 있는 LATERAL 뷰 역시 부분 범위 처리가 불가능하다.

-- 3-3
SELECT a.c1, b.c2, b.c3
  FROM t1 a
     , LATERAL
       (SELECT y.c2, y.c3
          FROM (SELECT   x.*
                    FROM t2 x
                   WHERE x.c1 = a.c1
                ORDER BY c2 DESC) y
         WHERE ROWNUM <= 2) b;

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Starts | A-Rows | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |      1 |      6 |      16 |          |
|   1 |  NESTED LOOPS                            |                 |      1 |      6 |      16 |          |
|   2 |   TABLE ACCESS FULL                      | T1              |      1 |      3 |      10 |          |
|   3 |   VIEW                                   | VW_LAT_A18161FF |      3 |      6 |       6 |          |
|*  4 |    COUNT STOPKEY                         |                 |      3 |      6 |       6 |          |
|   5 |     VIEW                                 |                 |      3 |      6 |       6 |          |
|*  6 |      SORT ORDER BY STOPKEY               |                 |      3 |      6 |       6 | 2048  (0)|
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2              |      3 |      9 |       6 |          | -- !
|*  8 |        INDEX RANGE SCAN                  | T2_X1           |      3 |      9 |       3 |          |
-----------------------------------------------------------------------------------------------------------

LATERAL 뷰에 ROW LIMITING 절을 사용해도 마찬가지다.

-- 3-4
SELECT a.c1, b.c2, b.c3
  FROM t1 a
     , LATERAL
       (SELECT   x.*
            FROM t2 x
           WHERE x.c1 = a.c1
        ORDER BY c2 DESC
           FETCH FIRST 2 ROWS ONLY) b;

----------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name            | Starts | A-Rows | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |      1 |      6 |      14 |          |
|   1 |  NESTED LOOPS                           |                 |      1 |      6 |      14 |          |
|   2 |   TABLE ACCESS FULL                     | T1              |      1 |      3 |      10 |          |
|   3 |   VIEW                                  | VW_LAT_A18161FF |      3 |      6 |       4 |          |
|*  4 |    VIEW                                 |                 |      3 |      6 |       4 |          |
|*  5 |     WINDOW SORT PUSHED RANK             |                 |      3 |      6 |       4 | 2048  (0)|
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| T2              |      3 |      9 |       4 |          | -- !
|*  7 |       INDEX RANGE SCAN                  | T2_X1           |      3 |      9 |       2 |          |
----------------------------------------------------------------------------------------------------------