"JPPD (Join Predicate PushDown)"의 두 판 사이의 차이
DB CAFE
(→JPPD(Join Predicate PushDown) 쿼리 변환이 동작하지 않는 사례) |
(→분석 함수가 포함된 뷰에 대한 JPPD 동작) |
||
(같은 사용자의 중간 판 7개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
+ | == JPPD(Join Predicate PushDown) 동작 조건 == | ||
+ | |||
+ | {{틀:고지상자 | ||
+ | |제목 = JPPD(Join Predicate PushDown) 동작 조건 | ||
+ | |내용 =# NL JOIN 에서만 발생 | ||
+ | # 후행 집합은 INLINE VIEW 나 VIEW 가 됨. | ||
+ | # 후행 집합과의 관계는 다음 조건일 경우에 | ||
+ | ## UNION ALL VIEW & UNION VIEW | ||
+ | ## OUTER JOIN VIEW | ||
+ | ## 랭킹 분석 함수를 사용한 VIEW | ||
+ | ## GROUP BY , DISTINCT 사용 VIEW | ||
+ | ## NL SEMI/ANTI JOIN VIEW | ||
+ | ## MULTI LEVEL VIEW | ||
+ | }} | ||
+ | ---- | ||
+ | |||
== JPPD(Join Predicate PushDown) 쿼리 변환이 동작하지 않는 사례 == | == JPPD(Join Predicate PushDown) 쿼리 변환이 동작하지 않는 사례 == | ||
JPPD(Join Predicate PushDown) 쿼리 변환은 힌트를 사용해도 동작하지 않는 경우 | JPPD(Join Predicate PushDown) 쿼리 변환은 힌트를 사용해도 동작하지 않는 경우 | ||
23번째 줄: | 39번째 줄: | ||
COMMIT; | COMMIT; | ||
</source> | </source> | ||
− | + | === JPPD 확인 플랜 === | |
− | + | * 실행 계획 3번에서 VIEW PUSHED PREDICATE 오퍼레이션을 확인할 수 있다. | |
<source lang=sql> | <source lang=sql> | ||
42번째 줄: | 58번째 줄: | ||
| 1 | NESTED LOOPS | | | | 1 | NESTED LOOPS | | | ||
| 2 | TABLE ACCESS FULL | T1 | | | 2 | TABLE ACCESS FULL | T1 | | ||
− | | 3 | VIEW PUSHED PREDICATE | | -- | + | | 3 | VIEW PUSHED PREDICATE | | -- JPPD 발생 |
|* 4 | FILTER | | | |* 4 | FILTER | | | ||
| 5 | SORT AGGREGATE | | | | 5 | SORT AGGREGATE | | | ||
49번째 줄: | 65번째 줄: | ||
------------------------------------------------- | ------------------------------------------------- | ||
</source> | </source> | ||
− | 아래 쿼리는 HAVING 절을 사용했기 때문에 JPPD가 동작하지 않는다. | + | |
+ | === GROUP BY HAVING 절 사용시 JPPD 미발생 === | ||
+ | |||
+ | * 아래 쿼리는 HAVING 절을 사용했기 때문에 JPPD가 동작하지 않는다. | ||
<source lang=sql> | <source lang=sql> | ||
-- 2-2 | -- 2-2 | ||
73번째 줄: | 92번째 줄: | ||
-------------------------------------- | -------------------------------------- | ||
</source> | </source> | ||
− | + | === JPPD 동작 유도 방법 (인라인뷰 중첩사용) === | |
− | 11.2 버전까지는 아래와 같이 인라인 뷰를 1단계 중첩시킨 후, 인라인 뷰를 아우터 조인하면 JPPD를 동작시킬 수 있다. | + | * 11.2 버전까지는 아래와 같이 인라인 뷰를 1단계 중첩시킨 후, 인라인 뷰를 아우터 조인하면 JPPD를 동작시킬 수 있다. |
<source lang=sql> | <source lang=sql> | ||
-- 2-3 | -- 2-3 | ||
96번째 줄: | 115번째 줄: | ||
| 2 | NESTED LOOPS OUTER | | | | 2 | NESTED LOOPS OUTER | | | ||
| 3 | TABLE ACCESS FULL | T1 | | | 3 | TABLE ACCESS FULL | T1 | | ||
− | | 4 | VIEW PUSHED PREDICATE | | -- | + | | 4 | VIEW PUSHED PREDICATE | | -- JPPD 발생 |
| 5 | VIEW | | | | 5 | VIEW | | | ||
|* 6 | FILTER | | | |* 6 | FILTER | | | ||
104번째 줄: | 123번째 줄: | ||
--------------------------------------------------- | --------------------------------------------------- | ||
</source> | </source> | ||
+ | === 이너조인 JPPD 발생(12c ~)=== | ||
12.1 이후 버전은 이너 조인으로도 JPPD가 동작한다. | 12.1 이후 버전은 이너 조인으로도 JPPD가 동작한다. | ||
<source lang=sql> | <source lang=sql> | ||
124번째 줄: | 144번째 줄: | ||
| 1 | NESTED LOOPS | | | | 1 | NESTED LOOPS | | | ||
| 2 | TABLE ACCESS FULL | T1 | | | 2 | TABLE ACCESS FULL | T1 | | ||
− | | 3 | VIEW PUSHED PREDICATE | | -- | + | | 3 | VIEW PUSHED PREDICATE | | -- JPPD 발생 |
| 4 | VIEW | | | | 4 | VIEW | | | ||
|* 5 | FILTER | | | |* 5 | FILTER | | | ||
132번째 줄: | 152번째 줄: | ||
---------------------------------------------------------- | ---------------------------------------------------------- | ||
</source> | </source> | ||
− | 아래 쿼리는 인라인 뷰에 UNION ALL 연산자가 포함되어 있지만, 실행 계획 4번 UNION ALL PUSHED PREDICATE 오퍼레이션에서 JPPD가 제대로 동작하는 것을 확인할 수 있다. | + | === UNION ALL에서 JPPD 발생 (12c ~) === |
+ | * 아래 쿼리는 인라인 뷰에 UNION ALL 연산자가 포함되어 있지만, 실행 계획 4번 UNION ALL PUSHED PREDICATE 오퍼레이션에서 JPPD가 제대로 동작하는 것을 확인할 수 있다. | ||
<source lang=sql> | <source lang=sql> | ||
-- 3-1 | -- 3-1 | ||
150번째 줄: | 171번째 줄: | ||
| 2 | TABLE ACCESS FULL | | | 2 | TABLE ACCESS FULL | | ||
| 3 | VIEW | | | 3 | VIEW | | ||
− | | 4 | UNION ALL PUSHED PREDICATE | -- | + | | 4 | UNION ALL PUSHED PREDICATE | |
+ | -- JPPD 발생 | ||
| 5 | TABLE ACCESS BY INDEX ROWID| | | 5 | TABLE ACCESS BY INDEX ROWID| | ||
|* 6 | INDEX RANGE SCAN | | |* 6 | INDEX RANGE SCAN | | ||
157번째 줄: | 179번째 줄: | ||
---------------------------------------- | ---------------------------------------- | ||
</source> | </source> | ||
− | 아래 쿼리는 인라인 뷰의 UNION ALL 쿼리에 GROUP BY 절을 사용했다. 11.2 버전까지 JPPD가 동작하지 않는다. | + | === UNION ALL + GROUP BY 사용시 JPPD 미발생 === |
+ | |||
+ | * 아래 쿼리는 인라인 뷰의 UNION ALL 쿼리에 GROUP BY 절을 사용했다. 11.2 버전까지 JPPD가 동작하지 않는다. | ||
+ | |||
<source lang=sql> | <source lang=sql> | ||
-- 3-2 | -- 3-2 | ||
182번째 줄: | 207번째 줄: | ||
-------------------------------------- | -------------------------------------- | ||
</source> | </source> | ||
− | 아래와 같이 인라인 뷰를 1단계 중첩시키면 JPPD를 동작시킬 수 있다. | + | |
+ | |||
+ | === UNION ALL + GROUP BY 사용시 JPPD 미발생 일때 ==> 인라인뷰 중첩으로 JPPD 발생 === | ||
+ | |||
+ | * 아래와 같이 인라인 뷰를 1단계 중첩시키면 JPPD를 동작시킬 수 있다. | ||
+ | |||
<source lang=sql> | <source lang=sql> | ||
-- 3-3 | -- 3-3 | ||
213번째 줄: | 243번째 줄: | ||
-------------------------------------------------- | -------------------------------------------------- | ||
</source> | </source> | ||
− | 아래 쿼리는 서브 쿼리 팩토링을 사용했다. 11.2 버전까지 JPPD가 동작하지 않는다. WITH 절을 사용하지 않고 인라인 뷰를 직접 기술해야 한다. | + | |
+ | === WITH절 + INLINE 힌트사용시 , 11g에서 JPPD 미발생 === | ||
+ | 아래 쿼리는 서브 쿼리 팩토링을 사용했다. | ||
+ | * 11.2 버전까지 JPPD가 동작하지 않는다. | ||
+ | * WITH 절을 사용하지 않고 인라인 뷰를 직접 기술해야 한다. | ||
<source lang=sql> | <source lang=sql> | ||
-- 4-1 | -- 4-1 | ||
233번째 줄: | 267번째 줄: | ||
------------------------------------- | ------------------------------------- | ||
</source> | </source> | ||
− | 12.1 이후 버전에서는 JPPD가 동작한다. | + | |
+ | === WITH절 + INLINE 힌트사용시 , 12c~ 에서 JPPD 발생 === | ||
+ | |||
+ | * 12.1 이후 버전에서는 JPPD가 동작한다. | ||
+ | |||
<source lang=sql> | <source lang=sql> | ||
-- 4-2 : 12.1 | -- 4-2 : 12.1 | ||
255번째 줄: | 293번째 줄: | ||
--------------------------------------------------------- | --------------------------------------------------------- | ||
</source> | </source> | ||
− | 메인 쿼리에 계층 쿼리 절을 사용해도 JPPD가 동작하지 않는다 | + | |
+ | === 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 === | ||
+ | * 메인 쿼리에 계층 쿼리 절을 사용해도 JPPD가 동작하지 않는다. | ||
<source lang=sql> | <source lang=sql> | ||
-- 5-1 | -- 5-1 | ||
282번째 줄: | 322번째 줄: | ||
---------------------------------------------------------- | ---------------------------------------------------------- | ||
</source> | </source> | ||
− | 칼럼이 적은 경우 아래와 같이 스칼라 서브 쿼리를 활용할 수 있다. | + | |
+ | === 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 1 (스칼라 서브쿼리)=== | ||
+ | |||
+ | * 칼럼이 적은 경우 아래와 같이 스칼라 서브 쿼리를 활용할 수 있다. | ||
+ | |||
<source lang=sql> | <source lang=sql> | ||
-- 5-2 | -- 5-2 | ||
311번째 줄: | 355번째 줄: | ||
----------------------------------------------------------- | ----------------------------------------------------------- | ||
</source> | </source> | ||
+ | |||
+ | === 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 2 (사용자 정의 타입 생성) === | ||
+ | |||
칼럼이 많은 경우 사용자 정의 타입을 사용할 수 있다. | 칼럼이 많은 경우 사용자 정의 타입을 사용할 수 있다. | ||
<source lang=sql> | <source lang=sql> | ||
344번째 줄: | 391번째 줄: | ||
----------------------------------------------------------- | ----------------------------------------------------------- | ||
</source> | </source> | ||
− | 사용 빈도가 높다면 PIPELINED 함수를 고려해 볼 수 있다. | + | |
+ | === 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 3 (PIPELINED 사용)=== | ||
+ | |||
+ | * 사용 빈도가 높다면 PIPELINED 함수를 고려해 볼 수 있다. | ||
<source lang=sql> | <source lang=sql> | ||
-- 5-4 | -- 5-4 | ||
384번째 줄: | 434번째 줄: | ||
---------------------------------------------------------- | ---------------------------------------------------------- | ||
</source> | </source> | ||
+ | |||
+ | === 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 4 (LATERAL 구문사용) === | ||
12.1 이후 버전은 LATERAL 인라인 뷰를 사용하면 손쉽게 JPPD와 같은 효과를 얻을 수 있다. | 12.1 이후 버전은 LATERAL 인라인 뷰를 사용하면 손쉽게 JPPD와 같은 효과를 얻을 수 있다. | ||
<source lang=sql> | <source lang=sql> | ||
420번째 줄: | 472번째 줄: | ||
</source> | </source> | ||
− | 분석 함수가 포함된 뷰에 대한 JPPD 동작 | + | === 분석 함수가 포함된 뷰에 대한 JPPD 동작 === |
+ | |||
+ | # 테스트를 위해 아래와 같이 테이블과 뷰를 생성 | ||
+ | # ROW_NUMBER 함수는 c1 열로 파티션을 지정 | ||
− | |||
<source lang=sql> | <source lang=sql> | ||
-- 1 | -- 1 | ||
437번째 줄: | 491번째 줄: | ||
SELECT c1, c2, c3, ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c3) AS rn FROM t2; | SELECT c1, c2, c3, ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c3) AS rn FROM t2; | ||
</source> | </source> | ||
− | 2-1번 쿼리는 파티션인 c1 열로 조인하여 JPPD가 동작하고, 2-2번 쿼리는 c2 열로 조인하여 JPPD가 동작하지 않는다. | + | |
+ | |||
+ | * 2-1번 쿼리는 파티션인 c1 열로 조인하여 JPPD가 동작하고, 2-2번 쿼리는 c2 열로 조인하여 JPPD가 동작하지 않는다. | ||
<source lang=sql> | <source lang=sql> | ||
-- 2-1 | -- 2-1 | ||
474번째 줄: | 530번째 줄: | ||
------------------------------------- | ------------------------------------- | ||
</source> | </source> | ||
− | 아래 쿼리는 Top-N을 수행한다. 3-1번 쿼리에서 WINDOW SORT PUSHED RANK 오퍼레이션이 동작하는 것을 확인할 수 있다. 인덱스로 소트를 대체할 수 있다면 4-2번 쿼리처럼 WINDOW BUFFER PUSHED RANK 오퍼레이션도 가능하다. | + | |
+ | 아래 쿼리는 Top-N을 수행한다. | ||
+ | 3-1번 쿼리에서 WINDOW SORT PUSHED RANK 오퍼레이션이 동작하는 것을 확인할 수 있다. | ||
+ | 인덱스로 소트를 대체할 수 있다면 4-2번 쿼리처럼 WINDOW BUFFER PUSHED RANK 오퍼레이션도 가능하다. | ||
+ | |||
<source lang=sql> | <source lang=sql> | ||
-- 3-1 | -- 3-1 | ||
517번째 줄: | 577번째 줄: | ||
</source> | </source> | ||
* 참조) https://hrjeong.tistory.com/241 | * 참조) https://hrjeong.tistory.com/241 | ||
+ | [[category:oracle]] |
2023년 10월 13일 (금) 15:19 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 JPPD(Join Predicate PushDown) 동작 조건
- 2 JPPD(Join Predicate PushDown) 쿼리 변환이 동작하지 않는 사례
- 2.1 JPPD 확인 플랜
- 2.2 GROUP BY HAVING 절 사용시 JPPD 미발생
- 2.3 JPPD 동작 유도 방법 (인라인뷰 중첩사용)
- 2.4 이너조인 JPPD 발생(12c ~)
- 2.5 UNION ALL에서 JPPD 발생 (12c ~)
- 2.6 UNION ALL + GROUP BY 사용시 JPPD 미발생
- 2.7 UNION ALL + GROUP BY 사용시 JPPD 미발생 일때 ==> 인라인뷰 중첩으로 JPPD 발생
- 2.8 WITH절 + INLINE 힌트사용시 , 11g에서 JPPD 미발생
- 2.9 WITH절 + INLINE 힌트사용시 , 12c~ 에서 JPPD 발생
- 2.10 계층쿼리(CONNECT BY) 사용시 JPPD 미발생
- 2.11 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 1 (스칼라 서브쿼리)
- 2.12 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 2 (사용자 정의 타입 생성)
- 2.13 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 3 (PIPELINED 사용)
- 2.14 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 4 (LATERAL 구문사용)
- 2.15 분석 함수가 포함된 뷰에 대한 JPPD 동작
1 JPPD(Join Predicate PushDown) 동작 조건[편집]
2 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;
2.1 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 | | -- JPPD 발생
|* 4 | FILTER | |
| 5 | SORT AGGREGATE | |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 7 | INDEX RANGE SCAN | T2_X1 |
-------------------------------------------------
2.2 GROUP BY HAVING 절 사용시 JPPD 미발생[편집]
- 아래 쿼리는 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 |
--------------------------------------
2.3 JPPD 동작 유도 방법 (인라인뷰 중첩사용)[편집]
- 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 | | -- JPPD 발생
| 5 | VIEW | |
|* 6 | FILTER | |
| 7 | SORT GROUP BY | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 9 | INDEX RANGE SCAN | T2_X1 |
---------------------------------------------------
2.4 이너조인 JPPD 발생(12c ~)[편집]
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 | | -- JPPD 발생
| 4 | VIEW | |
|* 5 | FILTER | |
| 6 | SORT GROUP BY | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
|* 8 | INDEX RANGE SCAN | T2_X1 |
----------------------------------------------------------
2.5 UNION ALL에서 JPPD 발생 (12c ~)[편집]
- 아래 쿼리는 인라인 뷰에 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 |
-- JPPD 발생
| 5 | TABLE ACCESS BY INDEX ROWID|
|* 6 | INDEX RANGE SCAN |
| 7 | TABLE ACCESS BY INDEX ROWID|
|* 8 | INDEX RANGE SCAN |
----------------------------------------
2.6 UNION ALL + GROUP BY 사용시 JPPD 미발생[편집]
- 아래 쿼리는 인라인 뷰의 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 |
--------------------------------------
2.7 UNION ALL + GROUP BY 사용시 JPPD 미발생 일때 ==> 인라인뷰 중첩으로 JPPD 발생[편집]
- 아래와 같이 인라인 뷰를 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 |
--------------------------------------------------
2.8 WITH절 + INLINE 힌트사용시 , 11g에서 JPPD 미발생[편집]
아래 쿼리는 서브 쿼리 팩토링을 사용했다.
- 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 |
-------------------------------------
2.9 WITH절 + INLINE 힌트사용시 , 12c~ 에서 JPPD 발생[편집]
- 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 |
---------------------------------------------------------
2.10 계층쿼리(CONNECT BY) 사용시 JPPD 미발생[편집]
- 메인 쿼리에 계층 쿼리 절을 사용해도 JPPD가 동작하지 않는다.
-- 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 |
----------------------------------------------------------
2.11 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 1 (스칼라 서브쿼리)[편집]
- 칼럼이 적은 경우 아래와 같이 스칼라 서브 쿼리를 활용할 수 있다.
-- 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 |
-----------------------------------------------------------
2.12 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 2 (사용자 정의 타입 생성)[편집]
칼럼이 많은 경우 사용자 정의 타입을 사용할 수 있다.
-- 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 |
-----------------------------------------------------------
2.13 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 3 (PIPELINED 사용)[편집]
- 사용 빈도가 높다면 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 |
----------------------------------------------------------
2.14 계층쿼리(CONNECT BY) 사용시 JPPD 미발생 개선방안 4 (LATERAL 구문사용)[편집]
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 |
---------------------------------------------------------------------
2.15 분석 함수가 포함된 뷰에 대한 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 |
------------------------------------------------