5. Outer 조인

조회 수 4228 추천 수 0 2011.03.30 08:31:08
실천하자 *.18.78.25

5. Outer 조인            ( Test 환경 - Windows 7, Oracle 11g2R, SQL*Plus: Release 11.2.0.1.0 )


(1) Outer NL 조인


select /*+ use_nl(d e) */ *

from   dept d, emp e

where  e.deptno(+) = d.deptno;


Execution Plan

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    14 |   812 |    10   (0)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER|      |    14 |   812 |    10   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| EMP  |     4 |   152 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("E"."DEPTNO"(+)="D"."DEPTNO")


  • ERD 표기를 따르는 SQL 개발의 중요성



(2) Outer 소트 머지 조인


select /*+ use_merge(d e) */ *

from   dept d, emp e

where  e.deptno(+) = d.deptno;


Execution Plan

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)| 00:00:01 |

|   1 |  MERGE JOIN OUTER            |         |    14 |   812 |     6  (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |

|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("E"."DEPTNO"(+)="D"."DEPTNO")

       filter("E"."DEPTNO"(+)="D"."DEPTNO")



(3) Outer 해시 조인

select /*+ use_hash(d e) */
from   dept d, emp e 
where  e.deptno(+) = d.deptno;


select /*+ use_hash(d e) swap_join_inputs(d) */
from   dept d, emp e 
where  e.deptno = d.deptno(+);

  • 9i 이전 버전에서 Outer 해시 조인 튜닝

select /*+ order index_ffs (o) full (c ) full (o2)  use_hash (o c) use_hash (o2)
        parallel_index(o) parallel(c) parallel(o2) */ c.*,o2.*
from 주문 o, 고객 c, 주문 o2
where c.고객번호(+)=o.고객번호
and o2.고객번호 = o.고객번호
and o2.상품번호 = o.상품번호
and o2.주문일시 = o.주문일시


(4) Full Outer 조인

exec dbms_random.seed(150);

create table 입금
as
select rownum 일련번호
     , round(dbms_random.value(1, 20)) 고객ID
     , round(dbms_random.value(1000, 100000),-2) 입금액
from dual connect by level <= 10;

create table 출금
as
select rownum 일련번호
     , round(dbms_random.value(1, 20)) 고객ID
     , round(dbms_random.value(1000, 10000),-2) 출금액
from dual connect by level <= 10;

exec dbms_stats.gather_table_stats(user, '입금');
exec dbms_stats.gather_table_stats(user, '출금');

  • 'Left Outer 조인 + Union All + Anti 조인(Not Exists 필터)' 이용

SQL> select a.고객ID, a.입금액, b.출금액
  2  from  (select 고객ID, sum(입금액) 입금액 from 입금 group by 고객ID) a
  3       ,(select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) b
  4  where  b.고객ID(+) = a.고객ID
  5  union all
  6  select 고객ID, null, 출금액
  7  from  (select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) a
  8  where not exists (select 'x' from 입금 where 고객ID = a.고객ID) ;

    고객ID     입금액     출금액
---------- ---------- ----------
        13       6800      14500
         2      23900       6200
         3      26600       2300
        19      40400       6900
         8      95700
         1      23100
         6      71000
        18      34300
         4     121900
        17                  9200
         7                  3900
        16                  7500
         9                  1300

13 개의 행이 선택되었습니다.

Execution Plan
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    17 |   540 |    16  (63)| 00:00:01 |
|   1 |  UNION-ALL            |      |       |       |            |          |
|*  2 |   HASH JOIN OUTER     |      |     9 |   468 |     9  (34)| 00:00:01 |
|   3 |    VIEW               |      |     9 |   234 |     4  (25)| 00:00:01 |
|   4 |     HASH GROUP BY     |      |     9 |    63 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| 입금 |    10 |    70 |     3   (0)| 00:00:01 |
|   6 |    VIEW               |      |     8 |   208 |     4  (25)| 00:00:01 |
|   7 |     HASH GROUP BY     |      |     8 |    48 |     4  (25)| 00:00:01 |
|   8 |      TABLE ACCESS FULL| 출금 |    10 |    60 |     3   (0)| 00:00:01 |
|   9 |   HASH GROUP BY       |      |     8 |    72 |     8  (25)| 00:00:01 |
|* 10 |    HASH JOIN ANTI     |      |    10 |    90 |     7  (15)| 00:00:01 |
|  11 |     TABLE ACCESS FULL | 출금 |    10 |    60 |     3   (0)| 00:00:01 |
|  12 |     TABLE ACCESS FULL | 입금 |    10 |    30 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."고객ID"(+)="A"."고객ID")
  10 - access("고객ID"="고객ID")

  • ANSI Full Outer 조인

SQL> select nvl(a.고객ID, b.고객ID) 고객ID, a.입금액, b.출금액
  2  from  (select 고객ID, sum(입금액) 입금액 from 입금 group by 고객ID) a
  3         full outer join
  4        (select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) b
  5      on a.고객ID = b.고객ID ;

    고객ID     입금액     출금액
---------- ---------- ----------
         1      23100
         6      71000
        13       6800      14500
         2      23900       6200
         4     121900
         8      95700
         3      26600       2300
        18      34300
        19      40400       6900
        17                  9200
         7                  3900
        16                  7500
         9                  1300

13 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     9 |   468 |     9  (34)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0 |     9 |   468 |     9  (34)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |     9 |   468 |     9  (34)| 00:00:01 |
|   3 |    VIEW               |          |     8 |   208 |     4  (25)| 00:00:01 |
|   4 |     HASH GROUP BY     |          |     8 |    48 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| 출금     |    10 |    60 |     3   (0)| 00:00:01 |
|   6 |    VIEW               |          |     9 |   234 |     4  (25)| 00:00:01 |
|   7 |     HASH GROUP BY     |          |     9 |    63 |     4  (25)| 00:00:01 |
|   8 |      TABLE ACCESS FULL| 입금     |    10 |    70 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."고객ID"="B"."고객ID")

  • Native Hash Full Outer 조인

SQL> select /*+ opt_param('_optimizer_native_full_outer_join', 'force') */
  2         nvl(a.고객ID, b.고객ID) 고객ID, a.입금액, b.출금액
  3  from  (select 고객ID, sum(입금액) 입금액 from 입금 group by 고객ID) a
  4         full outer join
  5        (select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) b
  6      on a.고객ID = b.고객ID ;

    고객ID     입금액     출금액
---------- ---------- ----------
         1      23100
         6      71000
        13       6800      14500
         2      23900       6200
         4     121900
         8      95700
         3      26600       2300
        18      34300
        19      40400       6900
        17                  9200
         7                  3900
        16                  7500
         9                  1300

13 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     9 |   468 |     9  (34)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0 |     9 |   468 |     9  (34)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |     9 |   468 |     9  (34)| 00:00:01 |
|   3 |    VIEW               |          |     8 |   208 |     4  (25)| 00:00:01 |
|   4 |     HASH GROUP BY     |          |     8 |    48 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| 출금     |    10 |    60 |     3   (0)| 00:00:01 |
|   6 |    VIEW               |          |     9 |   234 |     4  (25)| 00:00:01 |
|   7 |     HASH GROUP BY     |          |     9 |    63 |     4  (25)| 00:00:01 |
|   8 |      TABLE ACCESS FULL| 입금     |    10 |    70 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."고객ID"="B"."고객ID")

  • Union All을 이용한 Full Outer 조인

SQL> select 고객ID, sum(입금액) 입금액, sum(출금액) 출금액
  2  from (
  3    select 고객ID, 입금액, to_number(null) 출금액
  4    from   입금
  5    union all
  6    select 고객ID, to_number(null) 입금액, 출금액
  7    from   출금
  8  )
  9  group by 고객ID ;

    고객ID     입금액     출금액
---------- ---------- ----------
         1      23100
         6      71000
        13       6800      14500
         2      23900       6200
         4     121900
         8      95700
        17                  9200
         3      26600       2300
        18      34300
         7                  3900
        19      40400       6900
         9                  1300
        16                  7500

13 개의 행이 선택되었습니다.

Execution Plan
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     9 |   261 |     7  (15)| 00:00:01 |
|   1 |  HASH GROUP BY       |      |     9 |   261 |     7  (15)| 00:00:01 |
|   2 |   VIEW               |      |    20 |   580 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL         |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| 입금 |    10 |    70 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| 출금 |    10 |    60 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------







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