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 ALL 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 | 86 | ||||
| 1 | NESTED LOOPS | 9 | 558 | 86 | 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 | 4 | 00:00:01 | |
| 5 | UNION ALL PUSHED PREDICATE | |||||
| 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 24 | 2 | 00:00:01 |
| 7 | INDEX RANGE SCAN | EMP_JOB_DEPT | 1 | 1 | 00:00:01 | |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 28 | 2 | 00:00:01 |
| 9 | 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 ALL
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} ..... ********************************* 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: 86.0343 Degree: 1 Card: 9.0000 Bytes: 603 <-- ....... JPPD: Updated best state, Cost = 86.03 |
........
*********************************
Number of join permutations tried: 1
*********************************
(newjo-save) 1 0
Or-Expansion validity checks failed on query block OUTER (#1) because no push join pred. would need new qbc as in mp
Transfer Optimizer annotations for query block OUTER (#1)
Final cost for query block OUTER (#1) - All Rows Plan:
Best join order: 1
Cost: 86.0291 Degree: 1 Card: 9.0000 Bytes: 558 <--
Resc: 86.0291 Resc_io: 86.0000 Resc_cpu: 644154
Resp: 86.0291 Resp_io: 86.0000 Resc_cpu: 644154
kkoqbc-subheap (delete addr=0x0FB276C4, in-use=19004, alloc=22500)
kkoqbc-end:
:
call(in-use=89172, alloc=130992), compile(in-use=267572, alloc=270684), execution(in-use=4936, alloc=8136)
kkoqbc: finish optimizing query block OUTER (#1)
CBQT: Saved costed qb# 4 (SEL$4E984A90), key = SEL$4E984A90_00008080_0
CBQT: Saved costed qb# 3 (SEL$FAA94C03), key = SEL$FAA94C03_00008080_0
CBQT: Saved costed qb# 1 (OUTER), key = OUTER_00010000_0
JPPD: Updated best state, Cost = 86.03 <--
JPPD: Will use JPPD from OUTER (#1) to SET$1 (#2). <--
|
* JPPD + UNION All 변환을 control하는 파라미터는 _push_join_union_view (default true)
* PUSH_PRED / NO_PUSHE_PRED 힌트 사용.
* _optimizer_push_pred_cost_based가 false 인 경우 OJPPD 수행