程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> 關於Oracle數據庫 >> 在OracleE數據庫的字段上建立索引的方法

在OracleE數據庫的字段上建立索引的方法

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