SQL優化一則：取消檢視合併
SQL優化一則：取消檢視合併
檢視合併作為一種常見的查詢轉換方式，一直在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
結論：
取消檢視合併，先獲取較小結果集，反而帶來了效能的極大提升。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10009036/viewspace-1068352/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【SQL優化器查詢變換器】檢視合併(View Merging)SQL優化View
- SQL優化案例一則--複合索引沒有被使用SQL優化索引
- 【SQL】Oracle查詢轉換之檢視合併SQLOracle
- 一次UnionAll的合併優化優化
- PostgreSQL一複合查詢SQL優化例子-(多個exists,範圍檢索,IN檢索,模糊檢索組合)SQL優化
- 檢視合併（View Merging）View
- (轉)SQL 優化原則SQL優化
- Oracle查詢轉換（一）簡單檢視合併Oracle
- Git - 如何取消正在的合併Git
- SQL Command優化原則SQL優化
- Git合併時遇到衝突或錯誤後取消合併Git
- 段合併優化及注意事項優化
- 檢視SQL Server 效能優化工具SQLServer優化
- SQL優化（一）SQL優化
- SQL最佳化案例一則--複合索引沒有被使用SQL索引
- 檢視自動sql調優作業，最佳化sql訪問路徑SQL
- IO優化案例一則優化
- 優化C++程式碼（3）：常量合併優化C++
- sql優化基礎視訊SQL優化
- Oracle查詢轉換（二）複雜檢視合併Oracle
- SQL優化--用各種hints優化一條SQLSQL優化
- 一次系統檢視查詢的優化優化
- 一個SQL優化SQL優化
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- SQL優化（二）（聯合索引的使用）SQL優化索引
- Explain Extended檢視MySQL的優化AIMySql優化
- 34條簡單的SQL優化準則SQL優化
- SQL Server SQL語句進行優化的基本原則SQLServer優化
- Spark優化之小檔案是否需要合併?Spark優化
- 前端效能優化 --- 資源合併與壓縮前端優化
- MySQL 優化之 index_merge （索引合併）MySql優化Index索引
- 優化 RequireJS 專案（合併與壓縮）優化UIJS
- 【Mysql】Anemometer-視覺化慢sql檢視MySql視覺化
- Oracle查詢轉換（三）外連線檢視合併Oracle
- oracle優化一例之sql優化Oracle優化SQL
- SQL檢視SQL
- SQL語句優化的原則與方法QOSQL優化
- Sql Server系列：索引設計原則及優化SQLServer索引優化