DevelopOracle

Oracle - 서브쿼리 최적화

Oracle 서브쿼리 최적화

오라클에서 서브쿼리를 효율적으로 작성하고 최적화하는 방법을 다룹니다.

서브쿼리는 SQL의 강력한 기능이지만, 잘못 사용하면 성능 문제를 일으킬 수 있습니다. 적절한 서브쿼리 유형과 최적화 기법을 선택하는 것이 중요합니다.

EXISTS vs IN

EXISTS 사용

외부 쿼리가 큰 경우 효과적입니다.

-- EXISTS 사용 (외부 쿼리가 큰 경우)
SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d
  WHERE d.department_id = e.department_id
    AND d.location_id = 1700
);

-- 특징:
-- - 첫 번째 일치하는 행을 찾으면 즉시 반환
-- - NULL 값 처리에 안전
-- - Correlated 서브쿼리로 실행

IN 사용

서브쿼리 결과가 작은 경우 효과적입니다.

-- IN 사용 (서브쿼리 결과가 작은 경우)
SELECT * FROM employees
WHERE department_id IN (
  SELECT department_id FROM departments
  WHERE location_id = 1700
);

-- 특징:
-- - 서브쿼리 결과를 먼저 구한 후 비교
-- - 서브쿼리 결과가 작을 때 효율적
-- - NULL 값이 있으면 예상치 못한 결과 가능

NOT EXISTS vs NOT IN

-- NOT EXISTS (권장)
SELECT * FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM departments d
  WHERE d.department_id = e.department_id
);

-- NOT IN (비권장 - NULL 문제)
SELECT * FROM employees
WHERE department_id NOT IN (
  SELECT department_id FROM departments
  WHERE department_id IS NOT NULL  -- NULL 처리 필수
);

-- 주의: NOT IN은 서브쿼리 결과에 NULL이 있으면 결과가 0건

스칼라 서브쿼리 최적화

비효율적인 사용

-- 나쁜 예: 반복적인 스칼라 서브쿼리
SELECT e.employee_id, e.last_name,
  (SELECT department_name FROM departments d
   WHERE d.department_id = e.department_id) dept_name,
  (SELECT location_id FROM departments d
   WHERE d.department_id = e.department_id) loc_id
FROM employees e;

-- 문제점:
-- - 각 행마다 서브쿼리 실행
-- - 동일한 테이블에 여러 번 액세스
-- - 성능 저하

효율적인 대안

-- 좋은 예: 조인 사용
SELECT e.employee_id, e.last_name,
       d.department_name, d.location_id
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 장점:
-- - 한 번의 조인으로 모든 데이터 조회
-- - 옵티마이저 최적화 가능
-- - 성능 향상

인라인 뷰 최적화

-- 인라인 뷰로 데이터 미리 집계
SELECT e.department_id,
       e.employee_count,
       d.department_name
FROM (
  SELECT department_id, COUNT(*) as employee_count
  FROM employees
  GROUP BY department_id
) e
JOIN departments d ON e.department_id = d.department_id;

-- Pushdown 최적화
-- 옵티마이저가 조건을 인라인 뷰 안으로 이동
SELECT * FROM (
  SELECT employee_id, last_name, department_id, salary
  FROM employees
)
WHERE department_id = 10;  -- 이 조건이 인라인 뷰 안으로 이동 가능

WITH 절 (Common Table Expression)

-- WITH 절로 가독성과 성능 향상
WITH dept_summary AS (
  SELECT department_id,
         COUNT(*) as emp_count,
         AVG(salary) as avg_salary
  FROM employees
  GROUP BY department_id
),
high_salary_depts AS (
  SELECT department_id
  FROM dept_summary
  WHERE avg_salary > 10000
)
SELECT e.employee_id, e.last_name, e.salary
FROM employees e
JOIN high_salary_depts h ON e.department_id = h.department_id;

-- 장점:
-- - 복잡한 쿼리의 가독성 향상
-- - 서브쿼리 재사용
-- - 옵티마이저 최적화 가능

Correlated vs Non-Correlated 서브쿼리

Non-Correlated 서브쿼리

-- Non-Correlated: 한 번만 실행
SELECT * FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

-- 장점: 서브쿼리가 한 번만 실행됨

Correlated 서브쿼리

-- Correlated: 각 행마다 실행
SELECT e1.employee_id, e1.last_name, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

-- 주의: 외부 쿼리의 각 행마다 서브쿼리 실행
-- 대안: 인라인 뷰나 윈도우 함수 사용 권장

윈도우 함수로 변환

-- Correlated 서브쿼리
SELECT e1.employee_id, e1.last_name, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

-- 윈도우 함수로 개선
SELECT employee_id, last_name, salary
FROM (
  SELECT employee_id, last_name, salary,
         AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary
  FROM employees
)
WHERE salary > dept_avg_salary;

-- 장점:
-- - 한 번의 테이블 스캔
-- - 성능 크게 향상

서브쿼리 Unnesting

-- 서브쿼리 Unnesting 힌트
-- 서브쿼리를 조인으로 변환
SELECT /*+ UNNEST */ *
FROM employees
WHERE department_id IN (
  SELECT department_id FROM departments
  WHERE location_id = 1700
);

-- NO_UNNEST 힌트
-- 서브쿼리를 그대로 실행
SELECT /*+ NO_UNNEST */ *
FROM employees
WHERE department_id IN (
  SELECT department_id FROM departments
  WHERE location_id = 1700
);

서브쿼리 실행 계획 확인

-- 실행 계획으로 서브쿼리 실행 방식 확인
EXPLAIN PLAN FOR
SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d
  WHERE d.department_id = e.department_id
    AND d.location_id = 1700
);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 확인 사항:
-- - FILTER vs JOIN
-- - Unnesting 여부
-- - 실행 횟수 (Rows, A-Rows)

서브쿼리 캐싱

-- 스칼라 서브쿼리 캐싱
-- 오라클은 최근 결과를 자동으로 캐시
SELECT e.employee_id,
       e.last_name,
       (SELECT department_name
        FROM departments d
        WHERE d.department_id = e.department_id) as dept_name
FROM employees e;

-- 캐시 효과:
-- - 동일한 department_id에 대해 서브쿼리 재실행 방지
-- - 입력값 다양성이 낮을 때 효과적

서브쿼리 튜닝 체크리스트

  • NOT IN 대신 NOT EXISTS 사용
  • 스칼라 서브쿼리를 조인으로 변경 검토
  • Correlated 서브쿼리를 윈도우 함수로 변경 검토
  • EXISTS vs IN 적절한 선택
  • WITH 절로 복잡한 쿼리 단순화
  • 서브쿼리 Unnesting 확인
  • 인라인 뷰 Pushdown 최적화 확인
  • 서브쿼리 결과 크기 고려

실전 예제

-- 예제 1: TOP-N 쿼리
-- 나쁜 예: Correlated 서브쿼리
SELECT * FROM employees e1
WHERE 10 >= (
  SELECT COUNT(*) FROM employees e2
  WHERE e2.salary >= e1.salary
)
ORDER BY salary DESC;

-- 좋은 예: ROW_NUMBER 윈도우 함수
SELECT * FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (ORDER BY salary DESC) as rn
  FROM employees e
)
WHERE rn <= 10;

-- 예제 2: 부서별 최고 급여자
-- 나쁜 예: Correlated 서브쿼리
SELECT * FROM employees e1
WHERE salary = (
  SELECT MAX(salary) FROM employees e2
  WHERE e2.department_id = e1.department_id
);

-- 좋은 예: 인라인 뷰 조인
SELECT e.*
FROM employees e
JOIN (
  SELECT department_id, MAX(salary) as max_salary
  FROM employees
  GROUP BY department_id
) m ON e.department_id = m.department_id
   AND e.salary = m.max_salary;

-- 예제 3: 계층적 집계
WITH monthly_sales AS (
  SELECT TRUNC(sale_date, 'MM') as month,
         SUM(amount) as monthly_total
  FROM sales
  GROUP BY TRUNC(sale_date, 'MM')
)
SELECT month,
       monthly_total,
       SUM(monthly_total) OVER (ORDER BY month) as cumulative_total
FROM monthly_sales;

관련 문서

Share