JPPD와 분석 함수

분석 함수가 포함된 뷰에 대한 JPPD 동작을 살펴보자.

테스트를 위해 아래와 같이 테이블과 뷰를 생성하자. ROW_NUMBER 함수는 c1 열로 파티션을 지정했다.

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

CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2, ROWNUM AS c3 FROM XMLTABLE ('1 to 1000');
CREATE TABLE t2 AS SELECT * FROM t1;

CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t2_x2 ON t2 (c2);

CREATE OR REPLACE VIEW v2 AS
SELECT c1, c2, c3, ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c3) AS rn FROM t2;

2-1번 쿼리는 파티션인 c1 열로 조인하여 JPPD가 동작하고, 2-2번 쿼리는 c2 열로 조인하여 JPPD가 동작하지 않는다.

-- 2-1
SELECT /*+ LEADING(A) USE_NL(A B) */
       *
  FROM t1 a, v2 b
 WHERE b.c1 = a.c1;

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

-- 2-2
SELECT /*+ LEADING(A) USE_NL(A B) */
       *
  FROM t1 a, v2 b
 WHERE b.c2 = a.c2;

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  NESTED LOOPS        |      |
|   2 |   TABLE ACCESS FULL  | T1   |
|*  3 |   VIEW               | V2   | -- !
|   4 |    WINDOW SORT       |      |
|   5 |     TABLE ACCESS FULL| T2   |
-------------------------------------

아래 쿼리는 Top-N을 수행한다. 3-1번 쿼리에서 WINDOW SORT PUSHED RANK 오퍼레이션이 동작하는 것을 확인할 수 있다. 인덱스로 소트를 대체할 수 있다면 4-2번 쿼리처럼 WINDOW BUFFER PUSHED RANK 오퍼레이션도 가능하다.

-- 3-1
SELECT /*+ LEADING(A) USE_NL(A B) */
       *
  FROM t1 a, v2 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        | V2    |
|*  4 |    WINDOW SORT PUSHED RANK     |       |
|   5 |     TABLE ACCESS BY INDEX ROWID| T2    |
|*  6 |      INDEX RANGE SCAN          | T2_X1 |
------------------------------------------------

-- 4-2
CREATE INDEX t2_x3 ON t2 (c1, c3);

SELECT /*+ LEADING(A) USE_NL(A B) */
       *
  FROM t1 a, v2 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        | V2    |
|*  4 |    WINDOW BUFFER PUSHED RANK   |       |
|   5 |     TABLE ACCESS BY INDEX ROWID| T2    |
|*  6 |      INDEX RANGE SCAN          | T2_X3 |
------------------------------------------------