⑴ Oracle資料庫強制索引
當where子句對某一列使用函數時 除非利用這個簡單的技術強制索引 否則Oracle優化器不能在查詢中使用索引
通常情況下 如果在WHERE子句中不使用諸如UPPER REPLACE 或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 Joh jOh joH等等 除此之外 指定一個或兩個字元已足以加快查詢的運行速度了
現在讓我們看看 當我們引用不同的函數時 怎樣運用這個基本技術
使用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 子句而不會影響查詢結果
正確的條件 lishixin/Article/program/Oracle/201311/18519
⑵ 在Oracle中合理創建資料庫的索引
在Oracle資料庫中 創建索引雖然比較簡單 但是要合理的創建索引則比較困慧塌難了 筆者認為 在創建索引時要做到三個適當 即在適當的表上 適當的列上創建適當數量的索引 雖然這可以通過一句話來概括優化的索引的基本准則 但是要做到這一點的話 需要資料庫管理員做出很大的努力 具體的來說 要做到這個三個適當有如下幾個要求
一 根據表的大小來創建索引
雖然給表創建索引 可以提高查詢的效率 但是資料庫管理員需要注意的是 索引也需要一定的開銷的 為此並不是說給所有的表都創建索引 那麼就可以提高資料庫的性能 這個認識是錯誤的 恰恰相反 如果不管三七二十一 給所有的表都創建了索引 那麼其反而會給資料庫的性能造成負面的影響 因為此時濫用索引的開銷可能已經遠遠大於由此帶來的性能方面的收益 所以筆者認為 資料庫管理員首先需要做到 為合適的表來建立索引 而不是為所有的表建立索引
一般來說 不需要為比較小的表創建索引 如在一個ERP系統的資料庫中 department表用來存儲企業部門的信息 一般企業的部分也就十幾個 最多不會超過一百個 這 條記錄對於人來說 可能算是比較多了 但是對於計算機來說 這給他塞塞牙縫都還不夠 所以 對類似的小表沒有必要建立索引 因為即使建立了索引 其性能也不會得到很大的改善 相反索引建立的開銷 如維護成本等等 要比這個要大 也就是說 付出的要比得到的多 顯然違反常理
另外 就是對於超大的表 也不一定要建立索引 有些表雖然比較大 記錄數量非常的多 但是此時為這個表建立索引並一定的合適 如系統中有一張表 其主要用來保存資料庫中的一些變更信息 往往這些信息只給資料庫管理員使用 此時為這張表建立索引的話 反而不合適 因為這張表很少用到 只有在出問題的時候才需要查看 其次其即使查看 需要查詢的紀錄也不會很多 可能就是最近一周的更新記錄等等 對於對於一些超大的表 建立索引有時候往往不能夠達到預計的效果 而且在打表上建立索引 其索引的開銷要比普通的表大的多 那麼到底是否給大表建立索引呢?筆者認為 主要是看兩個方面的內容 首前中圓先是需要關注一下 在這張大表中經常需要查詢的記錄數量 一般來說 如果經常需要查詢的數據不超過 %到 %的話 那就沒有必要為其建立索引的必要 因為此時建立索引的開銷可能要比性能的改善大的多 這個比例只是一個經驗的數據 如果資料庫管理員需要得出一個比較精確的結論 那麼就需要進行測試分析 即資料庫管理員需要測試一下全表掃描的時間 看看其是否比建立索引後的查詢時間要長或者短 如果是長的話 則說明有建立索引的必要 但是如果沒有的話 則說明還是全表掃描速度來的快 此時也就沒有必要建立索引了
總之 在考慮是否該為表建立索引時 一般來說小表沒有建立索引的必要 而對於打表的話 則需要進行實際情況實際分析 簡單一點的 可以根據大致的比率來確定 如果要精確一點的 則可以進行全表掃描性能分析 以判斷建立索引後是否真的如預期那樣改善了資料庫性能
二 根據列的特徵來創建索引
列的特點不同 索引創建的效果也不同 資料庫管理員需要了解為哪些列創建索引可以起到事倍功半的效果 同時也需要了解為哪些列創建索引反而起到的是事倍功半的效果 這有利於他們了解到底給為怎麼樣的欄位建立索引
根據筆者的經驗 往往為如下特徵的列創建索引能夠起到比較明顯的效果 如對於一些重復內容比較少的列 特別是對於那些定義了唯一約束的列 在這些列上建立索引 往往可以起到非常不錯的效果 如對於一些null值的列與非Null值的列培敏混合情況下 如果用戶需要經常查詢所有的非Null值記錄的列 則最好為其設置索引 如果經常需要多表連接查詢 在用與連接的列上設置索引可以達到事半功倍的效果
可見 索引設置的是否恰當 不僅跟資料庫設計架構有關 而且還跟企業的經濟業務相關 為此 對於一些套裝軟體 雖然一開始資料庫管理員已經做了索引的優化工作 但是隨著後來經濟數據的增加 這個索引的效果會越來越打折扣 這主要是因為記錄的表化影響到了索引優化的效果 所以筆者建議各位資料庫管理員 即使採用的是大牌軟體公司的套裝軟體 也需要隔一段時間 如一年 對資料庫的索引進行優化 該去掉的去掉 該調整的調整 以提高資料庫的性能
如在資料庫中有一張表是用來保存用戶信息的 其中有個欄位身份證號碼 這是一個唯一的欄位 在資料庫設計時 給這個欄位創建了索引 但是當這個資料庫投入使用之後 用戶不怎麼輸入用戶的身份證號碼 而且平時也基本不按這個號碼來進行查詢 當記錄月來月多時 這個身份證號碼上的索引欄位不但不能夠改善資料庫的查詢性能 反而成了雞肋 對於這些有很多NULL值的列 而且不會經常查詢所有的非NULL值記錄的列 資料庫管理員要下決心 即使清除這些列上的索引
所以說索引的優化與調整是一個動態的過程 並不是說資料庫設計好之後就不需要經過調整 資料庫管理員往往需要根據記錄的變化情況 來進行適當的變更 以提高索引的效果
三 在一個表上創建多少索引合適?
雖然說 在表上創建索引的數量沒有限制 但是決不是越多越好 也就是說 在創建索引這項事情上 + 〉 往往不成立 有時候 創建索引越多 其可能會得到適得其反的效果 那麼在一個表上 到底給創建多少索引合適呢?這個沒有一個明確的標准 而是需要資料庫管理員根據實際的用途以及資料庫中記錄的情況 來進行判斷
通常來說 表的索引越多 其查詢的速度也就越快 但是 表的更新速度則會降低 這主要是因為表的更新(如往表中插入一條記錄)速度 反而隨著索引的增加而增加 這主要是因為 在更新記錄的同時需要更新相關的索引信息 為此 到底在表中創建多少索引合適 就需要在這個更新速度與查詢速度之間取得一個均衡點 如對於一些數據倉庫或者決策型資料庫系統 其主要用來進行查詢 相關的記錄往往是在資料庫初始化的時候倒入 此時 設置的索引多一點 可以提高資料庫的查詢性能 同時因為記錄不怎麼更新 所以索引比較多的情況下 也不會影響到更新的速度 即使在起初的時候需要導入大量的數據 此時也可以先將索引禁用掉 等到數據導入完畢後 再啟用索引 可以通過這種方式來減少索引對數據更新的影響 相反 如果那些表中經常需要更新記錄 如一些事務型的應用系統 數據更新操作是家常便飯的事情 此時如果在一張表中建立過多的索引 則會影響到更新的速度 由於更新操作比較頻繁 所以對其的負面影響 要比查詢效率提升要大的多 此時就需要限制索引的數量 只在一些必要的欄位上建立索引
筆者在平時資料庫優化時 往往會根據這些表的用途來為列設置索引 可以查詢相關的動態視圖 看看對於這張表的操作 是更新操作(包括更新 刪除 插入等等)占的比例大 還是查詢操作占的比例大 當過多的索引已經影響到更新操作的速度時 則資料庫管理員就需要先禁用某些索引 以提高資料庫的性能
lishixin/Article/program/Oracle/201311/18407