JPPD
| JPPD 예제 | |
|---|---|
| {code:sql | borderStyle=solid} SELECT /*+ QB_NAME(OUTER) LEADING(d) USE_NL(e) */ d.department_id, d.department_name, e.employee_id, e.job_id, e.email_phone_num FROM department d, (SELECT /*+ QB_NAME(INNER1) */ employee_id, department_id, job_id, email AS email_phone_num FROM employee WHERE job_id = :v_job1 --> 'AD_ASST' 대입 UNION SELECT /*+ QB_NAME(INNER2) */ employee_id, department_id, job_id, phone_number AS email_phone_num FROM employee WHERE job_id = :v_job2 ) e --> 'AD_PRES' 대입 ) WHERE d.department_id = e.department_id AND d.location_id = 1700; |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | 128 | ||||
| 1 | NESTED LOOPS | 9 | 558 | 128 | 00:00:02 | |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 21 | 420 | 2 | 00:00:01 |
| 3 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 21 | 1 | 00:00:01 | |
| 4 | VIEW | 1 | 42 | 6 | 00:00:01 | |
| 5 | SORT UNIQUE | 2 | 52 | 6 | 00:00:01 | |
| 6 | UNION ALL PUSHED PREDICATE | |||||
| 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 24 | 2 | 00:00:01 |
| 8 | INDEX RANGE SCAN | EMP_JOB_DEPT | 1 | 1 | 00:00:01 | |
| 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 28 | 2 | 00:00:01 |
| 10 | INDEX RANGE SCAN | EMP_JOB_DEPT | 1 | 1 | 00:00:01 |
|
||JPPD 예제 - QT가 바꾼 쿼리||
|{code:sql|borderStyle=solid}
SELECT d.department_id, d.department_name, e.employee_id, e.job_id, e.email_phone_num
FROM department d,
LATERAL ((SELECT employee_id, department_id, job_id, email AS email_phone_num <--
FROM employee e1
WHERE job_id = :v_job1 AND e1.department_id = d.department_id <--
UNION
SELECT employee_id, department_id, job_id, phone_number AS email_phone_num
FROM employee e2
WHERE job_id = :v_job2 AND e2..department_id = d.department_id ) e <--
WHERE d.location_id = 1700;
|
| JPPD 예제 - 10053 Trace |
|---|
| {code:sql} ..... *********************************** Cost-Based Join Predicate Push-down *********************************** JPPD: Checking validity of push-down in query block OUTER (#1) JPPD: Checking validity of push-down from query block OUTER (#1) to query block SET$1 (#2) JPPD: Passed validity checks JPPD: JPPD: Pushdown from query block OUTER (#1) passed validity checks. Join-Predicate push-down on query block OUTER (#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 OUTER (#1) to query block SET$1 (#2) |
FPD: Considering simple filter push in query block OUTER (#1)
"D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D"."LOCATION_ID"=1700
try to generate transitive predicate from check constraints for query block OUTER (#1)
finally: "D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D"."LOCATION_ID"=1700 <--
kkqfppRelFilter: Not pushing filter predicates in query block SET$1 (#2) because no predicate to push
FPD: Considering simple filter push in query block INNER2 (#3)
"EMPLOYEE"."JOB_ID"=:B1
try to generate transitive predicate from check constraints for query block INNER2 (#3)
finally: "EMPLOYEE"."JOB_ID"=:B1
FPD: Considering simple filter push in query block INNER1 (#4)
"EMPLOYEE"."JOB_ID"=:B1
try to generate transitive predicate from check constraints for query block INNER1 (#4)
finally: "EMPLOYEE"."JOB_ID"=:B1
JPPD: Costing transformed query. <--
.............
*********************************
Number of join permutations tried: 1
*********************************
(newjo-save) 1 0
Trying or-Expansion on query block OUTER (#1)
Transfer Optimizer annotations for query block OUTER (#1)
Final cost for query block OUTER (#1) - All Rows Plan:
Best join order: 1
Cost: 128.0356 Degree: 1 Card: 9.0000 Bytes: 603
Resc: 128.0356 Resc_io: 86.0000 Resc_cpu: 931127367
Resp: 128.0356 Resp_io: 86.0000 Resc_cpu: 931127367
kkoqbc-subheap (delete addr=0x0ECE3F28, in-use=19436, alloc=22500)
kkoqbc-end:
:
call(in-use=45668, alloc=81864), compile(in-use=169652, alloc=181084), execution(in-use=4444, alloc=8136)
kkoqbc: finish optimizing query block OUTER (#1)
CBQT: Saved costed qb# 4 (INNER1), key = INNER1_00000000_0
CBQT: Saved costed qb# 3 (INNER2), key = INNER2_00000000_0
CBQT: Saved costed qb# 2 (SET$1), key = SET$1_00004200_2
CBQT: Saved costed qb# 1 (OUTER), key = OUTER_00000000_0
JPPD: Updated best state, Cost = 128.04 <-- iteration 1의 cost는 128.04
JPPD: Starting iteration 2, state space = (2) : (1) <-- 곧바로 iteration 2 진행
JPPD: Performing join predicate push-down (candidate phase) from query block OUTER (#1) to query block SET$1 (#2)
JPPD: Pushing predicate "D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" <-- JPPD를 수행하기 위해 조인 조건을
from query block OUTER (#1) to query block SET$1 (#2) <-- 쿼리블럭 SET$01에 침투
..............
Registered qb: SEL$4E984A90 0xee7dfc4 (PUSHED PREDICATE INNER1) <-- JPPD가 수행되어 새로운 쿼리블럭 생성
Registered qb: SET$5715CE2E 0xee31d98 (PUSHED PREDICATE SET$1; OUTER; "E"@"OUTER" 2) <-- JPPD의 결과로 SET$1의 쿼리블럭명이 변경
Registered qb: SEL$FAA94C03 0xee32274 (PUSHED PREDICATE INNER2) <-- JPPD가 수행되어 새로운 쿼리블럭 생성
.............
FPD: Considering simple filter push in query block OUTER (#1)
"D"."LOCATION_ID"=1700
try to generate transitive predicate from check constraints for query block OUTER (#1)
finally: "D"."LOCATION_ID"=1700
kkqfppRelFilter: Not pushing filter predicates in query block SET$5715CE2E (#2) because no predicate to push
FPD: Considering simple filter push in query block SEL$FAA94C03 (#3)
"EMPLOYEE"."JOB_ID"=:B1 AND :B2="EMPLOYEE"."DEPARTMENT_ID"
try to generate transitive predicate from check constraints for query block SEL$FAA94C03 (#3)
finally: "EMPLOYEE"."JOB_ID"=:B1 AND "EMPLOYEE"."DEPARTMENT_ID"=:B2 <-- 각 쿼리블럭에 FPD가 발생하여 조인 조건을 새로 생성된 쿼리블럭들에 생성
FPD: Considering simple filter push in query block SEL$4E984A90 (#4)
"EMPLOYEE"."JOB_ID"=:B1 AND :B2="EMPLOYEE"."DEPARTMENT_ID"
try to generate transitive predicate from check constraints for query block SEL$4E984A90 (#4)
finally: "EMPLOYEE"."JOB_ID"=:B1 AND "EMPLOYEE"."DEPARTMENT_ID"=:B2 <-- 각 쿼리블럭에 FPD가 발생하여 조인 조건을 새로 생성된 쿼리블럭들에 생성
............
JPPD: Updated best state, Cost = 128.03 <-- JPPD costing 결과 128.03
JPPD: Will use JPPD from OUTER (#1) to SET$1 (#2). <-- costr가 저렴하므로 JPPD 선택
kkqctdrvTD-cleanup: transform(in-use=43284, alloc=45368) :
call(in-use=89124, alloc=114616), compile(in-use=303688, alloc=307188), execution(in-use=4936, alloc=8136)
kkqctdrvTD-end:
call(in-use=89124, alloc=114616), compile(in-use=258264, alloc=307188), execution(in-use=4936, alloc=8136)
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block OUTER (#1)
JPPD: JPPD: Pushdown from query block OUTER (#1) passed validity checks.
JPPD: Performing join predicate push-down (final phase) from query block OUTER (#1) to query block SET$1 (#2)
JPPD: Pushing predicate "D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" <--
from query block OUTER (#1) to query block SET$1 (#2)
JPPD: Push dest of pred 0x0EC67598 is qb 0x0E511DC0:query block SET$1 (#2)
JPPD: Push dest of pred 0x0EC6B648 is qb 0x0E511DC0:query block SET$1 (#2)
query block OUTER (#1) unchanged
FPD: Considering simple filter push in query block OUTER (#1)
"D"."LOCATION_ID"=1700
try to generate transitive predicate from check constraints for query block OUTER (#1)
finally: "D"."LOCATION_ID"=1700
kkqfppRelFilter: Not pushing filter predicates in query block SET$5715CE2E (#2) because no predicate to push
FPD: Considering simple filter push in query block SEL$FAA94C03 (#3)
"EMPLOYEE"."JOB_ID"=:B1 AND :B2="EMPLOYEE"."DEPARTMENT_ID"
try to generate transitive predicate from check constraints for query block SEL$FAA94C03 (#3)
finally: "EMPLOYEE"."JOB_ID"=:B1 AND "EMPLOYEE"."DEPARTMENT_ID"=:B2
FPD: Considering simple filter push in query block SEL$4E984A90 (#4)
"EMPLOYEE"."JOB_ID"=:B1 AND :B2="EMPLOYEE"."DEPARTMENT_ID"
try to generate transitive predicate from check constraints for query block SEL$4E984A90 (#4)
finally: "EMPLOYEE"."JOB_ID"=:B1 AND "EMPLOYEE"."DEPARTMENT_ID"=:B2
|
* JPPD + UNION 변환을 control하는 파라미터는 _push_join_union_view2 (default true)
* PUSH_PRED / NO_PUSHE_PRED 힌트 사용.
* JPPD의 수행 여부를 cost로서 판단하게 만드는 파라미터는 _optimizer_push_pred_cost_based (default true)
** 이 파라미터가 flase 되어 있다고 해도 JPPD가 수행될 수 있다.
** 이 기능을 OJPPD(old-style (non-cost-based) JPPD)라고 부르며 OJPPD가 수행되지 않을 때는 OLD_PUSH_PRED 힌트 사용.
|{code:sql|borderStyle=solid}
SELECT /*+ QB_NAME(OUTER) LEADING(d) USE_NL(e) OLD_PUSH_PRED(@OUTER E@OUTER (EMPLOYEE.DEPARTMENT_ID)) */
d.department_id, d.department_name, e.employee_id, e.job_id, e.email_phone_num
FROM department d,
(SELECT /*+ QB_NAME(INNER1) */
employee_id, department_id, job_id, email AS email_phone_num
FROM employee
WHERE job_id = :v_job1 --> 'AD_ASST' 대입
UNION
SELECT /*+ QB_NAME(INNER2) */
employee_id, department_id, job_id, phone_number AS email_phone_num
FROM employee
WHERE job_id = :v_job2 ) e --> 'AD_PRES' 대입 )
WHERE d.department_id = e.department_id
AND d.location_id = 1700;
-----------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 11 | |
| 1 | NESTED LOOPS | | 11 | 506 | 11 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 21 | 420 | 2 | 00:00:01 |
| 3 | INDEX RANGE SCAN | DEPT_LOCATION_IX| 21 | | 1 | 00:00:01 |
| 4 | VIEW | | 1 | 26 | 1 | 00:00:01 |
| 5 | SORT UNIQUE | | | | | |
| 6 | UNION-ALL PARTITION | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 24 | 2 | 00:00:01 |
| 8 | INDEX RANGE SCAN | EMP_JOB_DEPT | 1 | | 1 | 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 28 | 2 | 00:00:01 |
| 10 | INDEX RANGE SCAN | EMP_JOB_DEPT | 1 | | 1 | 00:00:01 |
-----------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("D"."LOCATION_ID"=1700)
8 - access("JOB_ID"=:V_JOB1 AND "DEPARTMENT_ID"="D"."DEPARTMENT_ID")
10 - access("JOB_ID"=:V_JOB2 AND "DEPARTMENT_ID"="D"."DEPARTMENT_ID")
-- Plan상에서는 알 수 없지만, Predicate information의 id 8,10을 보면 JPPD가 성공적으로 수행되었다.
OJPPD: Performing join predicate push-down in union view <--
from query block SET$1 (#0) to query block INNER2 (#0) <--
OJPPD: Pushing predicate "D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"
OJPPD: Used promoted index: EMP_DEPARTMENT_IX
Registered qb: SET$AD7CC163 0x102d17bc (OLD PUSHED PREDICATE SET$1; OUTER; "E"@"OUTER")
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SET$AD7CC163 nbfros=1 flg=0
fro(0): flg=0 objn=0 hint_alias="NULL_HALIAS"@"SET$AD7CC163"
Registered qb: SEL$AC386DA3 0x102d24ac (OLD PUSHED PREDICATE INNER2)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$AC386DA3 nbfros=1 flg=0
fro(0): flg=0 objn=74263 hint_alias="EMPLOYEE"@"INNER2"
OJPPD: Performing join predicate push-down in union view <--
from query block SET$AD7CC163 (#0) to query block INNER1 (#0) <--
OJPPD: Pushing predicate "D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"
-- OJPPD가 쿼리블럭 INNER1과 INNER2에 수행
-- Non-Cost0-Based-JPPD이므로 search type이나 iteration과정은 존재하지 않는다.
|