當where子句對某一列使用函數時除非利用這個簡單的技術強制索引否則Oracle優化器不能在查詢中使用索引
通常情況下如果在WHERE子句中不使用諸如UPPERREPLACE 或SUBSTRD等函數就不能對指定列建立特定的條件但如果使用了這些函數則會出現一個問題這些函數會阻礙Oracle優化器對列使用索引因而與采用索引的情況相比較查詢會花費更多的時間
慶幸的是如果在使用函數的這些列中包含了字符型數據可以用這樣一種方法修改查詢語句以達到強制性使用索引更有效地運行查詢這篇文章介紹了涉及的技術並說明了在兩種典型情況下怎樣實現
大小寫混合情況
在討論由於函數修改了列的內容如何強制使用索引前讓我們首先看看為什麼Oracle優化器在這種情況下不能使用索引假定我們要搜尋包含了大小寫混合的數據如在表中ADDRESS表的NAME列因為數據是用戶輸入的我們無法使用已經統一改為大寫的數據為了找到每一個名為john的地址我們使用包含了UPPER子句的查詢語句如下所示
SQL> select address from address where upper(name) like JOHN;
在運行這個查詢語句前如果我們運行了命令set autotrace on 將會得到下列結果其中包含了執行過程
ADDRESS
cleveland
row selected
Execution Plan
SELECT STATEMENT
TABLE ACCESS FULL ADDRESS
可以看到在這種情況下Oracle優化器對ADDRESS 表作了一次完整的掃描而沒有使用NAME 列的索引這是因為索引是根據列中數據的實際值建立的而UPPER 函數已經將字符轉換成大寫即修改了這些值因此該查詢不能使用這列的索引優化器不能與索引項比較JOHN沒有索引項對應於JOHN只有john
值得慶幸的是如果在這種情況下想要強制使用索引有一種簡便的方法只要在WHERE 子句中增加一個或多個特定的條件用於測試索引值並減少需要掃描的行但這並沒有修改原來SOL 編碼中的條件以下列查詢語句為例
SQL> select address from address where upper(name) like JO% AND (name
like J% or name like j%);
使用這種查詢語句（已設置AUTOTRACE）可得到下列結果
ADDRESS
cleveland
row selected
Execution Plan
SELECT STATEMENT
CONCATENATION
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
現在優化器為WHERE 子句中AND 聯結的兩個語句中每一個語句確定的范圍進行掃描第二個語句沒有引用函數因而使用了索引在兩個范圍掃描後將運行結果合並
在這個例子中如果數據庫有成百上千行可以用下列方法擴充WHERE 子句進一步縮小掃描范圍
select address from address where upper(name) like JOHN AND (name like JO%
or name like jo% or name like Jo or name like jO );
得到的結果與以前相同但是其執行過程如下所示表明有個掃描范圍
Execution Plan
SELECT STATEMENT
CONCATENATION
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
如果試圖進一步提高查詢速度我們可以在特定的name like條件中指明個或更多的字符然而這樣做會使得WHERE子句十分笨重因為需要大小寫字符所有可能的組合joh JohjOhjoH等等除此之外指定一個或兩個字符已足以加快查詢的運行速度了
現在讓我們看看當我們引用不同的函數時怎樣運用這個基本技術
使用REPLACE的情況
正如名字不總是以大寫輸入一樣電話號碼也會以許多格式出現 如 （） 等等
如果在列名為 PHONE_NUMBER中搜尋上述號碼時可能需要使用函數REPLACE以保證統一的格式如果在PHONE_NUMBER列中只包含空格連字符和數字where 子句可以如下所示
WHERE replace(replace(phone_number ) ) =
WHERE子句兩次使用REPLACE 函數去掉了連字符和空格保證了電話號碼是簡單的數字串然而該函數阻止了優化器在該列使用索引因此我們按如下方法修改WHERE子句以強制執行索引
WHERE replace(replace(phone_number ) ) =
AND phone_number like %
如果我們知道數據中可能包含圓括號WHERE 子句會稍微復雜一點我們可以再增加REPLACE 函數（去掉圓括號連字符和空格）按如下所示擴充增加的條件
WHERE replace(replace(replace(replace(phone_number ) ) ( )
) ) =
AND (phone number like % or phone_number like (% )
該例強調了巧妙地選用WHERE 子句條件的重要性而且這些條件不會改變查詢結果你的選擇應基於完全了解該列中存在的信息類型在該例中我們需要知道 PHONE_NUMBER 數據中存在幾種不同的格式這樣我們能夠修改WHERE 子句而不會影響查詢結果
正確的條件
以後當你遇到包含CHARACTER 數據修改函數列的WHERE 子句時應考慮怎樣利用增加一個或兩個特定的條件迫使優化器使用索引適當地選擇一組特定的條件能減少掃描行並且強制使用索引不會影響查詢結果但卻提高了查詢的執行速度
From:http://tw.wingwit.com/Article/program/Oracle/201311/18519.html
