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