[SQLD-SQL 최적화 기본원리]3.조인수행의 원리

Oracle에서 조인을 수행하는 원리는 SQL 쿼리에 지정된 조건과 데이터베이스 엔진의 최적화 알고리즘에 따라 달라집니다. Oracle의 조인은 크게 Nested Loops Join, Hash Join, Sort Merge Join의 세 가지 방식으로 수행됩니다. 각 방식의 수행 원리는 다음과 같습니다.

---

1. Nested Loops Join

• 원리:
  • 두 테이블 중 하나를 외부 테이블(Outer Table)로 선택하고, 나머지를 내부 테이블(Inner Table)로 설정합니다.
  • 외부 테이블의 각 레코드에 대해 내부 테이블에서 조건을 만족하는 레코드를 검색합니다.
• 특징:
  • 소량의 데이터 또는 인덱스가 잘 설정된 경우에 적합합니다.
  • 데이터가 많으면 느려질 수 있습니다(내부 테이블에 대해 반복적으로 검색하기 때문).
• 사용 사례:
  • 테이블이 작고, 내부 테이블에 적절한 인덱스가 있는 경우.
  • SQL 쿼리가 FORALL 등의 반복적인 처리와 결합된 경우.

---

2. Hash Join

• 원리:
  • 조인 조건에 따라 작은 테이블이나 입력 데이터로 해시 테이블을 생성합니다(메모리 내에서).
  • 다른 테이블의 데이터를 이 해시 테이블과 비교하여 매칭되는 데이터를 찾습니다.
• 특징:
  • 대용량 데이터를 처리하는 데 적합합니다.
  • 테이블 크기에 따라 메모리 사용량이 증가할 수 있으며, 메모리가 부족할 경우 디스크를 사용하여 성능 저하가 발생합니다.
• 사용 사례:
  • 조인 대상 테이블이 크고, 양쪽 테이블 모두 적절한 인덱스가 없는 경우.
  • EQUI-JOIN(동등 조건 조인)에 자주 사용됩니다.

---

3. Sort Merge Join

• 원리:
  • 조인 대상 테이블을 정렬한 후, 정렬된 데이터를 병합하여 매칭되는 레코드를 찾습니다.
• 특징:
  • 대용량 데이터에 적합하며, 인덱스가 없을 경우에도 효과적입니다.
  • 정렬 과정이 필요하기 때문에 추가적인 비용이 발생할 수 있습니다.
• 사용 사례:
  • 조인 조건이 등호가 아닌 경우(예: > 또는 <).
  • 두 테이블이 이미 정렬된 상태이거나 정렬이 간단한 경우.

---

Oracle 옵티마이저와 조인 방식 선택

Oracle은 SQL을 실행하기 전에 옵티마이저를 사용하여 가장 효율적인 조인 방식을 선택합니다. 옵티마이저는 다음 요소를 고려합니다:

1. 테이블 크기: 테이블 크기가 작으면 Nested Loops Join을 선호합니다.
2. 인덱스 유무: 적절한 인덱스가 있는 경우 Nested Loops Join이 선택될 가능성이 높습니다.
3. 메모리 가용성: 메모리가 충분하면 Hash Join을 사용할 가능성이 높아집니다.
4. 통계 정보: 테이블 및 인덱스의 통계 정보가 조인 계획에 영향을 미칩니다.

---

SQL 힌트를 사용한 조인 방식 강제 지정

(SQLP에서 출제되므로 대략 알아두시면 됩니다.)

Oracle에서는 힌트(Hint)를 사용하여 특정 조인 방식을 강제할 수 있습니다.

• Nested Loops Join: /*+ USE_NL(t1 t2) */
• Hash Join: /*+ USE_HASH(t1 t2) */
• Sort Merge Join: /*+ USE_MERGE(t1 t2) */

SELECT /*+ USE_HASH(a b) */ a.column1, b.column2
FROM table_a a
JOIN table_b b
ON a.id = b.id;

---

Oracle 조인 방식의 효율성은 테이블 크기, 데이터 분포, 인덱스 및 시스템 리소스에 따라 달라지므로, 최적의 조인 방식을 선택하려면 실행 계획(EXPLAIN PLAN)을 분석하고 필요 시 옵티마이저 힌트를 활용해야 합니다.