Oracle索引創(chuàng)建及管理

Oracle 索引創(chuàng)建及管理

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長期合作伙伴，公司提供的服務(wù)項(xiàng)目有：申請域名、虛擬空間、營銷軟件、網(wǎng)站建設(shè)、東安網(wǎng)站維護(hù)、網(wǎng)站推廣。

1. Oracle 索引簡介

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

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

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

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

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

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

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

2. 創(chuàng)建索引

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

1） B 樹索引

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

例：

Create bitmap index index _ test

On emp ( salary )

Tablespace users ;

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

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

Scope = spfile ；

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

3）反向鍵索引

在單調(diào)遞增的列上使用 B 樹索引時(shí)，若用戶對表中數(shù)據(jù)做了刪除操作，將導(dǎo)致對某一邊的葉子節(jié)點(diǎn)的大量占用。 Oracle 提供另一種索引機(jī)制，及反向鍵索引，它可以將添加的數(shù)據(jù)隨機(jī)分散到索引中。反向鍵索引是一種特殊的 B 樹索引，在順序遞增數(shù)列上建立索引非常有用。反向鍵索引在原理和存儲結(jié)構(gòu)方面和 B 樹索引類似。當(dāng)用戶插入記錄時(shí)，將列值進(jìn)行反向操作后進(jìn)行索引，此時(shí)數(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ù)庫時(shí)，最常遇到的問題是大小寫字符敏感。例如在 emp 表中 Job 字段有 MANAGER 的記錄，當(dāng)用戶使用小寫搜索時(shí)則無法找到該記錄，只能通過函數(shù) upper 對應(yīng)進(jìn)行轉(zhuǎn)換，在使用轉(zhuǎn)換后的數(shù)據(jù)進(jìn)行檢查。但是這樣查詢是，即便 job 列有普通索引， Oracle 也會執(zhí)行全表搜索，并為遇到的各個(gè)行計(jì)算 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 ));

3. 修改索引

修改索引使用 alter index 完成。

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

合并索引：

Alter index index _ test

Coalesce deallocate unused ;

重建索引：

Alter index index _ test rebuild

Tablespace user 1；

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

4. 刪除索引

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

Drop index index _ test ；

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

5. 顯示索引信息

為了顯示索引的信息， 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 ’

文章題目：Oracle索引創(chuàng)建及管理
文章鏈接：http://www.muchs.cn/article8/ijcpip.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、動(dòng)態(tài)網(wǎng)站、網(wǎng)站內(nèi)鏈、微信公眾號、網(wǎng)站改版、網(wǎng)站收錄

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容