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 |
---------------------------------------------------------------------'Oracle > Tuning' 카테고리의 다른 글
| CLUSTER_BY_ROWID 힌트 (0) | 2018.11.03 |
|---|---|
| 계층 쿼리 절의 수행 방식 (0) | 2018.11.03 |
| Join Elimination 쿼리 변환이 동작하지 않는 사례 (0) | 2018.11.03 |
| 사용자 정의 함수의 동작 방식 (0) | 2018.10.27 |
| 사용자 정의 함수의 실행 계획 (0) | 2018.10.27 |
