With 문의 함정

With 문을 사용하는 모드는 두 가지가 존재한다.
하나는 materialize, 또하나는 inline 모드로 사용한다.

With 이름
AS
(SELECT /*+ inline */ ...
)
With 이름
AS
(SELECT /*+ materialize */ ...
)

1. materialize는 실체뷰처럼 사용하기 때문에 임시테이블을 create하고 drop하는 과정을 동반한다.
그러므로 OLTP 환경에서 특히나 자주 사용자가 사용하는 쿼리라면 사용하지 않기를 바란다. 사용자가 클릭하면 클라이언트는 DB한테 DDL문을 보내는 것과 같다. 사용자가 많고 빈번하면 임시테이블을 생성하고 DROP하는데 시간이 지체될 수 있기 때문이다.
2. inline 은 assembliy 개발시 공통적인 코드를 따로 분리하여 작성하지만 코드를 어셈블하면 공통코드를 부르는 루틴안에 공통코드 바이너리가 삽입되는 원리와 같다.
경험한 바 공통 SQL 코드를 추출하여 WITH문을 작성한 다음 열심히 튜닝을 한 다음 inline으로 힌트를 주니까 SELECT 문에 준 힌트도 소용없이 실행계획이 망그러진다. 좀 더 스킬이 필요한 부분이다.
3. 만약 힌트가 없다면 옵티마이저가 판단하여 inline으로 할 지 materialize로 할 지 결정한다. 아마 해당 WITH문을 반복적으로 사용하는 곳이 많다면 materialize 아니면 inline으로 여겨진다.

materialize인 경우 실행계획은 이렇다.

Rows     Row Source Operation -------  ---------------------------------------------------       0  STATEMENT       0   TEMP TABLE TRANSFORMATION  (cr=8 pr=0 pw=0 time=7611 us)       0    LOAD AS SELECT  (cr=8 pr=0 pw=0 time=7470 us)       0     NESTED LOOPS  (cr=8 pr=0 pw=0 time=116 us)       1      NESTED LOOPS  (cr=8 pr=0 pw=0 time=109 us)       1       TABLE ACCESS BY INDEX ROWID 신청처리DB (cr=4 pr=0 pw=0 time=70 us)       1        INDEX RANGE SCAN 신청처리DB_IND_08 (cr=3 pr=0 pw=0 time=42 us)OF EES500MT_IND_08 (NONUNIQUE)       1       TABLE ACCESS BY INDEX ROWID 계획신고지원 (cr=4 pr=0 pw=0 time=37 us)       1        INDEX UNIQUE SCAN 계획신고지원 _PK (cr=3 pr=0 pw=0 time=26 us)OF EES300MT_PK (UNIQUE)       0      TABLE ACCESS BY INDEX ROWID 계획신고서(cr=0 pr=0 pw=0 time=4 us)       0       INDEX UNIQUE SCAN 계획신고서_PK (cr=0 pr=0 pw=0 time=1 us)OF 계획신고서_PK (UNIQUE)       0    NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=55 us)       0     VIEW  (cr=0 pr=0 pw=0 time=52 us)       0      UNION-ALL  (cr=0 pr=0 pw=0 time=50 us)