꿈꾸는 개발자, DBA 커뮤니티 구루비

WITH절 동작방식 이해하기

WITH절은 Materialize방식 또는 Inline View방식으로 수행된다.
11g이전에는 옵티마이저가 동작방식을 결정 (2회이상 수행되면 Materialize, 1회만 수행되면 Inline View 방식)
힌트 : /*+ MATERIALIZE \*/ /*+ INLINE \*/
11g에서는 \_WITH_SUBQUERY 히든파라미터를 통해 제어가 가능하다.

예제1


with wt1 as 
        (select /*+ materialize */
                *
        from   t1
        where  c2 in ('A','B','C') )
     , wt2 as 
        (select /*+ materialize */
          *
         from   t2
         where  c2 in ('A','B','C')
         and    c3 <= 10 )
select wt1.*, wt2.*
from   wt1, wt2
where  wt1.c1 = wt2.c1
and    wt1.c2 = 'A';

MATERIALIZE 동작방식

Global Temporary Table을 생성한 후, With절에서 추출한 결과를 저장한다.
Main SQL에서 With절을 호출하면, Global Temporary Table을 읽어서 데이터를 처리한다.
제어힌트 : /*\+ MATERIALIZE \*/

예제1을 MATERIALIZE방식으로 실행한 실행계획


Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=838 Card=217K Bytes=14M)
   1    0   TEMP TABLE TRANSFORMATION
   2    1     LOAD AS SELECT OF 'SYS_TEMP_0FD9D6604_621035'
   3    2       INLIST ITERATOR
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=130 Card=67K Bytes=2M)
   5    4           INDEX (RANGE SCAN) OF 'T1_IDX_02' (INDEX) (Cost=8 Card=2K)
   6    1     LOAD AS SELECT OF 'SYS_TEMP_0FD9D6605_621035'
   7    6       TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=276 Card=58K Bytes=2M)
   8    1     HASH JOIN (Cost=431 Card=217K Bytes=14M)
   9    8       VIEW (Cost=74 Card=67K Bytes=2M)
  10    9         TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_0FD9D6604_621035' (TABLE (TEMP)) (Cost=74 Card=67K Bytes=2M)
  11    8       VIEW (Cost=85 Card=58K Bytes=2M)
  12   11         TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_0FD9D6605_621035' (TABLE (TEMP)) (Cost=85 Card=58K Bytes=2M)
-----------------------------------------------------------

INLINE VIEW 동작방식

Global Temporary Table을 사용하지 않고, Inline View형태로 수행된다.
제어힌트 : /*\+ INLINE \*/

예제1을 INLINE VIEW방식으로 실행한 실행계획


Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=614 Card=22K Bytes=1M)
   1    0   HASH JOIN (Cost=614 Card=22K Bytes=1M)
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=337 Card=22K Bytes=632K)
   3    1     TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=276 Card=58K Bytes=2M)
-----------------------------------------------------------