출처 - DB Guide(http://www.dbguide.net)



WITH Clause 의 활용

<개념>

with clause 는 Oracle 9i 에서 추가된 기능으로 동일한 SQL 구문을 매크로와 유사한
개념으로 사용함으로써 SQL 을 간단하게 작성하 하며 performance 향상에도 도움을
주도록 고안되어 있습니다.

동일한 SQL 부분은 임시 테이블을 생성할 것인지 아니면 인라인뷰와 조인등으로 처리할 
것인지 옵티마이저가 판단하여 실행계획이 수립되게 됩니다. 



Scalar Subquery 는 함수처럼 사용할 수 있으나 제약조건중 하나가 인라인 뷰가 
중첩되어 있을때 메인쿼리의 조건이 스칼라 서브쿼리 안쪽의 인라인뷰 쪽으로 파고드는 
View Merging 이 일어나지 않는 다는 점입니다.

SELECT DEPTNO,
    (SELECT COUNT(*)    -- 1 레벨
    FROM  (SELECT DEPTNO
        FROM  DEPT C -- 2 레벨
        WHERE A.DEPTNO = C.DEPTNO
        GROUP BY DEPTNO) B,
        EMP E
    WHERE E.DEPTNO = B.DEPTNO) CNT
FROM  DEPT A;

ERROR at line 4:
ORA-00904: "A"."DEPTNO": invalid identifier
 
쿼리를 수행하면 위와 같이 에러가 나며 원인은 메인쿼리의 조건인 A.DEPTNO 가 
2레벨의 중첩된 인라인뷰 안으로 파고들지 못하기 때문입니다.

따라서 위의 쿼리를 with 구문을 사용하여 변경해 보면 다음과 같습니다.

SELECT DEPTNO,
    (WITH DEPT_TEST AS (SELECT DEPTNO
              FROM  DEPT
              GROUP BY DEPTNO)
     SELECT COUNT(*)
     FROM  DEPT_TEST B,
        EMP    E
     WHERE B.DEPTNO = E.DEPTNO
     AND  A.DEPTNO = B.DEPTNO) CNT 
FROM  DEPT A;

Execution Plan
--------------------------------------------------------------------------------
  0   SELECT STATEMENT Optimizer=CHOOSE
  1  0  SORT (AGGREGATE)
  2  1   NESTED LOOPS
  3  2    VIEW
  4  3     SORT (GROUP BY)
  5  4      INDEX (UNIQUE SCAN) OF ’PK_DEPT’ (UNIQUE)
  6  2    INDEX (RANGE SCAN) OF ’IX_EMP_DEPTNO’ (NON-UNIQUE)
  7  0  TABLE ACCESS (FULL) OF ’DEPT’

DEPT 를 FULL SCAN 하면서 DEPTNO 를 WITH 구문의 DEPT_TEST 로 전달하도록
View가 merging 되어 PK_DEPT 인덱스 Unique Scan 하는 것을 볼 수 있습니다.
DEPT_TEST 를 인라인뷰로 놓고 치환하면 에러가 발생하는 쿼리랑 동일하며
인라인뷰 안에 메인쿼리의 컬럼으로 조인하지 않고 바깥에서 조인하여
안으로 머지할 수 있도록 한 것이 차이점입니다.

SQL 문장 하나로 로직처리를 하고 싶으나 scalar subquery의 제약 조건 때문에
사용자 함수를 따로 작성하는 번거로움을 피하고 싶을 때 활용 가능 합니다.