SQL 튜닝비법 - 조인

  1. 조인이란? 두 개 이상의 테이블을 연결하여 원하는 데이터를 조회하거나 변경하고 삭제하는 작업을 일컫는다.
  2. 조인의 종류
    1. 조인의 종류는 대상 데이터에 따른 조인 종류와 수행 방식에 따른 조인 종류 두가지로 나누어서 볼 수 있다.
      1. 대상 데이터에 따른 조인 종류
        1. INNER JOIN : 일반적으로 조인으로 불리며, 두 개 이상의 테이블로 부터 조인 조건을 만족하는 데이터만 반환해준다.
          1. 사용 방법
            INNER JOIN 사용법
            SELECT E.NAME, D.DEPT_NAME, D.ADDRESS
            FROM EMP E, DEP D
            WHERE E.DEPTNO = D.DEPTNO
            ;

            SELECT E.NAME, D.DEPT_NAME, D.ADDRESS
            FROM EMP E INNER JOIN DEP D
            ON E.DEPTNO = D.DEPTNO
            ;
        2. OUTTER JOIN : 두 개 이상의 테이블 중에서 기준이 되는 테이블 하나를 선정하여 그 테이블의 데이터는 조인 조건을 만족하지 않아도 반환해준다.
          1. 기준 테이블을 FROM 절을 기준으로 왼쪽 테이블로 선정 시 LEFT OUTTER JOIN, 오른쪽 테이블로 선정 시 RIGHT OUTTER JOIN 이다.
          2. 사용 방법
            OUTTER JOIN 사용법
            -- LEFT OUTTER JOIN
            SELECT E.NAME, D.DEPT_NAME, D.ADDRESS
            FROM EMP E LEFT OUTTER JOIN DEP D
            WHERE E.DEPTNO = D.DEPTNO
            ;
            SELECT E.NAME, D.DEPT_NAME, D.ADDRESS
            FROM EMP E, DEP D
            WHERE E.DEPTNO = D.DEPTNO(+)
            ;

            -- RIGHT OUTTER JOIN
            SELECT E.NAME, D.DEPT_NAME, D.ADDRESS
            FROM EMP E RIGHT OUTTER JOIN DEP D
            WHERE E.DEPTNO = D.DEPTNO
            ;
            SELECT E.NAME, D.DEPT_NAME, D.ADDRESS
            FROM EMP E, DEP D
            WHERE E.DEPTNO(+) = D.DEPTNO
            ;
        3. FULL OUTTER JOIN : 조인이 되는 양쪽 테이블을 모두 기준 테이블로 하여 모든 경우의 수를 반환해준다.
          1. 사용 방법
            FULL JOIN 사용법
            SELECT E.NAME, D.DEPT_NAME, D.ADDRESS
            FROM EMP E FULL OUTTER JOIN DEP D
            ON E.DEPTNO = D.DEPTNO
            ;
        4. CARTESIAN JOIN : 조인 조건 없이 테이블을 조인하여 모든 경우의 수를 반환해준다.
          1. 데이터 복제라는 개념을 활용하기 위해 사용한다.
          2. 불필요한 경우 성능문제를 야기하기 때문에 의도한 것인지 반드시 확인하도록 한다.
          3. 사용 방법
            CARTESIAN JOIN 사용법
            SELECT E.NAME, D.DEPT_NAME, D.ADDRESS
            FROM EMP E, DEP D
            ;
          4. 적용 예시
            적용 예시
            -- 같은 조건의 테이블을 여러번 사용
            SELECT '직군별' AS CLASS, JOB, COUNT(*) AS CNT
            FROM EMP
            GROUP BY JOB
            UNION ALL
            SELECT '부서별' AS CLASS, TO_CHAR(DEPTNO), COUNT(*) AS CNT
            FROM EMP
            GROUP BY DEPTNO
            UNION ALL
            SELECT '총인원' AS CLASS, NULL, COUNT(*) AS CNT
            FROM EMP
            ;

            -- 데이터 복제를 이용
            SELECT
             DECODE(RN, 1, '직군별', 2, '부서별', '총인원') AS CLASS,
             DECODE(RN, 1, JOB, 2, DEPTNO),
             SUM(CNT)
            FROM (SELECT JOB, DEPTNO, COUNT(*) AS CNT
                        FROM EMP
                        GROUP BY JOB, DEPTNO),
                       (SELECT ROWNUM AS RN
                        FROM (SELECT LEVEL FROM DUAL
                        CONNECT BY ROWNUM <= 3))
            GROUP BY RN,
            DECODE(RN, 1, '직군별', 2, '부서별', '총인원'),
            DECODE(RN, 1, JOB, 2, DEPTNO)
            ;
        5. SELF JOIN : 하나의 테이블이 스스로 조인하는 방식으로 순환참조 관계로 표현된 개체를 참조할 때 사용된다.
          1. 사용 방법
            SELF JOIN 사용법
            SELECT E1.EMPNO E2.EMPNO AS MANAGERNO
            FROM EMP E1, EMP E2
            WHERE E1.MANAGERNO = E2.EMPNO
            ;
      2. 수행 방식에 따른 조인 종류
        1. 수행 방식에 따른 조인은 실행 계획으로 확인 가능하다.
        2. 수행 방식에 따른 조인을 확인하기 전에 드라이빙 테이블과 드리븐 테이블에 대해서 알아야 한다.
          1. 드라이빙 테이블 : 기준이 되는 테이블로 조인 시 작업이 선행되는 테이블로 해당 테이블을 기준으로 그 이후의 테이블에 데이터를 매칭하며, Outer Table 이라고도 한다.
          2. 드리븐 테이블 : Outer Table에 의해서 매칭이 되어지는 테이블이며, Inner Table 이라고도 한다.
        3. NESTED LOOP JOIN
          1. 옵티마이저가 드라이빙 테이블을 결정한다.
          2. 드라이빙 테이블로 선정된 테이블의 각 컬럼에 대해서 드리븐 테이블에 연관된 모든 컬럼을 조인한다.
          3. 튜닝 포인트
            1. 테이블 간의 조인 횟수를 최소화 할 수 있도록 구성한다.
            2. 드리븐 테이블의 연결 고리 컬럼에 대해서 인덱스를 반드시 구성한다.
          4. 장점과 단점
            1. 인덱스에 의한 랜덤 액세스에 기반하고 있어 대량의 데이터 처리 시에는 적합하지 않다.
            2. 드라이빙 테이블로는 테이블의 데이터가 적은 마스터 테이블이거나 WHERE 절 조건으로 적절하게 컬럼을 제어할 수 있는 것이어야 한다.
            3. 드리븐 테이블에는 조인을 위한 적절한 인덱스가 반드시 생성되어 있어야 한다.
          5. 조인 횟수를 줄이기 위해서는 테이블 간에 조인되는 순서가 무엇보다 중요하다.
            1. 조인 순서 제어 방법
              1. 조인 순서 제어를 위한 힌트 사용
                1. ORDERED : FROM 절에 기술한 테이블 순서로 제어
                2. LEADING(테이블 명) : 힌트 내에 제시한 테이블이 드라이빙 테이블로 채택
                3. ORDERED 힌트와 LEADING 힌트를 같이 사용 시 LEADING 힌트가 무시된다.
              2. 뷰(View) 활용
              3. 서프레싱(Suppressing) 활용
              4. FROM 절의 테이블 순서 변경 : RBO 에서만 적용 가능하며 CBO에서는 적용되지 않는다.
          6. 연결고리 컬럼에 대한 인덱스의 중요성
            1. 양쪽 모두 인덱스가 있는 경우 두 테이블 중 조회되는 결과가 적은 테이블을 옵티마이저가 선택하여 드라이빙 테이블로 선정한다.
            2. 한쪽만 인덱스가 있는 경우 인덱스가 없는 쪽 테이블을 드라이빙 테이블로 사용한다.
            3. 양쪽 모두 인덱스가 없는 경우 Nested Loops 조인 방식으로 조인이 이루어 지지 않고, HASH JOIN이나 SORT/MERGE JOIN으로 동작한다.
        4. HASH JOIN
          1. 동작 방법 : 읽어 들인 각 테이블의 데이터를 서로 조인하기 위해서 해싱(Hashing)을 이용한 해시 값을 만들어서 조인을 수행한다.
          2. 튜닝 포인트
            1. 드라이빙 테이블을 결정한다.
            2. 각 테이블로 부터 데이터를 읽어 들일 때, 빨리 읽을 수 있도록 한다.
            3. 메모리(HASH_AREA_SIZE)를 최적화 한다.
          3. 장점과 단점
            1. Hash Bucket이 조인 집합에 구성되어 해시 함수 결과를 저장해야 하는데 이러한 처리에는 많은 메모리와 CPU 자원을 소모하게 된다.
            2. 기본적으로  HASH_AREA_SIZE에 지정된 크기만큼의 메모리가 할당되어 사용된다.
            3. 하드웨어 자원이 넉넉한 상황에서는 다른 조인에 비해 보다 효율적인 수행이 가능하나 부족한 상황에서는 다른 조인 방법보다 오히려 느려질 수 있다.
        5. SORT/MERGE JOIN
          1. 수행되는 조건
            1. 연결고리의 컬럼에 인덱스가 전혀 없는 경우 SORT/MERGE JOIN을 수행한다.
            2. 대용량의 자료를 조인해야 함으로써 인덱스 사용에 따른 랜덤 액세스의 오버헤드가 많은 경우 SORT/MERGE JOIN을 수행한다.
          2. 동작 방법
            1. 각 테이블에 대해서 동시에 독립적으로 데이터를 먼저 읽어 들인다.
            2. 읽혀진 각 테이블의 데이터를 조인을 위한 연결 고리(조인 조건으로 연결된 컬럼)에 대해서 정렬을 수행한다.
            3. 정렬이 모두 끝난 후에 조인 작업이 수행된다. 어느 하나가 정렬이 끝나지 않으면 조인 작업을 수행하지 않는다.
          3. 튜닝 포인트
            1. 각 테이블로부터 데이터를 빨리 읽어 들이도록 한다.
            2. 메모리(SORT_AREA_SIZE)를 최적화 한다.
          4. 장점과 단점
            1. 연결고리에 인덱스가 생성되어 있지 않은 경우에 빠른 조인을 위해서 사용된다.
            2. 조인하고자 하는 각 테이블에 대해서 독립적으로 데이터를 읽어 들일 때, 이를 얼마나 빠르게 할 것인가가 중요하다.
            3. 각 테이블로 부터 읽혀진 데이터를 연결고리에 대해 정렬을 수행할 때 이를 얼마나 빠르게 할 것인가가 중요하다.
참고도서 : 실전사례로 살펴보는 SQL 튜닝비법