by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Non-Mergable Views




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으로 풀림(책과 다름)  
    

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.