“為什么索引沒(méi)有被使用”是一個(gè)涉及面較廣的問(wèn)題。有很多種原因會(huì)導(dǎo)致索引沒(méi)有被使用。下面是一些非常有用的檢查列表。請(qǐng)點(diǎn)擊下面鏈接來(lái)查看文章的具體內(nèi)容：

快速檢查

表上是否存在索引?

檢查您認(rèn)為應(yīng)該通過(guò)索引訪問(wèn)的表上是否真的有定義索引。那些索引可能已經(jīng)被刪掉或者在創(chuàng)建的時(shí)候就失敗了 – 比如一種可能的場(chǎng)景是，在對(duì)表做導(dǎo)入或 load 操作后，由于軟件或人為錯(cuò)誤造成索引沒(méi)有被創(chuàng)建。下面的語(yǔ)句可以用來(lái)檢查索引是否存在。

SELECT index_name FROM user_indexes WHERE table_name = &Table_Name; 

索引是否應(yīng)該被使用?

Oracle 不會(huì)僅僅因?yàn)橛兴饕嬖诰鸵欢ㄒ褂盟饕?。如果一個(gè)查詢需要檢索出這個(gè)表里所有的記錄(比如說(shuō)表之間做連接操作)，那為什么還要既訪問(wèn)索引的所有數(shù)據(jù)又訪問(wèn)表的所有數(shù)據(jù)呢?在這種情況下只訪問(wèn)表的數(shù)據(jù)會(huì)更快。對(duì)所有的查詢 Oracle Optimizer 會(huì)基于統(tǒng)計(jì)信息來(lái)計(jì)算各種訪問(wèn)路徑，包括索引，從而選出***的一個(gè)。

索引本身的問(wèn)題

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

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

示例:

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

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

例外:

• 只要索引中包含查詢所需的所有列, 而且至少有一個(gè)索引列中含有非空約束，CBO 就能夠使用索引快速全掃描(INDEX_FFS)。執(zhí)行 INDEX_FFS 不需要索引前置列。需要注意的是 INDEX_FFS 不能保證返回的行是排序的。結(jié)果的順序是與讀取索引塊的順序一致的，只有當(dāng)使用了 'order by' 子句時(shí)才能保證結(jié)果是排序的。
• CBO 能使用 Index Skip Scan (INDEX_SS). 執(zhí)行 INDEX_SS 不需要索引前置列。
• CBO 能夠選用一個(gè)索引來(lái)避免排序，但是索引列必須存于在 order by 子句中才可以。

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

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

emp.deptno = dept.deptno 

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

• 哈希/排序合并連接(Hash / Sort Merge Join): 對(duì)于哈希連接和排序合并，在連接執(zhí)行的時(shí)候，外部表的信息還沒(méi)有獲得，因此無(wú)法進(jìn)行對(duì)內(nèi)部表的行檢索。它的處理方式是將外部表和內(nèi)部表分別查詢后將結(jié)果合并。哈希連接和排序合并的內(nèi)部表不能通過(guò)連接的索引列單獨(dú)被訪問(wèn)。這是連接類(lèi)型的執(zhí)行機(jī)制的限制。嵌套循環(huán)連接有所不同，它們?cè)试S通過(guò)索引查詢內(nèi)部表的連接列。
• 嵌套循環(huán)連接(Nested Loops Join):嵌套循環(huán)連接讀取外部表，然后利用所收集的信息訪問(wèn)內(nèi)部表。該算法允許對(duì)內(nèi)部表基于索引進(jìn)行查詢。

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

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

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

例如：

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

索引列在 IN 或者多個(gè) OR 語(yǔ)句中?

比如：

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

或

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

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

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

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

隱式類(lèi)型轉(zhuǎn)換(implicit type conversion)是什么?

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

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

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

錯(cuò)誤類(lèi)型的索引掃描?

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

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

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

是否索引列為可空?

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

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

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

SELECT count(*) FROM emp; 

NLS_SORT是否設(shè)置為二進(jìn)制(BINARY)?

如果 NLS_SORT 未設(shè)置為二進(jìn)制，索引將不會(huì)被使用。這是因?yàn)樗饕腔?Key 值的二進(jìn)制順序來(lái)建立的(pre-sorted使用二進(jìn)制值)。無(wú)論優(yōu)化器設(shè)置為何種方法，NLS_SORT 不是二進(jìn)制時(shí)，將使用全表掃描，。更多關(guān)于NLS_SORT和索引的使用

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

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

優(yōu)化器和成本計(jì)算相關(guān)問(wèn)題

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

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

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

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

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

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

索引的選擇度不高?

索引的選擇度不高

使用它可能不是一個(gè)好的選擇...

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

• CBO 假定列數(shù)據(jù)不會(huì)傾斜，并均勻分布。如果不是這樣，那么統(tǒng)計(jì)信息可能沒(méi)有反映真實(shí)情況，那么即使某些值的選擇度高，索引也會(huì)因?yàn)檎麄€(gè)列的選擇度不高而不適用索引。 如果是這種情況，那么應(yīng)考慮采用直方圖記錄更準(zhǔn)確的列的數(shù)據(jù)分布或者采用提示(hint)。
• 統(tǒng)計(jì)信息不準(zhǔn)確導(dǎo)致索引看起來(lái)選擇性不高而不被選擇?？赡艿囊?guī)避方法:
• 收集更精確的統(tǒng)計(jì)值。
• 對(duì)于數(shù)據(jù)分布不均勻的列考慮收集列的統(tǒng)計(jì)信息

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

通常來(lái)說(shuō)，當(dāng)使用索引的時(shí)候，我們需要再次檢索表本身來(lái)找到索引中不存在的字段的值，這個(gè)操作比檢索索引本身的開(kāi)銷(xiāo)要大很多。由于 optimizer 是基于總體的成本來(lái)計(jì)算執(zhí)行計(jì)劃，如果通過(guò)索引檢索表的成本很大，并且超過(guò)了某個(gè)閥值，optimizer 就會(huì)考慮其他的訪問(wèn)路徑。

比如：

SELECT empno FROM emp WHERE empno=5 

這條語(yǔ)句可能會(huì)使用基于列 empno的索引，因?yàn)樗行枰臄?shù)據(jù)都存放在索引中所以不需要再對(duì)表做而外的訪問(wèn)。反之：

SELECT ename FROM emp WHERE empno=5 

這條語(yǔ)句會(huì)需要對(duì)表做而外的訪問(wèn)，因?yàn)?ename 字段沒(méi)有存放在索引中。檢索 ename 的開(kāi)銷(xiāo)會(huì)隨著查詢返回記錄條數(shù)的增加而變得昂貴。

Optimizer 使用"Clustering Factor"來(lái)判斷如果使用 index 的話需要而外對(duì)表做多少次訪問(wèn)

訪問(wèn)空索引并不意味著比訪問(wèn)有值的索引高效。

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

參數(shù)設(shè)置

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

其它問(wèn)題：

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

查詢涉及到視圖或者子查詢時(shí)可能會(huì)被改寫(xiě)，導(dǎo)致不使用索引(盡管該改寫(xiě)的目標(biāo)之一是擴(kuò)展更多的訪問(wèn)路徑)。這些改寫(xiě)(rewrite)一般來(lái)說(shuō)都是合并(merging)操作。

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

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

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

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

是否是包含了子查詢的Update語(yǔ)句?

在一些情況下，基于成本的考慮，索引沒(méi)有被選使用是因?yàn)樗蕾囉谝粋€(gè)子查詢返回的值。這種情況下，可以使用提示(hint)來(lái)強(qiáng)制使用索引。

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

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

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

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

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

索引提示(hint)不工作

請(qǐng)使用表的別名

有用的 hints: