索引的說明
索引是與表相關(guān)的一個(gè)可選結(jié)構(gòu),在邏輯上和物理上都獨(dú)立于表的數(shù)據(jù),索引能優(yōu)化查詢,不能優(yōu)化DML操作,Oracle自動(dòng)維護(hù)索引,頻繁的DML操作反而會(huì)引起大量的索引維護(hù)。
如果SQL語句僅訪問被索引的列,那么數(shù)據(jù)庫只需從索引中讀取數(shù)據(jù),而不用讀取表。
如果該語句同時(shí)還要訪問除索引列之外的列,那么,數(shù)據(jù)庫會(huì)使用rowid來查找表中的行。
通常,為檢索表數(shù)據(jù),數(shù)據(jù)庫以交替方式先讀取索引塊,然后讀取相應(yīng)的表塊。
索引的目的
主要是減少IO,這是本質(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索引分為兩大類結(jié)構(gòu):
B樹索引結(jié)構(gòu)balance>
類似于字典查詢,最后到leaf block ,存的是數(shù)據(jù)rowid和數(shù)據(jù)項(xiàng)
1.葉塊之間使用雙向鏈連接,為了可以范圍查詢。
2.刪除表行時(shí),索引葉塊也會(huì)更新,但只是邏輯更改,并不做物理的刪除葉塊。
3.索引葉塊不保存表行鍵值null的信息。
位圖索引結(jié)構(gòu)bitmap>
在oracle中是根據(jù)rowid來定位記錄的,因此,我們需要引入start rowid和end rowid,通過start rowid ,end rowid 和二進(jìn)制位的偏移,我們就可以非??焖俚挠?jì)算出二進(jìn)制位所代表的表記錄rowid。位圖索引的最終邏輯結(jié)構(gòu)如下圖:
我們稱每一單元的key ,startrowid,end rowid,bitmap>為一個(gè)位圖片段。當(dāng)我們修改某一行數(shù)據(jù)的時(shí)候,我們需要鎖定該行列值所對應(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.反向索引:為避免平衡樹索引熱塊,如t_doctor表中empno開頭都是“7”,這樣構(gòu)建索引樹的時(shí)候,很可能會(huì)把所有數(shù)據(jù)分配到一個(gè)塊里,使用反向索引,避免此類問題,使索引樹分布均勻
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
索引碎片問題
由于對基表做DML操作,導(dǎo)致索引表塊的自動(dòng)更改操作,尤其是基表的delete操作會(huì)引起index表的index_entries的邏輯刪除,注意只有當(dāng)一個(gè)索引塊中的全部index_entry都被刪除了,才會(huì)把這個(gè)索引塊刪除,索引對基表的delete、insert操作都會(huì)產(chǎn)生索引碎片問題。
在Oracle文檔里并沒有清晰的給出索引碎片的量化標(biāo)準(zhǔn),Oracle建議通過Segment Advisor(段顧問)解決表和索引的碎片問題,如果你想自行解決,可以通過查看index_stats視圖,當(dāng)以下三種情形之一發(fā)生時(shí),說明積累的碎片應(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ù)庫關(guān)于索引建立及使用的詳細(xì)介紹的文章就介紹到這了,更多相關(guān)oracle數(shù)據(jù)庫索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
標(biāo)簽:梧州 南陽 銀川 河源 柳州 泰安 焦作 酒泉
巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《oracle數(shù)據(jù)庫關(guān)于索引建立及使用的詳細(xì)介紹》,本文關(guān)鍵詞 oracle,數(shù)據(jù)庫,關(guān)于,索引,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。