delete相關的pl/sql調優

  今天開發找到我，說有個問題想徵求一下我的意見。
問題的大體意思是，對目前環境中的兩個表，我們就叫做表a,表b吧，他說根據一個時間欄位去判斷是否為5天前的記錄，但是這個欄位不是索引列欄位。
想問我是否需要新增索引會能極大地提高效能。
對於這個問題沒有肯定的回答，還是取決於資料和表的特性，首先我看了下表a,表b，根據我的印象，這兩個表的資料量都不大，簡單驗證了一下，一個在300萬，一個就10萬左右。所以從資料的角度來看，走全表掃描也是合理的，不需要再建立一個索引了，因為新增加的索引可能會對現有的一些sql語句造成印象，還需要做一些評估才可以。
其次，這兩個表都是核心層的表，這種表的結構式不能輕易修改的，表中的索引，結構都是在很多專案中反覆驗證透過的。所以也是不建議直接增加索引的。
開發的同事得到了希望的答案，過一會我就收到了另外一封郵件。
我一看就發現和開發的同事問我的問題有些關聯。
這一段指令碼是做一個清理相關的操作。最後還嘗試用到了分段提交。

DECLARE
  CNT_REC NUMBER(13);
  tot_rec number(13);
BEGIN
  CNT_REC := 0;
  tot_rec := 0;

  FOR C1 IN (select *from bpm_context_inst
              where objid in (select context_inst2context_inst from bpm_proc_inst where objid in
(select /*+ PARALLEL(coll_entity_history 4) */ proc_inst_id from coll_entity_history where END_TREATMENT_DATE >= SYSDATE -5 and  END_TREATMENT_DATE <= SYSDATE -1 )))
    LOOP
    BEGIN
      delete from bpm_context_inst t where t.objid = c1.objid;
    END;
    CNT_REC := CNT_REC + 1;
    tot_rec := tot_rec + 1;
 
    IF CNT_REC = 100 THEN
      COMMIT;
      CNT_REC := 0;
    END IF;
 
  END LOOP;
  COMMIT;

END;

這段指令碼讓我有些糾結，因為從這個開發同事的角度來看，這段pl/sql指令碼中一旦確定了coll_entity_history
不需要做索引掃描，可以走全表掃描，他們認為就萬事大吉了。但是我一看到上面的cursor中的那段程式碼，就開始擔心了。
因為他問我的問題似乎還是和這個問題有一定的差距。表bpm_context_inst，bpm_proc_inst這兩個表都是資料量近億的大表。如果從coll_entity_history中得到了太多的資料，外層關聯兩個大表，相比效能也會好不到哪去。如果資料量比較大，直接透過cursor得到快取資料集，做delete還是有一定的效能瓶頸的。對此我也是心有餘悸。可以參見我之前碰到的一個問題。http://blog.itpub.net/23718752/viewspace-1172818/
如果資料量較大，可以嘗試調優一些這段pl/sql。為了能夠準確地評估這段Pl/sql的執行情況，我申請從備份庫中匯出了這幾個表的dump,在測試庫中進行測試。
匯出很順利，但是匯入的時候表bpm_context_inst是相當慢，等了半個小時多，還是沒有什麼反應。
以下是透過imp匯入的一些資料，執行了15分鐘的時候，值匯入了60多萬行，按照這個進度，今天是不用幹別的了。
SQL> select  620540/15/60 from dual;
620540/15/60
------------
  689.488889
 按照之前的測試impdp效率也好不到哪去，為什麼呢，我一檢視錶結構恍然大悟，原來這個表中含有clob欄位。
好了想模擬測試資料，看來按照目前的情況還是不現實的了。
我們來顯分析一下資料。既然是需要按照每隔5天來做資料的清理，來看看這些天來的資料情況。 以下是我分析得到的近50天來的資料情況，每天的相關資料量其實還是很小的。

 

65788	2-Mar-15
30787	7-Feb-15
7987	11-Mar-15
33929	6-Feb-15
29370	10-Feb-15
53211	26-Feb-15
62	23-Mar-15
3382	17-Mar-15
4443	4-Mar-15
12095	13-Feb-15
8789	12-Feb-15
3074	13-Mar-15
7598	20-Feb-15
2239	8-Mar-15
2627	14-Mar-15
7726	24-Feb-15
6330	22-Feb-15
6537	19-Feb-15
7158	21-Feb-15
2872	7-Mar-15
7419	18-Feb-15
5104	9-Mar-15
46094	27-Feb-15
18377	4-Feb-15
7571	16-Feb-15
4145	6-Mar-15
4006	16-Mar-15
20	24-Mar-15
7578	17-Feb-15
29062	9-Feb-15
6821	15-Feb-15
886	18-Mar-15
9221	25-Feb-15
3822	10-Mar-15
953	20-Mar-15
62115	5-Mar-15
2484	15-Mar-15
26311	8-Feb-15
6997	14-Feb-15
16572	28-Feb-15
68	26-Mar-15
11202	11-Feb-15
9251	1-Mar-15
5056	22-Mar-15
2869	21-Mar-15
29508	5-Feb-15
1	25-Mar-15
493	19-Mar-15
55572	3-Mar-15
7469	23-Feb-15
5008	12-Mar-15

 按照這個資料量，原本存在效能隱患的那段pl/sql看起來也順眼多了。但是細細看來，還是有個硬傷。
就是cursor定義的部分，根據pl/sql的實現目標，沒有用到clob欄位，所以是不相關的。可以在cursor的部分直接過濾掉。
C1 IN (select *from bpm_context_inst
              where objid in (select context_inst2context_inst from bpm_proc_inst where objid in 
(select /*+ PARALLEL(coll_entity_history 4) */ proc_inst_id from coll_entity_history where END_TREATMENT_DATE >= SYSDATE -5 and  END_TREATMENT_DATE <= SYSDATE -1 ))) 
直接改為select objid from bpm_context_inst就可以了
最後我給出了兩種意見，第一種是上面的pl/sql完全可以透過一句delete語句來完成，至於他們關注的分段提交，其實在這個場景中，影響是忽略不計，實際上一次提交效能還要好於分批提交。
即
delete bpm_context_inst
              where objid in (select context_inst2context_inst from bpm_proc_inst where objid in 
(select /*+ PARALLEL(coll_entity_history 4) */ proc_inst_id from coll_entity_history where END_TREATMENT_DATE >= SYSDATE -5 and  END_TREATMENT_DATE <= SYSDATE -1 ))
另外一種就是把cursor定義的部分做一些改動，去除clob欄位。對於cursor的提升是還是很大的。
對於這兩種意見，開發同事說自己確實考慮得不夠周到，不過為了避免給客戶造成更多的困擾，還是選用第二種方案。
這個問題帶給我的啟示就是可能大家問你的問題是一個場景，但是和另外一個場景聯絡起來，原來的一些推論就不是那麼肯定了。就比如這個問題中，開發確定不需要索引對於查詢這個表效能影響不大，但是並不意味著在和其他的大表關聯時沒有問題。如果那種情況發生，還需要做一些額外的最佳化工作。根據資料量最後得知，變更的資料量很小，所以這個也算是化險為夷吧。