您当前的位置:首页 > 电脑百科 > 数据库 > Oracle

一文看懂Oracle数据库的三大索引类型

时间:2019-11-29 10:16:32  来源:  作者:

概述

今天主要介绍Oracle数据库的三大索引类型,仅供参考。

一文看懂Oracle数据库的三大索引类型

 

一、B-Tree索引

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

1.1利用索引特性进行优化

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

  • 统计类查询SQL:count(), avg(), sum(), max(), min()
  • 排序操作:order by字段建立索引
  • 去重操作:distinct
  • UNION/UNION ALL:union all不需要去重,不需要排序

1.2联合索引

应用场景一:SQL查询列很少,建立查询列的联合索引可以有效消除回表,但一般超过3个字段的联合索引都是不合适的.
应用场景二:在字段A返回记录多,在字段B返回记录多,在字段A,B同时查询返回记录少,比如执行下面的查询,结果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;

联合索引的列谁在前?
普遍流行的观点:重复记录少的字段放在前面,重复记录多的放在后面,其实这样的结论并不准确。

 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';

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

 --范围查询:
 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';

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

1.3索引的危害

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

  • 对delete操作,删除少量数据索引可以有效快速定位,提升删除效率,但是如果删除大量数据就会有负面影响;
  • 对update操作类似delete,而且如果更新的是非索引列则无影响。

1.4索引的监控

 --监控
 alter index [index_name] monitoring usage;
 select * from v$object_usage;
 --取消监控: 
 alter index [index_name] nomonitoring usage;

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

1.5索引的常见执行计划

INDEX FULL SCAN:索引的全扫描,单块读,有序
INDEX RANGE SCAN:索引的范围扫描
INDEX FAST FULL SCAN:索引的快速全扫描,多块读,无序
INDEX FULL SCAN(MIN/MAX):针对MAX(),MIN()函数的查询
INDEX SKIP SCAN:查询条件没有用到组合索引的第一列,而组合索引的第一列重复度较高时,可能用到

二、位图索引

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

优势: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';

三、函数索引

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

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

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

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

创建索引的语句就是

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



Tags:Oracle   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
安装环境Linux服务器:Centos 6 64位Oracle服务器:Oracle11gR2 64位 系统要求说明:内存必须高于1G的物理内存;交换空间,一般为内存的2倍(1G的内存可以设置swap 分区为3G大小);硬...【详细内容】
2021-12-27  Tags: Oracle  点击:(2)  评论:(0)  加入收藏
1、 登陆CentOS 2、 查看备份脚本,脚本放在\usr\local目录下 3、 编写数据库备份脚本,内容如下 前提是需要在Oracle创建directory文件,如果没有,可以create创建并授权给相应的用...【详细内容】
2021-12-10  Tags: Oracle  点击:(14)  评论:(0)  加入收藏
之前写了个Oracle12c在Redhat6.8下的静默安装,操作系统版本太老了而且没配插图,今天重新写个centos 7 下静默安装。 准备环境:centos 7 虚拟机一台,Oracle12c安装包。1、检查防...【详细内容】
2021-10-12  Tags: Oracle  点击:(29)  评论:(0)  加入收藏
JDK 17 正式发布+免费牛逼啊,JDK 16 刚发布半年(2021/03/16),JDK 17 又如期而至(2021/09/14),这个时间点牛逼啊,蹭苹果发布会的热度?记得当年 JDK 15 的发布也是同天,巧了。。虽然 iPho...【详细内容】
2021-09-16  Tags: Oracle  点击:(88)  评论:(0)  加入收藏
2021年免费vps服务器申请,oracle永久免费使用。Oracle 云免费套餐只需注册 Oracle 云帐户即可获取,不仅包含丰富的 Always Free 云服务,还提供 300 美元的免费试用储值,让您可以...【详细内容】
2021-09-08  Tags: Oracle  点击:(109)  评论:(0)  加入收藏
Oracle日志文件是Oracle数据库存储信息的重要文件,主要用来存储数据库变化的操作信息。Oracle日志文件可以分为两种:重做日志文件(redo log file)、归档日志文件,其中重做日志文...【详细内容】
2021-08-19  Tags: Oracle  点击:(101)  评论:(0)  加入收藏
本例子在真实环境应用过,获得了很好的体验,特将其分享出来供各位朋友使用。后续我会持续把相关的知识一点一点的更新,请各位多多关注,多多支持。测试速度:导出速度大概800M/s,如...【详细内容】
2021-08-17  Tags: Oracle  点击:(88)  评论:(0)  加入收藏
天冒险重启下Oracle rac,分三个步骤,停实例---停集群---重启服务器。 1、关闭实例检查状态su - gridcrsctl status res -t 注意:只在一个节点执行就行,或者为了保险期间,使用shut...【详细内容】
2021-07-16  Tags: Oracle  点击:(128)  评论:(0)  加入收藏
两个字符串相似度的比较:SYS.UTL_MATCH.edit_distance_similarity用法:select SYS.UTL_MATCH.edit_distance_similarity(&#39;河北沧州东塑股份有限公司&#39;,&#39;河北沧州东...【详细内容】
2021-07-07  Tags: Oracle  点击:(112)  评论:(0)  加入收藏
现在有一个用户A,想把这个用户A下面的所有的表查询权限都赋予用户X,该怎么做?同样的问题,如果有4个用户A、B、C、D,需要把这4个用户下面所有的表的只读权限赋予用户X、Y、Z三个用...【详细内容】
2021-06-23  Tags: Oracle  点击:(103)  评论:(0)  加入收藏
▌简易百科推荐
安装环境Linux服务器:Centos 6 64位Oracle服务器:Oracle11gR2 64位 系统要求说明:内存必须高于1G的物理内存;交换空间,一般为内存的2倍(1G的内存可以设置swap 分区为3G大小);硬...【详细内容】
2021-12-27  守护梦想2022    Tags:Oracle   点击:(2)  评论:(0)  加入收藏
1、 登陆CentOS 2、 查看备份脚本,脚本放在\usr\local目录下 3、 编写数据库备份脚本,内容如下 前提是需要在Oracle创建directory文件,如果没有,可以create创建并授权给相应的用...【详细内容】
2021-12-10  JasonTang    Tags:Oracle   点击:(14)  评论:(0)  加入收藏
之前写了个Oracle12c在Redhat6.8下的静默安装,操作系统版本太老了而且没配插图,今天重新写个centos 7 下静默安装。 准备环境:centos 7 虚拟机一台,Oracle12c安装包。1、检查防...【详细内容】
2021-10-12  it运维小土豆  今日头条  Tags:Oracle   点击:(29)  评论:(0)  加入收藏
JDK 17 正式发布+免费牛逼啊,JDK 16 刚发布半年(2021/03/16),JDK 17 又如期而至(2021/09/14),这个时间点牛逼啊,蹭苹果发布会的热度?记得当年 JDK 15 的发布也是同天,巧了。。虽然 iPho...【详细内容】
2021-09-16  Java技术栈  掘金  Tags:Oracle   点击:(88)  评论:(0)  加入收藏
Oracle日志文件是Oracle数据库存储信息的重要文件,主要用来存储数据库变化的操作信息。Oracle日志文件可以分为两种:重做日志文件(redo log file)、归档日志文件,其中重做日志文...【详细内容】
2021-08-19  free教程    Tags:Oracle日志   点击:(101)  评论:(0)  加入收藏
本例子在真实环境应用过,获得了很好的体验,特将其分享出来供各位朋友使用。后续我会持续把相关的知识一点一点的更新,请各位多多关注,多多支持。测试速度:导出速度大概800M/s,如...【详细内容】
2021-08-17  大树唛包    Tags:oracle   点击:(88)  评论:(0)  加入收藏
天冒险重启下Oracle rac,分三个步骤,停实例---停集群---重启服务器。 1、关闭实例检查状态su - gridcrsctl status res -t 注意:只在一个节点执行就行,或者为了保险期间,使用shut...【详细内容】
2021-07-16  死磕IT    Tags:Oracle rac   点击:(128)  评论:(0)  加入收藏
两个字符串相似度的比较:SYS.UTL_MATCH.edit_distance_similarity用法:select SYS.UTL_MATCH.edit_distance_similarity(&#39;河北沧州东塑股份有限公司&#39;,&#39;河北沧州东...【详细内容】
2021-07-07  Onceagain75783189    Tags:比较函数   点击:(112)  评论:(0)  加入收藏
现在有一个用户A,想把这个用户A下面的所有的表查询权限都赋予用户X,该怎么做?同样的问题,如果有4个用户A、B、C、D,需要把这4个用户下面所有的表的只读权限赋予用户X、Y、Z三个用...【详细内容】
2021-06-23  程序猿集锦    Tags:Oracle   点击:(103)  评论:(0)  加入收藏
oarcle数据库真正存放数据的是数据文件(data files),Oarcle表空间(tablespaces)实际上是一个逻辑的概念,他在物理上是并不存在的,那么把一组data files 捻在一起就成为一个表空间。...【详细内容】
2021-04-27  SQL小帅哥  今日头条  Tags:Oracle   点击:(200)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条