조인 검색

조인(join) 검색이란?

• 여러 개의 테이블을 연결하여 데이터를 검색하는 것
• FROM 절에 검색에 필요한 모든 테이블을 나열하고,
  WHERE 절에는 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시한다.
• 테이블은 다르지만 속성명이 같은 경우가 많으니 속성명 앞에 테이블명을 지정해준다.
• 테이블 지정 시 약칭도 같이 지정해준 것이 좋다.
  • 쿼리가 길어지는 것을 방지할 수 있다.
  • 같은 테이블을 조인할 떄 쉽게 구분하기 위한 용도도 있다.
• FROM 절에 해당하는 테이블을 지정하는 역할이라서
  WHERE, GROUP BY, HAVING, ORDER BY같은 키워드도 모두 사용할 수 있다.
• 종류
  • 논리적 조인
    • Inner Join (내부 조인)
    • Outer Join (외부 조인)
    • Cross Join (상호 조인)
    • Self Join (자체 조인)
  • 물리적 조인
    • Nested Loops Join (중첩 반복 조인)
    • Hash Join (해시 조인)
    • Sort Merge Join (정렬 병합 조인)

조인의 종류

• ANSI-SQL 기준으로 작성

Inner Join (내부 조인)

• 테이블 A와 테이블 B의 교집합에 해당하는 결과를 검색한다.
• A ∩ B
• 기본 형식

SELECT
    속성명_목록
FROM
    테이블A AS A
    [INNER] JOIN
        테이블B AS B
        ON A.조인할_속성명 = B.조인할_속성명

Outer Join (외부 조인)

Left Outer Join

• 테이블 A와 테이블 B의 교집합과 차집합을 합친 것에 해당하는 결과를 검색한다.
• 이 떄에 해당하는 차집합은 A - B다.
• (A ∩ B) ∪ (A - B)
• 기본 형식

SELECT
    속성명_목록
FROM
    테이블A AS A
    LEFT OUTER JOIN
        테이블B AS B
        ON A.조인할_속성명 = B.조인할_속성명

RIght Outer Join

• 테이블 A와 테이블 B의 교집합과 차집합을 합친 것에 해당하는 결과를 검색한다.
• 이 떄에 해당하는 차집합은 B - A다.
• (A ∩ B) ∪ (B - A)
• 기본 형식

SELECT
    속성명_목록
FROM
    테이블A AS A
    RIGHT OUTER JOIN
        테이블B AS B
        ON A.조인할_속성명 = B.조인할_속성명

Full Outer Join

• 테이블 A와 테이블 B의 합집합에 해당하는 결과를 검색한다.
• A ∪ B
• 기본 형식

SELECT
    속성명_목록
FROM
    테이블A AS A
    FULL OUTER JOIN
        테이블B AS B
        ON A.조인할_속성명 = B.조인할_속성명

Cross Join (상호 조인)

• 테이블 A의 모든 투플과 테이블 B의 모든 투플을 조인한 결과를 검색한다.
• 카티션 프로덕트라고도 부른다.
• 결과 투플의 개수는 각 테이블의 행 개수를 곱한 값이 된다.
• 기본 형식

SELECT
    속성명_목록
FROM
    테이블A AS A
    CROSS JOIN
        테이블B AS B
        ON A.조인할_속성명 = B.조인할_속성명

Self Join (자체 조인)

• 같은 테이블로 조인하는 방식
• 별도의 문법이 있는 것은 아니다.
  • 같은 테이블끼리 조인하면 자체 조인으로 취급한다.
• 기본 형식

SELECT
    속성명_목록
FROM
    테이블A AS A
    INNER JOIN JOIN
        테이블A AS B
        ON A.조인할_속성명 = B.조인할_속성명

조인 방식

• 테이블 조인 방식은 각 테이블 간 속성 매핑 시 사용한 메커니즘에 따라서 달라진다.
• 옵티마이져가 적합한 조인 메커니즘을 선택하여 실행 계획을 생성한다.
  • 테이블 통계정보의 오류 등으로 인하여 적합하지 않은 조인 방식이 선택될 수도 있다.
  • 잘못 선택된 조인 방식은 성능 상의 부하를 유발하는 경우가 있다.
• 각 조인 방식에 대한 메카니즘을 파악하여 조회 쿼리에 적합한 조인 방식이 선택되도록 유도하는 것이 중요하다.
  • 데이터베이스 성능 향상을 위한 중요한 방법 중의 하나다.
• 종류
  • Nested Loops Join (중첩 반복 조인)
  • Hash Join (해시 조인)
  • Sort Merge Join (정렬 병합 조인)

Nested Loops Join (중첩 반복 조인)

• 정의
  • 바깥 테이블(드라이빙 테이블)의 처리 범위를 하나씩 엑세스하면서
    그 추출된 값으로 안쪽 테이블(드리븐 테이블)을 조인하는 방식
  • 즉, 2중 반복문의 형태를 가진다.
  • NL 조인이라고도 부른다.
• 특징
  • 순차적인 진행
    • 첫 테이블 필터링 -> 두 테이블간 JOIN - > 최종운반 단위 산출까지 반복적, 순차적으로 진행한다.
  • 선행적
    • 선행 테이블의 처리 범위가 전체 일의 양을 결정한다.
    • 후행 테이블의 필터링 조건은 선행 테이블에서 나온 결과를
      한번 더 걸러주는 체크 조건 역할을 할 뿐
      전체 처리량을 좌우하지 않는다.
  • 종속적
    • 후행 테이블은 선행 테이블의 결과 값을 받아 처리된다.
    • 선행 테이블의 결과에 종속적이다.
    • 선행테이블을 후행 테이블의 전체 일의 양을 줄여줄 수 있는 필터링 조건으로는 사용 할 수 없다.
      (체크 조건으로만 사용 가능)
  • Random Access
    • 선행 테이블의 결과를 통해 후행 테이블을 할때 대량의 랜덤 I/O가 발생한다.
    • 선행 테이블은 최초 ROW만 엑세스가 발생하고 이후에는 스캔방식으로 진행한다.
• 장점
  • 처리량이 적다.
    • 랜덤 I/O 때문에 선행 테이블의 카디널리티를 획기적으로 줄일 수 있다면
      메모리를 가장 적게 사용하는 좋은 조인 방식이 된다.
  • 부분 범위 처리
    • 다른 조인 방법은 부분 범위 처리가 원천적으로 불가능하다.
  • 처리의 방향성이 필요하다.
    • 다른 테이블의 처리 결과를 받아야만 처리 범위를 확 줄여줄 수 있을 때 사용하면 좋다.
• 단점
  • 두 테이블을 연결할 때의 랜덤 I/O가 가장 큰 부담
• 관련 용어
  • 드라이빙 테이블(Driving Table)
    • 조인 대상 테이블 중 먼저 액세스하는 테이블
    • Outer Table이라고도 부른다.
  • 드리븐 테이블 (Driven-to Table)
    • 조인 대상 테이블 중 나중에 액세스하는 테이블
    • Inner Table이라고도 부른다.
• 좋은 성능을 내기 위한 조건
  • 조인 테이블 중 추출 건수가 적은 테이블을 드라이빙 테이블로 선정한다.
    • 드라이빙 테이블의 추출 건수가 곧 드리븐 테이블의 액세스 반복 횟수가 된다.
    • 엑세스 반복 횟수가 적을수록 성능이 올라간다.
  • 드리븐 테이블에 조인할 컬럼으로 구성된 인덱스가 존재해야 한다.
    • 드리븐 테이블은 드라이빙 테이블의 추출 건수만큼 반복해서 액세스하게 된다.
    • 드리븐 테이블에 조인할 컬럼으로 구성된 인덱스가 존재한다면
      드리븐 테이블의 모든 데이터를 액세스 하지 않아도
      인덱스를 이용해 조인 조건에 맞는 로우를 추출할 수 있다.
    • 드리븐 테이블에 조인 컬럼으로 구성된 인덱스가 존재하지 않는다면
      Full table scan이 반복적으로 발생하여 성능상 이슈가 발생할 수 있다.

Hash Join (해시 조인)

• 정의
  • 조인할 컬럼 값의 해시 함수 결과를 이용하여 조인하는 방식
• 특징
  • 조인할 테이블에 대해서 해시 테이블 생성 후 해시 테이블의 순서대로 결과를 출력한다.
    • 테이블의 인덱스는 사용되지 않는다.
    • 인덱스가 없거나 임의성 쿼리에서 성능이 좋다.
  • 해시 테이블을 만들기 때문에 많은 메모리가 사용한다.
  • 소량과 대용량 테이블을 조인할 때 사용하면 좋다.
• 관련 용어
  • 빌드 테이블 (Build Table)
    • 해시 조인에서 먼저 액세스 하는 테이블
  • 프로브 테이블 (Probe Table)
    • 해시 조인에서 나중에 액세스 하는 테이블
• 좋은 성능을 내기 위한 조건
  • 조인 테이블 중 추출 건수가 적은 테이블을 빌드 테이블로 선정한다.
    • 조인을 위해 빌드 테이블의 데이터는 PGA 메모리의 해시 테이블에 저장된다.
    • 해시 테이블의 크기가 커서 PGA 메모리가 부족해지면 가상 메모리를 사용하기 떄문에 DISK I/O가 발생하게 된다.
      • 즉, 데이터 건수가 적은 테이블이 빌드 테이블로 선정되야 한다.
    • 쿼리문 상에 결과 셋에 저장될 컬럼명만 명시하여 해시 테이블의 크기를 최소화해야 한다.
  • 프로브 테이블은 Full Table Scan이 유리하다.
    • 일반적으로 해시 조인에서 프로브 테이블은 대용량의 테이블이다.
    • 만약 인덱스를 이용해 프로브 테이블을 액세스하면 테이블 랜덤 액세스가 과도하게 발생하여 블록 I/O가 증가하게 된다.
    • 테이블 액세스 없이 INDEX SCAN만으로 데이터 추출이 가능한 경우는 제외하고,
      되도록이면 인덱스 스캔 없이 테이블만 스캔하여 블럭 I/O를 줄여야 한다.

Sort Merge Join (정렬 병합 조인)

• 정의
  • 조인할 컬럼을 기준으로 각 테이블을 정렬하여 작은 값부터 조인할 칼럼의 값이 매칭되는지 확인하면서 조인한다.
• 특징
  • 동시적 처리
    • 각 키에 의해 정렬된 양쪽 행들을 순차적으로 병합하여 조인을 수행한다.
  • 인덱스 필요
    • 양 테이블이 모두 조인키에 의해 정렬되어 있어야 한다.
  • 전체 범위 처리
    • 선행/후행 테이블의 크기는 성능에 영향을 미치지 않는다.
    • 다만, 선행 테이블에 중복되는 행이 존재하지 않을 때는 메모리 사용량이 적어서 정렬 병합 조인이 권장된다.
  • 스캔 방식
    • 주로 스캔 방식을 이용한다.
    • 자신의 처리 범위를 줄이기 위해 인덱스를 사용하는 경우만 랜덤 엑세스 방식을 이용한다.
      • 나머지 작업은 스캔 방식을 이용한다.
  • 독립적
    • 정렬 병합 조인에서 처리 범위를 줄일 수 있는 유일한 수단은 각자가 가지고 있는 필터링 조건이다.
• 장점
  • 처리량이 많을때 성능상 이점 존재한다.
• 단점
  • 정렬에 의해서 사용되는 메모리가 증가한다.
    • Clustered index를 사용하게 되면 정렬은 따로 하지 않아도 되서 부담은 없다.

STRAIGHT_JOIN이란?

• 드라이빙 테이블과 드리븐 테이블의 순서가 FROM절에 명시한대로가 아닌
  옵티마이저에 의해서 내부적으로 변경된 경우에 실행 속도가 느려지는 경우가 있다.
• 위의 경우에 해당한다면 STRAIGHT_JOIN 키워드를 통해서 강제로 순서를 지정해줄 수 있다.
• SELECT 키워드 바로 뒤에 명시하면 된다.
• MySQL에서만 지원한다.