Oracle索引

Oracle索引(index)最大的作用是用來優(yōu)化數(shù)據(jù)庫查詢的效率，提升數(shù)據(jù)庫的查詢性能。就好比書的目錄一樣，可以通過目錄來直接定位所需內(nèi)容存在的頁數(shù)，大大提高檢索效率。

Oracle數(shù)據(jù)庫中如果某列出現(xiàn)在查詢的條件中，而該列的數(shù)據(jù)是無序的，查詢時只能從第一行開始一行一行的匹配。創(chuàng)建索引就是對某些特定列中的數(shù)據(jù)進行排序或歸類，生成獨立的索引表。在某列上創(chuàng)建索引后，如果該列出現(xiàn)在查詢條件中，Oracle 會自動的引用該索引，先從索引表中查詢出符合條件記錄的 ROWID，由于 ROWID 是記錄的物理地址，因此可以根據(jù) ROWID 快速的定位到具體的記錄，當(dāng)表中的數(shù)據(jù)非常多時，引用索引帶來的查詢效率非常可觀 。

何時建立索引：

既然我們都知道建立索引有利于查詢速率的提升，那是不是所有字段都可以加上索引。這是萬萬不行的，建立索引不僅僅要浪費空間來存儲索引表，當(dāng)數(shù)據(jù)量較少時，直接查詢數(shù)據(jù)比經(jīng)過查詢索引表再定位到表數(shù)據(jù)的速度更快。索引可以提高查詢的效率，但是在數(shù)據(jù)增刪改時需要更新索引，因此索引對增刪改時會有負面影響。所以要根據(jù)實際情況， 考慮好再建立索引。

那何時建立索引，下面大概介紹幾點，其余的得在實際應(yīng)用和開發(fā)過程中，酌情考慮：

1、Oracle 數(shù)據(jù)庫會為表的主鍵和包含唯一約束的列自動創(chuàng)建索引，所以在建立唯一約束時，可以考慮該列是否必要建立。是否經(jīng)常要作為查詢條件。

2、如果某個表的數(shù)據(jù)量較大(十幾二十萬以上)，某列經(jīng)常作為where的查詢條件，并且檢索的出來的行數(shù)經(jīng)常是小于總表的5%，那該列可以考慮建立索引。

3、對于兩表連接的字段，應(yīng)該考慮建立索引。如果經(jīng)常在某表的一個字段進行Order By 則也經(jīng)過進行索引。

4、不應(yīng)該在小表上建立索引。上面也說過，小表之間查詢的數(shù)據(jù)會比建立索引的查詢速度更快，但是在某些字段，如性別：只有男、女和未知三種數(shù)據(jù)時，可以考慮位圖索引，可以增加查詢效率。

5、經(jīng)常進行DML操作，即經(jīng)常進行增刪改的操作的表，創(chuàng)建表索引時就要權(quán)衡一下，因為建索引會導(dǎo)致進行DML操作時速度變慢。所以可以根據(jù)實際情況，選擇某些字段建立索引，而不能盲目亂建。

索引的類別：

適當(dāng)?shù)氖褂盟饕梢蕴岣邤?shù)據(jù)檢索速度，那Oracle有哪些類型的索引呢？

1、b-tree索引：Oracle數(shù)據(jù)中最常見的索引，就是b-tree索引，create index創(chuàng)建的normal就是b-tree索引，沒有特殊的必須應(yīng)用在哪些數(shù)據(jù)上。

2、bitmap位圖索引：位圖索引經(jīng)常應(yīng)用于列數(shù)據(jù)只有幾個枚舉值的情況，比如上面說到過的性別字段，或者我們經(jīng)常開發(fā)中應(yīng)用的代碼字段。這個時候使用bitmap位圖索引，查詢效率將會最快。

3、函數(shù)索引：比如經(jīng)常對某個字段做查詢的時候經(jīng)常是帶函數(shù)操作的，那么此時建一個函數(shù)索引就有價值了。例如：trim(列名)或者substr(列名)等等字符串操作函數(shù)，這個時候可以建立函數(shù)索引來提升這種查詢效率。

4、hash索引：hash索引可能是訪問數(shù)據(jù)庫中數(shù)據(jù)的最快方法，但它也有自身的缺點。創(chuàng)建hash索引必須使用hash集群，相當(dāng)于定義了一個hash集群鍵，通過這個集群鍵來告訴oracle來存儲表。因此，需要在創(chuàng)建HASH集群的時候指定這個值。存儲數(shù)據(jù)時，所有相關(guān)集群鍵的行都存儲在一個數(shù)據(jù)塊當(dāng)中，所以只要定位到hash鍵，就能快速定位查詢到數(shù)據(jù)的物理位置。

5、reverse反向索引：這個索引不經(jīng)常使用到，但是在特定的情況下，是使用該索引可以達到意想不到的效果。如：某一列的值為{10000,10001,10021,10121,11000,....}，假如通過b-tree索引，大部分都密集發(fā)布在某一個葉子節(jié)點上，但是通過反向處理后的值將變成{00001,10001,12001,12101,00011,...}，很明顯的發(fā)現(xiàn)他們的值變得比較隨機，可以比較平均的分部在各個葉子節(jié)點上，而不是之前全部集中在某一個葉子節(jié)點上，這樣子就可大大提高檢索的效率。

6、分區(qū)索引和分區(qū)表的全局索引：這兩個索引是應(yīng)用在分區(qū)表上面的，前者的分區(qū)索引是對分區(qū)表內(nèi)的單個分區(qū)進行數(shù)據(jù)索引，后者是對分區(qū)表的全表進行全局索引。分區(qū)表的介紹，可以后期再做單獨詳解，這里就不累述了。

索引的創(chuàng)建

語法結(jié)構(gòu)：create[unique]|[bitmap]?index?index_name?--UNIQUE表示唯一索引、BITMAP位圖索引

on?table_name(column1,column2...|[express])--express表示函數(shù)索引

[tablespace?tab_name]?--tablespace表示索引存儲的表空間

[pctfree?n1]????--索引塊的空閑空間n1

[storage?????????--存儲塊的空間

(

initial?64K??--初始64k

next?1M

minextents?1

maxextents?unlimited

)];

語法解析：

1、UNIQUE:指定索引列上的值必須是唯一的。稱為唯一索引，BITMAP表示位圖索引。

2、index_name：指定索引名。

3、tabl_name：指定要為哪個表創(chuàng)建索引。

4、column_name：指定要對哪個列創(chuàng)建索引。我們也可以對多列創(chuàng)建索引，這種索引稱為組合索引。也可以是函數(shù)表達式，這種就是函數(shù)索引。

修改索引：

1、重命名索引：alter?index?index_old?rename?to?index_new;--重新命名索引

2、合并索引、重新構(gòu)造索引：我們索引建好后，經(jīng)過很長一段時間的使用，索引表中存儲的空間會產(chǎn)生一些碎片，導(dǎo)致索引的查詢效率會有所下降，這個時候可以合并索引，原理是按照索引規(guī)則重新分類存儲一下，或者也可以選擇刪除索引重新構(gòu)造索引。alter?index?index_name?coalesce;--合并索引

alter?index?index_name?rebuild;--重新構(gòu)造

刪除索引：drop?index?index_name;

查看索引：select?t.INDEX_NAME,--索引名字

t.index_type,--索引類型

t.TABLESPACE_NAME,--表空間

t.status,--狀態(tài)

t.UNIQUENESS--是否唯一索引

from?all_indexes?T

where??t.INDEX_NAME='index_name';

案例分析：

案例1、學(xué)生信息表(stuinfo)創(chuàng)建的時候就對學(xué)號(stuid)設(shè)置了主鍵(PK_STUINFO)，當(dāng)我們學(xué)生信息表數(shù)據(jù)量大的情況下，我們明顯發(fā)現(xiàn)班號(classno)需要一個索引，不僅僅是用來關(guān)聯(lián)班級信息表(class)、而且經(jīng)常作為查詢條件，因此創(chuàng)建腳本如下：create?index?STUDENT.IDX_STUINFO_CLASSNO?on?STUDENT.STUINFO?(CLASSNO)

tablespace?USERS

pctfree?10

initrans?2

maxtrans?255

storage

(

initial?64K

next?1M

minextents?1

maxextents?unlimited

);

案例2、對于學(xué)生信息我們經(jīng)常用性別作為統(tǒng)計條件進行對學(xué)生信息進行統(tǒng)計，因此我們可以在性別(sex)建立一個位圖索引進行查詢優(yōu)化。代碼如下：create?bitmap?index?STUDENT.IDX_STUINFO_SEX?on?STUDENT.STUINFO?(SEX)

tablespace?USERS

pctfree?10

initrans?2

maxtrans?255

storage

(

initial?64K

next?1M

minextents?1

maxextents?unlimited

);

查詢一下三種索引的狀態(tài)：select?t.INDEX_NAME,

t.index_type,

t.TABLESPACE_NAME,

t.status,

t.UNIQUENESS

from?all_indexes?T

where?t.TABLE_NAME='STUINFO'

AND?T.OWNER='STUDENT'

結(jié)果如下：

總結(jié)

以上是生活随笔為你收集整理的oracle表重命名 索引,Oracle索引的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。