DevelopOracle

Oracle - 조인 최적화

Oracle 조인 최적화

오라클에서 효율적인 조인 방법을 선택하고 최적화하는 방법을 다룹니다.

조인은 여러 테이블의 데이터를 결합하는 중요한 연산입니다. 적절한 조인 방식과 순서를 선택하면 쿼리 성능을 크게 향상시킬 수 있습니다.

조인 방식 선택

Nested Loop Join

작은 테이블과 인덱스가 있을 때 효과적입니다.

-- NESTED LOOP JOIN (작은 테이블 + 인덱스)
SELECT /*+ USE_NL(e d) */ e.*, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
  AND e.employee_id = 100;

-- 특징:
-- - 작은 결과 집합에 유리
-- - 선행 테이블의 각 행마다 후행 테이블 인덱스 검색
-- - OLTP 환경에 적합

Hash Join

큰 테이블 간 조인에 효과적입니다.

-- HASH JOIN (큰 테이블간 조인)
SELECT /*+ USE_HASH(e d) */ e.*, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- 특징:
-- - 큰 테이블 간 조인에 유리
-- - 등호(=) 조인에만 사용 가능
-- - 메모리 사용량이 많음
-- - 배치 처리에 적합

Merge Join

정렬된 데이터 조인에 효과적입니다.

-- MERGE JOIN (정렬된 데이터)
SELECT /*+ USE_MERGE(e d) */ e.*, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- 특징:
-- - 양쪽 테이블을 정렬 후 병합
-- - 정렬된 인덱스가 있으면 효율적
-- - 범위 조건에도 사용 가능

조인 순서 최적화

-- LEADING 힌트로 조인 순서 지정
SELECT /*+ LEADING(d e) */ e.*, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
  AND d.location_id = 1700;

-- ORDERED 힌트 (FROM 절 순서대로)
SELECT /*+ ORDERED */ e.*, d.department_name
FROM departments d, employees e
WHERE e.department_id = d.department_id
  AND d.location_id = 1700;

조인 방식 비교

-- 시나리오별 최적 조인 방식

-- 1. 작은 테이블 + 큰 테이블 (인덱스 존재)
-- → Nested Loop Join
SELECT /*+ USE_NL(e d) */ *
FROM departments d, employees e
WHERE d.department_id = e.department_id
  AND d.department_id = 10;

-- 2. 큰 테이블 + 큰 테이블 (Full Scan 필요)
-- → Hash Join
SELECT /*+ USE_HASH(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- 3. 정렬이 필요한 경우
-- → Merge Join
SELECT /*+ USE_MERGE(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY e.department_id;

3개 이상 테이블 조인

-- 조인 순서 지정
SELECT /*+ LEADING(d l e) USE_NL(l e) */
       e.employee_id,
       e.last_name,
       d.department_name,
       l.city
FROM departments d,
     locations l,
     employees e
WHERE d.location_id = l.location_id
  AND e.department_id = d.department_id
  AND l.city = 'Seattle';

-- 원칙:
-- 1. 가장 선택적인 조건을 가진 테이블을 먼저
-- 2. 작은 테이블을 선행 테이블로
-- 3. 조인 결과가 작아지는 순서로

Anti Join과 Semi Join

-- Semi Join (EXISTS)
SELECT /*+ SEMIJOIN */ *
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d
  WHERE d.department_id = e.department_id
    AND d.location_id = 1700
);

-- Anti Join (NOT EXISTS)
SELECT /*+ ANTIJOIN */ *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM departments d
  WHERE d.department_id = e.department_id
);

-- Hash Semi Join
SELECT /*+ HASH_SJ */ *
FROM employees e
WHERE department_id IN (
  SELECT department_id FROM departments
  WHERE location_id = 1700
);

Outer Join 최적화

-- LEFT OUTER JOIN
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- RIGHT OUTER JOIN을 LEFT OUTER JOIN으로 변환 (가독성 향상)
-- 나쁜 예
SELECT e.employee_id, e.last_name, d.department_name
FROM departments d
RIGHT JOIN employees e ON e.department_id = d.department_id;

-- 좋은 예
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Cartesian Product 방지

-- 나쁜 예: 조인 조건 누락 (Cartesian Product 발생)
SELECT e.last_name, d.department_name
FROM employees e, departments d;

-- 좋은 예: 조인 조건 명시
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

조인 성능 측정

-- 실행 계획 확인
EXPLAIN PLAN FOR
SELECT e.*, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 실행 통계 확인
SELECT /*+ GATHER_PLAN_STATISTICS */ e.*, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- SQL_ID로 실행 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

ANSI JOIN vs Oracle JOIN

-- ANSI JOIN (권장)
SELECT e.employee_id, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

-- Oracle JOIN (전통 방식)
SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
  AND e.salary > 5000;

-- 권장: ANSI JOIN
-- 이유: 가독성, 명확한 조인 조건 분리, 표준 SQL

조인 튜닝 체크리스트

  • 적절한 조인 방식 선택 (NL, Hash, Merge)
  • 조인 순서 최적화 (작은 테이블 먼저)
  • 조인 조건에 인덱스 존재 확인
  • WHERE 절 조건으로 조인 전 데이터 축소
  • Cartesian Product 방지
  • NOT IN 대신 NOT EXISTS 사용
  • 필요한 컬럼만 SELECT
  • Outer Join의 필요성 재검토

실전 예제

-- 예제 1: 효율적인 3-way 조인
SELECT /*+ LEADING(l d e) USE_HASH(d e) */
       e.employee_id,
       e.last_name,
       d.department_name,
       l.city
FROM locations l,
     departments d,
     employees e
WHERE l.location_id = d.location_id
  AND d.department_id = e.department_id
  AND l.country_id = 'US';

-- 예제 2: Nested Loop Join 활용
SELECT /*+ USE_NL(e d) INDEX(e idx_emp_dept) */
       e.employee_id,
       e.last_name,
       d.department_name
FROM departments d,
     employees e
WHERE d.department_id = e.department_id
  AND d.department_id = 10;

-- 예제 3: Semi Join 최적화
SELECT /*+ HASH_SJ */ *
FROM employees e
WHERE department_id IN (
  SELECT /*+ QB_NAME(subq) */ department_id
  FROM departments
  WHERE location_id IN (1400, 1500, 1700)
);

관련 문서

Share