in_list查詢與or擴充套件

wei-xh發表於2011-12-08
套件
select t.COMPANY_ID, t.GMT_CREATE, t.GMT_MODIFIED, t.PRODUCT_ID
  from t
 where (t.PRODUCT_ID) in
       (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50)
在沒有統計資訊的情況下，9I會走or擴充套件，rule規則產生的執行計劃。這種執行計劃的效率沒有IN_LIST查詢高，而且執行計劃會非常的長。佔用比較大的共享池。
如果想走 in_list查詢，可以嘗試如下幾種辦法：
1）收集統計資訊，那麼就會採用CBO去產生執行計劃，CBO會有限選擇IN_LIST查詢
2）增加HINT,增加有效的HINT後，也會打破RULE規則，而去使用CBO去產生執行計劃。有如下兩個HINT可以參考：
select /*+ index(t index_name) */
 t.COMPANY_ID, t.GMT_CREATE, t.GMT_MODIFIED, t.PRODUCT_ID
  from t
 where (t.PRODUCT_ID) in
       (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50);
 
select /*+ NO_EXPAND */ t.COMPANY_ID, t.GMT_CREATE, t.GMT_MODIFIED, t.PRODUCT_ID
  from t
 where (t.PRODUCT_ID) in
      (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-712965/，如需轉載，請註明出處，否則將追究法律責任。

相關文章