h2.11. 집합 연산을 조인으로 변환

• Intersect 나 Minus 같은 집합(Set)연산을 조인 형태로 변환하는 것을 말함

• deptno = 10 에 속한 사원들을 제외한 나머지 job, mgr 집합만 찾는 쿼리

select job,mgr from emp
minus
select job,mgr from emp
where deptno=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3686975449

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   213 |     8  (63)| 00:00:01 |
|   1 |  MINUS              |      |       |       |            |          |
|   2 |   SORT UNIQUE       |      |    14 |   168 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   168 |     3   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |      |     3 |    45 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMP  |     3 |    45 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("DEPTNO"=10)

• 각각 Sort Unique 연산을 수행한 후 Minus 연산을 수행

• _convert_set_to_join 파라미터를 true로 설정하고 실행한 결과 확인

SQL> alter session set "_convert_set_to_join" = true;

select job,mgr from emp
minus
select job,mgr from emp
where deptno=10;


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   213 |     8  (63)| 00:00:01 |
|   1 |  MINUS              |      |       |       |            |          |
|   2 |   SORT UNIQUE       |      |    14 |   168 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   168 |     3   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |      |     3 |    45 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMP  |     3 |    45 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("DEPTNO"=10)

• 해시 Anti 조인을 수행한후, 중복값 제거를 위해 hash unique 연산 수행
  -> 아래와 같은 형태로 쿼리 변환이 일어난 것임

select distinct job, mgr from emp e
where  not exists (
  select 'x' from emp
   where  deptno = 10
   and    sys_op_map_nonnull(job) = sys_op_map_nonnull(e.job)
   and    sys_op_map_nonnull(mgr) = sys_op_map_nonnull(e.mgr)
 ) ;

Execution Plan
----------------------------------------------------------
Plan hash value: 4030040631

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     6 |   162 |     8  (25)| 00:00:01 |
|   1 |  HASH UNIQUE        |      |     6 |   162 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN ANTI    |      |     6 |   162 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   168 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP  |     3 |    45 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(SYS_OP_MAP_NONNULL("JOB")=SYS_OP_MAP_NONNULL("E"."JOB")
              AND SYS_OP_MAP_NONNULL("MGR")=SYS_OP_MAP_NONNULL("E"."MGR"))
   4 - filter("DEPTNO"=10)

• job과 mgr이 null 허용이므로 위와 같은 처리가 일어남.
• sys_op_map_nonnull함수는 비공식적인 함수지만 가끔 유용할수 있다.
• null값끼리 '=' 비교(null=null) 하면 false 이지만 가끔 true 되도록 처리해야하는 경우가 있고, 그럴때는 sys_op_map_nonnull 함수를 사용하면 된다.