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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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優化一則：取消檢視合併

相關文章