充分利用索引
索引的限制
1. 索引對不等號和NOT的限制
如果WHERE條件中出現!=或者<>，即使該列建立了索引，則該索引也不會(huì )被使用；如果不恰當的使用了NOT，則索引也不會(huì )被使用。
Oracle 10g起，在基于CBO的優(yōu)化器模式下Oralce會(huì )進(jìn)行自動(dòng)優(yōu)化，但在基于RBO（基于規則）的優(yōu)化器模式下，依然保持此規則。
1. 索引對不等號和NOT的限制
RBO模式下，執行計劃如下：
1. 索引對不等號和NOT的限制
RBO模式下，執行情況如下：
此時(shí)使用變通寫(xiě)法的耗費為：0.407/2.187=1.60%!
1. 索引對不等號和NOT的限制
CBO模式下，執行情況如下：
此時(shí)使用變通寫(xiě)法的耗費節約不到0.03秒，但依然更優(yōu)，故此推薦此種變通寫(xiě)法，再看此時(shí)使用NOT：
1. 索引對不等號和NOT的限制
CBO模式下，在JYJE列的索引上使用NOT：
為使用<>的：0.156/0.329=47.42%!為變通寫(xiě)法的使用0.156/0.297=52.53%！ 因 此這種寫(xiě)法最優(yōu)！
1. 索引對不等號和NOT的限制
一般，WHERE條件中，如果索引列是字符列，使用NOT往往也不會(huì )使用索引：
結論：如果索引列是數字，則對于不等號的處理可以變更為NOT的方式或者(大于 OR 小于)的方式① ；對于確實(shí)無(wú)法不使用不等號的方式，可以使用默認值② ；如果可以建立位圖索引則使用位圖索引③ ；否則可以考慮使用分區等方法進(jìn)行優(yōu)化④ ，具體是情況而定。
2. 索引對IS NULL的限制
一般來(lái)說(shuō)，如果WHERE子句基于的列是可空的列，且其建立了索引，如果使用了IS NULL，由于NULL的列本身不包含在索引中，因此無(wú)法利用索引。
所以一般對要建立索引的列不要設置為可空，如果確實(shí)含有空值，建議使用默認值代替空值，具體參見(jiàn)前面章節“SQL優(yōu)化技巧”部分的“使用默認值”。
3. 索引對函數的限制
基于索引IDX_BIGTAB_OBJECTNAME，執行情況如下：
執行計劃情況如下：
這是因為該索引是常規b-tree索引，對該列在WHERE子句中使用了函數，則不能使用索引。因此，對在WHERE子句中經(jīng)常要使用函數時(shí)，應該建立基于函數的索引，且 只有當查詢(xún)語(yǔ)句包含該函數或者表達式時(shí)，基于函數的索引才會(huì )被調 用。詳情請參見(jiàn)索引部分的理論講解！
創(chuàng )建并使用函數索引：
創(chuàng )建并使用函數索引：
此時(shí)使用基于函數的索引效率是原來(lái)的2.782/0.188＝14.78！唄！
4. 索引對不匹配數據類(lèi)型的限制
先看執行情況：
不匹配的類(lèi)型執行的時(shí)間是匹配的類(lèi)型的 2.187/0.266=8.2 倍！
再看執行計劃：
原因分析：
因為ACCOUNT_TRADE表的字段YKKH是CHAR，因此在對其指定的值是數字時(shí)，Oracle雖然能隱式的執行數字和字符的轉換，但不會(huì )調用其索引。而當對其指定是字符時(shí)，則不存在此問(wèn)題，索引可以調用。
注意：因為數據類(lèi)型的不匹配和Oracle對數據類(lèi)型的隱式轉換，此種類(lèi)型的低效代碼在任何項目中均可能因為大意而存在，因此建議開(kāi)發(fā)人員和管理人員要定期抽查相應的代碼，以杜絕此類(lèi)低效代碼！
索引類(lèi)型總結
類(lèi)型
，描述
b-tree索引
最常最多使用的索引，其樹(shù)結構與二叉樹(shù)比較類(lèi)似，根據ROWID快速定位所訪(fǎng)問(wèn)的行
bitmap索引
使用位圖來(lái)管理與數據行的對應關(guān)系，適用于基數比較少的列
降序索引
降序索引在葉子節點(diǎn)中的存儲從左到右是按照從大到小排序的；一般是針對逆向排序較多的查詢(xún)時(shí)才使用該類(lèi)型索引
函數索引
針對要頻繁對列使用函數的索引，只有當查詢(xún)語(yǔ)句包含該函數或者表達式時(shí)，基于函數的索引才會(huì )被調用
反轉索引
反轉了b*tree索引碼中的字節，使索引條目分配更均勻，多用于并行服務(wù)器環(huán)境下，用于減少索引的競爭
分區索引
分區表的索引，又包括本地分區索引(本地前綴分區索引和本地非前綴分區索引)和全局索引，一般建議使用本地分區索引，因其與基表具有良好的數據均衡性和可維護性
訪(fǎng)問(wèn)路徑
1. 全表掃描
全表掃描（FULL TABLE SCANS）時(shí)所有行、所有數據塊均會(huì )被讀到，是 效率最 低的一種，一般會(huì )在表 缺少索引、 讀取大量數據、 訪(fǎng)問(wèn)小表或 高并發(fā)時(shí)發(fā)生。
2. ROWID掃描
ROWID掃描（ROWID SCANS）是通過(guò)ROWID中數據文件和塊位置訪(fǎng)問(wèn)數據行。一般作為訪(fǎng)問(wèn)索引后的第二步，如果訪(fǎng)問(wèn)的列全部包含在索引中，則不會(huì )執行ROWID掃描。
作為索引訪(fǎng)問(wèn)后的第二步：
訪(fǎng)問(wèn)的列全部在索引中不再執行ROWID掃描
3. 索引掃描
索引掃描(INDEX SCANS)包含全索引掃描（full index scan、FIS）、快速全索引掃描(fast full index scan、FFIS)、索引范圍掃描(index range scan)、索引唯一掃描 (index unique scan)、索引跳躍式掃描 (index skip scan)、位圖索引掃描(bitmap index scan)， 其中前5種在本系列課程的索引章 節部分已經(jīng)講解了其理論和示例。位圖索引示例如下：
3. 索引掃描
類(lèi)型
方式
發(fā)生條件
1.FULL INDEX SCANS
逐一讀取索引中的所有塊，由于索引中數據已按索引鍵排序，因此會(huì )忽略掉排序
1.ORDER BY中的列全部在該索引中時(shí)
2.ORDER BY中列的順序滿(mǎn)足索引中前導列的順序時(shí)
3.使用GROUP BY且該子句中的列在索引中時(shí)
2.FAST FULL INDEX SCANS
只掃描索引中的數據，不會(huì )掃描表中的數據；由于索引中數據未按索引鍵排序，因此不能忽略掉排序
當同時(shí)滿(mǎn)足下列條件是，Oracle用FFIS替代FIS:
1.查詢(xún)的所有列均包含在索引中
2.索引中的列至少一個(gè)具有not null約束
3.INDEX RANGE SCANS
訪(fǎng)問(wèn)選擇性數據最常用的掃描方式；按順序的對某個(gè)索引進(jìn)行掃描，返回數據是升序排列的，可以使用唯一索引和非唯一索引;如果對索引列使用ORDER BY/GROUP BY則可省略排序
1.在唯一索引上使用范圍操作符(>、<、>=、<=、<>、BETWEEN)
2.在組合索引上使用部分列進(jìn)行查詢(xún)，導致查出多行
4.INDEX UNIQUE SCANS
掃描唯一索引或主鍵，要么返回一行數據要么返回0行數據
1.當使用唯一索引時(shí)
2.當使用主鍵時(shí)
5.INDEX SKIP SCANS
其實(shí)質(zhì)是將索引分解成多個(gè)小的子索引來(lái)提高效率，系從9i開(kāi)始引入
復合索引中前導列的取值是枚舉的從而可以分拆為多個(gè)子索引,并且查詢(xún)條件中不含前導列時(shí)
為了 加深鞏固前面的知識，本處對前五種索引掃描復習總結如下：
(1).全索引掃描
逐一讀取索引中的所有塊，由于索引中數據已按索引鍵排序，因此會(huì )忽略掉排序，可能發(fā)生的情況如下：
A. ORDER BY中的列全部在某個(gè)索引中
全部在某個(gè)索引中：
(1).全索引掃描
B. ORDER BY中列的順序滿(mǎn)足索引中前導列的順序時(shí)
下面分別是滿(mǎn)足和不滿(mǎn)足前導列順序時(shí)：
C. 使用GROUP BY且該子句中的列在索引中時(shí)
(2).快速全索引掃描
只掃描索引中的數據，不會(huì )掃描表中的數據；由于索引中數據未按索引鍵排序，因此不能忽略掉排序。當同時(shí)滿(mǎn)足下列條件時(shí)，Oracle用FFIS替代FIS或FTS:
1.查詢(xún)的所有列均包含在索引中
2.索引中的列至少一個(gè)具有not null約束（10g開(kāi)始的，原低版本的系統中為查詢(xún)的列中不包含任何null值）
全部列均在索引中：
有列不在索引中：
刪除該索引，創(chuàng )建新索引，兩個(gè)列均為可空：
此時(shí)即使全部列在該索引中， 也不會(huì )發(fā)生FFIS
索引范圍掃描是訪(fǎng)問(wèn)選擇性數據最常用的掃描方式；按順序的對某個(gè)索引進(jìn)行掃描，返回數據是升序排列的，可以使用唯一索引和非唯一索引;如果對索引列使用ORDER BY/GROUP BY則可省略排序。
下列情形中會(huì )發(fā)生索引范圍掃描：
A.在唯一索引上使用范圍操作符(>、<、>=、<=、<>、BETWEEN)
B.在組合索引上使用部分列進(jìn)行查詢(xún)，導致查出多行
示例請參考本系列課程的索引章節部分
(4).索引唯一掃描
當使用主鍵或唯一索引時(shí)發(fā)生。 示例請參考本系列課程的索引章節部分。
(5).索引跳躍掃描
復合索引中前導列的取值是枚舉的從而可以分拆為多個(gè)子索引,并且查詢(xún)條件中不含前導列時(shí)。示例如下：
create table customers as select * from sh.customers;
CREATE INDEX customers_gender_email
ON customers (cust_gender, cust_email);
(5).索引跳躍掃描
沒(méi)進(jìn)行表分析前：
進(jìn)行表分后：
analyze table customers compute statistics;
何時(shí)需要索引
一般地，對于從表的總行中的大部分查詢(xún)只查詢(xún)不到10%數據（有的稱(chēng)為2%-4%）的表，可以考慮創(chuàng )建索引。一般考慮的索引的原則包括：
l對于經(jīng)常以查詢(xún)關(guān)鍵字為基礎的表，并且該表中的數據行是均勻分布的
l以查詢(xún)關(guān)鍵字為基礎，表中的數據行隨機排序
l表中包含的列數相對比較少（僅僅是相對，需要根據實(shí)際情況確定)
l表中的大多數查詢(xún)都包含相對簡(jiǎn)單的WHERE子句
l表的記錄數比較少的，不建議使用索引，如數據不超過(guò)1萬(wàn)行的表不要建立索
為索引選擇列和表達式
一般遵循的原則包括：
l經(jīng)常在WHERE子句中使用的列
lSQL語(yǔ)句中經(jīng)常用于表之間連接的列
l重復性少（可選擇性高）的關(guān)鍵字，如主鍵
l不宜將經(jīng)常UPDATE的列作為索引列
l不宜將經(jīng)常在WHERE子句中使用，但與函數或操作符相結合的列作為索引列
l對于取值較少的列，應考慮建立位圖索引，而不應該采用B樹(shù)索引
l如果經(jīng)常訪(fǎng)問(wèn)的列上要使用函數，應使用基于函數的索引
本處舉例說(shuō)明取值較少的列使用bitmap索引和b-tree的對比分析，B-tree時(shí)：
bitmap時(shí)：
使用復合索引
多個(gè)列聯(lián)合起來(lái)組成的索引稱(chēng)為復合索引、或聯(lián)合索引或者組合索引，往往聯(lián)合索引比單個(gè)索引具有更好的性能。創(chuàng )建聯(lián)合索引一般遵循的原則包括：
l經(jīng)常在WHERE子句中使用的列且這些列之間使用AND連接
l查詢(xún)條件可能包括n個(gè)列的AND關(guān)系，而大多數情況下使用m個(gè)列是(n>m)，應該考慮復合索引，且n個(gè)列為前導列
l某幾個(gè)列聯(lián)合起來(lái)能夠組成唯一索引，應堅決建立聯(lián)合唯一索引
l復合索引中，建議至少一個(gè)不能為null，且如果可能盡量將只是存在null的列對其null值采用其它默認值代替
本處舉例說(shuō)明Where中包含AND時(shí)使用多個(gè)索引性能低于聯(lián)合索引的示例，使用多個(gè)索引時(shí)：
本處舉例說(shuō)明Where中包含AND是使用多個(gè)索引性能低于聯(lián)合索引的示例，使用復合索引時(shí)：
結論：
項目
多個(gè)索引
復合索引
復合索引是多個(gè)索引的
一、執行時(shí)間
0.281
0.11
39.15%
二、執行計劃
1.總耗費
1658
464
27.99%
2.I/O耗費
1562
462
29.58%
3.時(shí)間
19
6
31.58%
可見(jiàn)，此時(shí)復合索引是多個(gè)索引的效率的 四倍以上！
監視索引的使用情況
u正確合適的索引是查詢(xún)優(yōu)化性能的首選
u索引是表的索引列排序后的小型化拷貝，會(huì )增加存儲開(kāi)銷(xiāo)，因此會(huì )帶來(lái)Insert、Update、Delete的額外開(kāi)銷(xiāo)
u一個(gè)表可以有一個(gè)索引，也可以有多個(gè)索引，往往過(guò)多的索引或不恰當的索引帶來(lái)的負面性能更多
u表索引的設計初衷，往往在40%甚至更高的情況下與最終的實(shí)際使用情況不符合，此舉視設計人員對業(yè)務(wù)和Oracle的理解不同而不同
u監視索引的實(shí)際使用情況，尤其在表具有多個(gè)索引的情況下，就顯得尤為重要，對經(jīng)常不使用的索引采用合并為復合索引或刪除是優(yōu)化的工作之一
示例如下：
1.創(chuàng )建索引
2.啟用所以監視
3.執行SQL
4.查看索引使用情況
我們可以根據一個(gè)持續時(shí)間的對索引的監控結果決定如何合并及刪除不恰當的索引。
5.停止監視索引
