5. Outer 조인

조회 수 14600 추천 수 0 2011.03.30 23:48:36
휘휘 *.38.213.10


(1) Outer NL 조인


  • NL조인
    • 특성상 Outer 조인할때 방향이 한쪽으로 고정
    • (+)가 붙지 않은 테이블이 항상 드라이빙 (leading 로 순서 바꿔 지지 않음)
    • 조인 순서에 가장 큰영향을 받으므로 불필요한 outer 조인이 발생하지 않도록 주의




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

 2  from dept d, emp e

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



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

 2  from dept d, emp e

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

Execution Plan

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

Plan hash value: 2022884187

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

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

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

|   0 | SELECT STATEMENT       |          |        14 |   798 |         9   (0)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER    |          |        14 |   798 |         9   (0)| 00:00:01 |

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

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

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








참고 - ERD 표기를 따르는 SQL 개발의 중요성


  • ERD를 잘못 해석하면 설계의 도와 다른 데이터를 발생시킬 수도 있음
  • SQL 작성시
    • 각 속성의 null 값 허용여부 확인
    • 엔티티 간 관계(relationship)
    • 카디널리티(crow’s foot, 1:1, 1:M, M:M등) 와 Optionality 확인


예1)

1.png



사원 없는 부서가 등록될수 있으며 모든 부서가 출력되도록 하려면 outer 기호 (+)를 반드시 붙여줘야함



예2)

2.png



사원 없는 부서가 등록될수 없으며 모든 부서가 출력되도록 하려고 불필요하게

outer 기호 (+)를 드시 붙여주면 성능이 나빠질수 있음


∴ 두 예 모두 사원쪽 부서번호가 필수 컬럼이므로 불필요한 값(null등)이 입력되지 않도록

not null제약조건등을 사용하거나 프리그램 작성 단계에서 설계에 맞게 작성해야 한다.




(02) Outer 소트 머지 조인

  • NL과 같으며 다만 소트된 중간 집합을 이용
  • 처리 방향이 한쪽으로 고정
  • Outer (+) 기호가 붙지 않은 테이블이 항상 First 테이블로 선택
  • leading 힌트로 순서가 바뀌지 않음





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

 2  from dept d, emp e

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


Execution Plan

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

Plan hash value: 2251696546

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

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

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

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

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

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

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






(3) Outer 해시 조인


  • 9i 까지는 방향이 고정 ( + 기호가 없는 테이블이 Build Input으로 선택)




9i


select /*+ use_hash(d e) */ d.dname, e.ename

from dept d, emp e

where e.deptno(+)=d.deptno

Execution Plan

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

  0          SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=328 Bytes=9512)

  1        0   HASH JOIN (OUTER) (Cost=5 Card=328 Bytes=9512)

  2        1         TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)

  3        1         TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=12 Bytes=84)


10g


Execution Plan

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

Plan hash value: 3713469723

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

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

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

|   0 | SELECT STATEMENT   |          |        14 |   252 |         7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |          |        14 |   252 |         7  (15)| 00:00:01 |

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

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

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

Predicate Information (identified by operation id):

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

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








3.png



1. Outer 집합인 dept 테이블을 해시테이블로 생성(Build)

2. Inner 집합인 emp 테이블을 읽어면서 해시 테이블을 탐색(Probe)

3. 조인 성공 -> 결과 집합에 삽입, 엔트리에 성공 표시

4. 조인 완료 후 해시 테이블을 스캔하면서 체크가 없는 dept 엔트리를 결과집합에 삽입




  • 조인순서 고정에 따른 문제점
    • 예를 들어 주문테이블을 기준으로 고객테이블과 outer 조인하는경우
      대용량인 주문테이블을 빌드하게 되므로 hash area부족에 따른
      디스크 쓰기, 버킷당 엔트리 개수의 증가등이 발생할수 있어 효율이 저하되게 된다.



Right Outer 해시 조인




select /*+ use_hash(d e) swap_join_inputs(d) */ d.dname, e.ename

from dept d, emp e

where e.deptno=d.deptno(+)


9i

Execution Plan

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

  0          SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=328 Bytes=9512)

  1        0   HASH JOIN (OUTER) (Cost=5 Card=328 Bytes=9512)

  2        1         TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=12 Bytes=84)

  3        1         TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)



10g

Execution Plan

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

Plan hash value: 4261033907

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

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

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

|   0 | SELECT STATEMENT      |      |        14 |   252 |         7  (15)| 00:00:01 |

|*  1 |  HASH JOIN RIGHT OUTER|      |        14 |   252 |         7  (15)| 00:00:01 |

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

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

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

Predicate Information (identified by operation id):

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

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




  • 방법
  1. Inner 집합인 dept 테이블을 해시 테이블로 빌드
  2. Outer 집합인 emp테이블을 읽으면서 해시테이블을 탐색
  3. 3.Outer 조인이므로 성공여부에 관계 없이 결과 집합에 삽입




참고 - 탄생배경


예1

4.png  


예1 모델 상으로 보면  고객없는 주문, 상품없는 주문을 허용하지 않게 된다.


outer 조인이 가능한경우는 고객을 기준으로 주문, 상품을 기준으로

주문과 outer조인을 하게되므로 큰문제가 없을거라 생각해서

9i까지는 outer테이블을 해시테이블로 빌드하도록 선택


예2

5.png  


하지만 운영중에서는 여러가지 이유로 ( 프로그램버그, 일부자료 삭제등)

예2와같은 경우게 생기게 되었으며 이로 인해

주문을 기준으로 고객과 상품을 outer조인할수 밖에 없는 경우가 발생하게 되어 이를 해결하기 위해

inner쪽 집합을 해시 테이블로 빌드 할수 있는 알고리즘 추가






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.주문일시




  • 방법
  1. 주문(o)과 고객(c) 테이블을 Outer 조인할때 주문 테이블에서 PK인덱스만 빠르게 읽어 Outer조인을 완성
  2. 주문 (o2)테이블과 다시한번 조인하면서 Inner조인
  • Build Input크기를 줄여서 디스크 쓰기 및 읽기 작업을 최소화
  • 하지만 버킷당 엔트리 개수 문제는 피할수 없음
  • 이때는 주문일시 구간을 나눠 쿼리를 여러번 수행하는 방법이로 처리가능





(4) Full Outer 조인



sample 생성


SQL> exec dbms_random.seed(150);

PL/SQL procedure successfully completed.


SQL> create table inc

as

select rownum serial

        , round(dbms_random.value(1, 20)) cmID

        , round(dbms_random.value(1000, 100000),-2) incamt

from dual connect by level <= 10;  2        3        4        5        6

Table created.


SQL> create table out

as

select rownum serial

        , round(dbms_random.value(1, 20)) cmID

        , round(dbms_random.value(1000, 10000),-2) outamt

from dual connect by level <= 10;  2        3        4        5        6

Table created.


SQL> exec dbms_stats.gather_table_stats(user, 'inc');

PL/SQL procedure successfully completed.


SQL> exec dbms_stats.gather_table_stats(user, 'out');

PL/SQL procedure successfully completed.






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

  • 입금 (inc) 과 출금 (out) 테이블을 Full Outer 조인해 고객별 입금액과 출금액을 집계할때



SQL> set autotrace on ;

SQL> select a.cmID, a.incamt, b.outamt

 2  from  (select cmID, sum(incamt) incamt from inc group by cmID) a

 3        ,(select cmID, sum(outamt) outamt from out group by cmID) b

 4        where  b.cmID(+) = a.cmID

 5        union all

 6  select cmID, null, outamt

 7        from  (select cmID, sum(outamt) outamt from out group by cmID) a

 8           where not exists (select 'x' from inc where cmID = a.cmID) ; 


         CMID         INCAMT         OUTAMT

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

           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

         CMID         INCAMT         OUTAMT

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

           16                      7500

            9                      1300

13 rows selected.



Execution Plan

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

Plan hash value: 1613876403

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

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

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

|   0 | SELECT STATEMENT              |          |        10 |   477 |        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    | INC      |        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    | OUT      |        10 |    60 |         3   (0)| 00:00:01 |

|   9 |   HASH GROUP BY               |          |         1 |     9 |         8  (25)| 00:00:01 |

|* 10 |        HASH JOIN ANTI         |          |         1 |     9 |         7  (15)| 00:00:01 |

|  11 |         TABLE ACCESS FULL     | OUT      |        10 |    60 |         3   (0)| 00:00:01 |

|  12 |         TABLE ACCESS FULL     | INC      |        10 |    30 |         3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  2 - access("B"."CMID"(+)="A"."CMID")

 10 - access("CMID"="CMID")







ANSI Full Outer 조인


  • 9i 부터 Ansi 구문을 지원




SQL> select nvl(a.cmID, b.cmID) cmID, a.incamt, b.outamt

from  (select cmID, sum(incamt) incamt from inc group by cmID) a

          full outer join

         (select cmID, sum(outamt) outamt from out group by cmID) b

        on a.cmID = b.cmID ;  2        3        4        5

         CMID         INCAMT         OUTAMT

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

           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

         CMID         INCAMT         OUTAMT

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

            9                      1300

           16                      7500

13 rows selected.


Execution Plan

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

Plan hash value: 1455049267

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

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

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

|   0 | SELECT STATEMENT             |      |    17 |   884 |    28  (15)| 00:00:01 |

|   1 |  VIEW                        |      |    17 |   884 |    28  (15)| 00:00:01 |

|   2 |   UNION-ALL                  |      |       |       |            |          |

|*  3 |        HASH JOIN OUTER       |      |     9 |   612 |     9  (34)| 00:00:01 |

|   4 |         VIEW                 |      |     9 |   342 |     4  (25)| 00:00:01 |

|   5 |          HASH GROUP BY       |      |     9 |    63 |     4  (25)| 00:00:01 |

|   6 |           TABLE ACCESS FULL  | INC  |    10 |    70 |     3   (0)| 00:00:01 |

|   7 |         VIEW                 |      |     8 |   240 |     4  (25)| 00:00:01 |

|   8 |          HASH GROUP BY       |      |     8 |    48 |     4  (25)| 00:00:01 |

|   9 |           TABLE ACCESS FULL  | OUT  |    10 |    60 |     3   (0)| 00:00:01 |

|  10 |        HASH GROUP BY         |      |     8 |    48 |    19   (6)| 00:00:01 |

|* 11 |         FILTER               |      |       |       |            |          |

|  12 |          TABLE ACCESS FULL   | OUT  |    10 |    60 |     3   (0)| 00:00:01 |

|  13 |          SORT GROUP BY NOSORT|      |     1 |     3 |     3   (0)| 00:00:01 |

|* 14 |           TABLE ACCESS FULL  | INC  |     1 |     3 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  3 - access("A"."CMID"="B"."CMID"(+))

 11 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "INC" "INC" WHERE

                 "CMID"=:B1 GROUP BY "CMID"))

 14 - filter("CMID"=:B1)




  • 쿼리만 간단해졌을 뿐 inc와 out테이블을 각각 두번식 액세스 하는 비효을은 그대로임



Native Hash Full Outer 조인


  • oracle 11g 에서 추가
  • 10.2.0.4 - hidden 파라미터 _optimizer_native_full_outer_join 을 조정해 사용가능


oracle 11.2.0.1.0




select  nvl(a.cmID, b.cmID) cmID, a.incamt, b.outamt

from  (select cmID, sum(incamt) incamt from inc group by cmID) a

          full outer join

         (select cmID, sum(outamt) outamt from out group by cmID) b

        on a.cmID = b.cmID ;

         CMID         INCAMT         OUTAMT

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

            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

         CMID         INCAMT         OUTAMT

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

           16                      7500

            9                      1300

13 rows selected.

Execution Plan

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

Plan hash value: 2412051235

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

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

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

|   0 | SELECT STATEMENT          |          |     9 |   468 |     7  (43)| 00:00:01 |

|   1 |  VIEW                     | VW_FOJ_0 |     9 |   468 |     7  (43)| 00:00:01 |

|*  2 |   HASH JOIN FULL OUTER    |          |     9 |   468 |     7  (43)| 00:00:01 |

|   3 |        VIEW               |          |     8 |   208 |     3  (34)| 00:00:01 |

|   4 |         HASH GROUP BY     |          |     8 |    48 |     3  (34)| 00:00:01 |

|   5 |          TABLE ACCESS FULL| OUT      |    10 |    60 |     2   (0)| 00:00:01 |

|   6 |        VIEW               |          |     9 |   234 |     3  (34)| 00:00:01 |

|   7 |         HASH GROUP BY     |          |     9 |    63 |     3  (34)| 00:00:01 |

|   8 |          TABLE ACCESS FULL| INC      |    10 |    70 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  2 - access("A"."CMID"="B"."CMID")




**10.2.0.4  


/*+ opt_param('_optimizer_native_full_outer_joinc', 'force') */


추가 해야함


  • 결과로 추정
  1. out 테이블을 해시 테이블로 빌드 (build)
  2. ins 테이블로 해시 테이블을 탐색 (Probe) 하면서 조인
  3. 조인 성공여부에 상관없이 결과집합에 삽입, 조인에 성공한 out레코드는 체크




Union All 을 이용한  Full Outer 조인


  • union all을 이용하면 버전에 상관없이 Full Outer 조인된 결과 집합을 얻을수 있다.




select cmID, sum(incamt) incamt, sum(outamt) outamt

from (

 select cmID, incamt, to_number(null) outamt

 from   inc

 union all

 select cmID, to_number(null) incamt, outamt

 from   out

)

group by cmID ;

         CMID         INCAMT         OUTAMT

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

            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

         CMID         INCAMT         OUTAMT

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

            9                      1300

           16                      7500

13 rows selected.


Execution Plan

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

Plan hash value: 624846006

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

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

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

|   0 | SELECT STATEMENT         |      |    20 |   780 |     7  (15)| 00:00:01 |

|   1 |  HASH GROUP BY           |      |    20 |   780 |     7  (15)| 00:00:01 |

|   2 |   VIEW                   |      |    20 |   780 |     6   (0)| 00:00:01 |

|   3 |        UNION-ALL         |      |       |       |            |          |

|   4 |         TABLE ACCESS FULL| INC  |    10 |    70 |     3   (0)| 00:00:01 |

|   5 |         TABLE ACCESS FULL| OUT  |    10 |    60 |     3   (0)| 00:00:01 |

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








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