"JPPD (Join Predicate PushDown)"의 두 판 사이의 차이
DB CAFE
(→JPPD(Join Predicate PushDown) 쿼리 변환이 동작하지 않는 사례) |
|||
418번째 줄: | 418번째 줄: | ||
|* 8 | INDEX RANGE SCAN | T2_X1 | | |* 8 | INDEX RANGE SCAN | T2_X1 | | ||
--------------------------------------------------------------------- | --------------------------------------------------------------------- | ||
+ | </source> | ||
+ | |||
+ | 분석 함수가 포함된 뷰에 대한 JPPD 동작 | ||
+ | |||
+ | 테스트를 위해 아래와 같이 테이블과 뷰를 생성하자. ROW_NUMBER 함수는 c1 열로 파티션을 지정했다. | ||
+ | <source lang=sql> | ||
+ | -- 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; | ||
+ | </source> | ||
+ | 2-1번 쿼리는 파티션인 c1 열로 조인하여 JPPD가 동작하고, 2-2번 쿼리는 c2 열로 조인하여 JPPD가 동작하지 않는다. | ||
+ | <source lang=sql> | ||
+ | -- 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 | | ||
+ | ------------------------------------- | ||
+ | </source> | ||
+ | 아래 쿼리는 Top-N을 수행한다. 3-1번 쿼리에서 WINDOW SORT PUSHED RANK 오퍼레이션이 동작하는 것을 확인할 수 있다. 인덱스로 소트를 대체할 수 있다면 4-2번 쿼리처럼 WINDOW BUFFER PUSHED RANK 오퍼레이션도 가능하다. | ||
+ | <source lang=sql> | ||
+ | -- 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 | | ||
+ | ------------------------------------------------ | ||
+ | </source> | ||
+ | <source lang=sql> | ||
+ | -- 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 | | ||
+ | ------------------------------------------------ | ||
</source> | </source> | ||
* 참조) https://hrjeong.tistory.com/241 | * 참조) https://hrjeong.tistory.com/241 |
2021년 11월 26일 (금) 11:07 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
JPPD(Join Predicate PushDown) 쿼리 변환이 동작하지 않는 사례[편집]
JPPD(Join Predicate PushDown) 쿼리 변환은 힌트를 사용해도 동작하지 않는 경우
- 테스트용 테이블 생성
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);
CREATE TABLE t2 (c1 NUMBER, c2 NUMBER, c3 NUMBER);
CREATE TABLE t3 (c1 NUMBER, c2 NUMBER, c3 NUMBER);
CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t3_x1 ON t3 (c1);
INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (2, 3);
INSERT INTO t2 VALUES (1, 1, 1);
INSERT INTO t2 VALUES (1, 1, 1);
INSERT INTO t3 VALUES (2, 2, 2);
INSERT INTO t3 VALUES (2, 2, 2);
COMMIT;
아래 쿼리는 JPPD가 제대로 동작한다. 실행 계획 3번에서 VIEW PUSHED PREDICATE 오퍼레이션을 확인할 수 있다.
-- 2-1
SELECT /*+ LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) */
a.c1, b.c2, b.c3
FROM t1 a
, (SELECT c1, SUM (c2) AS c2, SUM (c3) AS c3
FROM t2
GROUP BY 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 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 7 | INDEX RANGE SCAN | T2_X1 |
-------------------------------------------------
아래 쿼리는 HAVING 절을 사용했기 때문에 JPPD가 동작하지 않는다.
-- 2-2
SELECT /*+ LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) */
a.c1, b.c2, b.c3
FROM t1 a
, (SELECT c1, SUM (c2) AS c2, SUM (c3) AS c3
FROM t2
GROUP BY c1
HAVING SUM (c2) > 0) b
WHERE b.c1 = a.c1;
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | T1 |
|* 3 | VIEW | | -- !
|* 4 | FILTER | |
| 5 | SORT GROUP BY | |
| 6 | TABLE ACCESS FULL| T2 |
--------------------------------------
11.2 버전까지는 아래와 같이 인라인 뷰를 1단계 중첩시킨 후, 인라인 뷰를 아우터 조인하면 JPPD를 동작시킬 수 있다.
-- 2-3
SELECT /*+ LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) NO_OUTER_JOIN_TO_INNER */
a.c1, b.c2, b.c3
FROM t1 a
, (SELECT *
FROM (SELECT /*+ NO_MERGE */
c1, SUM (c2) AS c2, SUM (c3) AS c3
FROM t2
GROUP BY c1
HAVING SUM (c2) > 0)) b
WHERE b.c1(+) = a.c1
AND b.c1 IS NOT NULL;
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | NESTED LOOPS OUTER | |
| 3 | TABLE ACCESS FULL | T1 |
| 4 | VIEW PUSHED PREDICATE | | -- !
| 5 | VIEW | |
|* 6 | FILTER | |
| 7 | SORT GROUP BY | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 9 | INDEX RANGE SCAN | T2_X1 |
---------------------------------------------------
12.1 이후 버전은 이너 조인으로도 JPPD가 동작한다.
-- 2-4
SELECT /*+ LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) */
a.c1, b.c2, b.c3
FROM t1 a
, (SELECT *
FROM (SELECT /*+ NO_MERGE */
c1, SUM (c2) AS c2, SUM (c3) AS c3
FROM t2
GROUP BY c1
HAVING SUM (c2) > 0)) 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 | VIEW | |
|* 5 | FILTER | |
| 6 | SORT GROUP BY | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
|* 8 | INDEX RANGE SCAN | T2_X1 |
----------------------------------------------------------
아래 쿼리는 인라인 뷰에 UNION ALL 연산자가 포함되어 있지만, 실행 계획 4번 UNION ALL PUSHED PREDICATE 오퍼레이션에서 JPPD가 제대로 동작하는 것을 확인할 수 있다.
-- 3-1
SELECT /*+ LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) */
a.c1, b.c2, b.c3
FROM t1 a
, (SELECT * FROM t2
UNION ALL
SELECT * FROM t3) b
WHERE b.c1 = a.c1;
----------------------------------------
| Id | Operation |
----------------------------------------
| 0 | SELECT STATEMENT |
| 1 | NESTED LOOPS |
| 2 | TABLE ACCESS FULL |
| 3 | VIEW |
| 4 | UNION ALL PUSHED PREDICATE | -- !
| 5 | TABLE ACCESS BY INDEX ROWID|
|* 6 | INDEX RANGE SCAN |
| 7 | TABLE ACCESS BY INDEX ROWID|
|* 8 | INDEX RANGE SCAN |
----------------------------------------
아래 쿼리는 인라인 뷰의 UNION ALL 쿼리에 GROUP BY 절을 사용했다. 11.2 버전까지 JPPD가 동작하지 않는다.
-- 3-2
SELECT /*+ LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) */
a.c1, b.c2, b.c3
FROM t1 a
, (SELECT c1 , SUM (c2) AS c2, SUM (c3) AS c3 FROM t2 GROUP BY c1
UNION ALL
SELECT c1 , SUM (c2) AS c2, SUM (c3) AS c3 FROM t3 GROUP BY c1) b
WHERE b.c1 = a.c1;
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | T1 |
|* 3 | VIEW | | -- !
| 4 | UNION-ALL | |
| 5 | SORT GROUP BY | |
| 6 | TABLE ACCESS FULL| T2 |
| 7 | SORT GROUP BY | |
| 8 | TABLE ACCESS FULL| T3 |
--------------------------------------
아래와 같이 인라인 뷰를 1단계 중첩시키면 JPPD를 동작시킬 수 있다.
-- 3-3
SELECT /*+ LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) */
a.c1, b.c2, b.c3
FROM t1 a
, (SELECT *
FROM (SELECT /*+ NO_MERGE */ c1, SUM (c2) AS c2, SUM (c3) AS c3 FROM t2 GROUP BY c1)
UNION ALL
SELECT *
FROM (SELECT /*+ NO_MERGE */ c1, SUM (c2) AS c2, SUM (c3) AS c3 FROM t3 GROUP BY c1)) b
WHERE b.c1 = a.c1;
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | T1 |
| 3 | VIEW | |
| 4 | UNION ALL PUSHED PREDICATE | | -- !
| 5 | VIEW | |
| 6 | SORT GROUP BY | |
| 7 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 8 | INDEX RANGE SCAN | T2_X1 |
| 9 | VIEW | |
| 10 | SORT GROUP BY | |
| 11 | TABLE ACCESS BY INDEX ROWID| T3 |
|* 12 | INDEX RANGE SCAN | T3_X1 |
--------------------------------------------------
아래 쿼리는 서브 쿼리 팩토링을 사용했다. 11.2 버전까지 JPPD가 동작하지 않는다. WITH 절을 사용하지 않고 인라인 뷰를 직접 기술해야 한다.
-- 4-1
WITH w1 AS (SELECT /*+ INLINE */ c1, SUM (c2) AS c2, SUM (c3) AS c3 FROM t2 GROUP BY c1)
SELECT /*+ LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) */
a.c1, b.c2, b.c3
FROM t1 a, w1 b
WHERE b.c1 = a.c1;
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | T1 |
|* 3 | VIEW | | -- !
| 4 | SORT GROUP BY | |
| 5 | TABLE ACCESS FULL| T2 |
-------------------------------------
12.1 이후 버전에서는 JPPD가 동작한다.
-- 4-2 : 12.1
WITH w1 AS (SELECT /*+ INLINE */ c1, SUM (c2) AS c2, SUM (c3) AS c3 FROM t2 GROUP BY c1)
SELECT /*+ LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) */
a.c1, b.c2, b.c3
FROM t1 a, w1 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 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
|* 7 | INDEX RANGE SCAN | T2_X1 |
---------------------------------------------------------
메인 쿼리에 계층 쿼리 절을 사용해도 JPPD가 동작하지 않는다. 11.2 버전까지는 해법이 조금 복잡하다.
-- 5-1
SELECT /*+ LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) */
a.c1, b.c2, b.c3
FROM (SELECT *
FROM t1
START WITH c1 = 1
CONNECT BY c1 = PRIOR c2) a
, (SELECT c1, SUM (c2) AS c2, SUM (c3) AS c3
FROM t2
GROUP BY c1) b
WHERE b.c1 = a.c1;
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | VIEW | |
|* 3 | CONNECT BY NO FILTERING WITH START-WITH| |
| 4 | TABLE ACCESS FULL | T1 |
|* 5 | VIEW | | -- !
| 6 | SORT GROUP BY | |
| 7 | TABLE ACCESS FULL | T2 |
----------------------------------------------------------
칼럼이 적은 경우 아래와 같이 스칼라 서브 쿼리를 활용할 수 있다.
-- 5-2
SELECT c1
, TO_NUMBER (REGEXP_SUBSTR (t2, '[^!]+', 1, 2)) AS c2
, TO_NUMBER (REGEXP_SUBSTR (t2, '[^!]+', 1, 3)) AS c3
FROM (SELECT a.*
, (SELECT MAX (x.c1) || '!' || SUM (x.c2) || '!' || SUM (x.c3)
FROM t2 x
WHERE x.c1 = a.c1) AS t2
FROM (SELECT *
FROM t1
START WITH c1 = 1
CONNECT BY c1 = PRIOR c2) a)
WHERE REGEXP_SUBSTR (t2, '[^!]+', 1, 1) IS NOT NULL;
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID | T2 |
|* 3 | INDEX RANGE SCAN | T2_X1 |
|* 4 | VIEW | |
| 5 | VIEW | |
|* 6 | CONNECT BY NO FILTERING WITH START-WITH| |
| 7 | TABLE ACCESS FULL | T1 |
-----------------------------------------------------------
칼럼이 많은 경우 사용자 정의 타입을 사용할 수 있다.
-- 5-3
DROP TYPE trc1;
CREATE OR REPLACE TYPE trc1 AS OBJECT (c1 NUMBER, c2 NUMBER, c3 NUMBER);
/
SELECT a.c1
, a.t2.c2 AS c2
, a.t2.c3 AS c3
FROM (SELECT a.*
, (SELECT trc1 (MAX (x.c1), SUM (x.c2), SUM (x.c3))
FROM t2 x
WHERE x.c1 = a.c1) AS t2
FROM (SELECT *
FROM t1
START WITH c1 = 1
CONNECT BY c1 = PRIOR c2) a) a
WHERE a.t2.c1 IS NOT NULL;
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID | T2 |
|* 3 | INDEX RANGE SCAN | T2_X1 |
|* 4 | VIEW | |
| 5 | VIEW | |
|* 6 | CONNECT BY NO FILTERING WITH START-WITH| |
| 7 | TABLE ACCESS FULL | T1 |
-----------------------------------------------------------
사용 빈도가 높다면 PIPELINED 함수를 고려해 볼 수 있다.
-- 5-4
DROP TYPE tnt1;
CREATE OR REPLACE TYPE tnt1 IS TABLE OF trc1;
/
CREATE OR REPLACE FUNCTION fnc1 (i_c1 IN VARCHAR2) RETURN tnt1 PIPELINED
AS
l_t2 trc1;
BEGIN
FOR c1 IN (SELECT c1, SUM (c2) AS c2, SUM (c3) AS c3
FROM t2
WHERE c1 = i_c1
GROUP BY c1)
LOOP
l_t2 := trc1 (c1.c1, c1.c2, c1.c3);
PIPE ROW (l_t2);
END LOOP;
END fnc1;
/
SELECT a.c1, b.c2, b.c3
FROM (SELECT *
FROM t1
START WITH c1 = 1
CONNECT BY c1 = PRIOR c2) a
, TABLE (fnc1 (a.c1)) b;
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | VIEW | |
|* 3 | CONNECT BY NO FILTERING WITH START-WITH| |
| 4 | TABLE ACCESS FULL | T1 |
| 5 | COLLECTION ITERATOR PICKLER FETCH | FNC1 |
----------------------------------------------------------
12.1 이후 버전은 LATERAL 인라인 뷰를 사용하면 손쉽게 JPPD와 같은 효과를 얻을 수 있다.
-- 5-5
SELECT /*+ LEADING(A) USE_NL(B) NO_MERGE(B) PUSH_PRED(B) */
a.c1, b.c2, b.c3
FROM (SELECT *
FROM t1
START WITH c1 = 1
CONNECT BY c1 = PRIOR c2) a
, LATERAL
(SELECT SUM (x.c2) AS c2, SUM (x.c3) AS c3
FROM t2 x
WHERE x.c1 = a.c1
GROUP BY x.c1) b;
C1 C2 C3
-- -- --
1 1 1
1개의 행이 선택되었습니다.
---------------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | VIEW | |
|* 3 | CONNECT BY NO FILTERING WITH START-WITH| |
| 4 | TABLE ACCESS FULL | T1 |
| 5 | VIEW | VW_LAT_A18161FF |
| 6 | SORT GROUP BY | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 |
|* 8 | INDEX RANGE SCAN | T2_X1 |
---------------------------------------------------------------------
분석 함수가 포함된 뷰에 대한 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 |
------------------------------------------------