APP
索引是關(guān)系型數(shù)據(jù)庫(kù)的一個(gè)重要的優(yōu)化手段，可以極大地提高數(shù)據(jù)的查詢效率。Oracle作為關(guān)系型數(shù)據(jù)庫(kù)也不能免俗。
注：雖然索引專注于查詢效率，但是索引也存在一定弊端。索引會(huì)在數(shù)據(jù)表文件之外專門建立一份字段的映射文件(文件包括索引字段每行的內(nèi)容和對(duì)應(yīng)rowid)，它會(huì)隨數(shù)據(jù)的增加而增加。另外，一張表不是越多索引越好，索引越多維護(hù)起來(lái)也會(huì)很麻煩，對(duì)一些數(shù)據(jù)表結(jié)構(gòu)的操作如交換分區(qū)等也很讓人頭疼。
還有索引字段不能出現(xiàn)null值，出現(xiàn)null值，查詢時(shí)不會(huì)經(jīng)過(guò)索引的。
Oracle中的索引有如下幾種：
Btree索引、位圖索引、函數(shù)索引、反向索引、降序索引、interMedia全文索引等
索引
Btree索引
基礎(chǔ)(默認(rèn))索引，最常見的索引。類似于二叉樹結(jié)構(gòu)(非二叉樹)，通過(guò)rowid快速定位記錄。Btree索引很適合于字段內(nèi)容重復(fù)率相當(dāng)?shù)偷那樾?。?dāng)查詢目標(biāo)數(shù)據(jù)占全表總數(shù)據(jù)的一小部分時(shí)，Btree提供的效率時(shí)高于全表檢索的；但是當(dāng)查詢目標(biāo)占全表總數(shù)據(jù)的10%，Btree索引的效率達(dá)到瓶頸。
Btree的結(jié)構(gòu)中每個(gè)節(jié)點(diǎn)存放索引列值范圍和子節(jié)點(diǎn)索引鍵值位置，最終葉子結(jié)點(diǎn)才是存放索引值。所以可以知道一點(diǎn)：Btree索引的高度是一次查詢的最大查詢次數(shù)，遠(yuǎn)低于全表檢索。
對(duì)于范圍查詢，不需要分兩次從根結(jié)點(diǎn)開始查，可以先定位第一個(gè)條件范圍節(jié)點(diǎn)再在這個(gè)節(jié)點(diǎn)橫向定位第二個(gè)條件范圍。
位圖索引
使用位圖管理數(shù)據(jù)記錄的關(guān)系。與Btree索引相反，適合于字段重復(fù)率高的情形，最好內(nèi)容枚舉。比如性別，只有男女兩個(gè)選項(xiàng)。
位圖索引基于位圖，位圖是一種鍵值形式(類似二維數(shù)組)，橫向表示數(shù)據(jù)行，縱向表示有限的值選項(xiàng)，每行根據(jù)值圈定對(duì)應(yīng)值選項(xiàng)為1(true)，其他選項(xiàng)為0(false)。這種形式的位圖存放在Btree結(jié)構(gòu)的葉子結(jié)點(diǎn)，查詢相當(dāng)快速便捷。而且，位圖是以一種壓縮格式保存，還不會(huì)占用太大空間。
create bitmap index indexName on table(col);
而且面對(duì)查詢索引列值，Oracle內(nèi)部也會(huì)將位圖中的信息轉(zhuǎn)換為rowid獲取值。
另外位圖索引不應(yīng)該用于頻繁修改的字段，因?yàn)槲粓D索引不支持行級(jí)鎖定，所以當(dāng)更改某條記錄的索引字段值時(shí)，其他同等值的字段都將被鎖定，除非commit，否則其他需要更改的用戶操作就不能執(zhí)行。
函數(shù)索引
對(duì)函數(shù)建立索引，當(dāng)以該函數(shù)作為篩選條件時(shí)可以提高查詢效率。因?yàn)楹瘮?shù)具有計(jì)算的能力并且容易使用，可以不修改程序邏輯(邏輯可以編入到自定義函數(shù)中)就提高查詢效率。
但是使用函數(shù)索引需要滿足條件：
- 基于成本優(yōu)化器(cost)，否則將被忽略。
- 必須要有query rewrite 和 global query rewrite 權(quán)限
- 設(shè)置系統(tǒng)參數(shù)：QUERY_REWRITE=TRUE; QUERY_REWRITE_INTEGRITY=TRUSTED?？梢栽趇nit.ora文件中修改；也可以通過(guò)
alter system/session set xxx=xxx來(lái)更改。
舉例：
create index test.ind_fun on test.testindex(upper(a));
insert into testindex values('a',2);
commit;
select /*+ RULE*/* from test/testindex where upper(a) = 'A'; //臨時(shí)使用規(guī)則優(yōu)化器會(huì)發(fā)現(xiàn)查詢未使用函數(shù)索引
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'
--------------------------------------------------------------------
select * from test.testindex where upper(a) = 'A'; //使用默認(rèn)成本優(yōu)化器會(huì)發(fā)現(xiàn)函數(shù)索引生效
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=
1 Bytes=5)
2 1 INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
d=1)
--------------------------------------------------------------------
反向索引
將Btree索引中的字節(jié)反轉(zhuǎn)，可以均勻分配索引條目，適用于并行服務(wù)器，可以有效減少索引葉的競(jìng)爭(zhēng)。
想象一下，如果Btree索引列恰好有一組值遞增的記錄，則它們按范圍分大概率會(huì)分到一棵子樹下，這樣當(dāng)多個(gè)查詢或修改同時(shí)操作對(duì)應(yīng)的數(shù)據(jù)，可能會(huì)對(duì)這棵子樹進(jìn)行爭(zhēng)搶。反向索引會(huì)將索引碼反轉(zhuǎn)將數(shù)據(jù)打散均勻到不同位置，減少爭(zhēng)搶的可能性。
下面可以看到索引碼反轉(zhuǎn)后相鄰值的索引碼相差甚遠(yuǎn)
select 'number',dump(1,16) from dual
union all select 'number',dump(2,16) from dual
union all select 'number',dump(3,16) from dual;
select 'number',dump(reverse('1'),16) from dual
union all select 'number',dump(reverse('2'),16) from dual
union all select 'number',dump(reverse('3'),16) from dual;
//
number Typ=2 Len=2: 2,c1
number Typ=2 Len=2: 3,c1
number Typ=2 Len=2: 4,c1
但是反向索引不能建立在已經(jīng)建立其他索引的字段上，因?yàn)椴粫?huì)生效。這也是必然的，畢竟它是與Btree相反的。
降序索引
面向逆序查詢的索引。一般面對(duì)逆序查詢，數(shù)據(jù)查出來(lái)之后會(huì)經(jīng)過(guò)一個(gè)排序的過(guò)程，如果使用降序索引，會(huì)跳過(guò)排序。
舉例：
select * from test where a between 1 and 100 order by a desc,b asc;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1 0 SORT(ORDER BY)(Cost=2 Card=100 Bytes=400)
2 1 INDEX (RANGE SCAN) OF 'IND_BT' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
---------------------------------------------------------------
create index test.ind_desc on test.testrev(a desc,b asc);
commit;
select * from test where a between 1 and 100 order by a desc,b asc;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1 0 INDEX (RANGE SCAN) OF 'IND_DESC' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
----------------------------------------------------------------
注：安裝Oracle時(shí)要保證compatible參數(shù)為8.1.0及以上，否則創(chuàng)建索引時(shí)desc關(guān)鍵字會(huì)被忽略。
索引掃描
基于成本的優(yōu)化器(cost)會(huì)根據(jù)統(tǒng)計(jì)數(shù)值的方式推斷當(dāng)索引掃描比全表掃描更有效時(shí)就采用索引掃描。且查詢結(jié)果完全由索引得到。
Oracle中有4種索引掃描方式，主要有where的篩選條件選擇
在實(shí)際執(zhí)行語(yǔ)句前，可以通過(guò)執(zhí)行計(jì)劃(explain plan)查看該語(yǔ)句是否使用了索引掃描以及使用了哪種掃描和花費(fèi)時(shí)間等。
遠(yuǎn)程登錄工具(如plsql developer)一般會(huì)自帶執(zhí)行計(jì)劃按鈕，在命令行窗口需要自行開啟。
索引唯一掃描(index unique scan)
一般預(yù)期返回一條記錄的篩選條件會(huì)觸發(fā)唯一掃描，包括一個(gè)字段(單列索引)和多個(gè)字段(組合索引)。
唯一掃描也是比較常用的一種索引掃描。
create index index_xxx on tableName(name,class,school);
select * from tableName where name='xxx' and class='xxx' and school='xxx';
注：組合索引想使用索引掃描必須保證條件包含左邊字段。如組合索引(col1,col2,col3)，則只有條件 where col1=xxx where col1=xx and col2=xx where col1=xx and col2=xxx and col3=xx 這三種情況才能索引掃描生效。
對(duì)個(gè)單個(gè)字段，如果出現(xiàn)unique或primary key等保證唯一性的約束，也是可以使用系統(tǒng)的唯一掃描。
索引范圍掃描(index range scan)
面向組合索引，但是預(yù)期結(jié)果是多行記錄。比較典型的有通過(guò) < ,> ,<> , between and等篩選條件。
另外在所有非唯一索引上也都會(huì)使用范圍掃描。
索引全掃描(index full scan)
對(duì)應(yīng)全表掃描，實(shí)際上就是從左到右挨個(gè)掃描索引樹的每個(gè)葉子索引，出來(lái)的結(jié)果是有序的。
原理是從根結(jié)點(diǎn)先定位至索引樹最左葉子(樹遍歷不難),然后由節(jié)點(diǎn)的雙向鏈表依次向右掃描其他葉子。
但是這種掃描方式是基于成本優(yōu)化器(CBO)，因?yàn)樾枰鶕?jù)統(tǒng)計(jì)值比較決定是否使用全掃描還是全表掃描。
索引快速掃描(index fast full scan)
與全掃描類似，只是這種掃描是并行掃描索引塊，目的是大吞吐量和短時(shí)間。因此不會(huì)照顧到查詢結(jié)果是否有序。
全表掃描 vs 索引掃描
全表掃描就是一條一條訪問(wèn)每條記錄，雖然Oracle采取一次讀入多個(gè)數(shù)據(jù)塊方式優(yōu)化，但是對(duì)于大數(shù)據(jù)量來(lái)說(shuō)效率仍然低下。
索引掃描是采用基于rowid方式訪問(wèn)數(shù)據(jù)，直接接觸物理內(nèi)存地址，效率很高。Oracle實(shí)現(xiàn)了數(shù)據(jù)內(nèi)容與物理地址的聯(lián)系，而索引就是實(shí)現(xiàn)快速訪問(wèn)rowid。
對(duì)于索引掃描范圍唯一掃描和其他掃描，首先會(huì)通過(guò)唯一掃描刷掉一批，剩下再通過(guò)其他索引掃描。
PS：索引操作
創(chuàng)建索引
create index index_name on tableName(col/function/...);
空值不能被索引
一張表不必建立多個(gè)索引，否則適得必反
修改索引
alter index index_name rebuild storage(initial 1m next 512k);//重構(gòu)存儲(chǔ)
alter index index_name rebuild reverse;//(https://www.imooc.com/article/279505)
alter index index_name coalesce;//重構(gòu)合并索引無(wú)用空間
oracle中修改索引的概念是重構(gòu)索引以保證適應(yīng)索引存儲(chǔ)參數(shù)的增長(zhǎng)和數(shù)據(jù)的增加和清除無(wú)用的空間
rebuild相當(dāng)于truncate，經(jīng)歷了一個(gè)刪除重新建立的過(guò)程。
刪除索引
drop index index_name;
查看索引
Oracle中的系統(tǒng)表user_indexes和user_ind_columns存有當(dāng)前用戶下的表的索引信息
select * from user_indexes/user_ind_columns where table_name='表名大寫'
本文摘自 ：https://www.cnblogs.com/
