Oracle SQL最佳化之使用索引提示一例

在做資料庫的安檢時候，發現一個ORA-01555錯誤：

這個SQL語句明顯運行了很長時間而沒有完成。在觀察Statspack報告中這個SQL也在top
SQL中佔用了大量的db cache。物理讀很大。

下午做完其他的就打算最佳化一下這個SQL
首先查看這個SQL的執行計畫
在PL/SQL Developer中的執行計畫視窗中執行這個SQL然後得到執行計畫：如下

可以看到在巢狀查詢中使用了 提示 /*+ all_rows*/ （這個是我的錯，因為在上禮拜五的時候我發現一條同樣差不多的語句嵌套語句和另外一條語句是一模一樣的，我使用了這個    /*+ all_rows*/提示最佳化了一下，開發人員覺得第一張圖中中的語句也應該加上該提示，結果在今天這條語句出現了問題。）
Person表走的是索引全掃描這個效率有點兒低，但是更糟糕的是mailsend表走的是全表掃描，根據語句中的條件
select * from mailsend ms where ms.personid=p.userid and (sysdate-15)<=ms.senddate and ms.mailid=1102
從執行計畫可以看出次查詢並沒有使用索引，在去到 dba_indexs 中查詢mailsend表是否有索引
Select * from dba_indexs I where i.table_name=’MAILSEND’
果然沒有索引。
於是乎建立一條索引：
Create  index  idx_perid_mailsend  on mailsend(personid);
同時分析了一下該表
Analyzed  table mailsend compute statistics；
改SQL中還是用了 in 這個關鍵字，在查詢中最好將in使用exists替代來提高效能
修改後的sql如下：

 

在看一下執行計畫：
 

這個時候解決了mailsend表的全表掃描情況，但是person表最外層還是走的全表掃描（雖然內層走的是主鍵索引掃描）這才是很重要的原因，update因為要更新內層的結果集，所以走的是全表掃描，沒有使用索引，顯然是很慢的原因。
這個時候查看person的相關索引，只有兩個複合索引。
這時候想起了可以使用提示強制走索引執行於是添加了一個索引提示 /*+ INDEX  (tablename  indexname) */(文法)
修改結果如下：
update /*+ INDEX  ( per INDEX3_PERSON) */ person per
   set per.sort = nvl(per.sort, 0) + 1
 where exists
       (select userid
          from (select 
                 p.userid, p.email
                  from Person p
                 where (sysdate - p.lastupdate) >=
                       (p.lastupdate + 3 - p.lastupdate)
                   and p.status = 3
                   and p.sort != 3
                   and not exists (select *
                          from mailsend ms
                         where ms.personid = p.userid
                           and (sysdate - 15) <= ms.senddate
                           and ms.mailid = 1102)) us where us.userid=per.userid)

再來看看執行計畫：

IO耗費降低到66。
本文完。