by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

1. WITH절 동작방식 이해하기




WITH절 동작방식 이해하기

  • WITH절 동작방식 2가지
    1.MATERIALIZE 동작방식
    2 INLINE VIEW 동작방식
    
    -11g이전에는 옵티마이저가 동작방식을 결정 하였으나 11g부터는 11g에서는 _WITH_SUBQUERY 히든파라미터를 통해 제어가 가능하다
    (옵티마이저의 동작방식 선택 결정은 With절이 2번이상 수행되는지에 따라 결정)
    

MATERIALIZE 동작방식

MATERIALIZE 동작방식은 먼저 
1.Global Temporary Table 을 생성한 후, With 절에서 추출한 결과 셋을 저장
2.Main SQL 에서 With 절을 호출하면,추줄한 결과 셋이 저장되어 있는 Global Temporary Table 을 읽어 데이터를 처리
- 제어HINT : /*+ materialize */
MATERIALIZE 독작 제어
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';

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 동작방식

INLINE VIEW 동작방식은 With 절에서 추출한 결과 셋을 SQL 에서 1회 사용될 경우 Global Temporary Table 을 사용하지 않고, Inline View 형태로 수행되는 방식
- 제어HINT :  /*+ INLINE */ 
INLINE VIEW 독작 제어

with wt1 as 
        (select /*+ Inline*/
                *
        from   t1
        where  c2 in ('A','B','C') )
     , wt2 as 
        (select /*+ Inline */
          *
         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'
Union All
select wt1.*, wt2.*
from   wt1, wt2
where  wt1.c1 = wt2.c1
and    wt1.c2 = 'B';

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

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.