請問以下的 SQL 在沒有變更 MANM060'S index 下, 在不同時間下為何它的執行 INDEX
Path 會不同呢, Cost 結果也不同,



QUERY:
------
select count ( item_no ) from manm060
where item_no like '999__999X'
and gce_ver = '00'
and work_no ='51' AND manu_no ='02'

Estimated Cost: 17696
Estimated # of Rows Returned: 1

1) mis.manm060: INDEX PATH

Filters: (mis.manm060.item_no LIKE '999__999X' AND (mis.manm060.gce_ver = '00'
AND mis.manm060.manu_no = '02' ) )

(1) Index Keys: work_no
Lower Index Filter: mis.manm060.work_no = '51'




QUERY:
------
select count ( item_no ) from manm060
where item_no like '999__999X'
and gce_ver = '00'
and work_no ='51' AND manu_no ='02'

Estimated Cost: 8
Estimated # of Rows Returned: 1

1) mis.manm060: INDEX PATH

Filters: (mis.manm060.gce_ver = '00' AND (mis.manm060.work_no = '51' AND
(mis.manm060.manu_no = '02' AND mis.manm060.item_no LIKE '999__999X' ) ) )

(1) Index Keys: item_no
Lower Index Filter: mis.manm060.item_no LIKE '999%'

1. 可否先針對此table 作一次update statistics high for manm060 ,再執行一次你的
SQL看看結果是否有不一樣？
2. 如果指定走哪一個infex的方法，首先Check ONCONFIG中的DIRECTIVES 為 1
3. 改寫SQL :
select {+INDEX(item_no)} count ( item_no ) from manm060
where item_no like '999__999X'
and gce_ver = '00'
and work_no ='51' AND manu_no ='02'
指定他走item_no這個index ，在測一次.看他是否有照你指定的index執行。