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

대용량_WITH절 동작방식 이해하기




Chapter04 WITH절 이해와 효율적인 SQL 작성하기 

 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)
    -----------------------------------------------------------
    

문서정보

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