실행계획 - join

[ 실행계획 - join ]

1. 정의

   . 두 개 이상의 테이블을 마치 하나의 테이블처럼 만들기 위해 연결

2. Sort Merge Join

   - 동시적(무조건 전체범위처리). 전체 데이터를 리턴받는 시간이 Nested-Loop보다 빠르다.
   - 추가적인 sort메모리 비용이 필요하다. 메모리 공간이 부족하다면 temp tablespace를 사용하게 됨.
   - Sort 메모리에 위치하는 대상은 Join key뿐만 아니라 select list도 포함하므로 불필요한 select list는 제거해야 한다.
   - Sort를 요구하지 않는 경우는 Hash Join이 유리하다.
   - 독립적(자기의 처리범위만으로 처리량 결정)
   - 스캔(Scan)액세스 위주
   - 연결고리 상태에 영향이 없음
   - 주로 넓은범위 처리에 유리

 

   장단점
   단점 - 두 결과 집합의 크기가 많이 차이나는 경우에는 SORT Merge Join이 비효율적
   어느 한쪽이라도 정렬 작업이 종료되지 않으면 조인이 시작될 수 없으므로 
   두 테이블 조인 집합의 크기가 많이 차이가 난다면 한쪽에 '대기' 상태가 발생하여
   비효율적으로 처리가 된다. 이렇게 크기가 비슷하지 않은 집합의 조인을 위해서 
   HASH 조인을 사용할 수 있다.

 

   - Sort Merge Join 수행 예제

    SELECT /*+ USE_MERGE(E,D) */ E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

- PLAN

 

. DEPT 를 읽어서 정렬

  EMP 를 읽어서 정렬

   WHERE 조건인 DEPTNO 를 기준으로 서로 MERGE

   > 위 2가지를 수행하므로 SORT MERGE JOIN이라고 함

- 특징

  두 테이블을 모두 읽고 각각 정렬

  두 테이블간에 비교(WHERE 조건을 기준으로)

 

3. Nested LOOP JOIN

   - Nested LOOP Join 수행하면 좋은 경우

      . 조인 조건 컬럼에 인덱스가 없는 경우

      . 조인 조건 컬럼에 Data type이 다른 경우

      . 주로 랜덤 엑세스에 유리

      . 부분 범위처리 가능 (즉, 먼저 검색된 레코드를 먼저 join시킴)

      . 연결 고리 상태에 따라 성능 차이가 많이 발생

      . Drving table 지정시 ordering hint 사용하면 좋다.

 

   - 중첩 Loop join (프로그램으로는 이중 for 문으로 생각하면 됨)

   - 수행 방법 예제

     . 두개의 테이블간 JOIN을 수행 할 경우

       첫번째 테이블의 레코드 결과가 3개 존재 할 경우(PK값  A, B, C)

       첫번째 레코드 A값을 가진 레코드를 두번째 테이블과 JOIN하고

       두번째 레코드 B값을 가진 레코드를 두번째 테이블과 JOIN하고

       세번째 레코드 C값을 가진 레코드를 두번째 테이블과 JOIN 한다.

       > 이와 같이 하나의 레코드별로 반복 JOIN 수행 됨

       > Driving Table에 결과가 많을 경우 반복Join (Loop join)이 많이 수행되어 속도가 느리다.

     . Driving Table이 적은 결과 갯수일 수록 성능이 좋음

   - 조회

     SELECT /*+ USE_NL(E,D) */ E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO = 20;

  - PLAN

     

     . DEPT 를 먼저 읽고 EMP 를 나중에 읽음

       (예전에는 FROM 앞쪽부터 먼저 읽었지만 CBO로 넘어 오면서 뒤쪽부터 읽음)

     . 

   - FROM 앞쪽부터 테이블을 읽게 하려면 (hint  ORDERED)

      SELECT /*+ ORDERED USE_NL(E,D) */ E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

 

. PLAN

      

4. Hash Join

   - Hash Join 수행하면 좋은 경우

      .

   - Hash Join 수행 예제

     . 기존에 존재 하는 index들 모두 제거

      select constraint_name, table_name from user_constraints where table_name in ('EMP', 'DEPT'); alter table dept drop constraint pk_dept;

     . index가 없는 상태에서 join

      SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

 

   

   . dept, emp를 Table full scan 후 서로 Hash Join함

   .

  - HASH JOIN은 HASH기법을 이용하여 조인을 수행

    조인 칼럼을 기준으로 HASH FUNCTION를 수행하여 서로 동일한 HASH VALUE을

    갖는 것들 사이에서 실제 값이 같은지를 비교하면서 주인을 수행

    

  - NL Join랜덤 액세스와 SortMerge Join의 문제점인 정렬 작업의 부담을

    해결하기 위한 대안으로 등장

  - Hash Join은 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있는 기법

  - Hash Join은 해쉬 함수를 이용하여 조인을 수행하기 때문에 = 로 수행하는

    join, 동등조건에서만 사용할 수 있다.

  - Hash Join을 할 때는 결과 행의 수가적은 테이블을 Driving Table로 사용하는 것이 좋다.

  - 현재 가능 좋은 성능 

    

     > 선행테이블에서 자료를 추출 후 hash 값을 만들어서 SGA 영역에 올려놓고

       후행테이블에서 자료를 추출 후 hash 값을 만들어서 SGA 영역에 올려놓은 후

       서로 JOIN을 수행하여 결과 조회

       (이 Hash값을 만들어서 join하는 방법은 oracle의 노하우다)

5. OUTER JOIN

    . 단점

      - OUTER JOIN이 걸려 있는 테이블은 FULL SCAN하는 경우가 존재

        (차후 레코드가 많아지면 수행속도가 아주 느려진다.)

         > 예) SELECT * FROM 고객  C,   주문 O

                   WHERE C.고객NO(+) = O.고객NO;

    . 특징

      - 처리 방향이 한쪽으로 고정

      - Outer(+)기호가 붙지 않은 테이블이  항상 Driving table로 선택

      - READING힌트를 넣어도 순서가 바뀌지 않음