집합연산을 조인으로 변환

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

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 연산을 수행한 후 minus연산을 수행함.

SQL> alter session set "_convert_set_to_join" = true;

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)

-- 해쉬조인을 수행한후, 중복값 제거를 위해 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_nonull함수는 비공식적인 함수지만 가끔 유용할수 있다.
-- null값끼리 '=' 비교(null=null) 하면 false 이지만 가끔 true 되도록 처리해야하는 경우가 있고,
-- 그럴때는 sys_op_map_nonull 함수를 사용하면 된다.