꿈꾸는 개발자, DBA 커뮤니티 구루비

Non-Mergeable Views

Oracle은 가능한 View Merging을 시도한다. 하지만 항상 성공하는 것은 아니다.
Oracle Menual에서는 다음과 같은 View들은 Non-Mergeable Views, 즉 View Merging이 불가능한 View로 정의하고 있다.

Set operatior (UNION, UNION ALL, INTERSECT, MINUS)
A CONNECT BY clause
A ROWNUM pseudo column
Aggregate functions (AVG, COUNT, MAX, MIN, SUM) in ther select list
네번째의 경우, 실제로는 Merging이 성공할 때도 있고 실패할 때도 있다.
이외에도 Cursor Expression이 Main Query 에 사용된 경우, Analytic Function이 View에서 사용된 경우에도 View Merging이 이루어지지 않는다.

Set Operation이 있는 경우


select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select c1, c2, c3 from t2
    union all
   select c1, c2, c3 from t3) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN                     |       |      1 |     11 |    12   (9)|     11 |00:00:00.01 |      34 |  1095K|  1095K| 1048K (0)|
|   2 |   VIEW                         |       |      1 |     11 |     4   (0)|     11 |00:00:00.01 |       9 |       |       |          |
|   3 |    UNION-ALL PARTITION         |       |      1 |        |            |     11 |00:00:00.01 |       9 |       |       |          |
|*  4 |     TABLE ACCESS FULL          | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       7 |       |       |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| T3    |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       2 |       |       |          |
|*  6 |      INDEX RANGE SCAN          | T3_N2 |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       1 |       |       |          |
|   7 |   INDEX FAST FULL SCAN         | T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.04 |      25 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="V"."C1")
   4 - filter("C3"=1)
   6 - access("C3"=1)
   
==> Hash Join으로 풀림(책과 다름)
Oracle은 View Merging이 실패하는 경우에는 Predicate Pushing을 시도한다. v.c3 = 1 조건이 
Inline View안으로 삽입되어 t3에 대해서는 Index Range Scan으로 나타난다.

Aggregate Function이 사용된 Inline View의 경우


select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select c1, max(c2) as c2, max(c3) as c3
    from t2
    group by c1) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN            |       |      1 |     10 |    12  (17)|     10 |00:00:00.01 |      32 |  1095K|  1095K| 1063K (0)|
|   2 |   VIEW                |       |      1 |     10 |     4  (25)|     10 |00:00:00.01 |       7 |       |       |          |
|*  3 |    FILTER             |       |      1 |        |            |     10 |00:00:00.01 |       7 |       |       |          |
|   4 |     HASH GROUP BY     |       |      1 |     10 |     4  (25)|   1000 |00:00:00.01 |       7 |   873K|   873K| 1230K (0)|
|   5 |      TABLE ACCESS FULL| T2    |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       7 |       |       |          |
|   6 |   INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.03 |      25 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="V"."C1")
   3 - filter(MAX("C3")=1)
   
 ==> Hash Join 으로 풀림(책과 다름)
View Merging은 이루어지지 않았지만 v.c3 = 1 조건이 View안으로 Pushing 되어(3번 단계) 비효율성이 상당히 감소된 것을 알 수 있다.

ROWNUM Operation이 사용된 경우


select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select rownum as r, c1, c2, c3
    from t2) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN            |       |      1 |   1000 |    11  (10)|     10 |00:00:00.01 |      32 |  1095K|  1095K| 1102K (0)|
|*  2 |   VIEW                |       |      1 |   1000 |     3   (0)|     10 |00:00:00.01 |       7 |       |       |          |
|   3 |    COUNT              |       |      1 |        |            |   1000 |00:00:00.01 |       7 |       |       |          |
|   4 |     TABLE ACCESS FULL | T2    |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       7 |       |       |          |
|   5 |   INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.03 |      25 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="V"."C1")
   2 - filter("V"."C3"=1)

v.c3 = 1 조건이 View 안으로 Pushing 되지 못하고 View의 바깥에서 동작한다. 
ROWNUM 연산자에 의해 View Merging 뿐만 아니라 Predicate Pushing 또한 실패하기 때문이다.
ROWNUM을 Subquery나 View안에서 함부로 사용해서는 않되지만 이런 속성을 이용해 일부러 불필요한 ROWNUM을 사용하기도 한다.

Cursor Expression은 Multi Row를 Return하는 Subquery를 Select List에서 사용할 수 있도록 해주는 강력한 기능이다.
하지만 Fetch회수를 늘린다는 성능상의 단점이 존재한다. View Merging을 지원하지 않는다.
(상위 버젼으로 가면서 지원하기도 한다. 공식 문서 없음.)


select /*+ gather_plan_statistics */
  t1.c1, v.c2, 
  cursor(select * from t3 where t3.c1 = t1.c1) as csr
from
  t1, 
  (select c1, c2, c3
    from t2) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T3    |      0 |      1 |     2   (0)|      0 |00:00:00.01 |    0 |          |       |          |
|*  2 |   INDEX RANGE SCAN          | T3_N1 |      0 |      1 |     1   (0)|      0 |00:00:00.01 |    0 |          |       |          |
|*  3 |  HASH JOIN                  |       |      1 |     10 |    11  (10)|     10 |00:00:00.01 |   36 |  1095K|  1095K| 1079K (0)|
|   4 |   VIEW                      |       |      1 |     10 |     3   (0)|     10 |00:00:00.01 |    7 |          |       |          |
|*  5 |    TABLE ACCESS FULL        | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |    7 |          |       |          |
|   6 |   INDEX FAST FULL SCAN      | T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.03 |   29 |          |       |          |
---------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T3"."C1"=:B1)
   3 - access("T1"."C1"="V"."C1")
   5 - filter("C3"=1)

==> Hash Join으로 풀림(책과 다름)

Analytic Function이 사용된 경우


select /*+ gather_plan_statistics */
  t1.c1, v.*
from
  t1, 
  (select row_number() over (order by c1) as rn, 
          c1, c2, c3
  from t2) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN            |       |      1 |   1000 |    12  (17)|     10 |00:00:00.01 |      32 |   980K|   980K| 1067K (0)|
|*  2 |   VIEW                |       |      1 |   1000 |     4  (25)|     10 |00:00:00.01 |       7 |       |       |          |
|   3 |    WINDOW SORT        |       |      1 |   1000 |     4  (25)|   1000 |00:00:00.01 |       7 | 43008 | 43008 |38912  (0)|
|   4 |     TABLE ACCESS FULL | T2    |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       7 |       |       |          |
|   5 |   INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.03 |      25 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="V"."C1")
   2 - filter("V"."C3"=1)

Aggregate Function이 사용된 경우 View Merging이 일어나는 경우와 실패하는 경우
- 실패


select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select c1, c3, count(*) as c2
    from t2
    group by c1, c3) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN            |       |      1 |      8 |    12  (17)|     10 |00:00:00.01 |      32 |  1306K|  1306K| 1066K (0)|
|   2 |   VIEW                |       |      1 |      8 |     4  (25)|     10 |00:00:00.01 |       7 |       |       |          |
|   3 |    HASH GROUP BY      |       |      1 |      8 |     4  (25)|     10 |00:00:00.01 |       7 |  1049K|  1049K|  959K (0)|
|*  4 |     TABLE ACCESS FULL | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       7 |       |       |          |
|   5 |   INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.03 |      25 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="V"."C1")
   4 - filter("C3"=1)

-- 성공


select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select c1, c3, count(*) as c2
    from t2
    group by c1, c3) v
where
  t1.c1 = v.c1 and
  t1.c3 = 1
; 

@stat
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY      |      |      1 |    100 |    16  (13)|     10 |00:00:00.01 |      45 |   904K|   904K|  976K (0)|
|*  2 |   HASH JOIN         |      |      1 |    100 |    15   (7)|     10 |00:00:00.01 |      45 |  1095K|  1095K| 1169K (0)|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |    100 |    11   (0)|    100 |00:00:00.01 |      38 |    |          |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       7 |    |          |          |
------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."C1"="C1")
   3 - filter("T1"."C3"=1)

두 Query의 차이는 View Merging이 이루어지지 않는 경우에는 v.c3 = 1조건이, View Merging이 이루어지는 경우는 t1.c3 = 1 조건이 사용된다는 것이다.

View Merging이 실패한 Query에 대해 10053 Event에 의해 생성된 Trace File을 이용하면
Heuristics 에 의해, 더 정확하게 표현하면 CBO의 Source Code에서 사용하는 일종의 규칙(Rule, Heuristics)에 의해
View Merging이 비효율적일 것으로 판단했기 때문에 View Merging을 수행하지 않겠다는 것이다.
왜 Optimizer가 이런 판단을 했느지는 정확하게 알 수 없다. 역으로 추론해 볼 수 있는 것은 View Merging이 원천적으로 불가능한 것이 아니라
Optimizer의 어떤 판단에 의해 View Merging이 이루어지지 않았다는 것이다.

MERGE Hint를 강제로 부여하면 성공적으로 View Merging이 이루어 진다.


select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select /*+ merge */ c1, c3, count(*) as c2
    from t2
    group by c1, c3) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY         |       |      1 |     10 |    12  (17)|     10 |00:00:00.01 |      31 |   904K|   904K|  976K (0)|
|*  2 |   HASH JOIN            |       |      1 |     10 |    11  (10)|     10 |00:00:00.01 |      31 |  1306K|  1306K| 1046K (0)|
|*  3 |    TABLE ACCESS FULL   | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       7 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.04 |      24 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."C1"="C1")
   3 - filter("C3"=1)
==> Hash Join으로 풀림(책과 다름)

문서에 대하여

최초작성자 : 김종원
최초작성일 : 2009년 3월 21일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.*

Non-Mergable Views (by jongmali) [2009.04.30]

Non-Mergeable Views

문서에 대하여