一、簡(jiǎn)介

說(shuō)明：

1. 索引是數(shù)據(jù)庫(kù)對(duì)象之一，用于加快數(shù)據(jù)的檢索，類(lèi)似于書(shū)籍的索引。在數(shù)據(jù)庫(kù)中索引可以減少數(shù)據(jù)庫(kù)程序查詢(xún)結(jié)果時(shí)需要讀取的數(shù)據(jù)量，類(lèi)似于在書(shū)籍中我們利用索引可以不用翻閱整本書(shū)即可找到想要的信息。
2. 索引是建立在表上的可選對(duì)象；索引的關(guān)鍵在于通過(guò)一組排序后的索引鍵來(lái)取代默認(rèn)的全表掃描檢索方式，從而提高檢索效率
3. 索引在邏輯上和物理上都與相關(guān)的表和數(shù)據(jù)無(wú)關(guān)，當(dāng)創(chuàng)建或者刪除一個(gè)索引時(shí)，不會(huì)影響基本的表；
4. 索引一旦建立，在表上進(jìn)行DML操作時(shí)（例如在執(zhí)行插入、修改或者刪除相關(guān)操作時(shí)），oracle會(huì)自動(dòng)管理索引，索引刪除，不會(huì)對(duì)表產(chǎn)生影響
5. 索引對(duì)用戶(hù)是透明的，無(wú)論表上是否有索引，sql語(yǔ)句的用法不變
6. oracle創(chuàng)建主鍵時(shí)會(huì)自動(dòng)在該列上創(chuàng)建索引

二、索引原理

• 若沒(méi)有索引，搜索某個(gè)記錄時(shí)（例如查找name='wish'）需要搜索所有的記錄，因?yàn)椴荒鼙ＷC只有一個(gè)wish，必須全部搜索一遍
• 若在name上建立索引，oracle會(huì)對(duì)全表進(jìn)行一次搜索，將每條記錄的name值哪找升序排列，然后構(gòu)建索引條目（name和rowid），存儲(chǔ)到索引段中，查詢(xún)name為wish時(shí)即可直接查找對(duì)應(yīng)地方
• 創(chuàng)建了索引并不一定就會(huì)使用，oracle自動(dòng)統(tǒng)計(jì)表的信息后，決定是否使用索引，表中數(shù)據(jù)很少時(shí)使用全表掃描速度已經(jīng)很快，沒(méi)有必要使用索引

三、索引使用（創(chuàng)建、修改、刪除、查看）

1.創(chuàng)建索引語(yǔ)法

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，創(chuàng)建位圖索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]     --指定索引在數(shù)據(jù)塊中空閑空間
[STORAGE (INITIAL n2)]
[NOLOGGING]      --表示創(chuàng)建和重建索引時(shí)允許對(duì)表做DML操作，默認(rèn)情況下不應(yīng)該使用
[NOLINE]
[NOSORT];        --表示創(chuàng)建索引時(shí)不進(jìn)行排序，默認(rèn)不適用，如果數(shù)據(jù)已經(jīng)是按照該索引順序排列的可以使用

2.修改索引

1）重命名索引

alter index index_sno rename to bitmap_index;

2) 合并索引

（表使用一段時(shí)間后在索引中會(huì)產(chǎn)生碎片，此時(shí)索引效率會(huì)降低，可以選擇重建索引或者合并索引,合并索引方式更好些，無(wú)需額外存儲(chǔ)空間，代價(jià)較低）

alter index index_sno coalesce;

3.重建索引

方式一：刪除原來(lái)的索引，重新建立索引

方式二：

alter index index_sno rebuild;

3.刪除索引

drop index index_sno;

4.查看索引

select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';
 -- eg:    
create index index_sno on student('name');
select * from all_indexes where table_name='student';

四、索引分類(lèi)

1. B樹(shù)索引

（默認(rèn)索引，保存講過(guò)排序過(guò)的索引列和對(duì)應(yīng)的rowid值）

1）說(shuō)明：

1. oracle中最常用的索引；B樹(shù)索引就是一顆二叉樹(shù)；葉子節(jié)點(diǎn)（雙向鏈表）包含索引列和指向表中每個(gè)匹配行的ROWID值
2. 所有葉子節(jié)點(diǎn)具有相同的深度，因而不管查詢(xún)條件怎樣，查詢(xún)速度基本相同
3. 能夠適應(yīng)精確查詢(xún)、模糊查詢(xún)和比較查詢(xún)

2）分類(lèi)：

UNIQUE,NON-UNIQUE(默認(rèn)),REVERSE KEY（數(shù)據(jù)列中的數(shù)據(jù)是反向存儲(chǔ)的）

3）創(chuàng)建例子

craete index index_sno on student('sno');
 

4）適合使用場(chǎng)景：

列基數(shù)（列不重復(fù)值的個(gè)數(shù)）大時(shí)適合使用B數(shù)索引

2. 位圖索引

1）說(shuō)明：

1.創(chuàng)建位圖索引時(shí)，oracle會(huì)掃描整張表，并為索引列的每個(gè)取值建立一個(gè)位圖（位圖中，對(duì)表中每一行使用一位（bit，0或者1）來(lái)標(biāo)識(shí)該行是否包含該位圖的索引列的取值，如果為1，表示對(duì)應(yīng)的rowid所在的記錄包含該位圖索引列值），最后通過(guò)位圖索引中的映射函數(shù)完成位到行的ROWID的轉(zhuǎn)換

2)創(chuàng)建例子

create bitmap index index_sno on student(sno);

3) 適合場(chǎng)景：

對(duì)于基數(shù)小的列適合簡(jiǎn)歷位圖索引（例如性別等）

3.單列索引和復(fù)合索引（基于多個(gè)列創(chuàng)建）

1) 注意：

即如果索引建立在多個(gè)列上，只有它的第一個(gè)列被where子句引用時(shí)，優(yōu)化器才會(huì)使用該索引，即至少要包含組合索引的第一列

4. 函數(shù)索引

1)說(shuō)明：

1. 當(dāng)經(jīng)常要訪問(wèn)一些函數(shù)或者表達(dá)式時(shí)，可以將其存儲(chǔ)在索引中，這樣下次訪問(wèn)時(shí)，該值已經(jīng)計(jì)算出來(lái)了，可以加快查詢(xún)速度

2. 函數(shù)索引既可以使用B數(shù)索引，也可以使用位圖索引；當(dāng)函數(shù)結(jié)果不確定時(shí)采用B樹(shù)索引，結(jié)果是固定的某幾個(gè)值時(shí)使用位圖索引

3. 函數(shù)索引中可以水泥用len、trim、substr、upper（每行返回獨(dú)立結(jié)果），不能使用如sum、max、min、avg等

2）例子：

create index fbi  on student (upper(name));
select * from student where upper(name) ='WISH';

五、索引建立原則總結(jié)

1. 如果有兩個(gè)或者以上的索引，其中有一個(gè)唯一性索引，而其他是非唯一，這種情況下oracle將使用唯一性索引而完全忽略非唯一性索引
2. 至少要包含組合索引的第一列（即如果索引建立在多個(gè)列上，只有它的第一個(gè)列被where子句引用時(shí)，優(yōu)化器才會(huì)使用該索引）
3. 小表不要簡(jiǎn)歷索引
4. 對(duì)于基數(shù)大的列適合建立B樹(shù)索引，對(duì)于基數(shù)小的列適合簡(jiǎn)歷位圖索引
5. 列中有很多空值，但經(jīng)常查詢(xún)?cè)摿猩戏强沼涗洉r(shí)應(yīng)該建立索引
6. 經(jīng)常進(jìn)行連接查詢(xún)的列應(yīng)該創(chuàng)建索引
7. 使用create index時(shí)要將最常查詢(xún)的列放在最前面
8. LONG（可變長(zhǎng)字符串?dāng)?shù)據(jù)，最長(zhǎng)2G）和LONG RAW（可變長(zhǎng)二進(jìn)制數(shù)據(jù)，最長(zhǎng)2G）列不能創(chuàng)建索引
9. 限制表中索引的數(shù)量（創(chuàng)建索引耗費(fèi)時(shí)間，并且隨數(shù)據(jù)量的增大而增大；索引會(huì)占用物理空間；當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，降低了數(shù)據(jù)的維護(hù)速度）

注意事項(xiàng)：

1. 通配符在搜索詞首出現(xiàn)時(shí)，oracle不能使用索引，eg：

--我們?cè)趎ame上創(chuàng)建索引；
create index index_name on student('name');
--下面的方式oracle不適用name索引
select * from student where name like '%wish%';
--如果通配符出現(xiàn)在字符串的其他位置時(shí)，優(yōu)化器能夠利用索引；如下：
select * from student where name like 'wish%';

2. 不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not會(huì)停止使用索引，而采用全表掃描）

select * from student where not (score=100);
select * from student where score <> 100;
--替換為
select * from student where score>100 or score <100

3. 索引上使用空值比較將停止使用索引， eg：

select * from student where score is not null;

到此這篇關(guān)于oracle索引總結(jié)的文章就介紹到這了,更多相關(guān)oracle索引內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。