Oracle의 WITH 절 (Subquery Factoring)

Oracle의 WITH 절은 서브쿼리 팩터링(Subquery Factoring) 기능을 제공하여 복잡한 쿼리를 단순화하고 성능을 최적화하는 데 사용됩니다.
이 절을 사용하면 서브쿼리를 여러 번 재사용하거나, 임시 데이터셋을 저장하여 쿼리 실행 성능을 향상할 수 있습니다.

Table of Contents

Toggle

1. `WITH` 절 기본 문법

WITH 서브쿼리_이름 AS (
    SELECT 컬럼1, 컬럼2
    FROM 테이블
    WHERE 조건
)
SELECT * FROM 서브쿼리_이름;

🔹 주요 특징

WITH 절 내의 서브쿼리는 일반적인 서브쿼리처럼 동작하며, FROM 절에서 참조할 수 있습니다.
동일한 서브쿼리를 여러 번 참조할 경우 성능이 개선될 수 있습니다.
옵티마이저가 WITH 절을 인라인 뷰처럼 처리하거나, 별도의 임시 테이블로 저장할 수도 있습니다.

2. `WITH` 절을 사용한 예제

(1) 기본적인 `WITH` 절 사용

WITH 직원정보 AS (
    SELECT 사원번호, 이름, 부서, 급여
    FROM 직원
    WHERE 급여 > 5000
)
SELECT * FROM 직원정보 WHERE 부서 = 'IT';

직원정보 라는 가상의 서브쿼리를 정의하고, 메인 쿼리에서 이를 조회합니다.
동일한 필터링(급여 > 5000)이 여러 번 사용될 경우 성능이 최적화됩니다.

(2) `WITH` 절을 활용한 여러 개의 서브쿼리 사용

WITH 절에서는 여러 개의 서브쿼리를 정의할 수 있으며, 이를 서로 참조할 수도 있습니다.

WITH
    고액연봉직원 AS (
        SELECT 사원번호, 이름, 급여
        FROM 직원
        WHERE 급여 > 7000
    ),
    IT부서직원 AS (
        SELECT 사원번호, 이름
        FROM 직원
        WHERE 부서 = 'IT'
    )
SELECT * 
FROM 고액연봉직원 G
JOIN IT부서직원 I ON G.사원번호 = I.사원번호;

고액연봉직원과 IT부서직원이라는 두 개의 서브쿼리를 정의하고, 이를 조인하여 최종 데이터를 생성합니다.

3. `WITH` 절과 인덱스 힌트 활용

Oracle의 WITH 절에서 특정 인덱스를 사용하도록 강제할 수도 있습니다.

(1) `INDEX` 힌트 사용

WITH 직원정보 AS (
    SELECT /*+ INDEX(직원 직원_PK) */ 사원번호, 이름, 부서, 급여
    FROM 직원
    WHERE 급여 > 5000
)
SELECT * FROM 직원정보 WHERE 부서 = 'IT';

옵티마이저가 직원 테이블의 직원_PK 인덱스를 사용하도록 유도합니다.

(2) `MATERIALIZE` 힌트 사용

Oracle 12c 이전 버전에서는 MATERIALIZE 힌트를 사용하여 WITH 절을 물리적인 임시 테이블로 저장할 수 있었습니다.

WITH 직원정보 AS (
    SELECT /*+ MATERIALIZE */ 사원번호, 이름, 급여
    FROM 직원
    WHERE 급여 > 5000
)
SELECT * FROM 직원정보;

⚠️ 주의:

MATERIALIZE는 Oracle 12c 이후로는 공식적으로 지원되지 않으며, 대신 옵티마이저가 자동으로 WITH 절을 INLINE 또는 MATERIALIZE 여부를 결정합니다.

(3) `INLINE` 힌트 사용

WITH 직원정보 AS (
    SELECT /*+ INLINE */ 사원번호, 이름, 급여
    FROM 직원
    WHERE 급여 > 5000
)
SELECT * FROM 직원정보;

INLINE 힌트는 WITH 절을 임시 테이블이 아니라 일반 서브쿼리처럼 직접 사용하도록 합니다.

4. `WITH` 절을 사용한 `RECURSIVE CTE` (재귀 쿼리)

Oracle 11g 이상에서는 WITH 절을 활용하여 재귀적(Common Table Expression, CTE) 방식으로 데이터를 처리할 수 있습니다.

(1) 계층적 데이터 조회 (부서 구조 예제)

WITH 조직구조 (부서ID, 부서명, 상위부서ID, 레벨) AS (
    SELECT 부서ID, 부서명, 상위부서ID, 1 AS 레벨
    FROM 부서
    WHERE 상위부서ID IS NULL
    UNION ALL
    SELECT B.부서ID, B.부서명, B.상위부서ID, O.레벨 + 1
    FROM 부서 B
    JOIN 조직구조 O ON B.상위부서ID = O.부서ID
)
SELECT * FROM 조직구조 ORDER BY 레벨;

WITH 절을 사용하여 UNION ALL을 통해 계층적 데이터(부서 구조) 를 재귀적으로 조회할 수 있습니다.

5. `WITH` 절을 사용한 DML 문 (INSERT, UPDATE, DELETE)

WITH 절은 INSERT, UPDATE, DELETE 문에서도 사용할 수 있습니다.

(1) `WITH` 절을 사용한 `INSERT`

WITH 신규사원 AS (
    SELECT '홍길동' AS 이름, 'IT' AS 부서, 6000 AS 급여 FROM DUAL
)
INSERT INTO 직원 (이름, 부서, 급여)
SELECT * FROM 신규사원;

WITH 절을 사용하여 임시 데이터를 생성하고, 이를 INSERT 문에서 활용할 수 있습니다.

(2) `WITH` 절을 사용한 `UPDATE`

WITH 급여인상 AS (
    SELECT 사원번호, 급여 * 1.1 AS 인상급여
    FROM 직원
    WHERE 부서 = 'IT'
)
UPDATE 직원
SET 급여 = (SELECT 인상급여 FROM 급여인상 WHERE 직원.사원번호 = 급여인상.사원번호)
WHERE EXISTS (SELECT 1 FROM 급여인상 WHERE 직원.사원번호 = 급여인상.사원번호);

WITH 절을 활용하여 급여인상 데이터를 만들고, 이를 이용해 UPDATE 수행

(3) `WITH` 절을 사용한 `DELETE`

WITH 퇴사자 AS (
    SELECT 사원번호 FROM 직원 WHERE 근속년수 < 1
)
DELETE FROM 직원 WHERE 사원번호 IN (SELECT 사원번호 FROM 퇴사자);

WITH 절을 사용하여 특정 데이터를 필터링한 후, 해당 데이터를 삭제하는 방식

6. `WITH` 절과 성능 고려 사항

WITH 절을 사용하면 서브쿼리를 여러 번 재사용할 때 성능이 개선될 수 있습니다.
하지만 옵티마이저는 WITH 절을 자동으로 인라인 뷰처럼 처리할 수도 있고, 임시 테이블처럼 저장할 수도 있음을 알아야 합니다.
실행 계획을 반드시 확인하여 WITH 절이 기대한 대로 동작하는지 검증해야 합니다.

sql복사편집EXPLAIN PLAN FOR
WITH 직원정보 AS (
    SELECT /*+ INDEX(직원 직원_PK) */ 사원번호, 이름, 급여
    FROM 직원
)
SELECT * FROM 직원정보;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

7. 결론

✅ WITH 절을 사용하면 서브쿼리 재사용, 성능 최적화, 재귀 쿼리, DML 처리 등 다양한 용도로 활용할 수 있습니다.
✅ 옵티마이저 힌트(INDEX, INLINE, MATERIALIZE 등)를 활용하여 실행 방식을 최적화할 수 있습니다.
✅ 실행 계획을 확인하여 기대한 성능 최적화가 이루어졌는지 확인하는 것이 중요합니다.