[ORACLE] WITH절

WITH절이란?

WITH절은 오라클9 이상 버전에서 사용 가능하며 서브쿼리, VIEW 등과 같은 기능을 한다고 볼 수 있다.

사용하는 이유

반복적으로 SQL문을 사용하는 경우 그 블록에 이름을 부여해 재사용 가능함으로써 쿼리 성능을 높일 수 있다.

또한 긴 문장의 서브 쿼리를 WITH절로 만들어 SELECT문의 가독성을 높일 수 있다.

출처 : https://gent.tistory.com/419

사용법

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 절의 동작 방식(힌트 사용)

Orcle의 WITH절은 두 가지의 동작 방식이 있다.

• Materialize 방식
  : 임시 테이블을 생성 후 WITH 절의 결과를 저장하며, 반복 호출 시 쿼리를 실행하지 않고 임시 테이블에 저장된 결과를 사용한다.

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

• Inline 방식
  : 임시 테이블을 생성하지 않고 Inline View 형태로 수행하며, 참조된 횟수만큼 반복적으로 쿼리를 실행한다.

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

---

참고

https://coding-factory.tistory.com/445
https://gent.tistory.com/419