by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

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




집합 연산을 조인으로 변환

  1. intersect나 minus 같은 집합 연산을 조인 형태로 변환
  2. sys_op_map_nonnull 함수 : 비공식적인 함수. null끼리 "=" 비교(null = null)하면 false이지만, 가끔 true가 되도록 처리할 때 사용

테스트

  • join형태로 변환1
    select job, mgr from emp
    minus
    select job, mgr from emp
    where  deptno = 10 ;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4030040631
    
    ----------------------------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |    13 |  1157 |     8  (25)| 00:00:01 |
    |   1 |  HASH UNIQUE        |      |    13 |  1157 |     8  (25)| 00:00:01 |
    |*  2 |   HASH JOIN ANTI    |      |    13 |  1157 |     7  (15)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
    |*  4 |    TABLE ACCESS FULL| EMP  |     3 |   153 |     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)
    
  • _convert_set_to_join" = true
    • hash anti join 수행 후, 중복값 제거를 위해 hash unique 연산 수행으로 쿼리 변환
select job, mgr from emp
minus
select job, mgr from emp
where  deptno = 10 ;

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

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    13 |  1157 |     8  (25)| 00:00:01 |
|   1 |  HASH UNIQUE        |      |    13 |  1157 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN ANTI    |      |    13 |  1157 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP  |     3 |   153 |     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)

**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$09AAA538 (#0).
PM:   Checking validity of predicate move-around in SEL$09AAA538 (#0).
PM:     PM bypassed: Outer query contains no views.
FPD: Considering simple filter push in SEL$09AAA538 (#0)
FPD:   Current where clause predicates in SEL$09AAA538 (#0) :
         "EMP"."DEPTNO"=10 AND SYS_OP_MAP_NONNULL("EMP"."JOB")=SYS_OP_MAP_NONNULL("EMP"."JOB") AND SYS_OP_MAP_NONNULL("EMP"."MGR")=SYS_OP_MAP_NONNULL("EMP"."MGR")
kkogcp: try to generate transitive predicate from check constraints for SEL$09AAA538 (#0)
predicates with check contraints: "EMP"."DEPTNO"=10 AND SYS_OP_MAP_NONNULL("EMP"."JOB")=SYS_OP_MAP_NONNULL("EMP"."JOB") AND SYS_OP_MAP_NONNULL("EMP"."MGR")=SYS_OP_MAP_NONNULL("EMP"."MGR")
after transitive predicate generation: "EMP"."DEPTNO"=10 AND SYS_OP_MAP_NONNULL("EMP"."JOB")=SYS_OP_MAP_NONNULL("EMP"."JOB") AND SYS_OP_MAP_NONNULL("EMP"."MGR")=SYS_OP_MAP_NONNULL("EMP"."MGR")
finally: "EMP"."DEPTNO"=10 AND SYS_OP_MAP_NONNULL("EMP"."JOB")=SYS_OP_MAP_NONNULL("EMP"."JOB") AND SYS_OP_MAP_NONNULL("EMP"."MGR")=SYS_OP_MAP_NONNULL("EMP"."MGR")
  • not exists의 경우
    Current SQL statement for this session:
    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    |      |    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)
    
    **************************
    Predicate Move-Around (PM)
    **************************
    PM: Considering predicate move-around in SEL$5DA710D3 (#1).
    PM:   Checking validity of predicate move-around in SEL$5DA710D3 (#1).
    PM:     PM bypassed: Outer query contains no views.
    JPPD: Applying transformation directives
    JPPD: Checking validity of push-down in query block SEL$5DA710D3 (#1)
    JPPD:   No view found to push predicate into.
    FPD: Considering simple filter push in SEL$5DA710D3 (#1)
    FPD:   Current where clause predicates in SEL$5DA710D3 (#1) :
             "EMP"."DEPTNO"=10 AND SYS_OP_MAP_NONNULL("EMP"."JOB")=SYS_OP_MAP_NONNULL("E"."JOB") AND SYS_OP_MAP_NONNULL("EMP"."MGR")=SYS_OP_MAP_NONNULL("E"."MGR")
    kkogcp: try to generate transitive predicate from check constraints for SEL$5DA710D3 (#1)
    predicates with check contraints: "EMP"."DEPTNO"=10 AND SYS_OP_MAP_NONNULL("EMP"."JOB")=SYS_OP_MAP_NONNULL("E"."JOB") AND SYS_OP_MAP_NONNULL("EMP"."MGR")=SYS_OP_MAP_NONNULL("E"."MGR")
    after transitive predicate generation: "EMP"."DEPTNO"=10 AND SYS_OP_MAP_NONNULL("EMP"."JOB")=SYS_OP_MAP_NONNULL("E"."JOB") AND SYS_OP_MAP_NONNULL("EMP"."MGR")=SYS_OP_MAP_NONNULL("E"."MGR")
    finally: "EMP"."DEPTNO"=10 AND SYS_OP_MAP_NONNULL("EMP"."JOB")=SYS_OP_MAP_NONNULL("E"."JOB") AND SYS_OP_MAP_NONNULL("EMP"."MGR")=SYS_OP_MAP_NONNULL("E"."MGR")
    

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.