Intersect 나 Minus 같은 집합(set) 연산을 조인 형태로 변환하는 것을 말한다.
아래는 deptno = 10에 속한 사원들의 job,mgr 을 제외시키고, 나머지 job,mgr 집합만 찾는 쿼리다.

SQL> create table emp as select * from scott.emp;

테이블이 생성되었습니다.

SQL> select job, mgr from emp
  2  minus
  3  select job, mgr from emp
  4  where  deptno = 10 ;

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

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   362 |     8  (63)| 00:00:01 |
|   1 |  MINUS              |      |       |       |            |          |
|   2 |   SORT UNIQUE       |      |    14 |   266 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   266 |     3   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |      |     3 |    96 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMP  |     3 |    96 |     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;

세션이 변경되었습니다.

SQL>
SQL> select job, mgr from emp
  2  minus
  3  select job, mgr from emp
  4  where  deptno = 10 ;

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

----------------------------------------------------------------------------
| 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연산을 수행하는 것을 볼수 있음
아래와 같은 형태로 쿼리변환이 일어난 것임

SQL> select distinct job, mgr from emp e
  2  where  not exists (
  3    select 'x' from emp
  4    where  deptno = 10
  5    and    sys_op_map_nonnull(job) = sys_op_map_nonnull(e.job)
  6    and    sys_op_map_nonnull(mgr) = sys_op_map_nonnull(e.mgr)
  7  ) ;

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

----------------------------------------------------------------------------
| 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("E"."JOB")
              AND SYS_OP_MAP_NONNULL("MGR")=SYS_OP_MAP_NONNULL("E"."MGR"))
   4 - filter("DEPTNO"=10)

sys_op_map_nonull함수는 비공식적인 함수지만 가끔유용하게 사용할수 있음
null값끼리 '=' 비교 (null = null) 하면 false이지만 가끔 true가 되도록 처리해야 하는 경우가 있고,
그럴때 sys_op_map_nonull함수를 사용하면 됨
위에서는 job 과 mgr이 null허용 컬럼이기 때문에 우와 같은 처리가 일어났음

문서에 대하여

• 최초작성일 : 2010년 05월 14일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*