DevelopOracle

Oracle - 조인 최적화

Oracle 조인 최적화

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

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

조인 성능이 느려지는 가장 흔한 이유는 다음과 같습니다.

  • 조인 순서가 비효율적일 때
  • 조인 컬럼의 인덱스나 통계 정보가 부정확할 때
  • 조인 전에 줄일 수 있는 데이터를 먼저 줄이지 않았을 때
  • 필요 이상으로 많은 컬럼과 많은 행을 읽을 때
  • Outer Join, 서브쿼리, 중복 제거가 불필요하게 섞여 있을 때

즉 조인 튜닝은 단순히 힌트 하나를 넣는 작업이 아니라, 어떤 테이블을 먼저 읽고 어떤 방식으로 결합할지 결정하는 과정에 가깝다.

조인 튜닝의 기본 관점

조인 성능을 볼 때는 먼저 아래 3가지를 같이 봐야 한다.

  • 어떤 테이블이 선행 테이블(Driving Table) 인가
  • 각 단계에서 예상 건수와 실제 건수가 얼마나 차이 나는가
  • 조인 컬럼에 적절한 인덱스와 통계가 있는가

선행 테이블은 후행 테이블 접근 횟수에 직접적인 영향을 주기 때문에 특히 중요하다.
예를 들어 Nested Loop Join 에서 선행 테이블이 너무 크면, 후행 테이블 인덱스를 아무리 잘 만들어도 반복 접근 횟수가 많아져 성능이 급격히 나빠질 수 있다.

조인 방식 선택

Nested Loop Join

작은 결과 집합을 빠르게 찾아야 하고, 후행 테이블에 적절한 인덱스가 있을 때 효과적입니다.

동작 방식은 단순하다.

  1. 선행 테이블에서 한 건 또는 소량의 행을 읽는다.
  2. 읽은 값으로 후행 테이블을 반복 조회한다.
  3. 보통 후행 테이블은 인덱스를 통해 접근한다.

따라서 Nested Loop Join 은 전체 데이터를 한 번에 많이 읽는 배치성 SQL 보다는, 조건이 명확하고 응답 속도가 중요한 OLTP SQL 에 잘 맞는다.

-- 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 환경에 적합

다만 USE_NL 힌트만으로는 충분하지 않을 때가 많다.
오라클은 USE_NL 에 지정된 테이블을 내부 테이블로 사용할 때만 힌트를 반영하므로, 실제로는 LEADING 이나 ORDERED 와 함께 쓰는 편이 안전하다.

SELECT /*+ LEADING(d e) USE_NL(e) */ 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;

이 예시는 departments 를 먼저 읽고, 그 결과를 바탕으로 employees 를 Nested Loop 로 찾겠다는 의도를 더 명확하게 전달한다.

Hash Join

대량 데이터를 한 번에 읽어서 조인해야 할 때 효과적입니다.

보통 작은 쪽 집합으로 해시 테이블을 만든 뒤, 큰 쪽 집합을 읽으며 해시 매칭을 수행한다.
인덱스가 없어도 사용할 수 있고, 대량 데이터 조인에서 강력하지만 메모리와 Temp 사용량이 커질 수 있다.

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

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

Hash Join 은 아래와 같은 상황에서 자주 선택된다.

  • 양쪽 테이블 모두 읽어야 하는 비중이 높을 때
  • 인덱스를 타고 건건이 접근하는 것보다 Full Scan 이 더 유리할 때
  • 집계, 대량 배치, DW 성격의 SQL 일 때

반대로 결과 건수가 매우 적은데도 Hash Join 으로 풀리면, 불필요하게 많은 블록을 읽는 계획이 될 수 있다.

Merge Join

양쪽 입력이 조인 키 기준으로 이미 정렬되어 있거나, 정렬 비용을 감수해도 될 때 효과적입니다.

두 집합을 조인 키 순서대로 맞춰가며 병합하므로, 범위 조인이나 대량 정렬 이후 병합이 필요한 상황에서 사용할 수 있다.
다만 정렬 비용이 크면 오히려 Hash Join 보다 불리해질 수 있다.

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

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

실무에서는 Merge Join 이 가장 먼저 떠오르는 경우는 많지 않지만, 다음 경우에는 후보가 된다.

  • 이미 소트된 결과를 재사용할 수 있을 때
  • 대량 범위 조인이 필요할 때
  • 해시 메모리가 부족한 환경에서 대안이 필요할 때

조인 순서 최적화

조인 튜닝에서 조인 방식보다 더 중요한 것이 조인 순서인 경우가 많다.
같은 Nested Loop Join 이라도 어느 테이블을 먼저 읽느냐에 따라 성능 차이가 크게 난다.

기본 원칙은 다음과 같다.

  • 먼저 읽는 테이블에서 데이터가 많이 줄어들어야 한다.
  • 선택도가 높은 조건이 있는 테이블을 먼저 검토한다.
  • 후행 테이블은 조인 컬럼 인덱스를 통해 빠르게 찾을 수 있어야 한다.
-- 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;

LEADING 은 어떤 테이블부터 읽을지 직접 지정하는 힌트이고, ORDERED 는 FROM 절에 적은 순서를 그대로 따르게 하는 힌트다.
실무에서는 가독성과 의도 전달 면에서 LEADING 을 더 선호하는 경우가 많다.

조인 전에 데이터를 줄이는 것이 먼저

조인 자체를 빠르게 만드는 것보다, 조인 대상 건수를 먼저 줄이는 것이 더 효과적인 경우가 많다.

-- 나쁜 예: 조인 후 필터링
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE SUBSTR(e.hire_date, 1, 4) = '2024';

-- 좋은 예: 조인 전에 필터링 가능하게 작성
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.hire_date >= DATE '2024-01-01'
  AND e.hire_date < DATE '2025-01-01';

위 예시는 조인 문장 자체는 같지만, 필터 조건을 인덱스 친화적으로 작성하느냐에 따라 선행 테이블 건수가 크게 달라질 수 있다.

조인 방식 비교

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

-- 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;

정리하면 다음처럼 이해하면 편하다.

  • 적은 건수를 빨리 찾는 SQL: Nested Loop Join
  • 대량 데이터를 폭넓게 결합하는 SQL: Hash Join
  • 정렬 기반 병합이나 범위 조인: Merge Join

물론 실제 선택은 통계 정보, 인덱스, 병렬 처리, 메모리 상태에 따라 달라진다.

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. 조인 결과가 작아지는 순서로

특히 3개 이상 조인에서는 중간 결과 집합이 얼마나 커지는지를 봐야 한다.
처음 두 테이블 조인 결과가 너무 커지면, 마지막 테이블 조인 단계에서 급격히 느려질 수 있다.

Anti Join과 Semi Join

EXISTS, IN, NOT EXISTS, NOT IN 은 내부적으로 세미 조인이나 안티 조인으로 변환될 수 있다.
이 구간은 겉보기보다 성능 차이가 큰 편이라서 의도를 명확히 쓰는 것이 중요하다.

  • EXISTS: 일치 여부만 확인하면 될 때
  • IN: 서브쿼리 결과가 비교적 단순하고 중복 영향이 크지 않을 때
  • NOT EXISTS: 부정 조건 처리에서 가장 안전한 편
  • NOT IN: 서브쿼리 결과에 NULL 이 섞이면 의도와 다른 결과가 나올 수 있음

실무에서는 NOT IN 보다 NOT EXISTS 를 더 자주 권장한다.

-- Semi Join (EXISTS)
SELECT *
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 *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

-- Hash Semi Join
SELECT *
FROM departments d
WHERE EXISTS (
  SELECT /*+ HASH_SJ */ 1
  FROM employees e
  WHERE e.department_id = d.department_id
    AND e.salary > 3000
);

힌트를 줄 때도 위치를 주의해야 한다.
HASH_SJ, MERGE_SJ, NL_SJ 같은 세미 조인 힌트는 보통 EXISTS 서브쿼리 안에 넣는 형태로 사용한다.

Outer Join 최적화

Outer Join 은 반드시 필요한 경우에만 사용하는 것이 좋다.
불필요한 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;

또한 LEFT JOIN 이후 WHERE d.department_name = 'SALES' 같은 조건을 쓰면 사실상 INNER JOIN 과 비슷하게 동작하는 경우가 많다.
이럴 때는 정말 외부 조인이 필요한지 다시 확인하는 것이 좋다.

Cartesian Product 방지

조인 조건 하나가 빠지는 순간 결과 건수가 폭발할 수 있다.
실행 계획에서 CARTESIAN 이 보이면 거의 항상 조인 조건 누락이나 잘못된 조건을 먼저 의심해야 한다.

-- 나쁜 예: 조인 조건 누락 (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;

ANSI JOIN 문법을 사용하면 조인 조건과 필터 조건을 분리해서 볼 수 있어서 이런 실수를 줄이는 데 도움이 된다.

조인 컬럼 인덱스와 통계 확인

조인 성능은 인덱스뿐 아니라 통계 정보 정확도에 매우 민감하다.

  • 조인 컬럼에 인덱스가 있는지 확인
  • 편향된 데이터라면 히스토그램이 필요한지 확인
  • 대량 적재 후 통계가 오래되었는지 확인
  • 실행 계획의 예상 건수와 실제 건수 차이가 큰지 확인

예를 들어 옵티마이저가 10건이라고 예상했는데 실제로는 100만 건이라면, 조인 방식 선택 자체가 잘못될 가능성이 높다.

조인 성능 측정

-- 실행 계획 확인
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'));

실행 계획을 볼 때는 단순히 NESTED LOOPS, HASH JOIN 만 보지 말고 아래 항목도 같이 본다.

  • 각 단계의 예상 Rows 와 실제 Rows
  • 어떤 테이블이 먼저 읽혔는지
  • TABLE ACCESS FULL 이 필요한 상황이었는지
  • 조인 직전 필터링이 충분히 되었는지
  • Temp, Buffer Gets, Physical Reads 가 과도하지 않은지

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

실무에서는 ANSI JOIN 이 훨씬 읽기 쉽다.
특히 조인 조건과 필터 조건이 많아질수록 유지보수성이 크게 좋아지고, Outer Join 해석도 더 명확해진다.

조인 튜닝 체크리스트

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

추가로 아래 항목도 같이 확인하면 좋다.

  • 조인 전에 WHERE 절로 데이터를 충분히 줄였는가
  • 실행 계획의 예상 건수와 실제 건수가 크게 다르지 않은가
  • USE_NL, USE_HASH, USE_MERGE 힌트와 조인 순서 힌트를 함께 검토했는가
  • NOT IN 사용 시 NULL 문제를 점검했는가
  • 중간 결과 집합이 불필요하게 커지지 않는가

실전 예제

-- 예제 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 *
FROM employees e
WHERE EXISTS (
  SELECT /*+ HASH_SJ QB_NAME(subq) */ 1
  FROM departments d
  WHERE d.department_id = e.department_id
    AND d.location_id IN (1400, 1500, 1700)
);

정리

조인 튜닝의 핵심은 다음 순서로 보면 된다.

  1. 조인 전에 데이터를 줄일 수 있는지 본다.
  2. 어떤 테이블을 먼저 읽어야 하는지 결정한다.
  3. 그 순서에서 가장 유리한 조인 방식이 무엇인지 본다.
  4. 인덱스와 통계 정보가 그 선택을 뒷받침하는지 확인한다.
  5. 마지막에 실행 계획과 실제 통계로 검증한다.

조인은 대부분의 SQL 성능 문제에서 중심에 있는 연산이다.
그래서 조인 힌트만 암기하기보다, 드라이빙 테이블, 예상 건수, 중간 결과 집합, 조인 컬럼 인덱스를 함께 보는 습관이 더 중요하다.

관련 문서

Share