Subquery Pushing

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