Subquery Pushing

• Oracle 은 Subquery Unnesting 이 실패할 경우 Subquery Pushing 이라는 이름의 Transformation 을 추가적으로 시도한다.
• 간단한 예제를 통해 Subquery Pushing 의 역할을 알아보자.

• 아래 예제는 NO_UNNEST Hint 와 NO_PUSH_SUBQ Hint 를 사용해 Subquery Unnesting 과 Subquery Pushing 을 모두 비활성화한, 즉 Query Transformation 이 완전히 실패한 경우이다.

{section}
{column:width=50}

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

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

       999 dummy
      1000 dummy

1000 개의 행이 선택되었습니다.

{column}
{column:width=50}
{column}
{section}

• 이 경우 실행 계획은 다음과 같이 나타난다.
• 이 실행 계획에서 유의해서 볼 것은 Table 에 대한 Access 순서이다.
• Table t2 와 t1 을 Join 한 후 그 결과를 t3(Subquery)에 대해 Filtering 한다.

{section}
{column:width=50}

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  5r5nxjgu2393k, child number 0
-------------------------------------
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)

Plan hash value: 1175524174

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |    13 (100)|   1000 |00:00:00.02 |     104 |       |       |          |
|*  1 |  FILTER                |       |      1 |        |            |   1000 |00:00:00.02 |     104 |       |       |          |
|*  2 |   HASH JOIN            |       |      1 |   1000 |    11  (10)|   1000 |00:00:00.02 |      99 |   870K|   870K| 1237K (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.01 |      92 |       |       |          |
|*  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)

{column}
{column:width=50}
{column}
{section}

• 이 경우 다음과 같은 의문을 품을 수 있다. "만일 Subquery 가 포함하는 Data 의 양이 적다면, Subquery(Table t3)을 Table t1 보다 먼저 처리하는 것이 더 유리하지 않겠는가?
• 이것이 Subquery Pushing 의 기본적인 아이디어이다.
  즉, Subquery 가 처리되는 위치를 앞으로 옮기겠다(Push)는 것이다.

• PUSH_SUBQ Hint 를 이용해 Subquery 를 Pushing 시킨 경우에는 실행 계획이 다음과 같이 변한다.
• Table 을 Access 하는 순서가 T2/T3/T1 으로 변경된 것을 확인할 수 있다.

{section}
{column:width=50}

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

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  7h89fcqh3r1w3, child number 0
-------------------------------------
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)

Plan hash value: 1423053827

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |      1 |        |    13 (100)|   1000 |00:00:00.02 |     104 |       |       |          |
|*  1 |  HASH JOIN            |       |      1 |     50 |    11  (10)|   1000 |00:00:00.02 |     104 |   870K|   870K| 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.01 |      92 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="T2"."C1")
   2 - filter( IS NOT NULL)
   3 - filter("C2"=:B1)

{column}
{column:width=50}
{column}
{section}

문서에 대하여

• 최초작성일 : 2011년 04월 27일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)엑셈에서 출간한 'Optimizing Oracle Optimizer'를 참고하였습니다.*