oracle數(shù)據(jù)庫(kù)關(guān)于索引建立及使用的詳細(xì)介紹

索引的說(shuō)明

索引是與表相關(guān)的一個(gè)可選結(jié)構(gòu)，在邏輯上和物理上都獨(dú)立于表的數(shù)據(jù)，索引能優(yōu)化查詢，不能優(yōu)化DML操作，Oracle自動(dòng)維護(hù)索引，頻繁的DML操作反而會(huì)引起大量的索引維護(hù)。

如果SQL語(yǔ)句僅訪問(wèn)被索引的列，那么數(shù)據(jù)庫(kù)只需從索引中讀取數(shù)據(jù)，而不用讀取表。

如果該語(yǔ)句同時(shí)還要訪問(wèn)除索引列之外的列，那么，數(shù)據(jù)庫(kù)會(huì)使用rowid來(lái)查找表中的行。

通常，為檢索表數(shù)據(jù)，數(shù)據(jù)庫(kù)以交替方式先讀取索引塊，然后讀取相應(yīng)的表塊。

索引的目的

主要是減少I(mǎi)O,這是本質(zhì),這樣才能體現(xiàn)索引的效率。

1大表，返回的行數(shù)5%

2經(jīng)常使用where子句查詢的列

3離散度高的列

4更新鍵值代價(jià)低

5邏輯AND、OR效率高

6查看索引在建在那表、列：

select * from user_indexes;

select * from user_ind_columns;

索引結(jié)構(gòu)

oracle索引分為兩大類(lèi)結(jié)構(gòu)：

B樹(shù)索引結(jié)構(gòu)balance>

類(lèi)似于字典查詢，最后到leaf block ，存的是數(shù)據(jù)rowid和數(shù)據(jù)項(xiàng)

1.葉塊之間使用雙向鏈連接，為了可以范圍查詢。
2.刪除表行時(shí)，索引葉塊也會(huì)更新，但只是邏輯更改，并不做物理的刪除葉塊。
3.索引葉塊不保存表行鍵值null的信息。

位圖索引結(jié)構(gòu)bitmap>

在oracle中是根據(jù)rowid來(lái)定位記錄的，因此，我們需要引入start rowid和end rowid，通過(guò)start rowid ，end rowid 和二進(jìn)制位的偏移，我們就可以非常快速的計(jì)算出二進(jìn)制位所代表的表記錄rowid。位圖索引的最終邏輯結(jié)構(gòu)如下圖：

我們稱(chēng)每一單元的key ,startrowid,end rowid,bitmap>為一個(gè)位圖片段。當(dāng)我們修改某一行數(shù)據(jù)的時(shí)候，我們需要鎖定該行列值所對(duì)應(yīng)的位圖片段，如果我們進(jìn)行的是更新操作，同時(shí)還會(huì)鎖定更新后新值所在的位圖片段。例如我們將列值從01修改為03，就需要同時(shí)鎖定01和03位圖片段，此時(shí)如果有其他用戶需要修改與01或者03關(guān)聯(lián)的表記錄上的索引字段，就會(huì)被阻塞，因此位圖索引不適合并發(fā)環(huán)境，在并發(fā)環(huán)境下可能會(huì)造成大量事務(wù)的阻塞。
我們一般在表數(shù)據(jù)離散度比較低的時(shí)候使用位圖索引，如t_doctor的empno值重復(fù)分布的比較多，則離散度比較低：create bitmap index job_bitmap on t_doctor(empno)

建立索引的方式

1.唯一索引：鍵值不重復(fù)

create unique index doctor_index on t_doctor(empno)

drop indexdoctor_index

2.一般索引：鍵值可重復(fù)

create index doctor_index on t_doctor(empno)

drop indexdoctor_index

3.復(fù)合索引：綁定了多個(gè)列

create index doctor_index ont_doctor(empno，job)

drop indexdoctor_index

4.反向索引：為避免平衡樹(shù)索引熱塊，如t_doctor表中empno開(kāi)頭都是“7”，這樣構(gòu)建索引樹(shù)的時(shí)候，很可能會(huì)把所有數(shù)據(jù)分配到一個(gè)塊里，使用反向索引，避免此類(lèi)問(wèn)題，使索引樹(shù)分布均勻

create index doctor_index ont_doctor(empno) reverse

drop indexdoctor_index

5.函數(shù)索引：查詢時(shí)必須用到這個(gè)函數(shù)，才會(huì)使用到

create index func_index ont_doctor(lower(empno))

--select * from t_doctor where lower(empno) = 'lina'

drop indexfunc_index

6.壓縮索引：不常用

create index doctor_index ont_doctor(empno) compress

drop indexdoctor_index

7.升序降序索引：

create index doctor_index ont_doctor(empno desc, job asc)

drop indexdoctor_index

索引碎片問(wèn)題

由于對(duì)基表做DML操作，導(dǎo)致索引表塊的自動(dòng)更改操作，尤其是基表的delete操作會(huì)引起index表的index_entries的邏輯刪除，注意只有當(dāng)一個(gè)索引塊中的全部index_entry都被刪除了，才會(huì)把這個(gè)索引塊刪除，索引對(duì)基表的delete、insert操作都會(huì)產(chǎn)生索引碎片問(wèn)題。

在Oracle文檔里并沒(méi)有清晰的給出索引碎片的量化標(biāo)準(zhǔn),Oracle建議通過(guò)Segment Advisor(段顧問(wèn)）解決表和索引的碎片問(wèn)題，如果你想自行解決，可以通過(guò)查看index_stats視圖，當(dāng)以下三種情形之一發(fā)生時(shí)，說(shuō)明積累的碎片應(yīng)該整理了（僅供參考）。

查看執(zhí)行計(jì)劃：set autotrace traceonly explain;

分析索引列：

analyze index ind_1 validate structure;

select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;

HEIGHT >=4 


 PCT_USED 50% 


DEL_LF_ROWS/LF_ROWS>0.2

alter index ind_1 rebuild [online] [tablespace name];

到此這篇關(guān)于oracle數(shù)據(jù)庫(kù)關(guān)于索引建立及使用的詳細(xì)介紹的文章就介紹到這了,更多相關(guān)oracle數(shù)據(jù)庫(kù)索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！