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