视图合并作为一种常见的查询转换方式，一直在SQL优化上有很好的效果，但在减少待选集（包括块和行），保持高舍弃的思想下，我们会发现视图合并有时候并不那么合适，甚至起到反效果。
以下是取消视图合并来给SQL进行优化的一则例子：
原SQL语句：
with z1 as (
select m.owc_week_number,
sum(t.teu) week_teu
from t_voyage_throughput_ft t,t_date_dm m
where t.plan_actual='A'
and t.work_date_uid=m.date_uid
and t.work_date_uid in
(select d.date_uid from t_date_dm d where d.owc_year_no=2013 and d.owc_week_number=43
union all
select to_number(to_char(d.date_id-7,'yyyymmdd')) from t_date_dm d where d.owc_year_no=2013 and d.owc_week_number=43 )
group by m.owc_week_number,
m.owc_week_desc
)
select substr(S.LINE_OPERATOR_UID,3) ,
M.OWC_WEEK_NUMBER ,
COUNT(DISTINCT T.VESSEL_REFERENCE_UID) ,
SUM(T.TEU) ,
to_char(round(SUM(T.TEU)/z1.week_teu*100,2))||'%' ,
SUM(CASE WHEN C.VESSEL_IE='I' AND C.CTN_STATUS='E' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.VESSEL_IE='I' AND C.CTN_STATUS<>'E' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.VESSEL_IE='E' AND C.CTN_STATUS='E' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.VESSEL_IE='E' AND C.CTN_STATUS<>'E' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.CTN_CATEGORY='Z' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.CTN_CATEGORY='T' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.CTN_CATEGORY not in ('T','Z') THEN T.TEU ELSE 0 END)
from t_voyage_throughput_ft t,
t_date_dm m,
t_ctn_dm c,
t_sparcsline_dm s,
z1
where t.plan_actual='A'
and t.ctn_type_uid=c.ctn_type_uid
and t.sparcsline_uid=s.sparcsline_uid
and s.line_operator_uid in ('A-MAR','A-CSC','A-CMA','A-APL','A-UASC','A-HMM','A-EMC')
and t.work_date_uid=m.date_uid
and m.owc_week_number=z1.owc_week_number
and t.work_date_uid in
(select d.date_uid from t_date_dm d where d.owc_year_no=2013 and d.owc_week_number=43
union all
select to_number(to_char(d.date_id-7,'yyyymmdd')) from t_date_dm d where d.owc_year_no=2013 and d.owc_week_number=43 )
group by substr(S.LINE_OPERATOR_UID,3) ,
M.OWC_WEEK_NUMBER ,
z1.week_teu;检查执行计划和执行效果如下：
执行计划：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2146715632
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14795
| 1 | SORT GROUP BY | | 14795
| 2 | VIEW | VM_NWVW_0 | 14795
| 3 | HASH GROUP BY | | 14795
|* 4 | HASH JOIN | | 14795
| 5 | VIEW | VW_NSO_2 | 2
| 6 | HASH UNIQUE | | 2
| 7 | UNION-ALL | |
|* 8 | TABLE ACCESS FULL | T_DATE_DM | 1
|* 9 | TABLE ACCESS FULL | T_DATE_DM | 1
|* 10 | HASH JOIN | | 24
|* 11 | HASH JOIN | | 40266
|* 12 | HASH JOIN | | 537
|* 13 | HASH JOIN | | 537
| 14 | NESTED LOOPS | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 15 | NESTED LOOPS | | 537
| 16 | NESTED LOOPS | | 1196
| 17 | VIEW | VW_NSO_1 | 2
| 18 | HASH UNIQUE | | 2
| 19 | UNION-ALL | |
|* 20 | TABLE ACCESS FULL | T_DATE_DM | 1
|* 21 | TABLE ACCESS FULL | T_DATE_DM | 1
|* 22 | TABLE ACCESS BY INDEX ROWID| T_VOYAGE_THROUGHPUT_FT | 598
|* 23 | INDEX RANGE SCAN | I_VOYAGE_THROUGHPUT_FT3 | 1189
|* 24 | INDEX UNIQUE SCAN | PK_SPARCSLINE_DM | 1
|* 25 | TABLE ACCESS BY INDEX ROWID | T_SPARCSLINE_DM | 1
| 26 | TABLE ACCESS FULL | T_DATE_DM | 3976
| 27 | TABLE ACCESS FULL | T_CTN_DM | 23040
| 28 | TABLE ACCESS FULL | T_DATE_DM | 3976
|* 29 | TABLE ACCESS FULL | T_VOYAGE_THROUGHPUT_FT | 1947
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."WORK_DATE_UID"="DATE_UID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
8 - filter("D"."OWC_YEAR_NO"=2013 AND "D"."OWC_WEEK_NUMBER"=43)
9 - filter("D"."OWC_YEAR_NO"=2013 AND "D"."OWC_WEEK_NUMBER"=43)
10 - access("T"."WORK_DATE_UID"="M"."DATE_UID")
11 - access("M"."OWC_WEEK_NUMBER"="M"."OWC_WEEK_NUMBER")
12 - access("T"."CTN_TYPE_UID"="C"."CTN_TYPE_UID")
13 - access("T"."WORK_DATE_UID"="M"."DATE_UID")
20 - filter("D"."OWC_YEAR_NO"=2013 AND "D"."OWC_WEEK_NUMBER"=43)
21 - filter("D"."OWC_YEAR_NO"=2013 AND "D"."OWC_WEEK_NUMBER"=43)
22 - filter("T"."PLAN_ACTUAL"='A')
23 - access("T"."WORK_DATE_UID"="DATE_UID")
24 - access("T"."SPARCSLINE_UID"="S"."SPARCSLINE_UID")
25 - filter("S"."LINE_OPERATOR_UID"='A-APL' OR "S"."LINE_OPERATOR_UID"='A-CMA'
"S"."LINE_OPERATOR_UID"='A-CSC' OR "S"."LINE_OPERATOR_UID"='A-EMC'
"S"."LINE_OPERATOR_UID"='A-MAR' OR "S"."LINE_OPERATOR_UID"='A-UASC
29 - filter("T"."PLAN_ACTUAL"='A')
说明：
注意上文的红色部分，已经发生了视图合并。
由于T_VOYAGE_THROUGHPUT_FT出现了重复的join，导致了过大的中间结果集，严重影响了查询效率。
执行结果：
Result:
…………………………………………………
…………………………………………………
10 rows selected
Executed in 224.688 seconds
调整后：
取消视图合并：
SQL(注意黄色背景部分，取消视图合并):
with z1 as (
select m.owc_week_number,
sum(t.teu) week_teu
from t_voyage_throughput_ft t,t_date_dm m
where t.plan_actual='A'
and t.work_date_uid=m.date_uid
and t.work_date_uid in
(select d.date_uid from t_date_dm d where d.owc_year_no=2013 and d.owc_week_number=43
union all
select to_number(to_char(d.date_id-7,'yyyymmdd')) from t_date_dm d where d.owc_year_no=2013 and d.owc_week_number=43 )
group by m.owc_week_number,
m.owc_week_desc
)
select /*+no_merge(z1)*/
substr(S.LINE_OPERATOR_UID,3) ,
M.OWC_WEEK_NUMBER ,
COUNT(DISTINCT T.VESSEL_REFERENCE_UID) ,
SUM(T.TEU) ,
to_char(round(SUM(T.TEU)/z1.week_teu*100,2))||'%' ,
SUM(CASE WHEN C.VESSEL_IE='I' AND C.CTN_STATUS='E' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.VESSEL_IE='I' AND C.CTN_STATUS<>'E' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.VESSEL_IE='E' AND C.CTN_STATUS='E' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.VESSEL_IE='E' AND C.CTN_STATUS<>'E' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.CTN_CATEGORY='Z' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.CTN_CATEGORY='T' THEN T.TEU ELSE 0 END) ,
SUM(CASE WHEN C.CTN_CATEGORY not in ('T','Z') THEN T.TEU ELSE 0 END)
from t_voyage_throughput_ft t,
t_date_dm m,
t_ctn_dm c,
t_sparcsline_dm s,
z1
where t.plan_actual='A'
and t.ctn_type_uid=c.ctn_type_uid
and t.sparcsline_uid=s.sparcsline_uid
and s.line_operator_uid in ('A-MAR','A-CSC','A-CMA','A-APL','A-UASC','A-HMM','A-EMC')
and t.work_date_uid=m.date_uid
and m.owc_week_number=z1.owc_week_number
and t.work_date_uid in
(select d.date_uid from t_date_dm d where d.owc_year_no=2013 and d.owc_week_number=43
union all
select to_number(to_char(d.date_id-7,'yyyymmdd')) from t_date_dm d where d.owc_year_no=2013 and d.owc_week_number=43 )
group by substr(S.LINE_OPERATOR_UID,3) ,
M.OWC_WEEK_NUMBER ,
z1.week_teu;检查语句的执行计划和执行效果：
执行计划：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3692001353
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 155 |
| 1 | SORT GROUP BY | | 155 |
|* 2 | HASH JOIN | | 155 |
| 3 | NESTED LOOPS | | |
| 4 | NESTED LOOPS | | 155 |
|* 5 | HASH JOIN | | 379 |
| 6 | VIEW | | 1228 |
| 7 | HASH GROUP BY | | 1228 |
|* 8 | HASH JOIN | | 1228 |
| 9 | NESTED LOOPS | | |
| 10 | NESTED LOOPS | | 1228 |
| 11 | VIEW | VW_NSO_1 | 2 |
| 12 | HASH UNIQUE | | 2 |
| 13 | UNION-ALL | | |
|* 14 | TABLE ACCESS FULL | T_DATE_DM | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 15 | TABLE ACCESS FULL | T_DATE_DM | 1 |
|* 16 | INDEX RANGE SCAN | I_VOYAGE_THROUGHPUT_FT3 | 1284 |
|* 17 | TABLE ACCESS BY INDEX ROWID| T_VOYAGE_THROUGHPUT_FT | 614 |
| 18 | TABLE ACCESS FULL | T_DATE_DM | 3976 |
|* 19 | HASH JOIN | | 1228 |
| 20 | NESTED LOOPS | | |
| 21 | NESTED LOOPS | | 1228 |
| 22 | VIEW | VW_NSO_2 | 2 |
| 23 | HASH UNIQUE | | 2 |
| 24 | UNION-ALL | | |
|* 25 | TABLE ACCESS FULL | T_DATE_DM | 1 |
|* 26 | TABLE ACCESS FULL | T_DATE_DM | 1 |
|* 27 | INDEX RANGE SCAN | I_VOYAGE_THROUGHPUT_FT3 | 1284 |
|* 28 | TABLE ACCESS BY INDEX ROWID | T_VOYAGE_THROUGHPUT_FT | 614 |
| 29 | TABLE ACCESS FULL | T_DATE_DM | 3976 |
|* 30 | INDEX UNIQUE SCAN | PK_SPARCSLINE_DM | 1 |
|* 31 | TABLE ACCESS BY INDEX ROWID | T_SPARCSLINE_DM | 1 |
| 32 | TABLE ACCESS FULL | T_CTN_DM | 23040 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
2 - access("T"."CTN_TYPE_UID"="C"."CTN_TYPE_UID")
5 - access("M"."OWC_WEEK_NUMBER"="Z1"."OWC_WEEK_NUMBER")
8 - access("T"."WORK_DATE_UID"="M"."DATE_UID")
14 - filter("D"."OWC_YEAR_NO"=2013 AND "D"."OWC_WEEK_NUMBER"=43)
15 - filter("D"."OWC_YEAR_NO"=2013 AND "D"."OWC_WEEK_NUMBER"=43)
16 - access("T"."WORK_DATE_UID"="DATE_UID")
17 - filter("T"."PLAN_ACTUAL"='A')
19 - access("T"."WORK_DATE_UID"="M"."DATE_UID")
25 - filter("D"."OWC_YEAR_NO"=2013 AND "D"."OWC_WEEK_NUMBER"=43)
26 - filter("D"."OWC_YEAR_NO"=2013 AND "D"."OWC_WEEK_NUMBER"=43)
27 - access("T"."WORK_DATE_UID"="DATE_UID")
28 - filter("T"."PLAN_ACTUAL"='A')
30 - access("T"."SPARCSLINE_UID"="S"."SPARCSLINE_UID")
31 - filter("S"."LINE_OPERATOR_UID"='A-APL' OR "S"."LINE_OPERATOR_UID"='A-CMA'
"S"."LINE_OPERATOR_UID"='A-CSC' OR "S"."LINE_OPERATOR_UID"='A-EMC'
OR "S"."LINE_OPERATOR_UID"='A-MAR' OR "S"."LINE_OPERATOR_UID"='A-U
说明：
注意红色部分，已经取消视图合并。
执行效果：
SQL> alter system flush buffer_cache;
System altered
Executed in 0.094 seconds
SQL> alter system flush shared_pool;
System altered
Executed in 0.437 seconds
Result:
…………………………………………………
…………………………………………………
10 rows selected
Executed in 0.951 seconds
结论：
取消视图合并，先获取较小结果集，反而带来了性能的极大提升。