Oracle數(shù)據(jù)庫中有哪些索引類型

今天就跟大家聊聊有關(guān)Oracle數(shù)據(jù)庫中有哪些索引類型，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

一、B-Tree索引

三大特點：高度較低、存儲列值、結(jié)構(gòu)有序

1. 利用索引特性進行優(yōu)化

• 外鍵上建立索引：不但可以提升查詢效率，而且可以有效避免鎖的競爭(外鍵所在表delete記錄未提交，主鍵所在表會被鎖住)。

• 統(tǒng)計類查詢SQL：count(), avg(), sum(), max(), min()

• 排序操作：order by字段建立索引

• 去重操作：distinct

• UNION/UNION ALL：union all不需要去重，不需要排序

2. 聯(lián)合索引

應(yīng)用場景一：SQL查詢列很少，建立查詢列的聯(lián)合索引可以有效消除回表，但一般超過3個字段的聯(lián)合索引都是不合適的.

應(yīng)用場景二：在字段A返回記錄多，在字段B返回記錄多，在字段A,B同時查詢返回記錄少，比如執(zhí)行下面的查詢，結(jié)果c1,c2都很多，c3卻很少。

select count(1) c1 from t where A = 1; select count(1) c2 from t where B = 2; select count(1) c3 from t where A = 1 and B = 2;

聯(lián)合索引的列誰在前?

普遍流行的觀點：重復(fù)記錄少的字段放在前面，重復(fù)記錄多的放在后面，其實這樣的結(jié)論并不準(zhǔn)確。

drop table t purge; create table t as select * from dba_objects; create index idx1_object_id on t(object_id,object_type); create index idx2_object_id on t(object_type,object_id);

等值查詢：

select * from t where object_id = 20 and object_type = 'TABLE'; select /*+ index(t,idx1_object_id) */ * from t where object_id = 20 and object_type = 'TABLE'; select /*+ index(t,idx2_object_id) */ * from t where object_id = 20 and object_type = 'TABLE';

結(jié)論：等值查詢情況下，組合索引的列無論哪一列在前，性能都一樣。

范圍查詢：

select * from t where object_id >=20 and object_id < 2000 and object_type = 'TABLE'; select /*+ index(t,idx1_object_id) */ * from t where object_id >=20 and object_id < 2000 and object_type = 'TABLE'; select /*+ index(t,idx2_object_id) */ * from t where object_id >=20 and object_id < 2000 and object_type = 'TABLE';

結(jié)論：組合索引的列，等值查詢列在前，范圍查詢列在后。  但如果在實際生產(chǎn)環(huán)境要確定組合索引列誰在前，要綜合考慮所有常用SQL使用索引情況，因為索引過多會影響入庫性能。

3. 索引的危害

表上有過多索引主要會嚴(yán)重影響插入性能;

• 對delete操作，刪除少量數(shù)據(jù)索引可以有效快速定位，提升刪除效率，但是如果刪除大量數(shù)據(jù)就會有負面影響;

• 對update操作類似delete，而且如果更新的是非索引列則無影響。

4. 索引的監(jiān)控

--監(jiān)控 alter index [index_name] monitoring usage; select * from v$object_usage; --取消監(jiān)控：  alter index [index_name] nomonitoring usage;

根據(jù)對索引監(jiān)控的結(jié)果，對長時間未使用的索引可以考慮將其刪除。

5. 索引的常見執(zhí)行計劃

• INDEX FULL SCAN：索引的全掃描，單塊讀，有序

• INDEX RANGE SCAN：索引的范圍掃描

• INDEX FAST FULL SCAN：索引的快速全掃描，多塊讀，無序

• INDEX FULL SCAN(MIN/MAX)：針對MAX(),MIN()函數(shù)的查詢

• INDEX SKIP SCAN：查詢條件沒有用到組合索引的第一列，而組合索引的第一列重復(fù)度較高時，可能用到

二、位圖索引

應(yīng)用場景：表的更新操作極少，重復(fù)度很高的列。

優(yōu)勢：count(*) 效率高

create table t( name_id, gender not null, location not null, age_range not null, data )as select  rownum, decode(floor(dbms_random.value(0,2)),0,'M',1,'F') gender, ceil(dbms_random.value(0,50)) location, decode(floor(dbms_random.value(0,4)),0,'child',1,'young',2,'middle',3,'old') age_range, rpad('*',20,'*') data from dual connect by rownum <= 100000;

create index idx_t on t(gender,location,age_range); create bitmap index gender_idx on t(gender); create bitmap index location_idx on t(location); create bitmap index age_range_idx on t(age_range);

select * from t where gender = 'M' and location in (1,10,30) and age_range = 'child'; select /*+ index(t,idx_t) */* from t where gender = 'M' and location in (1,10,30) and age_range = 'child';

三、函數(shù)索引

應(yīng)用場景：不得不對某一列進行函數(shù)運算的場景。

利用函數(shù)索引的效率要低于利用普通索引的。

oracle中創(chuàng)建函數(shù)索引即是 你用到了什么函數(shù)就建什么函數(shù)索引，比如substr

select * from table where 11=1 and substr(field,0,2) in ('01')

創(chuàng)建索引的語句就是

create index indexname on table(substr(fileld,0,2)) online nologging ;

看完上述內(nèi)容，你們對Oracle數(shù)據(jù)庫中有哪些索引類型有進一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。