꿈꾸는 개발자, DBA 커뮤니티 구루비

3.11 JPPD Outer Join View : 뷰에 Outer 조인을 사용한 경우 조인 조건을 침투시켜라

JPPD

JPPD(Join Predicate Push Down)는 Outer 조인을 사용한 뷰 혹은 인라인뷰에서도 수행된다.

JPPD 예제
{code:sql	borderStyle=solid} SELECT e.employee_id, e.first_name, e.last_name, e.phone_number, d.city, d.country_id FROM employee e, (SELECT d.department_id, l.city, l.country_id FROM department d, location l WHERE d.location_id = l.location_id) d WHERE e.department_id = d.department_id(+) AND e.job_id = 'AD_PRES' ;

+

---+

Id

Operation

Name

Rows

Bytes

Cost

Time

+

---+

0	SELECT STATEMENT				4
1	NESTED LOOPS OUTER		1	71	4	00:00:01
2	TABLE ACCESS BY INDEX ROWID	EMPLOYEE	1	46	2	00:00:01
3	INDEX RANGE SCAN	EMP_JOB_IX	1		1	00:00:01
4	VIEW PUSHED PREDICATE		1	25	2	00:00:01
5	NESTED LOOPS		1	22	2	00:00:01
6	TABLE ACCESS BY INDEX ROWID	DEPARTMENT	1	7	1	00:00:01
7	INDEX UNIQUE SCAN	DEPT_ID_PK	1		0
8	TABLE ACCESS BY INDEX ROWID	LOCATION	23	345	1	00:00:01
9	INDEX UNIQUE SCAN	LOC_ID_PK	1		0

+

---+
Predicate Information:

--
3 - access("E"."JOB_ID"='AD_PRES')
7 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID") <--
9 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

|
||JPPD 예제 - QT가 바꾼 쿼리||
|{code:sql|borderStyle=solid}
SELECT e.employee_id, e.first_name, e.last_name, e.phone_number, d.city, d.country_id
  FROM employee e,
      LATERAL (SELECT d.department_id, l.city, l.country_id
          FROM department d, location l
         WHERE d.location_id = l.location_id
         AND e.department_id = d.department_id)(+) d
 WHERE e.job_id = 'AD_PRES' ;

|

JPPD 예제 - 10053 Trace

{code:sql}
***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$1 (#1)
JPPD: Checking validity of push-down from query block SEL$1 (#1) to query block SEL$2 (#2)
Check Basic Validity for Non-Union View for query block SEL$2 (#2)
JPPD: Passed validity checks
JPPD: JPPD: Pushdown from query block SEL$1 (#1) passed validity checks.
Join-Predicate push-down on query block SEL$1 (#1)
JPPD: Using search type: linear <--
JPPD: Considering join predicate push-down
JPPD: Starting iteration 1, state space = (2) : (0) <--
JPPD: Performing join predicate push-down (no transformation phase) from query block SEL$1 (#1) to query block SEL$2 (#2)

FPD: Considering simple filter push in query block SEL$1 (#1)
"E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+) AND "E"."JOB_ID"='AD_PRES'
try to generate transitive predicate from check constraints for query block SEL$1 (#1)
finally: "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+) AND "E"."JOB_ID"='AD_PRES'

kkqfppRelFilter: Not pushing filter predicates in query block SEL$2 (#2) because no predicate to push
FPD: Considering simple filter push in query block SEL$2 (#2)
"D"."LOCATION_ID"="L"."LOCATION_ID"
try to generate transitive predicate from check constraints for query block SEL$2 (#2)
finally: "D"."LOCATION_ID"="L"."LOCATION_ID" <--

JPPD: Costing transformed query. <--

.............

*********************************
Number of join permutations tried: 1
*********************************
...
JPPD: Updated best state, Cost = 8.50 <--
JPPD: Starting iteration 2, state space = (2) : (1) <--
JPPD: Performing join predicate push-down (candidate phase) from query block SEL$1 (#1) to query block SEL$2 (#2)
JPPD: Pushing predicate "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+) <--
from query block SEL$1 (#1) to query block SEL$2 (#2) <--
JPPD: Push dest of pred 0x2A9A7204 is qb 0x2A9A8F74:query block SEL$2 (#2)

Registered qb: SEL$639F1A6F 0x2a9a8f74 (PUSHED PREDICATE SEL$2; SEL$1; "D"@"SEL$1" 2) <--

-
QUERY BLOCK SIGNATURE

-
signature (): qb_name=SEL$639F1A6F nbfros=2 flg=0
fro(0): flg=0 objn=74260 hint_alias="D"@"SEL$2"
fro(1): flg=0 objn=74277 hint_alias="L"@"SEL$2"

FPD: Considering simple filter push in query block SEL$1 (#1)
"E"."JOB_ID"='AD_PRES'
try to generate transitive predicate from check constraints for query block SEL$1 (#1)
finally: "E"."JOB_ID"='AD_PRES'

kkqfppRelFilter: Not pushing filter predicates in query block SEL$639F1A6F (#2) because no predicate to push
FPD: Considering simple filter push in query block SEL$639F1A6F (#2)
"D"."LOCATION_ID"="L"."LOCATION_ID" AND :B1="D"."DEPARTMENT_ID"
try to generate transitive predicate from check constraints for query block SEL$639F1A6F (#2)
finally: "D"."LOCATION_ID"="L"."LOCATION_ID" AND "D"."DEPARTMENT_ID"=:B1 <--

JPPD: Costing transformed query. <--

................

Final cost for query block SEL$1 (#1) - All Rows Plan:
Best join order: 1
Cost: 4.0014 Degree: 1 Card: 1.0000 Bytes: 71
....
JPPD: Updated best state, Cost = 4.00 <--
JPPD: Will use JPPD from SEL$1 (#1) to SEL$2 (#2). <--

|
* JPPD + Outer Join View 변환을 control하는 파라미터는 _push_join_predicate (default true)
* PUSH_PRED / NO_PUSHE_PRED 힌트 사용.
* _optimizer_push_pred_cost_based가 false 인 경우 Cost Based Query Transformation 이 아닌 Heuristic Transformation 발생.
** 이때 OJPPD 수행될 수 있으며, OJPPD가 수행되지 않을 때는 OLD_PUSH_PRED 힌트 사용.

3.11 JPPD Outer Join View 뷰에 Outer 조인을 사용한 경우 조인 조건을 침투시켜라 (by jongmali) [2013.07.06]

3.11 JPPD Outer Join View : 뷰에 Outer 조인을 사용한 경우 조인 조건을 침투시켜라