| {code:SQL | title= _optimizer_native_full_outer_join=FORCE | borderStyle=solid} alter session set "_optimizer_native_full_outer_join" = FORCE; |
|---|
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SELECT /*+ gather_plan_statistics */
a.employee_id, a.first_name, a.last_name, b.department_name
FROM employee a FULL OUTER JOIN department b
ON (a.department_id = b.department_id) ;
ALTER SESSION SET EVENTS '10053 trace name context off';
============
Plan Table
============
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | 5 | ||||
| 1 | VIEW | VW_FOJ_0 | 122 | 6832 | 5 | 00:00:01 |
| 2 | HASH JOIN FULL OUTER | 122 | 4392 | 5 | 00:00:01 | |
| 3 | TABLE ACCESS FULL | DEPARTMENT | 27 | 378 | 2 | 00:00:01 |
| 4 | TABLE ACCESS FULL | EMPLOYEE | 107 | 2354 | 2 | 00:00:01 |
||* Union All과 Anti 조인이 사라졌다. 같은 테이블을 2 번씩 Scan하는 비효율이 제거되었다
-이 기능은 Native Hash Full Outer Join 이라고 불린다
-Native Full Outer Join이 발생하면 VW_FOJ_0(Full Outer Join)뷰가 생성된다.
-Native Hash Full Outer Join은 힌트로도 제어가 가능
/*+ NO_NATIVE_FULL_OUTER_JOIN */ Native Hash Full Outer Join 기능 제거
/*+ NATIVE_FULL_OUTER_JOIN */ Native Hash Full Outer Join 기능 사용|
|* Native Hash Full Outer Join 기능 때문에 Query Transformation을 방해하는 것을 여러 곳에서 볼 수 있다.
{code:SQL|title= Query transformations |borderStyle=solid}
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: Full outer-joined table.
Check Basic Validity for Non-Union View for query block SEL$1 (#0)
JPPD: JPPD bypassed: View has full outer join. <--
JF: Checking validity of join factorization for query block SEL$2 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$2 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$2 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 9kpbkmnjhbsrn.
CSE: Considering common sub-expression elimination in query block SEL$2 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
CSE: CSE not performed on query block SEL$2 (#0).
OBYE: Considering Order-by Elimination from view SEL$2 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE bypassed: no order by to eliminate.
CVM: Considering view merge in query block SEL$2 (#0)
OJE: Begin: find best directive for query block SEL$2 (#0)
OJE: OJ to IJ validity check falied.
OJE: End: finding best directive for query block SEL$2 (#0)
CVM: Checking validity of merging in query block SEL$1 (#0)
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
SVM: SVM bypassed: Full Outer Join. <--
SLP: Removed select list item QCSJ_C000000000300000 from query block SEL$1
SLP: Removed select list item QCSJ_C000000000300001 from query block SEL$1
query block SEL$2 (#0) unchanged
Considering Query Transformations on query block SEL$2 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: Full outer-joined table.
Check Basic Validity for Non-Union View for query block SEL$1 (#0)
JPPD: JPPD bypassed: View has full outer join. <--
|