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

Intersect 나 Minus 같은 집합연산을 조인형태로 변환하는것을 말한다.

 
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)	

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

 
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    |      |    13 |   663 |     8  (25)| 00:00:01 |
|   1 |  HASH UNIQUE        |      |    13 |   663 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN ANTI    |      |    13 |   663 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   266 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP  |     3 |    96 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access(SYS_OP_MAP_NONNULL("JOB")=SYS_OP_MAP_NONNULL("JOB") AND
              SYS_OP_MAP_NONNULL("MGR")=SYS_OP_MAP_NONNULL("MGR"))
   4 - 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) 
   ;

sys_op_map_nonnull 함수는 비공식적 인 함수이다.
Null값끼리 '=' 비교하면(null = null) false지만 가끔 true가 되도록 처리해야 하는 경우 사용.