SQLP 요점정리 #15 : 3과목 Tuning - 4장 3절 JOIN

3과목 SQL 고급 활용 및 Tuning

4장 Index와 Join

4.3 JOIN

Nested Loop Join

• Index 상황

* PK_DELP        : DEPT.DEPTNO
* DEPT_LOC_IDX   : DEPT.LOC
* PK_EMP         : EMP.EMPNO
* EMP_DEPTNO_IDX : EMP.DEPTNO
* EMP_SAL_IDX    : EMP.SAL

• Query

SELECT /*+ ORDERED USE_NL(e) */ E.EMPNO, E.ENAME, D.DNAME, E.JOB, E.SAL
  FROM DEPT d, EMP e
 WHERE e.DEPTNO = d.DEPTNO    -- #1
   AND d.LOC    = 'DALLAS'    -- #2
   AND e.SAL   >= 1500        -- #3
 ORDER BY SAL DESC

위의 경우 실행계획은 다음과 같다.

• Execution Plan

SELECT STATEMENT OPTIMIZER = ALL_ROWS
    SORT (ORDER BY)
        NESTED LOOPS
            NESTED LOOPS
                TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE)
                    INDEX (RANGE SCAN) OF 'DEPT_LOC_IDX' (INDEX)
                INDEX (RANGE SCAN) OF 'EMP_DEPT_INDEX' (INDEX)
            TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)

• 순서를 살펴보면 다음과 같다.
  1. DEPT_LOC_IDX Index를 이용하여 LOC = 'DALLAS' 조건에 맞는 ROWID를 찾아낸다.
  2. 해당 ROWID를 이용하여 DEPT Table에서 record를 찾는다.
  3. 위 조건에 만족하는 Table의 DEPTNO 칼럼과 EMP_DEPT_INDEX Index의 DEPTNO 칼럼을 NL Join한다.
  4. NL Join 결과에서의 EMP_DEPT_INDEX의 ROWID로 EMP Table을 액세스 해서 SAL >= 1500 조건에 만족하는 record를 찾는다.
  5. 2번과 5번의 record를 NL Join 한다.
  6. 그 결과를 SAL DESC 기준으로 정렬한다.
• NL Join의 특징
  1. DBMS는 Block단위 I/O를 수행하는데, Random 액세스 하므로 아무리 Index 구성이 완벽해도 대량의 데이터를 JOIN하면 매우 비효율적이다.
  2. record 단위로 순차적으로 JOIN을 수행하므로 대용량 데이터 처리시 매우 치명적이다.
  3. 하지만, 부분범위처리가 가능한 상황에서 최초의 응답시간은 매우 짧다.
  4. 순차적으로 처리되는 특징때문에 Driving Table의 처리 범위에 의해 전체 일량이 결정된다.
  5. Index 구성 전략이 중요하다.
  6. 소량 데이터를 주로 처리하거나 부분범위처리가 가능한 온라인 트랜잭션 환경에 적합하다.

Sort Merge Join

• 진행 단계
  1. Sort : 양쪽 집합을 JOIN 컬럼 기준으로 정렬 (단, Oracle의 경우 Outer Table에 해당 컬럼에 대한 Index가 있다면 생략 가능)
  2. Merge : 정렬된 양쪽 집합을 Merge
• SQL

SELECT /*+ ORDERED USE_MERGE(e) */ d.deptno, d.name, e.empno, e.ename
  FROM dept d, emp e
 WHERE d.deptno = e.deptno

• Execute Plan

SELECT STATEMENT OPTIMIZER = ALL_ROWS
    MERGE JOIN
        TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE)
            INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE))
        SORT (JOIN)
            TABLE ACCESS (FULL) OF 'EMP' (TABLE)

• 특징
  • JOIN하기 전에 양쪽 집합을 정렬한다.
    • 대용량 Table의 경우 Sort자체가 큰 비용을 수반하기 때문에 비효율적일 수 있다.
    • 하지만, Cluster나 Index처럼 미리 정렬된 오브젝트를 이용하면 효과적이다.
  • 부분적으로 부분범위 처리가 가능하다.
    • Outer 집합이 미리 정렬된 상태에서 일부만 Fet하다 멈춘다면 Outer 집합은 끝까지 읽을 필요가 없다.
  • Table별 검색 조건에 의해 전체 일량이 결정
    • NL Join의 경우 Outer 집합에서 조인 대상이 되는 건수에 의해 좌우된다.
    • Sort Merge Join의 경우 각 집합의 크기, 즉 각 테이블별 검색 조건에 의해 좌우된다.
  • Inner Table을 반복 액세스하지 않는다.

Hash Join

• 진행 단계
  1. 둘 중 작은 집합(Build input)을 읽어 Hash Area에 Hash Map을 생성 (Hash Bucket으로 구성된 배열)
  2. 큰 집합(Probe Input)을 읽어 Hash Map을 탐색하면서 JOIN
• SQL

SELECT /*+ ORDERED USE_HASH(e) */ d.deptno, d.dname, e.empno, e.ename
  FROM dept d, emp e
 WHERE d.deptno = e.deptno

• Execute Plan

SELECT STATEMENT OPTIMIZER = ALL_ROWS
  HASH JOIN
      TABLE ACCESS (FULL) OF 'DEPT' (TABLE)
      TABLE ACCESS (FULL) OF 'EMP'  (TABLE)

• 특징
  • NL Join처럼 Random 액세스 부하도 없으며, Sort Merge Join 처럼 Sort에 대한 부하도 없다.
  • Build Input의 크기가 작아야 효과적이다.
  • Hash Join 자체는 전체 Table을 다 읽어야 하지만, Probe Input을 Scan하는 단계는 NL Join처럼 부분범위처리가 가능하다.
  • Build Input이 Memory 공간을 초과하는 경우
    1. Partition 단계 : 양쪽 테이블 모두 Hash값에 따라 동적으로 파티셔닝
    2. Join 단계 : 각 파티션별로 크기가 작은 쪽을 Build Input으로 큰 쪽을 Probe Input으로 해서 Hash Join 수행
    3. Join 하기위해서는 Memory로 Load해야 하는데 그 과정에서 가용 Memory를 초과하면 계속해서 Recursive 하게 Partition 단계를 수행한다.
  • Build Input의 Hash Key 중복이 많을 경우 비효율적이게 된다.
    • Hash Bucket에서는 Sequential Scan을 해야 하므로 엔트리가 많아지면 그만큼 비효율적이다.
• Hash Join 전략
  • 가장 극적으로 효과가 좋기 위한 조건
    • 한 쪽 Table이 가용 Memory에 담길 정도로 충분히 작아야 함
    • Build Input HashKey 칼럼의 중복 값이 거의 없어야 함
  • 다음과 같은 경우 Hash Join의 사용을 고려해 보아야 한다.
    • Join 칼럼에 적당한 Index가 없어 NL Join이 비효율적일 때
    • Index가 있더라도 Driving 집합에서 Inner 쪽으로 Join 액세스가 많아 Random 액세스 부하가 심할 때
    • Sort Merge Join을 하기에 두 테이블이 너무 커 Sort 부하가 심할 때
    • 수행빈도가 낮고 수행 시간이 오래 걸리는 대용량 Table Join 시
      • Hash 결과는 일회용이다. (재사용이 안된다.)
      • 수행빈도가 높은 작업을 Hash로 하면 메모리 확보를 위해 Latch 경합이 발생해 시스템 동시성을 떨어뜨릴 수 있다.

Scalar Subquery

• Scalar Subquery : 1개의 data (1 row 1 column)만 반환. SQL문 중 column이 위치할 수 있는 대부분의 곳에 사용 가능

대부분의 Scalar Subquery는 Outer Join문으로 변경이 가능하다.

SELECT empno, ename, sal, hiredate,
       (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) dname
  FROM EMP e
 WHERE sal >= 2000;

 SELECT empno, ename, sal, hiredate, dname
   FROM emp e, dept d
  WHERE e.deptno(+) = d.deptno
    AND sal >= 2000;

결과만 100% 같을 뿐 아니라 처리 경로도 동일하다.
하지만 Scarlar Subquery는 내부적으로 Caching 기법이 작용한다.

• Scarlar Subquery Cache
  • Subquery의 입력값과 출력값을 Cache에 저장
  • Main Query에서 같은 입력값이 들어오면 캐시된 출력값을 리턴
  • Hash 알고리즘을 이용하여 찾기 때문에 입력값의 종류가 소수일 경우 더욱 효과적
• 2개 이상의 값을 return하고 싶을 땐
  • 값을 결합하여 1개로 만들어서 return하고 밖에서 SUBSTR로 분리하는 방법을 사용

SELECT d.deptno, d.dname, avg_sal, min_sal, max_sal
  FROM dept d,
       (SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal FROM emp GROUP BY deptno) e
 WHERE d.deptno = e.deptno(+)
   AND d.loc = 'CHICAGO';

위의 경우에는 일단 Inline View를 만들기 위해 emp 테이블 전체를 읽어야 한다.

SELECT d.deptno, d.dname,
      (SELECT AVG(sal) FROM emp WHERE emp.deptno = d.deptno) avg_sal,
      (SELECT MIN(sal) FROM emp WHERE emp.deptno = d.deptno) min_sal,
      (SELECT MAX(sal) FROM emp WHERE emp.deptno = d.deptno) max_sal
  FROM dept d
 WHERE d.loc = 'CHICAGO';

위의 경우에는 emp를 3번 호출하였다.

 SELECT deptno, dname, 
        TO_NUMBER(SUBSTR(ret,1,5)) avg_sal, 
        TO_NUMBER(SUBSTR(ret,6,5)) min_sal, 
        TO_NUMBER(SUBSTR(ret,11,5)) max_sal
   FROM (SELECT d.deptno, d.dname,
                (SELECT LPAD(AVG(sal),5) || LPAD(MIN(sal),5) || LPAD(MAX(sal),5) 
                   FROM emp WHERE emp.deptno = d.deptno) ret
           FROM dept d
          WHERE d.loc = 'CHICAGO');

다소 Query가 좀 복잡해 졌지만, emp 테이블을 1번만 읽고도 같은 결과를 출력하였다.