[Oracle] 오라클 WITH 절 사용법 및 동작방식 (WITH 여러개)

오라클 9i R2부터 WITH 절을 사용할 수 있도록 기능이 추가되었다. WITH 절은 임시 테이블 또는 가상 테이블이라고 생각하면 된다. 반복되는 서브쿼리 블록을 하나의 WITH 절 블록으로 만들어서 사용하거나, 긴 문장의 서브 쿼리를 WITH 절로 만들어서 SELECT 문의 가독성을 높일 수 있다. WITH AS를 여러 개 사용하기 위해서는 쉼표를 붙여서 여러 개 선언하면 된다.

 

오라클 12c부터 WITH 절에 함수와 프로시저를 선언할 수 있으니 아래를 참고하면 된다.

 

WITH 절 사용법

WITH emp_w1 AS
( 
  SELECT deptno
       , SUM(sal) AS sal
    FROM emp
   GROUP BY deptno
)

SELECT a.deptno
     , b.dname
     , a.sal
  FROM emp_w1 a
     , dept b
 WHERE a.deptno = b.deptno

 

서브쿼리를 WITH 절로 만들어서 테이블을 사용하듯 사용할 수 있다. 특히 여러 번 반복되는 서브쿼리를 WITH 절로 만들어서 사용하면 쿼리의 성능을 높일 수 있다.

 

오라클 11g R1까지는 WITH 절에 선언된 임시 테이블을 SELECT 문에 사용하지 않으면 오류가 발생하였지만, 11g R2부터는 선언 후 사용하지 않아도 오류가 발생하지 않는다.

 

 

다중 WITH 절 사용법 (여러 개)

WITH emp_w1 AS
( 
  SELECT deptno
       , AVG(sal) AS sal_avg
    FROM emp
   GROUP BY deptno
), 
emp_w2 AS 
(
  SELECT job
       , AVG(sal) AS sal_avg
    FROM emp
   GROUP BY job
)

SELECT a.ename
     , a.job
     , a.sal
     , b.sal_avg AS dept_avg
     , c.sal_avg AS job_avg
  FROM emp a
     , emp_w1 b
     , emp_w2 c
 WHERE a.empno = 7788
   AND a.deptno = b.deptno
   AND a.job = c.job
 

 

WITH 절에 여러 개의 임시 테이블을 만들고 싶을 때는 쉼표(", ")를 찍고 이어서 선언하면 된다. 

 

WITH 절 함수, 프로시저 선언 방법

WITH
   FUNCTION fn_get_dept_name(p_deptno IN NUMBER) 
      RETURN VARCHAR2
   IS
      v_dname VARCHAR2(14);
   BEGIN
      SELECT dname
        INTO v_dname
        FROM dept
       WHERE deptno = p_deptno;

      RETURN v_dname;
   END;  
 
SELECT ename
     , deptno
     , fn_get_dept_name(deptno) AS dname
  FROM emp
 WHERE empno = 7788

 

오라클 12c부터 WITH 절에 함수를 선언할 수 있으며, 선언된 함수는 SELECT 문에서 사용할 수 있다.

 

WITH
   PROCEDURE with_procedure(p_dname IN VARCHAR) IS
   BEGIN
      DBMS_OUTPUT.put_line('p_dname=' || p_dname);
   END;   
   
   FUNCTION fn_get_dept_name(p_deptno IN NUMBER) 
      RETURN VARCHAR2
   IS
      v_dname VARCHAR2(100);
   BEGIN
      SELECT dname
        INTO v_dname
        FROM dept
       WHERE deptno = p_deptno;

      -- with 절에 선언된 프로시저 호출 가능
      with_procedure(v_dname);
      
      RETURN v_dname;
   END;
 
SELECT ename
     , deptno
     , fn_get_dept_name(deptno) AS dname
  FROM emp
 WHERE empno = 7788

 

프로시저도 선언이 가능하며 프로시저는 SELECT 문에는 사용할 수 없으며,  WITH 절에 선언된 함수 내부에서만 사용이 가능하다.

 

WITH 절 동작 방식 (힌트 사용)

오라클의 WITH 절은 Materialize과 Inline View 2가지의 동작 방식이 있다.

 

Materialize 방식 : 임시 테이블을 생성 후 WITH 절의 결과를 저장하며, 반복 호출 시 쿼리를 실행하지 않고 임시 테이블에 저장된 결과를 사용한다. Inline 방식 : 임시 테이블을 생성하지 않고 Inline View 형태로 수행하며, 참조된 횟수만큼 반복적으로 쿼리를 실행한다.

 

오라클 11g 이전에는 옵티마이저가 동작 방식을 결정하였으며, 11g부터는 직접 힌트를 사용하여 동작 방식을 바꿀 수 있다. (옵티마이저 결정 방식: 2번 이상 수행되면 Materialize, 1회만 수행되면 Inline View)

 

Materialize 방식은 서브 쿼리의 조회 시간이 오래 걸리고 WITH 절의 임시 테이블을 여러 번 반복해서 사용한다면 성능을 높일 수 있는 방식이지만, 임시 테이블을 Create 하고 Drop 하는 행위를 반복하기 때문에 자주 수행되는 쿼리문에 사용하면 시스템에 부하가 생길 수 있다.

 

대부분 WITH 절을 사용할 때는 힌트를 사용하지 않고 옵티마이저 결정 방식으로 사용하지만, WITH 절의 동작 방식을 이해했다면 힌트를 사용하여 조금 더 효율 적으로 쿼리문을 작성할 수 있을 것이다.

 

Materialize 힌트

WITH emp_w1 AS
( 
  SELECT /*+ materialize */
        deptno
       , SUM(sal) AS sal
    FROM emp
   GROUP BY deptno
)

SELECT a.deptno
     , b.dname
     , a.sal
  FROM emp_w1 a
     , dept b
 WHERE a.deptno = b.deptno

 

 

Inline 힌트

WITH emp_w1 AS
( 
  SELECT /*+ inline */
        deptno
       , SUM(sal) AS sal
    FROM emp
   GROUP BY deptno
)

SELECT a.deptno
     , b.dname
     , a.sal
  FROM emp_w1 a
     , dept b
 WHERE a.deptno = b.deptno

 

 

동일한 WITH 절을 사용한 쿼리문 이지만 힌트에 따라서 쿼리문의 수행 방식이 달라지는 것을 확인할 수 있다.