oracle – SQL最佳化

 最佳化規則：

1.CBO模式下，表從右至左查詢，右邊第一個為基礎資料表
選擇行數的少作為基礎資料表，
三個及三個以上的表，需要選擇交叉表(串連另外兩個表的表)作為基礎資料表，

2.資料量大的時候，使用exists 而非 in

3.where
自下而上的解析where子句，
過濾資料記錄的條件寫在where字句的
尾部，以便在過濾了資料之後在進行表串連處理(即 a.name='張三'的寫後面，再讓a表中欄位name為'張三'的資料去跟b表關聯)
select * from table_name a,ccc b
where a.column =b.column
and a.deptno in(20,30)

4.使用decode

語句塊使用短路徑，if( aa > 100) or (bb = 'cc'),
第一個對了，就不會走進第二個，
if( 1 = 2) and ( a in(1,2,3,4,5))
如果第一個就不滿足的話，也不會走進第二個
所以將開銷較低的放前面

5.避免隱式類型的轉換，把pls_integer複製給number時，會出現隱式轉換

 比較方法：

--查看對應行數語句塊的效能

1.還有一個是按F5鍵查看執行計畫

名詞解釋：

Cost：   操作消耗的成本（根據uses   disk   I/O,   CPU   usage,   memory   usage   等計算出來）
cardinality   操作訪問的資料行數
Bytes     操作訪問的資料集的大小。

2.一個是下面的方法
declare
  -- Local variables here
  v_run_number integer;
   p_contractid contract.contractid%type;
begin
  --啟動profiler
  dbms_profiler.start_profiler(run_number => v_run_number);
  --顯示當前跟蹤的運行行號
  dbms_output.put_line('run_number:' || v_run_number);
  --運行要跟蹤的PLSQL
  for aa in (select * from contract where rownum < 100) loop
   p_contractid:=aa.contractid;
  end loop;
  --停止profiler
  dbms_profiler.stop_profiler;
end;

 

 1.子查詢與串連查詢比較

大量資料子查詢時：

 大量資料連線查詢時：

 

--子查詢

View Code

 1 --有子查詢 2 select a.contractno       保單號碼, 3       (select  chnname from syscode where recordid =a.contractstatus)          狀態, 4        a.acceptdate       受理日期, 5        a.effectivedate    生效日期, 6        a.approvedate      承保日期, 7        a.policyreturndate 回單日期, 8        a.surrenderdate    退保日期, 9        b.checkyearmonth   對賬日期,10        max(b.policyyear)       年度,11        b.productname      險種名稱,12        b.premiumperiod    年期,13        (select abbrname from branch where branchid =b.bizcbranchid) 督導區,14        (select abbrname from branch where branchid=b.bizubranchid )   營業部,15        b.applicantname    投保人,16        b.insuredname      被保險人,17        a.AGENTNAME        代理人,18        b.modalprem        保費,19        b.valueprem        價值保費20   from contract a, premium b21  where  a.contractid = b.contractid22    and a.providerid in ('PRO0000000000006', 'PRO00000000000G6')23    and a.bizbranchid = 'BRA0000000000006'24    and a.channeltype = 'CHANNELTYPE_A'25     group by a.contractno,26           a.acceptdate,27           a.effectivedate,28           a.approvedate,29           a.policyreturndate,30           surrenderdate,31           checkyearmonth,32           productname,33           b.premiumperiod,34           b.applicantname,35           b.insuredname,36           a.AGENTNAME,37           modalprem,38           a.contractstatus,39           b.bizcbranchid,40           b.bizubranchid,41           b.valueprem 42    43    --select a.providerid,a.providername from provider a where providername like '%信泰%'44    --select * from syscode where recordid like '%CHANNEL%'

結果集都是14678條，花費的三次時間依次為 27.627 26.582 26.692

--串連查詢

View Code

 1 --全部是串連查詢 2 select a.contractno       保單號碼, 3       c.chnname         狀態, 4        a.acceptdate       受理日期, 5        a.effectivedate    生效日期, 6        a.approvedate      承保日期, 7        a.policyreturndate 回單日期, 8        a.surrenderdate    退保日期, 9        b.checkyearmonth   對賬日期,10        max(b.policyyear)       年度,11        b.productname      險種名稱,12        b.premiumperiod    年期,13        e.branchname 督導區,14        f.branchname  營業部,15        b.applicantname    投保人,16        b.insuredname      被保險人,17        a.AGENTNAME        代理人,18        b.modalprem        保費,19        b.valueprem        價值保費20   from contract a, premium b,syscode c,branch e,branch f21  where  a.contractid = b.contractid22  and c.recordid = a.contractstatus23  and b.bizcbranchid = e.branchid24  and b.cbranchid = f.branchid25   and a.channeltype = 'CHANNELTYPE_A'26    and a.providerid in ('PRO0000000000006', 'PRO00000000000G6')27    and a.bizbranchid = 'BRA0000000000006'28     group by a.contractno,29           a.acceptdate,30           a.effectivedate,31           a.approvedate,32           a.policyreturndate,33           surrenderdate,34           checkyearmonth,35           productname,36           b.premiumperiod,37           b.applicantname,38           b.insuredname,39           a.AGENTNAME,40           modalprem,41           c.chnname,42           e.branchname,43           f.branchname,44           b.valueprem 45    46    --select a.providerid,a.providername from provider a where providername like '%信泰%'

結果集都是14678條，花費的三次時間依次為 25.178 23.806 25.037

 

2.索引方面

  沒有建立索引之前

  建立索引之後

總結：效率提高接近100倍，那麼哪些欄位應該建立索引了？當你的一個不是很複雜的sql，例如上面的，花費的時間比較長，就應該從多方面調優，當其他方法無法調優，確實需要建立索引的時候，如，那個objectname的contract表花費了很長時間，看下你的where語句中有三個欄位用到了branchid,contractstatus,effectivedate,然後，機構ID(branchid)的值不多隻有幾百個，保單狀態(contractstatus)的值也不多，只有幾十個，但是生效時間(effectivedate)卻有千萬，一天365天，24小時，等等，所以慢就慢在這裡，加個索引 create index IDX_CONT_EFFECTIVEDATE on CONTRACT (EFFECTIVEDATE);索引這個用的好，效率提高，反之更慢，還會影響整個資料庫的效能。

索引介紹：http://www.cnblogs.com/o-andy-o/archive/2012/08/16/2641974.html