예제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 동작방식{+}
예제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 동작방식{+}
예제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)
-----------------------------------------------------------