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