Oracle/Tuning 一支跑不出來的 view

User 回報查詢一個  view 的內容跑不出來, 原始建 view 的 SQL statement 如下:




CREATE VIEW PS_M_COFE31RH (ACAD_ORG, SUBJECT, STRM, ACAD_CAREER,
 CRSE_ID, CLASS_NBR, CRSE_OFFER_NBR, SESSION_CODE, CLASS_SECTION,
 DESCR_LABEL, DESCR, DESCR15, CATALOG_NBR, RQRMNT_GROUP, DESCR254A,
 DESCR10, UNITS_MINIMUM, XLATSHORTNAME, WEEK_WORKLOAD_HRS,
 CONTACT_HOURS, DESCRSHORT, ENRL_CAP, ENRL_TOT, WAIT_CAP,
 SCTN_COMBINED_ID, CLASS_STAT, ATTRVALUE, RQRMNT_GROUP_LAST) AS SELECT
 A.ACAD_ORG , A.SUBJECT , A.STRM , A.ACAD_CAREER , A.CRSE_ID ,
 A.CLASS_NBR , A.CRSE_OFFER_NBR , A.SESSION_CODE , A.CLASS_SECTION ,
 DECODE(SUBSTR(A.CATALOG_NBR , 5 , 5) , 'R' , '必修' , 'E' , '選修') ,
 A.DESCR , A_T.DESCRSHORT , A.CATALOG_NBR ,
 DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B.RQRMNT_GROUP , ' ')
, DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B_E.DESCR254A , ' ')
, DECODE(B.SSR_CRSE_TYPOFF_CD , 'SH' , '半年' , '全年') , C.UNITS_MINIMUM (下略)

查看了執行計劃

Step # Step Name
79  SELECT STATEMENT
8  HASH JOIN
4  NESTED LOOPS
1  SYSADM.PS_CLASS_ATTRIBUTE INDEX [RANGE SCAN]
3  SYSADM.PS_CRSE_ATTR_VALUE TABLE ACCESS [BY INDEX ROWID]
2  SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
7  SYS.VW_SQ_1 VIEW
6  SORT [GROUP BY]
5  SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
16  HASH JOIN
12  NESTED LOOPS
9  SYSADM.PS_CLASS_ATTRIBUTE INDEX [RANGE SCAN]
11  SYSADM.PS_CRSE_ATTR_VALUE TABLE ACCESS [BY INDEX ROWID]
10  SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
15  SYS.VW_SQ_2 VIEW
14  SORT [GROUP BY]
13  SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
78  FILTER
75  NESTED LOOPS
72  NESTED LOOPS
69  NESTED LOOPS [OUTER]
67  NESTED LOOPS
64  NESTED LOOPS [OUTER]
61  NESTED LOOPS
58  HASH JOIN
50  NESTED LOOPS
47  HASH JOIN
39  NESTED LOOPS
36  HASH JOIN
28  NESTED LOOPS
26  HASH JOIN (下略)



沒有統計資料還用 Cost Base, 真是的,
算了, 收集統計資料先...

********* 過了很久 **********
再測試一次, 情況還是沒有改善, 改用 HINT 測看看

CREATE VIEW PS_M_COFE31RH (ACAD_ORG, SUBJECT, STRM, ACAD_CAREER,
 CRSE_ID, CLASS_NBR, CRSE_OFFER_NBR, SESSION_CODE, CLASS_SECTION,
 DESCR_LABEL, DESCR, DESCR15, CATALOG_NBR, RQRMNT_GROUP, DESCR254A,
 DESCR10, UNITS_MINIMUM, XLATSHORTNAME, WEEK_WORKLOAD_HRS,
 CONTACT_HOURS, DESCRSHORT, ENRL_CAP, ENRL_TOT, WAIT_CAP,
 SCTN_COMBINED_ID, CLASS_STAT, ATTRVALUE, RQRMNT_GROUP_LAST) AS SELECT
 /*+ USE_HASH (A A_D B A_T B_E C D D_E H I) */ A.ACAD_ORG , A.SUBJECT , A.STRM , A.ACAD_CAREER , A.CRSE_ID ,
 A.CLASS_NBR , A.CRSE_OFFER_NBR , A.SESSION_CODE , A.CLASS_SECTION ,
 DECODE(SUBSTR(A.CATALOG_NBR , 5 , 5) , 'R' , '必修' , 'E' , '選修') ,
 A.DESCR , A_T.DESCRSHORT , A.CATALOG_NBR ,
 DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B.RQRMNT_GROUP , ' ')
, DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B_E.DESCR254A , ' ')
, DECODE(B.SSR_CRSE_TYPOFF_CD , 'SH' , '半年' , '全年') , C.UNITS_MINIMUM (下略)

總算有改善了
原因:根據以往的經驗，當表格資料中筆數超過一定的比率或範圍時，CBO往往比較偏好選擇Hash Join，也就是代表Hash Join所花費的成本會比Nested Loop來得低，當聯集的表格為一大一小時，CBO反而會比較偏好使用Nested Loop，但是當兩表格筆數相當或相對趨向於大時，CBO則又會比較偏好選擇Hash Join，當然大家也不要忘記PGA的大小也是CBO決定聯集方式的重要關鍵因素。