Comples View Merging

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

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이 이루어진다. (책과 다름)

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
;