스칼라 서브쿼리내 with 문 활용

WITH Clause 의 활용
<개념>
with clause 는 Oracle 9i 에서 추가된 기능으로 동일한 SQL 구문을 매크로와 유사한
개념으로 사용함으로써 SQL 을 간단하게 작성하 하며 performance 향상에도 도움을
주도록 고안되어 있습니다.
동일한 SQL 부분은 임시 테이블을 생성할 것인지 아니면 인라인뷰와 조인등으로 처리할
것인지 옵티마이저가 판단하여 실행계획이 수립되게 됩니다.
<Scalar Subquery의 nested inline view merging 해소 방안>
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의 제약 조건 때문에
사용자 함수를 따로 작성하는 번거로움을 피하고 싶을 때 활용 가능 합니다.