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

▶ Intersect나 minus 같은 집합(set) 연산을 조인 형태로 변환.

SQL> set autotrace traceonly exp;

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

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=14 Bytes=243)

   1    0   MINUS
   2    1     SORT (UNIQUE) (Cost=4 Card=14 Bytes=168)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=168)

   4    1     SORT (UNIQUE) (Cost=4 Card=5 Bytes=75)
   5    4       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=5 Bytes=75)

 

Predicate Information

----------------------------------------------------------
  5  - filter ("DEPTNO"=10)
 

SQL> alter session set "_convert_set_to_join" = true;

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

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=8 Bytes=216)

   1    0   SORT (UNIQUE) (Cost=8 Card=8 Bytes=216)
   2    1     HASH JOIN (ANTI) (Cost=7 Card=8 Bytes=216)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=168)

   4    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=5 Bytes=75)

 

 

Predicate Information

----------------------------------------------------------
  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
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=8 Bytes=216)

   1    0   SORT (UNIQUE) (Cost=8 Card=8 Bytes=216)
   2    1     HASH JOIN (ANTI) (Cost=7 Card=8 Bytes=216)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=168)

   4    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=5 Bytes=75)

 

Predicate Information

----------------------------------------------------------
  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_nonnull 함수는 비공식적 함수.

      Null값끼리 ‘=’ 비교하면 false지만 가끔 true가 되도록 처리해야 하는 경우 사용.

   - job, mgr이 null 허용 컬럼이기 때문에 사용됨.

오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
작성자: 이주영 (suspace)
최초작성일: 2011년 6월 7일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^