索引是提高數(shù)據(jù)查詢的方法，也是最難全面掌握的技術(shù)，因?yàn)檎_的索引可能使效率提高10000倍，而無效的索引可能是浪費(fèi)了數(shù)據(jù)庫空間，甚至大大降低查詢性能。
索引的管理成本
1、 存儲(chǔ)索引的磁盤空間
2、 執(zhí)行數(shù)據(jù)修改操作（INSERT、UPDATE、DELETE）產(chǎn)生的索引維護(hù)
3、 在數(shù)據(jù)處理時(shí)回需額外的回退空間。
實(shí)際數(shù)據(jù)修改測(cè)試：
一個(gè)表有字段A、B、C，同時(shí)進(jìn)行插入10000行記錄測(cè)試
在沒有建索引時(shí)平均完成時(shí)間是2.9秒
在對(duì)A字段建索引后平均完成時(shí)間是6.7秒
在對(duì)A字段和B字段建索引后平均完成時(shí)間是10.3秒
在對(duì)A字段、B字段和C字段都建索引后平均完成時(shí)間是11.7秒
從以上測(cè)試結(jié)果可以明顯看出索引對(duì)數(shù)據(jù)修改產(chǎn)生的影響
索引按存儲(chǔ)方法分類
B*樹索引
B*樹索引是最常用的索引，其存儲(chǔ)結(jié)構(gòu)類似書的索引結(jié)構(gòu)，有分支和葉兩種類型的存儲(chǔ)數(shù)據(jù)塊，分支塊相當(dāng)于書的大目錄，葉塊相當(dāng)于索引到的具體的書頁。一般索引及約束索引都使用B*樹索引。
位圖索引
位圖索引儲(chǔ)存主要用來節(jié)省空間，減少ORACLE對(duì)數(shù)據(jù)塊的訪問，它采用位圖偏移方式來與表的行ID號(hào)對(duì)應(yīng)，采用位圖索引一般是重復(fù)值太多的表字段。位圖索引在實(shí)際密集型OLTP（數(shù)據(jù)事務(wù)處理）中用得比較少，因?yàn)镺LTP會(huì)對(duì)表進(jìn)行大量的刪除、修改、新建操作，ORACLE每次進(jìn)行操作都會(huì)對(duì)要操作的數(shù)據(jù)塊加鎖，所以多人操作很容易產(chǎn)生數(shù)據(jù)塊鎖等待甚至死鎖現(xiàn)象。在OLAP（數(shù)據(jù)分析處理）中應(yīng)用位圖有優(yōu)勢(shì)，因?yàn)镺LAP中大部分是對(duì)數(shù)據(jù)庫的查詢操作，而且一般采用數(shù)據(jù)倉庫技術(shù)，所以大量數(shù)據(jù)采用位圖索引節(jié)省空間比較明顯。
索引按功能分類
索引
索引有兩個(gè)作用，一個(gè)是數(shù)據(jù)約束，一個(gè)是數(shù)據(jù)索引，其中數(shù)據(jù)約束主要用來保證數(shù)據(jù)的完整性，索引產(chǎn)生的索引記錄中每一條記錄都對(duì)應(yīng)一個(gè)的ROWID。
主關(guān)鍵字索引
主關(guān)鍵字索引產(chǎn)生的索引同索引，只不過它是在數(shù)據(jù)庫建立主關(guān)鍵字時(shí)系統(tǒng)自動(dòng)建立的。
一般索引
一般索引不產(chǎn)生數(shù)據(jù)約束作用，其功能主要是對(duì)字段建立索引表，以提高數(shù)據(jù)查詢速度。
索引按索引對(duì)象分類
單列索引（表單個(gè)字段的索引）
多列索引（表多個(gè)字段的索引）
函數(shù)索引（對(duì)字段進(jìn)行函數(shù)運(yùn)算的索引）建立函數(shù)索引的方法：
create index 收費(fèi)日期索引 on GC_DFSS(trunc(sk_rq))
create index 完全客戶編號(hào)索引 on yhzl(qc_bh||kh_bh)
在對(duì)函數(shù)進(jìn)行了索引后，如果當(dāng)前會(huì)話要引用應(yīng)設(shè)置當(dāng)前會(huì)話的query_rewrite_enabled為TRUE。
alter session set query_rewrite_enabled=true
注：如果對(duì)用戶函數(shù)進(jìn)行索引的話，那用戶函數(shù)應(yīng)加上 deterministic參數(shù)，意思是函數(shù)在輸入值固定的情況下返回值也固定。例：
create or replace function trunc_add（input_date date）return date deterministic
as
begin
return trunc(input_date+1);
end trunc_add;
應(yīng)用索引的掃描分類
INDEX UNIQUE SCAN（按索引值掃描）
select * from zl_yhjbqk where hbs_bh=@#5420016000@#
INDEX RANGE SCAN（按索引值范圍掃描）
select * from zl_yhjbqk where hbs_bh>@#5420016000@#
select * from zl_yhjbqk where qc_bh>@#7001@#
INDEX FAST FULL SCAN（按索引值快速全部掃描）
select hbs_bh from zl_yhjbqk order by hbs_bh
select count(*) from zl_yhjbqk
select qc_bh from zl_yhjbqk group by qc_bh
什么情況下應(yīng)該建立索引
表的主關(guān)鍵字
自動(dòng)建立索引
如zl_yhjbqk（用戶基本情況）中的hbs_bh（戶標(biāo)識(shí)編號(hào)）
表的字段約束
ORACLE利用索引來保證數(shù)據(jù)的完整性
如lc_hj（流程環(huán)節(jié)）中的lc_bh+hj_sx（流程編號(hào)+環(huán)節(jié)順序）
直接條件查詢的字段
在SQL中用于條件約束的字段
如zl_yhjbqk（用戶基本情況）中的qc_bh（區(qū)冊(cè)編號(hào)）
select * from zl_yhjbqk where qc_bh=’7001’
查詢中與其它表關(guān)聯(lián)的字段
字段常常建立了外鍵關(guān)系
如zl_ydcf（用電成份）中的jldb_bh（計(jì)量點(diǎn)表編號(hào)）
select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’
查詢中排序的字段
排序的字段如果通過索引去訪問那將大大提高排序速度 select * from zl_yhjbqk order by qc_bh（建立qc_bh索引）
select * from zl_yhjbqk where qc_bh=@#7001@# order by cb_sx（建立qc_bh+cb_sx索引，注：只是一個(gè)索引，其中包括qc_bh和cb_sx字段）
查詢中統(tǒng)計(jì)或分組統(tǒng)計(jì)的字段
select max(hbs_bh) from zl_yhjbqk
select qc_bh,count(*) from zl_yhjbqk group by qc_bh
什么情況下應(yīng)不建或少建索引
表記錄太少
如果一個(gè)表只有5條記錄，采用索引去訪問記錄的話，那首先需訪問索引表，再通過索引表訪問數(shù)據(jù)表，一般索引表與數(shù)據(jù)表不在同一個(gè)數(shù)據(jù)塊，這種情況下ORACLE至少要往返讀取數(shù)據(jù)塊兩次。而不用索引的情況下ORACLE會(huì)將所有的數(shù)據(jù)一次讀出，處理速度顯然會(huì)比用索引快。
如表zl_sybm（使用部門）一般只有幾條記錄，除了主關(guān)鍵字外對(duì)任何一個(gè)字段建索引都不會(huì)產(chǎn)生性能優(yōu)化，實(shí)際上如果對(duì)這個(gè)表進(jìn)行了統(tǒng)計(jì)分析后ORACLE也不會(huì)用你建的索引，而是自動(dòng)執(zhí)行全表訪問。如：
select * from zl_sybm where sydw_bh=@#5401@#（對(duì)sydw_bh建立索引不會(huì)產(chǎn)生性能優(yōu)化）
經(jīng)常插入、刪除、修改的表
對(duì)一些經(jīng)常處理的業(yè)務(wù)表應(yīng)在查詢?cè)试S的情況下盡量減少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等業(yè)務(wù)表。
數(shù)據(jù)重復(fù)且分布平均的表字段
假如一個(gè)表有10萬行記錄，有一個(gè)字段A只有T和F兩種值，且每個(gè)值的分布概率大約為50%，那么對(duì)這種表A字段建索引一般不會(huì)提高數(shù)據(jù)庫的查詢速度。
經(jīng)常和主字段一塊查詢但主字段索引值比較多的表字段
如gc_dfss（電費(fèi)實(shí)收）表經(jīng)常按收費(fèi)序號(hào)、戶標(biāo)識(shí)編號(hào)、抄表日期、電費(fèi)發(fā)生年月、操作標(biāo)志來具體查詢某一筆收款的情況，如果將所有的字段都建在一個(gè)索引里那將會(huì)增加數(shù)據(jù)的修改、插入、刪除時(shí)間，從實(shí)際上分析一筆收款如果按收費(fèi)序號(hào)索引就已經(jīng)將記錄減少到只有幾條，如果再按后面的幾個(gè)字段索引查詢將對(duì)性能不產(chǎn)生太大的影響。
如何只通過索引返回結(jié)果
一個(gè)索引一般包括單個(gè)或多個(gè)字段，如果能不訪問表直接應(yīng)用索引就返回結(jié)果那將大大提高數(shù)據(jù)庫查詢的性能。對(duì)比以下三個(gè)SQL，其中對(duì)表zl_yhjbqk的hbs_bh和qc_bh字段建立了索引：
1 select hbs_bh,qc_bh,xh_bz from zl_yhjbqk where qc_bh=’7001’
執(zhí)行路徑:
SELECT STATEMENT, GOAL = CHOOSE 11 265 5565
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 5565
INDEX RANGE SCAN DLYX 區(qū)冊(cè)索引 1 265
平均執(zhí)行時(shí)間（0.078秒）
2 select hbs_bh,qc_bh from zl_yhjbqk where qc_bh=’7001’
執(zhí)行路徑:
SELECT STATEMENT, GOAL = CHOOSE 11 265 3710
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 3710
INDEX RANGE SCAN DLYX 區(qū)冊(cè)索引 1 265
平均執(zhí)行時(shí)間（0.078秒）
3 select qc_bh from zl_yhjbqk where qc_bh=’7001’
執(zhí)行路徑:
SELECT STATEMENT, GOAL = CHOOSE 1 265 1060
INDEX RANGE SCAN DLYX 區(qū)冊(cè)索引 1 265 1060
平均執(zhí)行時(shí)間（0.062秒）
從執(zhí)行結(jié)果可以看出第三條SQL的效率。執(zhí)行路徑可以看出第1、2條SQL都多執(zhí)行了TABLE ACCESS BY INDEX ROWID（通過ROWID訪問表） 這個(gè)步驟，因?yàn)榉祷氐慕Y(jié)果列中包括當(dāng)前使用索引(qc_bh)中未索引的列（hbs_bh,xh_bz），而第3條SQL直接通過QC_BH返回了結(jié)果，這就是通過索引直接返回結(jié)果的方法。
如何重建索引
alter index 表電量結(jié)果表主鍵 rebuild
如何快速新建大數(shù)據(jù)量表的索引
如果一個(gè)表的記錄達(dá)到100萬以上的話，要對(duì)其中一個(gè)字段建索引可能要花很長的時(shí)間，甚至導(dǎo)致服務(wù)器數(shù)據(jù)庫死機(jī)，因?yàn)樵诮ㄋ饕臅r(shí)候ORACLE要將索引字段所有的內(nèi)容取出并進(jìn)行全面排序，數(shù)據(jù)量大的話可能導(dǎo)致服務(wù)器排序內(nèi)存不足而引用磁盤交換空間進(jìn)行，這將嚴(yán)重影響服務(wù)器數(shù)據(jù)庫的工作。解決方法是增大數(shù)據(jù)庫啟動(dòng)初始化中的排序內(nèi)存參數(shù)，如果要進(jìn)行大量的索引修改可以設(shè)置10M以上的排序內(nèi)存（ORACLE缺省大小為64K），在索引建立完成后應(yīng)將參數(shù)修改回來，因?yàn)樵趯?shí)際OLTP數(shù)據(jù)庫應(yīng)用中一般不會(huì)用到這么大的排序內(nèi)存。
索引的管理成本
1、 存儲(chǔ)索引的磁盤空間
2、 執(zhí)行數(shù)據(jù)修改操作（INSERT、UPDATE、DELETE）產(chǎn)生的索引維護(hù)
3、 在數(shù)據(jù)處理時(shí)回需額外的回退空間。
實(shí)際數(shù)據(jù)修改測(cè)試：
一個(gè)表有字段A、B、C，同時(shí)進(jìn)行插入10000行記錄測(cè)試
在沒有建索引時(shí)平均完成時(shí)間是2.9秒
在對(duì)A字段建索引后平均完成時(shí)間是6.7秒
在對(duì)A字段和B字段建索引后平均完成時(shí)間是10.3秒
在對(duì)A字段、B字段和C字段都建索引后平均完成時(shí)間是11.7秒
從以上測(cè)試結(jié)果可以明顯看出索引對(duì)數(shù)據(jù)修改產(chǎn)生的影響
索引按存儲(chǔ)方法分類
B*樹索引
B*樹索引是最常用的索引，其存儲(chǔ)結(jié)構(gòu)類似書的索引結(jié)構(gòu)，有分支和葉兩種類型的存儲(chǔ)數(shù)據(jù)塊，分支塊相當(dāng)于書的大目錄，葉塊相當(dāng)于索引到的具體的書頁。一般索引及約束索引都使用B*樹索引。
位圖索引
位圖索引儲(chǔ)存主要用來節(jié)省空間，減少ORACLE對(duì)數(shù)據(jù)塊的訪問，它采用位圖偏移方式來與表的行ID號(hào)對(duì)應(yīng)，采用位圖索引一般是重復(fù)值太多的表字段。位圖索引在實(shí)際密集型OLTP（數(shù)據(jù)事務(wù)處理）中用得比較少，因?yàn)镺LTP會(huì)對(duì)表進(jìn)行大量的刪除、修改、新建操作，ORACLE每次進(jìn)行操作都會(huì)對(duì)要操作的數(shù)據(jù)塊加鎖，所以多人操作很容易產(chǎn)生數(shù)據(jù)塊鎖等待甚至死鎖現(xiàn)象。在OLAP（數(shù)據(jù)分析處理）中應(yīng)用位圖有優(yōu)勢(shì)，因?yàn)镺LAP中大部分是對(duì)數(shù)據(jù)庫的查詢操作，而且一般采用數(shù)據(jù)倉庫技術(shù)，所以大量數(shù)據(jù)采用位圖索引節(jié)省空間比較明顯。
索引按功能分類
索引
索引有兩個(gè)作用，一個(gè)是數(shù)據(jù)約束，一個(gè)是數(shù)據(jù)索引，其中數(shù)據(jù)約束主要用來保證數(shù)據(jù)的完整性，索引產(chǎn)生的索引記錄中每一條記錄都對(duì)應(yīng)一個(gè)的ROWID。
主關(guān)鍵字索引
主關(guān)鍵字索引產(chǎn)生的索引同索引，只不過它是在數(shù)據(jù)庫建立主關(guān)鍵字時(shí)系統(tǒng)自動(dòng)建立的。
一般索引
一般索引不產(chǎn)生數(shù)據(jù)約束作用，其功能主要是對(duì)字段建立索引表，以提高數(shù)據(jù)查詢速度。
索引按索引對(duì)象分類
單列索引（表單個(gè)字段的索引）
多列索引（表多個(gè)字段的索引）
函數(shù)索引（對(duì)字段進(jìn)行函數(shù)運(yùn)算的索引）建立函數(shù)索引的方法：
create index 收費(fèi)日期索引 on GC_DFSS(trunc(sk_rq))
create index 完全客戶編號(hào)索引 on yhzl(qc_bh||kh_bh)
在對(duì)函數(shù)進(jìn)行了索引后，如果當(dāng)前會(huì)話要引用應(yīng)設(shè)置當(dāng)前會(huì)話的query_rewrite_enabled為TRUE。
alter session set query_rewrite_enabled=true
注：如果對(duì)用戶函數(shù)進(jìn)行索引的話，那用戶函數(shù)應(yīng)加上 deterministic參數(shù)，意思是函數(shù)在輸入值固定的情況下返回值也固定。例：
create or replace function trunc_add（input_date date）return date deterministic
as
begin
return trunc(input_date+1);
end trunc_add;
應(yīng)用索引的掃描分類
INDEX UNIQUE SCAN（按索引值掃描）
select * from zl_yhjbqk where hbs_bh=@#5420016000@#
INDEX RANGE SCAN（按索引值范圍掃描）
select * from zl_yhjbqk where hbs_bh>@#5420016000@#
select * from zl_yhjbqk where qc_bh>@#7001@#
INDEX FAST FULL SCAN（按索引值快速全部掃描）
select hbs_bh from zl_yhjbqk order by hbs_bh
select count(*) from zl_yhjbqk
select qc_bh from zl_yhjbqk group by qc_bh
什么情況下應(yīng)該建立索引
表的主關(guān)鍵字
自動(dòng)建立索引
如zl_yhjbqk（用戶基本情況）中的hbs_bh（戶標(biāo)識(shí)編號(hào)）
表的字段約束
ORACLE利用索引來保證數(shù)據(jù)的完整性
如lc_hj（流程環(huán)節(jié)）中的lc_bh+hj_sx（流程編號(hào)+環(huán)節(jié)順序）
直接條件查詢的字段
在SQL中用于條件約束的字段
如zl_yhjbqk（用戶基本情況）中的qc_bh（區(qū)冊(cè)編號(hào)）
select * from zl_yhjbqk where qc_bh=’7001’
查詢中與其它表關(guān)聯(lián)的字段
字段常常建立了外鍵關(guān)系
如zl_ydcf（用電成份）中的jldb_bh（計(jì)量點(diǎn)表編號(hào)）
select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’
查詢中排序的字段
排序的字段如果通過索引去訪問那將大大提高排序速度 select * from zl_yhjbqk order by qc_bh（建立qc_bh索引）
select * from zl_yhjbqk where qc_bh=@#7001@# order by cb_sx（建立qc_bh+cb_sx索引，注：只是一個(gè)索引，其中包括qc_bh和cb_sx字段）
查詢中統(tǒng)計(jì)或分組統(tǒng)計(jì)的字段
select max(hbs_bh) from zl_yhjbqk
select qc_bh,count(*) from zl_yhjbqk group by qc_bh
什么情況下應(yīng)不建或少建索引
表記錄太少
如果一個(gè)表只有5條記錄，采用索引去訪問記錄的話，那首先需訪問索引表，再通過索引表訪問數(shù)據(jù)表，一般索引表與數(shù)據(jù)表不在同一個(gè)數(shù)據(jù)塊，這種情況下ORACLE至少要往返讀取數(shù)據(jù)塊兩次。而不用索引的情況下ORACLE會(huì)將所有的數(shù)據(jù)一次讀出，處理速度顯然會(huì)比用索引快。
如表zl_sybm（使用部門）一般只有幾條記錄，除了主關(guān)鍵字外對(duì)任何一個(gè)字段建索引都不會(huì)產(chǎn)生性能優(yōu)化，實(shí)際上如果對(duì)這個(gè)表進(jìn)行了統(tǒng)計(jì)分析后ORACLE也不會(huì)用你建的索引，而是自動(dòng)執(zhí)行全表訪問。如：
select * from zl_sybm where sydw_bh=@#5401@#（對(duì)sydw_bh建立索引不會(huì)產(chǎn)生性能優(yōu)化）
經(jīng)常插入、刪除、修改的表
對(duì)一些經(jīng)常處理的業(yè)務(wù)表應(yīng)在查詢?cè)试S的情況下盡量減少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等業(yè)務(wù)表。
數(shù)據(jù)重復(fù)且分布平均的表字段
假如一個(gè)表有10萬行記錄，有一個(gè)字段A只有T和F兩種值，且每個(gè)值的分布概率大約為50%，那么對(duì)這種表A字段建索引一般不會(huì)提高數(shù)據(jù)庫的查詢速度。
經(jīng)常和主字段一塊查詢但主字段索引值比較多的表字段
如gc_dfss（電費(fèi)實(shí)收）表經(jīng)常按收費(fèi)序號(hào)、戶標(biāo)識(shí)編號(hào)、抄表日期、電費(fèi)發(fā)生年月、操作標(biāo)志來具體查詢某一筆收款的情況，如果將所有的字段都建在一個(gè)索引里那將會(huì)增加數(shù)據(jù)的修改、插入、刪除時(shí)間，從實(shí)際上分析一筆收款如果按收費(fèi)序號(hào)索引就已經(jīng)將記錄減少到只有幾條，如果再按后面的幾個(gè)字段索引查詢將對(duì)性能不產(chǎn)生太大的影響。
如何只通過索引返回結(jié)果
一個(gè)索引一般包括單個(gè)或多個(gè)字段，如果能不訪問表直接應(yīng)用索引就返回結(jié)果那將大大提高數(shù)據(jù)庫查詢的性能。對(duì)比以下三個(gè)SQL，其中對(duì)表zl_yhjbqk的hbs_bh和qc_bh字段建立了索引：
1 select hbs_bh,qc_bh,xh_bz from zl_yhjbqk where qc_bh=’7001’
執(zhí)行路徑:
SELECT STATEMENT, GOAL = CHOOSE 11 265 5565
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 5565
INDEX RANGE SCAN DLYX 區(qū)冊(cè)索引 1 265
平均執(zhí)行時(shí)間（0.078秒）
2 select hbs_bh,qc_bh from zl_yhjbqk where qc_bh=’7001’
執(zhí)行路徑:
SELECT STATEMENT, GOAL = CHOOSE 11 265 3710
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 3710
INDEX RANGE SCAN DLYX 區(qū)冊(cè)索引 1 265
平均執(zhí)行時(shí)間（0.078秒）
3 select qc_bh from zl_yhjbqk where qc_bh=’7001’
執(zhí)行路徑:
SELECT STATEMENT, GOAL = CHOOSE 1 265 1060
INDEX RANGE SCAN DLYX 區(qū)冊(cè)索引 1 265 1060
平均執(zhí)行時(shí)間（0.062秒）
從執(zhí)行結(jié)果可以看出第三條SQL的效率。執(zhí)行路徑可以看出第1、2條SQL都多執(zhí)行了TABLE ACCESS BY INDEX ROWID（通過ROWID訪問表） 這個(gè)步驟，因?yàn)榉祷氐慕Y(jié)果列中包括當(dāng)前使用索引(qc_bh)中未索引的列（hbs_bh,xh_bz），而第3條SQL直接通過QC_BH返回了結(jié)果，這就是通過索引直接返回結(jié)果的方法。
如何重建索引
alter index 表電量結(jié)果表主鍵 rebuild
如何快速新建大數(shù)據(jù)量表的索引
如果一個(gè)表的記錄達(dá)到100萬以上的話，要對(duì)其中一個(gè)字段建索引可能要花很長的時(shí)間，甚至導(dǎo)致服務(wù)器數(shù)據(jù)庫死機(jī)，因?yàn)樵诮ㄋ饕臅r(shí)候ORACLE要將索引字段所有的內(nèi)容取出并進(jìn)行全面排序，數(shù)據(jù)量大的話可能導(dǎo)致服務(wù)器排序內(nèi)存不足而引用磁盤交換空間進(jìn)行，這將嚴(yán)重影響服務(wù)器數(shù)據(jù)庫的工作。解決方法是增大數(shù)據(jù)庫啟動(dòng)初始化中的排序內(nèi)存參數(shù)，如果要進(jìn)行大量的索引修改可以設(shè)置10M以上的排序內(nèi)存（ORACLE缺省大小為64K），在索引建立完成后應(yīng)將參數(shù)修改回來，因?yàn)樵趯?shí)際OLTP數(shù)據(jù)庫應(yīng)用中一般不會(huì)用到這么大的排序內(nèi)存。