Oracle은 Subquery Unnesting이 실패할 경우 Subquery Pushing 이라는 이름의 Transformation을 추가적으로 시도한다
NO_UNNEST, NO_PUSH_SUBQ Hint를 사용하여 Subquery Unnesting, Subquery Pushing을 모두 비활성화 하여 Query Transformation이 완전히 실패한 경우이다
select /*+ gather_plan_statistics */
t1.c1, t2.c2
from
t1, t2
where
t1.c1 = t2.c1 and
t2.c2 in (select /*+ no_unnest no_push_subq */ c2 from t3)
;
@stat
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | | 1000 |00:00:00.03 | 103 | | | |
|* 2 | HASH JOIN | | 1 | 1000 | 11 (10)| 1000 |00:00:00.02 | 98 | 1095K| 1095K| 1211K (0)|
| 3 | TABLE ACCESS FULL | T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | |
| 4 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.04 | 91 | | | |
|* 5 | TABLE ACCESS FULL | T3 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 5 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("T1"."C1"="T2"."C1")
5 - filter("C2"=:B1)
Table t2와 t1을 Join 한 후 그 결과를 t3(Subquery)에 대해 Filtering 한다.
"만일 Subquery가 포함하는 Data의 양이 적다면, Subquery(Table t3)을 Table t1보다 먼저 처리하는 것이 더 유리하지 않겠는가?"
이것이 Subquery Pushing의 기본적인 아이디어이다. 즉, Subquery가 처리되는 위치를 앞으로 옮기겠다(Push)는 것이다.
PUSH_SUBQ Hint를 이용해 Subquery를 Pushing 시킨 경우에는 Table을 Access 하는 순서가 T2/T3/T1 로 바뀐다.
select /*+ gather_plan_statistics */
t1.c1, t2.c2
from
t1, t2
where
t1.c1 = t2.c1 and
t2.c2 in (select /*+ no_unnest push_subq */ c2 from t3)
;
@stat
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 50 | 11 (10)| 1000 |00:00:00.02 | 103 | 1095K| 1095K| 1213K (0)|
|* 2 | TABLE ACCESS FULL | T2 | 1 | 50 | 3 (0)| 1000 |00:00:00.01 | 12 | | | |
|* 3 | TABLE ACCESS FULL | T3 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 5 | | | |
| 4 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.04 | 91 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="T2"."C1")
2 - filter( IS NOT NULL)
3 - filter("C2"=:B1)