Optimizing Oracle Optimizer (2009년)
Non Unnestable Subquery 0 0 51,981

by 구루비스터디 Transformation Non Unnestable Subquery [2018.07.14]


Non Unnestable Subquery

  • Subquery가 항상 Unnesting 되지는 않는다.
  • 항상 실행 계획을 통해 Subquery Unnesting의 발생 여부를 확인해야 한다.



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  
;

@stat
---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  FILTER            |       |      1 |        |            |   1000 |00:00:00.03 |   19905 |
|   2 |   TABLE ACCESS FULL| T1    |      1 |  10000 |    11   (0)|  10000 |00:00:00.04 |     105 |
|*  3 |   INDEX RANGE SCAN | T2_N1 |   9900 |      1 |     1   (0)|    900 |00:00:00.12 |   19800 |
---------------------------------------------------------------------------------------------------

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

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


  • Oracle은 Or Operation이 사용되는 경우 Subquery Unnesting이 수행하지 못하는 한계를 지니고 있다.
  • 이를 해결하는 유일한 방법은 Query를 재 작성하는 것이다.

select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
   t1.c1 in (select c1 from t2)
union
select 
  t1.c1, t1.c2
from
  t1
where
  t1.c1 between 1 and 100
;

@stat
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT UNIQUE                  |       |      1 |   1100 |    19  (32)|   1000 |00:00:00.01 |   48 | 57344 | 57344 |51200  (0)|
|   2 |   UNION-ALL                   |       |      1 |        |            |   1100 |00:00:00.02 |   48 |          |       |          |
|*  3 |    HASH JOIN                  |       |      1 |   1000 |    14   (8)|   1000 |00:00:00.01 |   45 |     1517K|  1517K| 1466K (0)|
|   4 |     INDEX FAST FULL SCAN      | T2_N1 |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |    7 |          |       |          |
|   5 |     TABLE ACCESS FULL         | T1    |      1 |  10000 |    11   (0)|  10000 |00:00:00.04 |   38 |          |       |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| T1    |      1 |    100 |     3   (0)|    100 |00:00:00.01 |    3 |          |       |          |
|*  7 |     INDEX RANGE SCAN          | T1_N1 |      1 |    100 |     2   (0)|    100 |00:00:00.01 |    2 |          |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("T1"."C1"="C1")
   7 - access("T1"."C1">=1 AND "T1"."C1"<=100)

or

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
;

@stat
----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
|   1 |  UNION-ALL                   |       |      1 |        |            |   1000 |00:00:00.02 |  122 |          |       |          |
|*  2 |   HASH JOIN RIGHT SEMI       |       |      1 |    901 |    14   (8)|    900 |00:00:00.01 |  105 |  1517K|  1517K| 1465K (0)|
|*  3 |    INDEX FAST FULL SCAN      | T2_N1 |      1 |    901 |     2   (0)|    900 |00:00:00.01 |    7 |          |       |          |
|   4 |    TABLE ACCESS FULL         | T1    |      1 |  10000 |    11   (0)|  10000 |00:00:00.04 |   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)


  • 실행계획을 통해 Subquery Unnesting이 어떤 방식으로 이루어 졌는지 관찰할 수 있어야 한다.
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3875

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입