반응형

/********************************************************************************************
-- Title : [2k] 조인 방식에 따른 조인의 특성 비교
-- Reference : dbrang.tistory.com
--                  blog.naver.com/ssayagain/90036001354

-- Key word : 조인 join driving driven 안쪽 바깥 선행 후행 nested loop sorted merge hash
********************************************************************************************/



-- 용어 정리
  ㅇ선행 테이블 = Driving 테이블 = 바깥 테이블(outer)
  ㅇ후행 테이블 = Driven 테이블  = 안쪽 테이블(inner)
  ㅇ여기서 바깥/안쪽의 의미는 조인에서의 위치가 아닌 먼저 조회 되느냐 마느냐의 의미임.
    ┌──┐    ┌──┐
    │      │    │      │    ┌──┐
    │      │ →│      │    │      │
    │      │    │      │ →│      │
    │      │ →│      │ →│      │
    │      │    │      │    │      │
    │      │ →│      │    └──┘
    └──┘    └──┘
     바깥
     Driving
     선행 
 
  ㅇ힌트가 없을 시 Driving 테이블은 옵티마이저가 알아서 배치하나 힌트가 있을 시에는
    테이블 배치 순서에 따른다.

  ㅇ아래 쿼리는 title에 PK가 설정된 것으로 Q2은 강제로 loop 힌트를 주어 titles가
    driving되었고, Q1는 옵티마이저가 선택하여 titleauthor를 driving 시킨다.
    -- Q1)
    select title
    from titles inner join titleauthor
                on titles.title_id = titleauthor.title_id
    where titleauthor.royaltyper > 50
    -- Q2)
    select title
    from titles inner loop join titleauthor
                on titles.title_id = titleauthor.title_id
    where titleauthor.royaltyper > 50

-- 조인의 종류
  ㅇNested Loop Join
    > 바깥 테이블의 일치하는 모든 행을 안쪽 테이블에서 반복하여 찾는다.
   
  ㅇMerge Join
    > 조인 컬럼을 순서대로 양족 테이블에서 한번 읽어서 연결한다.

  ㅇHash Join
    > 해쉬 테이블을 바깥 테이블을 근거로 만들고,
      안쪽 테이블의 모든행을 해쉬 테이블을 통과 시켜 일치하는 행을 찾는다.

-- Nested Loop Join
  ㅇ선행 테이블의 일치하는 모든 행을 후행 테이블에서 반복하여 찾는다.
  ㅇ후행 테이블에 인덱스가 있으면 최상(인덱스 없는 놈을 나중에 쓴다.)
  ㅇ선행 테이블의 처리 결과를 후행 테이블에서 받아 필터링하기에 선행 테이블이 전체 일의 양이다.
  ㅇ후행 테이블의 필터링 조건은 선행 테이블에서 나온 결과에 대한 추가 필터링 역할만 한다.
  ㅇ메모리 사용량 적다.
  ㅇ어느 테이블이 먼저 사용? 크기와 상관 없다.(필터링된 양이 적은 테이블이 선행되는게 좋겠지..)
  ㅇ필요하다면 선행 테이블을 정렬하고 조인된다.
  ㅇ양이 적은 경우에 유효하다.
  ㅇRanddom I/O가 많이 발생할 수 있다.
  ㅇCPU(Random I/O에 의한 ↑), I/O (↓), Memory (?)


-- Merge Join
  ㅇ양 테이블이 모두 조인 키에 의해 정렬되어 있다.
  ㅇ처리량은 각 테이블이 가진 필터링 조건에 의해 결정된다.
  ㅇ정렬하기에 적합한 인덱스가 제공된다.
  ㅇ어느 테이블이 먼저 사용? 큰 것이 안/바깥 모두에 나타날 수도 있다.
  ㅇ후행 테이블에 중복 행이 존재하지 않는다면 메모리 사용량이 적다.
  ㅇLoop 조인이나 Hash 조인과 달리 어느 쪽 테이블 먼저 사용하는 것이 중요치 않지만
    > 양쪽 테이블에 모두 중복이 있다면, 테이블은 반드시 조인 하기 전에 정렬되어야 한다.
  ㅇ양쪽에 정렬이 되니 양쪽에 인덱스가 있으면 좋다.
  ㅇ처리량이 많을 때 Random I/O가 적어 유효하다.
  ㅇ정렬에 따른 메모리 사용이 증가할 수 있다.
  ㅇCPU(정렬로 ↑), I/O (?), Memory (정렬로 ↑)


-- Hash Join
  ㅇ선행 테이블이 해쉬 테이블로 사용된다.
  ㅇ선행 테이블은 후행 테이블이 처리 되기 전에 완전히 모두 읽혀진다.(hash join의 특징)
  ㅇ작은 테이블이 바깥 테이블로 사용된다.(작은 테이블 우선)
  ㅇNo order is preserved(hash join 특징으로 필요시 Order by를 해줘야 한다.)
  ㅇ선행 테이블이 커지면 해시 버켓으로 인해 메모리 사용량은 늘어난다.
  ㅇ인덱스는 사용되지 않는다. 따라서 임의성 쿼리에 탁월한 성능 발휘.(hash index생성)
  ㅇ자주 나타나면 인덱스가 없거나 상당히 큰 테이블에 유효하다.
  ㅇCPU(해시처리로 ↑), I/O (↓), Memory(해시 버켓 생성으로 ↑)

-- Input Order가 필요한가?(요즘은 옵티마이저가 똑똑해서 필요 읎다!!)
  ㅇLoop Join  - 아니다, 하지만 더 나은 성능을 위해 선행 테이블이 정렬될 수도 있다.
  ㅇMerge Join - 양 테이블은 반드시 조인 키에 의해 정렬되어야 한다.
  ㅇHash Join  - 필요없다.

-- Sort Order가 필요한가?
  ㅇLoop Join  - 선행 테이블
  ㅇMerge Join - 양 테이블
  ㅇHash Join  - 필요없다.

-- 선행/후행 테이블 선택은?
  ㅇLoop Join  - 후행 테이블은 인덱스가 있거나 작아야 한다.
  ㅇMerge Join - 별로 중요하지 않다.
  ㅇHash Join  - 작은 테이블이 선행 테이블이 되어야 한다.
  ㅇOuter Join의 경우 outer 되는 쪽이 Driving된다.

-- 메모리 사용량
  ㅇLoop Join  - 추가 메모리 필요 없다.
  ㅇMerge Join - back loop를 위해 추가 메모리 필요.
  ㅇHash Join  - 해쉬 테이블 위해 메모리 필요.

/*
ref) 조인 관련 그림은 단순히 다음 쿼리에 대한 전개 순서를 보여준 것이다.
SELECT COL1, COL2     -- indexed on a.key and b.key only.
FROM TAB1 A
INNER JOIN TAB2 B
ON A.KEY = B.KEY
WHERE A.KEY = '111'  
AND A.COL1 LIKE '222%'
AND B.COL2 = '333'
*/

반응형

+ Recent posts