當(dāng)where子句對(duì)某一列使用函數(shù)時(shí)，除非利用這個(gè)簡(jiǎn)單的技術(shù)強(qiáng)制索引，否則Oracle優(yōu)化器不能在查詢(xún)中使用索引。 通常情況下，如果在WHERE子句中不使用諸如UPPER、REPLACE 或SUBSTRD等函數(shù)，就不能對(duì)指定列建立特定的條件。但如果使用了這些函數(shù)，則會(huì)出現(xiàn)一個(gè)問(wèn)題：這些函數(shù)會(huì)阻礙Oracle優(yōu)化器對(duì)列使用索引，因而與采用索引的情況相比較，查詢(xún)會(huì)花費(fèi)更多的時(shí)間。 慶幸的是，如果在使用函數(shù)的這些列中包含了字符型數(shù)據(jù)，可以用這樣一種方法修改查詢(xún)語(yǔ)句，以達(dá)到強(qiáng)制性使用索引，更有效地運(yùn)行查詢(xún)。這篇文章介紹了涉及的技術(shù)，并說(shuō)明了在兩種典型情況下怎樣實(shí)現(xiàn)。
大小寫(xiě)混合情況 在討論由于函數(shù)修改了列的內(nèi)容，如何強(qiáng)制使用索引前，讓我們首先看看為什么Oracle優(yōu)化器在這種情況下不能使用索引。假定我們要搜尋包含了大小寫(xiě)混合的數(shù)據(jù)，如在表1中ADDRESS表的NAME列。因?yàn)閿?shù)據(jù)是用戶輸入的，我們無(wú)法使用已經(jīng)統(tǒng)一改為大寫(xiě)的數(shù)據(jù)。為了找到每一個(gè)名為john的地址，我們使用包含了UPPER子句的查詢(xún)語(yǔ)句。如下所示：
| SQL> select address from address where upper(name) like 'JOHN'; |
在運(yùn)行這個(gè)查詢(xún)語(yǔ)句前，如果我們運(yùn)行了命令"set autotrace on", 將會(huì)得到下列結(jié)果，其中包含了執(zhí)行過(guò)程：
| ADDRESS cleveland 1 row selected. Execution Plan SELECT STATEMENT TABLE ACCESS FULL ADDRESS |
可以看到，在這種情況下，Oracle優(yōu)化器對(duì)ADDRESS 表作了一次完整的掃描，而沒(méi)有使用NAME 列的索引。這是因?yàn)樗饕歉鶕?jù)列中數(shù)據(jù)的實(shí)際值建立的，而UPPER 函數(shù)已經(jīng)將字符轉(zhuǎn)換成大寫(xiě),即修改了這些值，因此該查詢(xún)不能使用這列的索引。優(yōu)化器不能與索引項(xiàng)比較"JOHN"，沒(méi)有索引項(xiàng)對(duì)應(yīng)于"JOHN"-只有"john" 。 值得慶幸的是，如果在這種情況下想要強(qiáng)制使用索引，有一種簡(jiǎn)便的方法：只要在WHERE 子句中增加一個(gè)或多個(gè)特定的條件，用于測(cè)試索引值，并減少需要掃描的行，但這并沒(méi)有修改原來(lái)SQL 編碼中的條件。以下列查詢(xún)語(yǔ)句為例：
| SQL> select address from address where upper(name) like 'JO%' AND (name like 'J%' or name like 'j%'); |
使用這種查詢(xún)語(yǔ)句（已設(shè)置AUTOTRACE），可得到下列結(jié)果：
| ADDRESS cleveland 1 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 |
現(xiàn)在，優(yōu)化器為WHERE 子句中AND 聯(lián)結(jié)的兩個(gè)語(yǔ)句中每一個(gè)語(yǔ)句確定的范圍進(jìn)行掃描----第二個(gè)語(yǔ)句沒(méi)有引用函數(shù)，因而使用了索引。在兩個(gè)范圍掃描后，將運(yùn)行結(jié)果合并。 在這個(gè)例子中，如果數(shù)據(jù)庫(kù)有成百上千行，可以用下列方法擴(kuò)充WHERE 子句，進(jìn)一步縮小掃描范圍：
| 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' ); |
得到的結(jié)果與以前相同，但是，其執(zhí)行過(guò)程如下所示，表明有4個(gè)掃描范圍。
| 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 |
如果試圖進(jìn)一步提高查詢(xún)速度，我們可以在特定的"name like"條件中指明3個(gè)或更多的字符。然而，這樣做會(huì)使得WHERE子句十分笨重。因?yàn)樾枰笮?xiě)字符所有可能的組合-joh ,Joh,jOh,joH等等。除此之外，指定一個(gè)或兩個(gè)字符已足以加快查詢(xún)的運(yùn)行速度了。 現(xiàn)在讓我們看看，當(dāng)我們引用不同的函數(shù)時(shí)，怎樣運(yùn)用這個(gè)基本技術(shù)。
使用REPLACE的情況 正如名字不總是以大寫(xiě)輸入一樣，電話號(hào)碼也會(huì)以許多格式出現(xiàn)： 如 123-456-7890， 123 456 7890，（123）456-7890 等等。 如果在列名為 PHONE_NUMBER中搜尋上述號(hào)碼時(shí)，可能需要使用函數(shù)REPLACE以保證統(tǒng)一的格式。如果在PHONE_NUMBER列中只包含空格、連字符和數(shù)字，where 子句可以如下所示：
| WHERE replace(replace(phone_number , '-' ) , ' ' ) = '1234567890' |
WHERE子句兩次使用REPLACE 函數(shù)去掉了連字符和空格，保證了電話號(hào)碼是簡(jiǎn)單的數(shù)字串。然而，該函數(shù)阻止了優(yōu)化器在該列使用索引。因此，我們按如下方法修改WHERE子句，以強(qiáng)制執(zhí)行索引。
| WHERE replace(replace(phone_number, '-' ) , ' ' ) = '1234567890' |
AND phone_number like '123% '如果我們知道數(shù)據(jù)中可能包含圓括號(hào)，WHERE 子句會(huì)稍微復(fù)雜一點(diǎn)。我們可以再增加REPLACE 函數(shù)（去掉圓括號(hào)、連字符和空格），按如下所示擴(kuò)充增加的條件：
| WHERE replace(replace(replace(replace(phone_number , ' - ' ) ,' '), '( ' ) , ' ) ' ) = '1234567890' AND (phone number like ' 123% ' or phone_number like ' (123% ' ) ' |
該例強(qiáng)調(diào)了巧妙地選用WHERE 子句條件的重要性，而且，這些條件不會(huì)改變查詢(xún)結(jié)果。你的選擇應(yīng)基于完全了解該列中存在的信息類(lèi)型。在該例中，我們需要知道 PHONE_NUMBER 數(shù)據(jù)中存在幾種不同的格式，這樣，我們能夠修改WHERE 子句而不會(huì)影響查詢(xún)結(jié)果。
正確的條件 以后當(dāng)你遇到包含CHARACTER 數(shù)據(jù)修改函數(shù)列的WHERE 子句時(shí)，應(yīng)考慮怎樣利用增加一個(gè)或兩個(gè)特定的條件，迫使優(yōu)化器使用索引。適當(dāng)?shù)剡x擇一組特定的條件能減少掃描行，并且強(qiáng)制使用索引不會(huì)影響查詢(xún)結(jié)果----但卻提高了查詢(xún)的執(zhí)行速度。
