hash join의 이해와 예제

SELECT ...
        FROM aaa A, bbb B
    WHERE A.ID = B.ID
            AND A.TYPE = '0'
            AND B.NODE_TYPE = 'C';

먼저 회원님의 이해를 돕기 위해서 위의 예를 통해 Hash Join에 대해 말씀 드리겠습니다.
A Table과 B Table이 각각의 상수 조건을 가지고 각각의 join 대상이 되는 집합을 구성하는 데,
A Table은 A.TYPE = '0' 조건만을 가지고 Join을 위한 중간 집합을 만든다.
만약 A.TYPE을 선행 컬럼으로 하는 Index가 있다면 해당 Index를 사용하여 Access하고 없다면
Full Index Scan 또는 Full Table Scan을 통해 중간 집합을 구성합니다.
B Table은 B.NODE_TYPE = 'C' 조건만을 가지고 Join을 위한 중간 집합을 만든다.
만약 B.NODE_TYPE 을 선행 컬럼으로 하는 Index가 있다면 해당 Index를 사용하여 Access하고 없
다면 Full Index Scan 또는 Full Table Scan을 통해 중간 집합을 구성합니다.
따라서 Hash Join은 Index를 사용하지 않는다는 오해는 풀리셨을 겁니다.
그런데, Hash Join은 Hash Join의 메카니즘 상 작은 집합이 먼저 풀린 후 큰 집합이 풀려서
Hash Join되는 것이 속도면에서 더 유리합니다.
따라서 Optimizer Mode가 Cost Base라면 통계정보를 이용하여 더 작은 집합을 구성하는 테이블
을 먼저 Driving할 것입니다.
만약 A Table이 먼저 풀렸다면 Where 조건을 통과한 Row(Where 절에 포함된 컬럼과 Select List
에 포함된 컬럼)들은 A.ID가 Join을 위한 Hash Function에 적용된 후 동일한 Hash 값을 갖는 Row
들 끼리 값은 Bucket에 모이게 되는데 이것을 Hash Table이라 한다.
그런 후 B Table을 Where 조건을 통과한 Row들은 B.Id가 Join을 위한 Hash Funcion에 적용된 후
같은 Bucket을 갖게되면 바로 조인한 후 결과를 Return한다.
이 경우는 메모리 안에서 Join이 가능한 경우이고, 그렇지 못하다면, A Table의 중간 집합과 B
Table의 중간집합이 각각의 Partition을 Disk에 구성한 후 같은 Hash 값을 가는 Partition 쌍 끼
리 Join을 수행 한 후 최종 결과를 Return 합니다.

아래에 기술된 Tip들은 Hash Join이 가진 문제들을 해결하는데 유용합니다.

1. 작은 Table이 Driving Table(Build Input)이 되게하라.

2. Join Table 및 Column들이 적절하게 Analyze 되었는지 확인하라.

3. Histograms은 불규칙한 컬럼 분포에 대해서만 권장되어진다. 필요하다면 ORDERED hint를 사용
하여Cost Based Optimizer에 의해 Join 순서를 조정할 수 있다.

4. 최소한 hash area size만큼 hash join을 위해 메모리가 할당되도록 hash area size를 확인하라.

5. Temporary Tablespace에 I/O가 일어나지 않거나 최소화되도록 hash area size를 setting 하라.
   Hash Area Size = 1.6 * Small Table Size

6. Parallel Hash Join은 CPU 사용율과 V$PQSTAT를 사용하여 보내고 받은 Messages에 의해
   Slave Processes들의 작업부하가 편중되지않도록 하라.
   이 Operation의 Elapsed Time에 대한 통계치를 Monitoring하는 것은 어떤 Slave Process에 작업부하가 편중되었는지를 보여준다.
   작업부하에 대한 편중은 Equi-Join시에 Join Column값의 Cardinality가 아주 작기 때문에 발생된다.


  출처 : en-core forum