這篇文章主要介紹了oracle數(shù)據(jù)庫中為什么在查詢里索引未被使用，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

創(chuàng)新互聯(lián)建站是專業(yè)的鎮(zhèn)安網(wǎng)站建設公司，鎮(zhèn)安接單;提供成都網(wǎng)站設計、網(wǎng)站建設,網(wǎng)頁設計,網(wǎng)站設計,建網(wǎng)站,PHP網(wǎng)站建設等專業(yè)做網(wǎng)站服務;采用PHP框架,可快速的進行鎮(zhèn)安網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團隊,希望更多企業(yè)前來合作!

快速檢查

表上是否存在索引?

檢查您認為應該通過索引訪問的表上是否真的有定義索引。那些索引可能已經(jīng)被刪掉或者在創(chuàng)建的時候就失敗了 – 比如一種可能的場景是，在對表做導入或 load  操作后，由于軟件或人為錯誤造成索引沒有被創(chuàng)建。下面的語句可以用來檢查索引是否存在。

SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;

索引是否應該被使用?

Oracle  不會僅僅因為有索引存在就一定要使用索引。如果一個查詢需要檢索出這個表里所有的記錄(比如說表之間做連接操作)，那為什么還要既訪問索引的所有數(shù)據(jù)又訪問表的所有數(shù)據(jù)呢?在這種情況下只訪問表的數(shù)據(jù)會更快。對所有的查詢  Oracle Optimizer 會基于統(tǒng)計信息來計算各種訪問路徑，包括索引，從而選出***的一個。

索引本身的問題

索引列或者索引的前置列是否在單表(non-join)查詢的 Where 條件中(predicate list)?

如果不是，至少需要索引前置列在查詢謂詞列表中，查詢才能使用索引。(例外：請見下面的 Skip Scan)。

示例:

在列 EMP.EMPNO 上定義了單列索引 EMPNO_I1，同時在列 EMP.EMPNO 和 EMP.DEPT 上定義了聯(lián)合索引  EMPNO_DEPT_I2(EMP.EMPNO為索引前置列)。那么必須在查詢謂詞列表中(where從句)使用列  EMP.EMPNO，優(yōu)化器才能使用這兩個索引中的某一個。

SELECT ename, sal, deptno FROM emp WHERE empno<100;

例外:

• 只要索引中包含查詢所需的所有列, 而且至少有一個索引列中含有非空約束，CBO 就能夠使用索引快速全掃描(INDEX_FFS)。執(zhí)行 INDEX_FFS  不需要索引前置列。需要注意的是 INDEX_FFS 不能保證返回的行是排序的。結果的順序是與讀取索引塊的順序一致的，只有當使用了 'order by'  子句時才能保證結果是排序的。

• CBO 能使用 Index Skip Scan (INDEX_SS). 執(zhí)行 INDEX_SS 不需要索引前置列。

• CBO 能夠選用一個索引來避免排序，但是索引列必須存于在 order by 子句中才可以。

索引列是否用在連接謂詞中(join predicates)?

例如，下面這個連接謂詞定義了如何在表 emp 和 dept 的 deptno 列上做連接：

emp.deptno = dept.deptno

如果索引列是連接謂詞的一部分，那么查詢在執(zhí)行時使用了哪種類型的連接?

• 哈希/排序合并連接(Hash / Sort Merge Join):  對于哈希連接和排序合并，在連接執(zhí)行的時候，外部表的信息還沒有獲得，因此無法進行對內(nèi)部表的行檢索。它的處理方式是將外部表和內(nèi)部表分別查詢后將結果合并。哈希連接和排序合并的內(nèi)部表不能通過連接的索引列單獨被訪問。這是連接類型的執(zhí)行機制的限制。嵌套循環(huán)連接有所不同，它們允許通過索引查詢內(nèi)部表的連接列。

• 嵌套循環(huán)連接(Nested Loops Join):嵌套循環(huán)連接讀取外部表，然后利用所收集的信息訪問內(nèi)部表。該算法允許對內(nèi)部表基于索引進行查詢。

只有嵌套循環(huán)連接(Nested loops join)允許索引在內(nèi)部表中僅基于連接列進行查找。

另外，連接的順序(join order)是否允許使用索引?

一個嵌套循環(huán)連接的外部表必須已經(jīng)訪問過，才可以在內(nèi)部表中使用索引。查看 explain  plan，以確定哪些訪問路徑已經(jīng)使用。由于這個限制，表的連接順序是很重要的。

例如：

如果我們通過"emp.deptno = dept.deptno"來對 EMP 和 DEPT 做連接，并且在 EMP.DEPTNO  有一個索引，并假設查詢中沒有與 EMP.DEPTNO 相關的其他謂詞，EMP 是在 DEPT 前被訪問，然后沒有值可用于在 EMP.DEPTNO  索引中查詢。在這種連接順序下，要想使用這個索引我們只能使用全索引掃描或索引快速全掃描。在這種情況下，全表掃描(FTS)的成本可能更小。

索引列在 IN 或者多個 OR 語句中?

比如：

emp.deptno IN (10,23,34,....)

或

emp.deptno = 10  OR emp.deptno = 23  OR emp.deptno = 34  ....

這種情況下查詢可能已經(jīng)被轉化為不能使用索引的語句。

索引列是否被函數(shù)修改?

索引不能用于被函數(shù)修改的列。函數(shù)索引(function based indexes)可以用來解決這個問題。

隱式類型轉換(implicit type conversion)是什么?

如果進行比較的兩個值的數(shù)據(jù)類型不同，則 Oracle  必須將其中一個值進行類型轉換使其能夠比較。這就是所謂的隱式類型轉換。通常當開發(fā)人員將數(shù)字存儲在字符列時會導致這種問題的產(chǎn)生。Oracle  在運行時會強制轉化其中一個值，(由于固定的規(guī)則)在索引字符列使用 to_number。由于添加函數(shù)到索引列所以導致索引不被使用。實際上，Oracle  也只能這么做，類型轉換是一個應用程序設計因素。由于轉換是在每行都進行的，這會導致性能問題。

是否在語義(semantically)上無法使用索引?

出于對查詢整體成本的考慮，一個成本較低的執(zhí)行計劃中可能是無法使用索引的。某索引可能已經(jīng)被考慮在某種連接排序及方法中，但是成本***的那個執(zhí)行計劃中卻無法從“語義”角度使用該索引。

錯誤類型的索引掃描?

例如：快速全索引掃描而不是索引范圍掃描

這可能是優(yōu)化器選擇了所需的索引，但卻使用了客戶不希望的掃描方法。在這種情況下，利用 INDEX_FFS，INDEX_ASC 和 INDEX_DESC  提示來強制使用需要的掃描類型

我們還可以定義索引的排序順序為遞增或遞減。Oracle  對待降序索引就好像它是基于函數(shù)的索引，因此與缺省使用的升序的執(zhí)行計劃不同。通過查看執(zhí)行計劃，您看不到使用升序或降序，需要額外檢查視圖  DBA_IND_COLUMNS 的'DESCEND'列。

是否索引列為可空?

索引不存儲 NULL 值，除非該索引為聯(lián)合索引(即多列索引)，或者它是一個位圖索引。

只有至少有一個索引列有值，聯(lián)合索引才存儲空值。聯(lián)合索引中尾部的空值也會被存放在索引中。如果所有列的值都為空，這行將不會存儲在索引中。由于索引中缺乏  NULL 值，那么一些結果中可能會返回 NULL 值(如count)的操作可能會被禁用索引。這是因為優(yōu)化器不能保證在單獨使用索引時可以獲得準確的信息。關于使用  NOT IN 和 NULL 的其他一些考慮。

位圖索引允許存儲空值。因此優(yōu)化器會使用這些索引,無論它們的結果可信與否。索引上的空值有時很有用，特別對于某些類型的 SQL 語句，如與聚合函數(shù) COUNT  查詢。示例:

SELECT count(*) FROM emp;

NLS_SORT是否設置為二進制(BINARY)?

如果 NLS_SORT 未設置為二進制，索引將不會被使用。這是因為索引是基于 Key  值的二進制順序來建立的(pre-sorted使用二進制值)。無論優(yōu)化器設置為何種方法，NLS_SORT  不是二進制時，將使用全表掃描，。更多關于NLS_SORT和索引的使用

是否使用的是不可見索引(invisible indexes)?

從 Oracle Database 11g Release 1開始，您可以創(chuàng)建不可見索引或?qū)⒁粋€已經(jīng)存在的索引標記為不可見。Optimizer  不會考慮不可見索引，除非在 session 或 system 級將參數(shù) OPTIMIZER_USE_INVISIBLE_INDEXES 設置為 TRUE。DML  操作還是會維護這些不可見索引的。

優(yōu)化器和成本計算相關問題

是否存在準確且合適的統(tǒng)計信息(Statistics)?

CBO 依賴于準確的、***的和完整的統(tǒng)計信息來確定一個特定查詢的***執(zhí)行計劃。如果使用 CBO，請確保統(tǒng)計信息已經(jīng)收集。如果沒有統(tǒng)計信息， CBO  將使用預定義的統(tǒng)計信息，這樣是很可能不會產(chǎn)生良好的計劃或讓應用程序使用索引。

請注意，CBO  會根據(jù)開銷(COST)來決定使用不同的索引。除了基本的表和索引的信息之外，如果說在某些列上數(shù)據(jù)分布是不均勻的，那么還需要收集這些列的數(shù)據(jù)的分布。

在一般情況下，對象的數(shù)據(jù)或結構的改變會使以前的統(tǒng)計信息不準確，因此應該重新收集新的統(tǒng)計信息。例如，對表裝載了大量的數(shù)據(jù)后，需要收集新的統(tǒng)計信息。安裝新補丁集(Patchset)后,也建議重新收集統(tǒng)計信息。表訪問***效果是統(tǒng)計信息是在相同版本的數(shù)據(jù)庫中生成的。

一個索引是否與其它的索引有相同的等級或者成本(cost)?

對于相同開銷(COST)的索引，CBO  會使用多種辦法將不同的索引區(qū)分開，如將索引名稱按字母順序排序，完全匹配的索引掃描會選擇更大的NDK(不同鍵值的個數(shù))的索引(不適用于快速全掃描)或選擇葉塊數(shù)量較少的索引。請注意一般很少發(fā)生這種情況。

索引的選擇度不高?

索引的選擇度不高

使用它可能不是一個好的選擇...

列數(shù)據(jù)不是平均分布的。

• CBO  假定列數(shù)據(jù)不會傾斜，并均勻分布。如果不是這樣，那么統(tǒng)計信息可能沒有反映真實情況，那么即使某些值的選擇度高，索引也會因為整個列的選擇度不高而不適用索引。  如果是這種情況，那么應考慮采用直方圖記錄更準確的列的數(shù)據(jù)分布或者采用提示(hint)。

• 統(tǒng)計信息不準確導致索引看起來選擇性不高而不被選擇?？赡艿囊?guī)避方法:

• 收集更精確的統(tǒng)計值。

• 對于數(shù)據(jù)分布不均勻的列考慮收集列的統(tǒng)計信息

在總體成本中，表掃描的成本占大部分

通常來說，當使用索引的時候，我們需要再次檢索表本身來找到索引中不存在的字段的值，這個操作比檢索索引本身的開銷要大很多。由于 optimizer  是基于總體的成本來計算執(zhí)行計劃，如果通過索引檢索表的成本很大，并且超過了某個閥值，optimizer 就會考慮其他的訪問路徑。

比如：

SELECT empno FROM emp WHERE empno=5

這條語句可能會使用基于列 empno的索引，因為所有需要的數(shù)據(jù)都存放在索引中所以不需要再對表做而外的訪問。反之：

SELECT ename FROM emp WHERE empno=5

這條語句會需要對表做而外的訪問，因為 ename 字段沒有存放在索引中。檢索 ename 的開銷會隨著查詢返回記錄條數(shù)的增加而變得昂貴。

Optimizer 使用"Clustering Factor"來判斷如果使用 index 的話需要而外對表做多少次訪問

訪問空索引并不意味著比訪問有值的索引高效。

Reorganization, Truncation 或刪除操作不一定會影響 SQL  語句執(zhí)行的成本。需要注意的是刪除操作并不會從對象中真正釋放空間。也就是說，刪除操作不會重置對象的高水位線。Truncate  操作會重置高水位線?？諌K的存在會使索引/表掃描的成本比實際應該的成本高。刪掉并重建會重組對象的結構從而有可能會有幫助(也有可能變壞)。這類問題通常在比較兩個有相同數(shù)據(jù)的不同系統(tǒng)查詢性能時更容易看到。

參數(shù)設置

某些參數(shù)的設置可能會影響索引的使用。比如在大多數(shù)情況下都建議使用 DB_FILE_MULTIBLOCK_READ_COUNT 和  OPTIMIZER_INDEX_COST_ADJ  的默認值。除非某些特定的操作有特定的建議，使用其它值會使索引的成本不現(xiàn)實的減少或變大從而極大的降低查詢的性能。

其它問題：

是否使用了視圖/子查詢?

查詢涉及到視圖或者子查詢時可能會被改寫，導致不使用索引(盡管該改寫的目標之一是擴展更多的訪問路徑)。這些改寫(rewrite)一般來說都是合并(merging)操作。

是否存在遠程表(remote table)?

通常遠程表不會使用索引。索引在分布式查詢中的使用依賴于被發(fā)送到遠程的查詢。CBO  將評估遠程訪問的成本，并評估比較發(fā)送或者不發(fā)送索引的謂詞到遠程站點的成本。因此，CBO  可以做出有關遠程表上使用索引的更加明智的決定。一個非常有效的方法就是,在遠程建立包含相關謂詞的視圖并強制使用索引，之后在本地查詢中使用這個視圖。

是否使用并行執(zhí)行(PX)?

在并行執(zhí)行時索引的采用比在串行執(zhí)行((serial execution))時更加嚴格。一個快速檢測的方法就是禁用并行，然后查看該索引是否被使用。

是否是包含了子查詢的Update語句?

在一些情況下，基于成本的考慮，索引沒有被選使用是因為它依賴于一個子查詢返回的值。這種情況下，可以使用提示(hint)來強制使用索引。

查詢是否使用了綁定變量?

CBO 對 like 或范圍謂詞的綁定變量不能產(chǎn)生準確的成本(cost)。這可能會導致索引不被選擇。

查詢是否引用了帶有延遲約束的列?

如果一個表中的某一列上含有延遲約束(比如 NOT  NULL)并且這一列上有索引，那么不管這個約束當前是延遲狀態(tài)或是被顯式地設置為立即使用，我們都不會考慮使用這一列上的索引。例如：

這個現(xiàn)象在以下 bug 中記錄，關閉為"not a bug":

索引提示(hint)不工作

請使用表的別名

有用的 hints:

感謝你能夠認真閱讀完這篇文章，希望小編分享的“oracle數(shù)據(jù)庫中為什么在查詢里索引未被使用”這篇文章對大家有幫助，同時也希望大家多多支持創(chuàng)新互聯(lián)，關注創(chuàng)新互聯(lián)行業(yè)資訊頻道，更多相關知識等著你來學習!

網(wǎng)站欄目：oracle數(shù)據(jù)庫中為什么在查詢里索引未被使用
網(wǎng)頁網(wǎng)址：http://jinyejixie.com/article26/gpecjg.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供App設計、商城網(wǎng)站、小程序開發(fā)、網(wǎng)站建設、網(wǎng)站排名、響應式網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)