Oracle数据库中有哪些索引类型

2025-03-26 技术教程

今天就跟大家聊聊有关Oracle数据库中有哪些索引类型，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

一、B-Tree索引

三大特点：高度较低、存储列值、结构有序

1. 利用索引特性进行优化

外键上建立索引：不但可以提升查询效率，而且可以有效避免锁的竞争(外键所在表delete记录未提交，主键所在表会被锁住)。

统计类查询SQL：count(), avg(), sum(), max(), min()

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

去重操作：distinct

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

2. 联合索引

应用场景一：SQL查询列很少，建立查询列的联合索引可以有效消除回表，但一般超过3个字段的联合索引都是不合适的.

应用场景二：在字段A返回记录多，在字段B返回记录多，在字段A,B同时查询返回记录少，比如执行下面的查询，结果c1,c2都很多，c3却很少。

selectcount(1)c1fromtwhereA=1;selectcount(1)c2fromtwhereB=2;selectcount(1)c3fromtwhereA=1andB=2;

联合索引的列谁在前?

普遍流行的观点：重复记录少的字段放在前面，重复记录多的放在后面，其实这样的结论并不准确。

droptabletpurge;createtabletasselect*fromdba_objects;createindexidx1_object_idont(object_id,object_type);createindexidx2_object_idont(object_type,object_id);

等值查询：

select*fromtwhereobject_id=20andobject_type='TABLE';select/*+index(t,idx1_object_id)*/*fromtwhereobject_id=20andobject_type='TABLE';select/*+index(t,idx2_object_id)*/*fromtwhereobject_id=20andobject_type='TABLE';

结论：等值查询情况下，组合索引的列无论哪一列在前，性能都一样。

范围查询：

select*fromtwhereobject_id>=20andobject_id<2000andobject_type='TABLE';select/*+index(t,idx1_object_id)*/*fromtwhereobject_id>=20andobject_id<2000andobject_type='TABLE';select/*+index(t,idx2_object_id)*/*fromtwhereobject_id>=20andobject_id<2000andobject_type='TABLE';

结论：组合索引的列，等值查询列在前，范围查询列在后。但如果在实际生产环境要确定组合索引列谁在前，要综合考虑所有常用SQL使用索引情况，因为索引过多会影响入库性能。

3. 索引的危害

表上有过多索引主要会严重影响插入性能;

对delete操作，删除少量数据索引可以有效快速定位，提升删除效率，但是如果删除大量数据就会有负面影响;

对update操作类似delete，而且如果更新的是非索引列则无影响。

4. 索引的监控

--监控alterindex[index_name]monitoringusage;select*fromv$object_usage;--取消监控：alterindex[index_name]nomonitoringusage;

根据对索引监控的结果，对长时间未使用的索引可以考虑将其删除。

5. 索引的常见执行计划

INDEX FULL SCAN：索引的全扫描，单块读，有序

INDEX RANGE SCAN：索引的范围扫描

INDEX FAST FULL SCAN：索引的快速全扫描，多块读，无序

INDEX FULL SCAN(MIN/MAX)：针对MAX(),MIN()函数的查询

INDEX SKIP SCAN：查询条件没有用到组合索引的第一列，而组合索引的第一列重复度较高时，可能用到

二、位图索引

应用场景：表的更新操作极少，重复度很高的列。

优势：count(*) 效率高

createtablet(name_id,gendernotnull,locationnotnull,age_rangenotnull,data)asselectrownum,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,'*')datafromdualconnectbyrownum<=100000;

createindexidx_tont(gender,location,age_range);createbitmapindexgender_idxont(gender);createbitmapindexlocation_idxont(location);createbitmapindexage_range_idxont(age_range);

select*fromtwheregender='M'andlocationin(1,10,30)andage_range='child';select/*+index(t,idx_t)*/*fromtwheregender='M'andlocationin(1,10,30)andage_range='child';

三、函数索引

应用场景：不得不对某一列进行函数运算的场景。

利用函数索引的效率要低于利用普通索引的。

oracle中创建函数索引即是你用到了什么函数就建什么函数索引，比如substr

select*fromtablewhere11=1andsubstr(field,0,2)in('01')

创建索引的语句就是

createindexindexnameontable(substr(fileld,0,2))onlinenologging;

看完上述内容，你们对Oracle数据库中有哪些索引类型有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。