2.13  NFOJ*(Native Full Outer Join )

Full Outer 조인 시 중복 Scan 되는 테이블을 제거하라

 

 

10g : full outer join 실행

=> union all 로 분리되어 outer 조인과 anti join 이 각각 수행

이유 ) _opimizer_native_full_outer_join = off 설정

11g :  _opimizer_native_full_outer_join = force  ( Default  설정 )

=> Union all/ Anti Join 없음

 

 

alter session set "_optimizer_native_full_outer_join" = FORCE;

select 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 );

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |      1 |        |       |     7 (100)|          |    123 |00:00:00.01 |      16 |       |       |          |
|   1 |  VIEW                    | VW_FOJ_0         |      1 |    120 |  6720 |     7  (15)| 00:00:01 |    123 |00:00:00.01 |      16 |       |       |          |
|*  2 |   HASH JOIN FULL OUTER   |                  |      1 |    120 |  4560 |     7  (15)| 00:00:01 |    123 |00:00:00.01 |      16 |   862K|   862K| 1206K (0)|
|   3 |    VIEW                  | index$_join$_002 |      1 |     27 |   432 |     3  (34)| 00:00:01 |     27 |00:00:00.01 |       8 |       |       |          |
|*  4 |     HASH JOIN            |                  |      1 |        |       |            |          |     27 |00:00:00.01 |       8 |   870K|   870K| 1227K (0)|
|   5 |      INDEX FAST FULL SCAN| DEPT_ID_PK       |      1 |     27 |   432 |     1   (0)| 00:00:01 |     27 |00:00:00.01 |       4 |       |       |          |
|   6 |      INDEX FAST FULL SCAN| DEPT_NAME_IDX    |      1 |     27 |   432 |     1   (0)| 00:00:01 |     27 |00:00:00.01 |       4 |       |       |          |
|   7 |    TABLE ACCESS FULL     | EMPLOYEE         |      1 |    107 |  2354 |     4   (0)| 00:00:01 |    107 |00:00:00.01 |       8 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
   4 - access(ROWID=ROWID)

 

 

결과설명

 _opimizer_native_full_outer_join = force  하자 union All과 anti 조인 이 사라졌다.

따라서 같은 테이블을 2번씩 Scan 하는 비효율이 제거 되었다.

이 기능은 Natvie Hash Full Outer Join 이라고 불린다.

Plan 상에 WV_FOJ_0라는 view 가 존재하는데 Full Outer Join 의 약어이며

Native 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 기능 사용.

 

 

특별히 Full Outer Join 변환과 관련된 10053  Trace 정보는 없지만 Native Hash Full Outer Join 기능 때문에 Query Transformation 을 방해하는 것을 여러 곳에서 볼수 있다.

 

***********************

Query transformation (QT)

***********************

Check Basic Validity for Non-Union View for query block SEL$1 (#0 )

JPPD :  JPPD BYPASSED : View has full outer join

 

*************************

Order-by elimination (OBYE)

**************************

OBYE :  OBYE bypasses : no order by to eliminate.

CVM : Considering View merge in 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)

SVM:  SVM bypassed : Full Outer Join.

 

**************************

Predicate Move-Around (PM)

**************************

PM : PM bypassed : Full Outer Join

 

이처럼 Native Full Outer Join 기능 역시 Query Transformation이  발생하지 않는 경우가 있다.

이점만 주의하면 Native Full Outer Join 은 최적의 선택이 될 것이다