View Merging

• View를 Main Query 안으로 통합(Merge) 시킨다는 것을 의미한다.
• (Inline) View와 Subquery를 적절히 Transformation 할 수 있다면 여러 개의 Query Block을 하나의 단일한 Query Block으로 통합할 수 있으며, Optimization 단계가 성공적으로 이루어질 가능성이 높아진다.

Simple View Merging

• Simple View는 말 그대로 Group By 등의 복잡한 Operation을 포함하지 않는 단순한 View를 의미한다.

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1(c1 int, c2 char(10), c3 int);
create table t2(c1 int, c2 char(10), c3 int);
create table t3(c1 int, c2 char(10), c3 int);


create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);

create index t1_n2 on t1(c3);
create index t2_n2 on t2(c3);
create index t3_n2 on t3(c3);

-- Cost Based Query Transformation 비활성화
alter session set "_optimizer_cost_based_transformation" = off;
alter session set "_optimizer_push_pred_cost_based" = false;

-- Column c1 : Unique
-- Column c2 : 하나의 Distinct Count
-- Column c3 : 0~99의 100개의 Distinct Count
-- Table t1 : 10,000건
-- Table t2 : 1,000건
-- Table t3 : 100건

insert into t1 
select level, 'dummy', mod(level, 100) from dual
connect by level <= 10000
;

insert into t2
select level, 'dummy', mod(level, 100)  from dual
connect by level <= 1000
;

insert into t3
select level, 'dummy', mod(level, 100) from dual
connect by level <= 100
;


commit;

@gather t1
@gather t2
@gather t3

• NO_MERGE Hint를 사용해 View Merging을 강제로 비활성화한 경우

select count(*) 
from (
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select /*+ no_merge */ c1, c2 
    from t2 
    where c1 between 1 and 1000) v
where
  t1.c1 = v.c1
)
; 

@stat
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE         |       |      1 |      1 |            |      1 |00:00:00.01 |      31 |       |       |          |
|*  2 |   HASH JOIN             |       |      1 |   1000 |    10  (10)|   1000 |00:00:00.02 |      31 |  1517K|  1517K| 1251K (0)|
|   3 |    VIEW                 |       |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       7 |       |       |          |
|*  4 |     INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       7 |       |       |          |
|   5 |    INDEX FAST FULL SCAN | T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.05 |      24 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."C1"="V"."C1")
   4 - filter(("C1">=1 AND "C1"<=1000))

-- 3번 단께의 Veiw Operation이 View Merging이 실패했다는 것을 잘 보여준다.
-- View Merging이 실패한 경우 Oracle은 View에 대한 Optimization 작업과 전체 Query 에 대한 Optimization 작업을 별개로 처리하게 된다.
-- Oracle의 Optimization 작업은 Query Block 단위로 이루어지기 때문이다.
-- Inline View는 Index Range Scan, Main Query는 View 결과를 Index t1_n1에 대한 Index Fast Full Scan과 
-- Hash Join을 수행하게끔 Optimization이 이루어진다. (책과 다름)

• View Merging이 성공적으로 이루어진 경우

select count(*)
from (
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select c1, c2 
    from t2 
    where c1 between 1 and 1000) v
where
  t1.c1 = v.c1
)
; 

@stat
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |       |      1 |      1 |            |      1 |00:00:00.01 |      10 |       |       |          |
|*  2 |   HASH JOIN            |       |      1 |    999 |     6  (17)|   1000 |00:00:00.02 |      10 |  1517K|  1517K| 1337K (0)|
|*  3 |    INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       7 |       |       |          |
|*  4 |    INDEX RANGE SCAN    | T1_N1 |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       3 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."C1"="C1")
   3 - filter(("C1">=1 AND "C1"<=1000))
   4 - access("T1"."C1">=1 AND "T1"."C1"<=1000)

-- View Operation이 없어졌다. View Merging으로 인해 View가 없어졌다는 것을 의미한다.
-- 하나의 큰 변화는 Table t1, t2에 대해 모두 Index Range Scan을 사용하게 되었다는 것이다.(책과 다름)
-- 4번 단계에서 사용된 Predicate를 보면 Oracle에 의해 추가적으로 생성된 Predicate의 존재로 인해 
-- Table t2뿐만 아니라 Table t1에 대해서도 Index Range Scan을 사용할 수 있게 되었다.

-- Simple View Merging에 의해 다음과 같이 변형되었다는 것을 알 수 있다.

select
  t1.c1, t2.c2
from
  t1, t2
where
  t1.c1 = t2.c1 and
  t1.c1 between 1 and 1000 and
  t2.c1 between 1 and 1000
;