[Oracle] WITH절이란? (기본 사용법부터 재귀적 WITH절까지)

이슈)

새로운 개발 건 때문에 부하 정도를 러프하게라도 알아보려고 일주일치에 대한 통계를 내는 쿼리를 작성을 하던 도중,,,

실행 계획을 보니 빨간 글자가 너무 많았다.(대충 쿼리를 잘못짜서 실행하면 속 터진다... 라는 일종의 경고문..)

쿼리 구조는 테이블 A에 있는 값과 테이블 B, C, D 각각을 join 하고 group by를 사용해 집계함수를 쓰는 것이였다.

속도가 너무 느려 방법이 없을까 하다가 WITH절을 사용했는데 한번 정리해본다.

 

WITH절

→ 쿼리 앞부분에 하위 쿼리를 미리 정의해두고, 이후 메인 쿼리에서 미리 정의한 쿼리를 참조할 수 있게 해준다.

일종의 임시 뷰(temporary view) 역할을 한다고 생각하면 된다.

 

장점

• 쿼리 가독성 향상 : 복잡한 서브쿼리를 여러 번 쓰지 않고, 이름을 붙여 재사용 가능
• 성능 최적화 : 옵티마이저가 공통 서브쿼리를 한 번만 수행하도록 할 수 있음
• 재귀 쿼리 지원 : WITH + CONNECT BY 없이도 계층형 테이터를 재귀적으로 조회할 수 있음(Oracle 11g 이상)

WITH subquery_name AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM subquery_name
WHERE ...;

 

사용 예시)

1) 기본적인 서브쿼리 팩터링

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_sal;

 

2) 다중 서브쿼리 정의

WITH
high_sal AS (
    SELECT employee_id, salary FROM employees WHERE salary > 10000
),
low_sal AS (
    SELECT employee_id, salary FROM employees WHERE salary <= 10000
)
SELECT * FROM high_sal
UNION ALL
SELECT * FROM low_sal;

 

---

재귀적 WITH(Oracle 11g~)

→ 계층 구조 데이터를 재귀적으로 탐색할 수 있게 해주는 문법

 

구조)

WITH subquery_name (col1, col2, ...) AS (
    -- 1. Anchor member (기준이 되는 최초 쿼리)
    SELECT ...
    FROM ...
    WHERE ...   -- 시작 조건

    UNION ALL

    -- 2. Recursive member (자기 자신을 다시 호출)
    SELECT ...
    FROM ...
    JOIN subquery_name ON ...   -- 재귀적으로 연결
)
SELECT *
FROM subquery_name;

 

예제) 숫자 생성시 (1 ~ 10 까지)

WITH numbers (n) AS (
    SELECT 1 FROM dual       -- Anchor
    UNION ALL
    SELECT n + 1             -- Recursive
    FROM numbers
    WHERE n < 10
)
SELECT * FROM numbers;

 

※주의할 점

1. 무한 루프 방지 → 반드시 (종료 조건 WHERE절 필요)
2. 성능
   - 큰 데이터셋에서는 인덱스와 필터링 조건을 잘 걸어줘야 함.