Non Unnestable Subquery

• Subquery 가 항상 Unnesting 되지는 않는다 는 사실을 다시 한번 명심하자.
• 항상 실행 계획을 통해 Subquery Unnesting 에 실패하곤 하기 때문이다.

• 아래 예제를 보면 Oracle 의 Transformation 이 아직 얼마나 불완전한지 잘 알 수 있다.

{section}
{column:width=50}

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL>SELECT /*+ gather_plan_statistics */
	 t1.c1, t1.c2
	FROM
	  t1
	WHERE
	   t1.c1 IN (SELECT c1 FROM t2) OR
	   t1.c1 BETWEEN 1 AND 100  
;


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  21dns20d9m1d5, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */   t1.c1, t1.c2  FROM    t1  WHERE
   t1.c1 IN (SELECT c1 FROM t2) OR     t1.c1 BETWEEN 1 AND 100

Plan hash value: 2243065295

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |    11 (100)|   1000 |00:00:00.09 |   10022 |
|*  1 |  FILTER            |       |      1 |        |            |   1000 |00:00:00.09 |   10022 |
|   2 |   TABLE ACCESS FULL| T1    |      1 |  10000 |    11   (0)|  10000 |00:00:00.02 |     105 |
|*  3 |   INDEX RANGE SCAN | T2_N1 |   9900 |      1 |     1   (0)|    900 |00:00:00.04 |    9917 |
---------------------------------------------------------------------------------------------------

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

   1 - filter((("T1"."C1"<=100 AND "T1"."C1">=1) OR  IS NOT NULL))
   3 - access("C1"=:B1)

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

• 매우 간단한 형태의 Subquery 임에도 불구하고 Semi Join 으로 Unnesting 되지 못하고 Filter Operation 이 사용되는 것을 알 수 있다.
• Join 으로의 변환에 실패한 것이다.
• Oracle 은 Or Operation 이 사용되는 경우 Subquery Unnesting 이 수행하지 못하는 한계를 지니고 있다.
• 이를 해결하는 유일한 방법은 다음과 같이 Query 를 재 작성하는 것이다.

{section}
{column:width=50}

SQL>SELECT /*+ gather_plan_statistics  */
	  t1.c1, t1.c2
	FROM
	  t1
	WHERE
	   t1.c1 IN (SELECT c1 FROM t2 WHERE t2.c1 NOT BETWEEN 1 AND 100)
	UNION ALL
	SELECT 
	  t1.c1, t1.c2
	FROM
	  t1
	WHERE
	  t1.c1 BETWEEN 1 AND 100
;


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  1smz9p2rf7yc0, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics  */    t1.c1, t1.c2  FROM    t1
WHERE     t1.c1 IN (SELECT c1 FROM t2 WHERE t2.c1 NOT BETWEEN 1 AND
100)  UNION ALL  SELECT    t1.c1, t1.c2  FROM    t1  WHERE    t1.c1
BETWEEN 1 AND 100

Plan hash value: 2931889415

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |    17 (100)|   1000 |00:00:00.02 |  123 |          |       |     |
|   1 |  UNION-ALL                   |       |      1 |        |            |   1000 |00:00:00.02 |  123 |          |       |     |
|*  2 |   HASH JOIN RIGHT SEMI       |       |      1 |    901 |    14   (8)|    900 |00:00:00.02 |  106 |  1066K|  1066K| 1180K (0)|
|*  3 |    INDEX FAST FULL SCAN      | T2_N1 |      1 |    901 |     2   (0)|    900 |00:00:00.01 |    8 |          |       |     |
|   4 |    TABLE ACCESS FULL         | T1    |      1 |  10000 |    11   (0)|  10000 |00:00:00.01 |   98 |          |       |     |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |    100 |     3   (0)|    100 |00:00:00.01 |   17 |          |       |     |
|*  6 |    INDEX RANGE SCAN          | T1_N1 |      1 |    100 |     2   (0)|    100 |00:00:00.01 |    9 |          |       |     |
----------------------------------------------------------------------------------------------------------------------------------------


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

   2 - access("T1"."C1"="C1")
   3 - filter(("T2"."C1">100 OR "T2"."C1"<1))
   6 - access("T1"."C1">=1 AND "T1"."C1"<=100)

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

• 이런 간단한 경우 외에도 매우 복잡한 Subquery 에서는 Unnesting 이 실패하거나 비효율적인 방식으로 Unnesting 이 이루어지는 경우가 얼마든지 존재할 수 있다.
• 중요한 것은 실행 계획을 통해 Subquery Unnesting 이 어떤 방식으로 이루어졌는지 관찰할 수 있어야 한다는 것이다.

문서에 대하여

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