Root為根節(jié)點,branch 為分支節(jié)點,leaf 到最下面一層稱為葉子節(jié)點。每個節(jié)點表示一層,當(dāng)查找某一數(shù)據(jù)時先讀根節(jié)點,再讀支節(jié)點,最后找到葉子節(jié)點。葉子節(jié)點會存放index entry (索引入口),每個索引入口對應(yīng)一條記錄。
Index entry 的組成部分:
Indexentry entry header 存放一些控制信息。
Key column length 某一key的長度
Key column value 某一個key 的值
ROWID 指針,具體指向于某一個數(shù)據(jù)
創(chuàng)建索引:
創(chuàng)建表:
SQL> create table dex (id int,sex char(1),name char(10));
Table created.
向表中插入1000條數(shù)據(jù)
SQL> begin
for i in 1..1000
loop
insert into dex values(i,'M','chongshi');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
查看表記錄
SQL> select * from dex;
ID SE NAME
---------- -- --------------------
... . .....
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
rows selected.
創(chuàng)建索引:
SQL> create index dex_idx1 on dex(id);
Index created.
注:對表的第一列(id)創(chuàng)建索引。
查看創(chuàng)建的表與索引
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
索引分離于表,作為一個單獨的個體存在,除了可以根據(jù)單個字段創(chuàng)建索引,也可以根據(jù)多列創(chuàng)建索引。Oracle要求創(chuàng)建索引最多不可超過32列。
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
DEX_INDEX2 INDEX
這里需要理解:
編寫一本書,只有章節(jié)頁面定好之后再設(shè)置目錄;數(shù)據(jù)庫索引也是一樣,只有先插入好數(shù)據(jù),再建立索引。那么我們后續(xù)對數(shù)據(jù)庫的內(nèi)容進(jìn)行插入、刪除,索引也需要隨之變化。但索引的修改是由oracle自動完成的。
上面這張圖能更加清晰的描述索引的結(jié)構(gòu)。
跟節(jié)點記錄0至50條數(shù)據(jù)的位置,分支節(jié)點進(jìn)行拆分記錄0至10.......42至50,葉子節(jié)點記錄每第數(shù)據(jù)的長度和值,并由指針指向具體的數(shù)據(jù)。
最后一層的葉子節(jié)是雙向鏈接,它們是被有序的鏈接起來,這樣才能快速鎖定一個數(shù)據(jù)范圍。
如:
ID SE NAME
---------- -- --------------------
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
rows selected.
如上面查找的列子,通過索引的方式先找到第23條數(shù)據(jù),再找到第32條數(shù)據(jù),這樣就能快速的鎖定一個查找的范圍,如果每條數(shù)據(jù)都要從根節(jié)點開始查找的話,那么效率就會非常低下。
位圖索引
位圖索引主要針對大量相同值的列而創(chuàng)建。拿全國居民登錄一第表來說,假設(shè)有四個字段:姓名、性別、年齡、和身份證號,年齡和性別兩個字段會產(chǎn)生許多相同的值,性別只有男女兩種值,年齡,1到120(假設(shè)最大年齡120歲)個值。那么不管一張表有幾億條記錄,但根據(jù)性別字段來區(qū)分的話,只有兩種取值(男、女)。那么位圖索引就是根據(jù)字段的這個特性所建立的一種索引。
Bitmap Index
從上圖,我們可以看出,一個葉子節(jié)點(用不同顏色標(biāo)識)代表一個key , start rowid 和 end rowid規(guī)定這種類型的檢索范圍,一個葉子節(jié)點標(biāo)記一個唯一的bitmap值。因為一個數(shù)值類型對應(yīng)一個節(jié)點,當(dāng)時行查詢時,位圖索引通過不同位圖取值直接的位運算(與或),來獲取到結(jié)果集合向量(計算出的結(jié)果)。
舉例講解:
假設(shè)存在數(shù)據(jù)表T,有兩個數(shù)據(jù)列A和B,取值如下,我們看到A和B列中存在相同的數(shù)據(jù)。
對兩個數(shù)據(jù)列A、B分別建立位圖索引:idx_t_bita和idx_t_bitb。兩個索引對應(yīng)的存儲邏輯結(jié)構(gòu)如下:
Idx_t_bita索引結(jié)構(gòu),對應(yīng)的是葉子節(jié)點:
Idx_t_bitb索引結(jié)構(gòu),對應(yīng)的是葉子節(jié)點:
對于上面表來說sex(性別)只有兩種值,最適合用來創(chuàng)建位圖所引
創(chuàng)建索引:
SQL> create bitmap index my_bit_idx on dex(sex);
Index created.
查看創(chuàng)建的所引
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
MY_BIT_IDX INDEX
創(chuàng)建索引的一些規(guī)則
1、權(quán)衡索引個數(shù)與DML之間關(guān)系,DML也就是插入、刪除數(shù)據(jù)操作。
這里需要權(quán)衡一個問題,建立索引的目的是為了提高查詢效率的,但建立的索引過多,會影響插入、刪除數(shù)據(jù)的速度,因為我們修改的表數(shù)據(jù),索引也要跟著修改。這里需要權(quán)衡我們的操作是查詢多還是修改多。
2、把索引與對應(yīng)的表放在不同的表空間。
當(dāng)讀取一個表時表與索引是同時進(jìn)行的。如果表與索引和在一個表空間里就會產(chǎn)生資源競爭,放在兩個表這空就可并行執(zhí)行。
3、最好使用一樣大小是塊。
Oracle默認(rèn)五塊,讀一次I/O,如果你定義6個塊或10個塊都需要讀取兩次I/O。最好是5的整數(shù)倍更能提高效率。
4、如果一個表很大,建立索引的時間很長,因為建立索引也會產(chǎn)生大量的redo信息,所以在創(chuàng)建索引時可以設(shè)置不產(chǎn)生或少產(chǎn)生redo信息。只要表數(shù)據(jù)存在,索引失敗了大不了再建,所以可以不需要產(chǎn)生redo信息。
5、建索引的時候應(yīng)該根據(jù)具體的業(yè)務(wù)SQL來創(chuàng)建,特別是where條件,還有where條件的順序,盡量將過濾大范圍的放在后面,因為SQL執(zhí)行是從后往前的。(小李飛菜刀)
索引常見操作
改變索引:
索引創(chuàng)建后,感覺不合理,也可以對其參數(shù)進(jìn)行修改。詳情查看相關(guān)文檔
調(diào)整索引的空間:
釋放空間
SQL> alter index oraers_id_idx deallocate unused;
索引在使用的過程中可能會出現(xiàn)空間不足或空間浪費的情況,這個時候需要新增或釋放空間。上面兩條命令完成新增與釋放操作。關(guān)于空間的新增oracle可以自動幫助,如果了解數(shù)據(jù)庫的情況下手動增加可以提高性能。
重新創(chuàng)建索引:
所引是由oracle自動完成,當(dāng)我們對數(shù)據(jù)庫頻繁的操作時,索引也會跟著進(jìn)行修改,當(dāng)我們在數(shù)據(jù)庫中刪除一條記錄時,對應(yīng)的索引中并沒有把相應(yīng)的索引只是做一個刪除標(biāo)記,但它依然占據(jù)著空間。除非一個塊中所有的標(biāo)記全被刪除的時,整個塊的空間才會被釋放。這樣時間久了,索引的性能就會下降。這個時候可以重新建立一個干凈的索引來提高效率。
通過上面的命令就可以重現(xiàn)建立一個索引,oracle重建立索引的過程:
1、鎖表,鎖表之后其他人就不能對表做任何操作。
2、創(chuàng)建新的(干凈的)臨時索引。
3、把老的索引刪除掉
4、把新的索引重新命名為老索引的名字
5、對表進(jìn)行解鎖。
移動所引:
其實,我們移動索引到其它表空間也同樣使用上面的命令,在指定表空間時指定不同的表空間。新的索引創(chuàng)建在別位置,把老的干掉,就相當(dāng)于移動了。
在線重新創(chuàng)建索引:
上面介紹,在創(chuàng)建索引的時候,表是被鎖定,不能被使用。對于一個大表,重新創(chuàng)建索引所需要的時間較長,為了滿足用戶對表操作的需求,就產(chǎn)生的這種在線重新創(chuàng)建索引。
1、鎖住表
2、創(chuàng)建立臨時的和空的索引和IOT表用來存在on-going DML。普通表存放的鍵值,IOT所引表直接存放的表中數(shù)據(jù);on-gong DML也就是用戶所做的一些增刪改的操作。
3、對表進(jìn)行解鎖
4、從老的索引創(chuàng)建一個新的索引。
5、IOT表里存放的是on-going DML信息,IOT表的內(nèi)容與新創(chuàng)建的索引合并。
6、鎖住表
7、再次將IOT表的內(nèi)容更新到新索引中,把老的索引干掉。
8、把新的索引重新命名為老索引的名字
9、對表進(jìn)行解鎖
整合索引碎片:
如上圖,在很多索引中有剩余的空間,可以通過一個命令把剩余空間整合到一起?! ?/P>
刪除索引:
分析索引
檢查所引的有效果,前面介紹,索引用的時間久了會產(chǎn)生大量的碎片、垃圾信息與浪費的剩余空間了??梢酝ㄟ^重新創(chuàng)建索引來提高所引的性能。
可以通過一條命令來完成分析索引,分析的結(jié)果會存放在在index_stats表中。
COUNT(*)
----------
執(zhí)行分析索引命令:
SQL> analyze index my_bit_idx validate structure;
Index analyzed.
再次查看 index_stats 已經(jīng)有了一條數(shù)據(jù)
SQL> select count(*) from index_stats;
COUNT(*)
----------
把數(shù)據(jù)查詢出來:
SQL> select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;
HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------------------------------------------------------------------- ---------- -----------
MY_BIT_IDX 1000 3 100
分析數(shù)據(jù)分析:
(HEIGHT)這個所引高度是2 ,(NAME)索引名為MY_BIT_IDX ,(LF_ROWS)所引表有1000行數(shù)據(jù),(LF_BLKS)占用3個塊,(DEL_LF_ROWS)刪除100條記錄。
這里也驗證了前面所說的一個問題,刪除的100條數(shù)據(jù)只是標(biāo)記為刪除,因為總的數(shù)據(jù)條數(shù)依然為1000條,占用3個塊,那么每個塊大于333條記錄,只有刪除的數(shù)據(jù)大于333條記錄,這時一個塊被清空,總的數(shù)據(jù)條數(shù)才會減少。
標(biāo)簽:泰安 宜春 海北 葫蘆島 酒泉 孝感 六安 淮南
巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《oracle索引介紹(圖文詳解)》,本文關(guān)鍵詞 oracle,索引,介紹,圖文,詳解,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。