Oracle 中如何創(chuàng)建和管理索引

這篇文章將為大家詳細(xì)講解有關(guān)Oracle 中如何創(chuàng)建和管理索引，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

　　在 Oracle 數(shù)據(jù)庫中，存儲的每一行數(shù)據(jù)都有一個 rowID 來標(biāo)識。當(dāng) Oracle 中存儲著大量的數(shù)據(jù)時，意味著有大量的 rowID ，此時想要快速定位指定的 rowID ，就需要使用索引對象。

　　當(dāng)對 Oracle 表執(zhí)行指定條件的查詢時，常規(guī)的方法是將所有的記錄取出來，然后再把每一條記錄與查詢條件作對比，最后返回滿足條件的記錄。這樣操作不僅耗費時間并耗費資源。當(dāng)有了索引之后，只需要在索引中找到符合查詢條件的索引字段值，就可以通過保存在索引中的 rowID 快速找到表中對應(yīng)的記錄。

　　用戶可以根據(jù)情況不同創(chuàng)建多種類型的索引。按照索引的存儲方式將索引分為 B 樹索引、位圖索引、反向索引和基于函數(shù)的索引。創(chuàng)建索引時需要注意以下幾點：

　　l 索引應(yīng)該建立在 where 子句頻繁引用、排序以及分組的列上，如果選擇的列不合適將無法提升查詢速度;

　　l 限制索引的個數(shù)。索引只要提升查詢速度，但會降低 DML 操作的速度;

　　l 指定索引塊空間的使用參數(shù)?；诒斫⑺饕龝r， Oracle 會將相應(yīng)表添加到索引塊。為索引添加數(shù)據(jù)時， Oracle 會按照 pctfree 參數(shù)在索引塊上預(yù)留部分空間。如果將來在表上執(zhí)行大量的 insert 操作，那么應(yīng)該在建立索引時設(shè)置較大的 pctfree ;

　　l 將表和索引部署到相同的表空間，可以簡化表空間的管理;將表和索引部署到不同的表空間，可以提高訪問性能( Oracle 能夠并行讀取不同硬盤的數(shù)據(jù));

Oracle如何創(chuàng)建索引

　　在創(chuàng)建索引時， Oracle 首先對將要簡歷索引的字段進(jìn)行排序，然后將排序后的字段值和對應(yīng)記錄的 rowID 存儲在索引段中。查詢時，根據(jù)索引查詢指定條件的 rowID ，再根據(jù) rowID 提取數(shù)據(jù)行。

　　1) B 樹索引

　　B 樹索引時 Oracle 最常用的索引類型(也是默認(rèn)類型)，是以 B 樹結(jié)構(gòu)組織并存放索引數(shù)據(jù)。默認(rèn)情況下 B 樹索引中的數(shù)據(jù)是以升序方式排列的。 B 樹索引是由根塊、分支塊和葉塊組成。

　　例：為 emp 表的 deptno 列創(chuàng)建索引 index _ test

　　Create index index _ test on emp ( deptno )

　　Pctfree 25

　　Tablespace users ;

　　其中，子句 pctfree 指定為將來 insert 操作所預(yù)留的空閑空間，子句 tablespace 指定索引段所在的表空間

　　2) 位圖索引

　　當(dāng)需創(chuàng)建索引列包含的取值太少時，如對性別列創(chuàng)建索引，取值只能是“男”或者“女”，使用 B 樹索引取出來的值任然太多，失去了索引的意義。這種情況需要使用位數(shù)索引，

　　例：

　　Create bitmap index index _ test

　　On emp ( salary )

　　Tablespace users ;

　　注：初始化參數(shù) create _ bitmap _ area _ size 用于指定建立位圖索引時分配的位圖區(qū)大小，默認(rèn)值為8 MB ，該參數(shù)越大建立位圖索引的速度越快。修改該參數(shù)語句為：

　　Alter system set create _ bitmap _ area _ size =8388608

　　Scope = spfile ;

　　修改后需要重新啟動數(shù)據(jù)庫方可生效;

　　3) 反向鍵索引

　　在單調(diào)遞增的列上使用 B 樹索引時，若用戶對表中數(shù)據(jù)做了刪除操作，將導(dǎo)致對某一邊的葉子節(jié)點的大量占用。 Oracle 提供另一種索引機制，及反向鍵索引，它可以將添加的數(shù)據(jù)隨機分散到索引中。反向鍵索引是一種特殊的 B 樹索引，在順序遞增數(shù)列上建立索引非常有用。反向鍵索引在原理和存儲結(jié)構(gòu)方面和 B 樹索引類似。當(dāng)用戶插入記錄時，將列值進(jìn)行反向操作后進(jìn)行索引，此時數(shù)據(jù)不在是遞增的，所以新數(shù)據(jù)在值的范圍上分布通常比原來的有序樹更均勻。

　　例：

　　Create index index _ test

　　On emp ( deptno ) reverse

　　Tablespace users ;

　　如果該列上已經(jīng)建立了 B 樹索引，那么可以將其修改為反向鍵索引：

　　Alter index index _ test

　　Rebulid reverse ;’

　　4) 基于函數(shù)的索引

　　用戶在使用數(shù)據(jù)庫時，最常遇到的問題是大小寫字符敏感。例如在 emp 表中 Job 字段有 MANAGER 的記錄，當(dāng)用戶使用小寫搜索時則無法找到該記錄，只能通過函數(shù) upper 對應(yīng)進(jìn)行轉(zhuǎn)換，在使用轉(zhuǎn)換后的數(shù)據(jù)進(jìn)行檢查。但是這樣查詢是，即便 job 列有普通索引， Oracle 也會執(zhí)行全表搜索，并為遇到的各個行計算 upper 函數(shù)。這種情況可以使用建立基于函數(shù)的索引，通常只是常規(guī) B 樹索引，但是它存放的數(shù)據(jù)是由表中數(shù)據(jù)應(yīng)用函數(shù)后得到的，而不是直接存放表中的數(shù)據(jù)本身。

　　如果習(xí)慣使用小寫字符串，可以創(chuàng)建如下索引：

　　Create index index _ test

　　On emp ( lower ( job ));

修改索引

　　修改索引使用 alter index 完成。

　　為表建立索引后，隨著對表不斷進(jìn)行更替、插入和刪除動作，索引中國會產(chǎn)生越來越多的存儲碎片，導(dǎo)致索引工作效率降低。這是可以采取重建索引和合并索引清除碎片。合并索引只是將 B 樹中葉子節(jié)點的存儲碎片合并在一起，并不會改變索引的物流組織結(jié)構(gòu)。

　　合并索引：

　　Alter index index _ test

　　Coalesce deallocate unused ;

　　重建索引：

　　Alter index index _ test rebuild

　　Tablespace user 1;

　　重建索引時可以更改索引的類型，存儲表空間等

刪除索引

　　刪除索引使用 drop index 語句。當(dāng)索引過于碎片化，或者不經(jīng)常被用到時，既可以刪除索引：

　　Drop index index _ test ;

　　注：刪除表是也會刪除其相應(yīng)的索引。

顯示索引信息

　　為了顯示索引的信息， Oracle 提供了一系列數(shù)據(jù)字典視圖，使用戶了解索引的各方面信息。

　　1) 顯示表的所有索引：顯示 emp 的所有索引

　　Select * from dba _ indexs where owner =’ EMP ’

　　2) 顯示索引列：顯示 index _ test 所使用的索引列：

　　Select * from user _ ind _ columns where index _ name =’ INDEX _ TEST ’

　　3) 顯示索引位置及大小

　　Select * from user _ segments where segment _ name =’ INDEX _ TEST ’

　　4) 顯示函數(shù)索引

　　Select * from user _ ind _ expressions where index _ name =’ INDEX _ TEST ’

關(guān)于Oracle 中如何創(chuàng)建和管理索引就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。