索引在Oracle中的应用深入分析
发布时间:2023-07-04 19:50:49
索引是提高数据查询最有效的方法,也是最难全面掌握的技术,因为正确的索引可能使效率提高10000倍,而无效的索引可能是浪费了数据库空间,甚至大大降低查询性能。
一.索引的管理成本
1、 存储索引的磁盘空间
2、 执行数据修改操作(INSERT、UPDATE、DELETE)产生的索引维护
3、 在数据处理时所需额外的回退空间。
二.实际数据修改测试:
一个表有字段A、B、C,同时进行插入10000行记录测试
在没有建索引时平均完成时间是2.9秒
在对A字段建索引后平均完成时间是6.7秒
在对A字段和B字段建索引后平均完成时间是10.3秒
在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒
从以上测试结果可以明显看出索引对数据修改产生的影响
三.索引按存储方法分类
1.B*树索引
B*树索引是最常用的索引,其存储结构类似书的索引结构,有分支和叶两种类型的存储数据块,分支块相当于书的大目录,叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B*树索引。
2.位图索引
位图索引储存主要用来节省空间,减少ORACLE对数据块的访问,它采用位图偏移方式来与表的行ID号对应,采用位图索引一般是重复值太多的表字段。位图索引在实际密集型OLTP(数据事务处理)中用得比较少,因为OLTP会对表进行大量的删除、修改、新建操作,ORACLE每次进行操作都会对要操作的数据块加锁,所以多人操作很容易产生数据块锁等待甚至死锁现象。在OLAP(数据分析处理)中应用位图有优势,因为OLAP中大部分是对数据库的查询操作,而且一般采用数据仓库技术,所以大量数据采用位图索引节省空间比较明显。
四.索引按功能分类
1.唯一索引
唯一索引有两个作用,一个是数据约束,一个是数据索引,其中数据约束主要用来保证数据的完整性,唯一索引产生的索引记录中每一条记录都对应一个唯一的ROWID。
2.主关键字索引
主关键字索引产生的索引同唯一索引,只不过它是在数据库建立主关键字时系统自动建立的。
3.一般索引
一般索引不产生数据约束作用,其功能主要是对字段建立索引表,以提高数据查询速度。
五.索引按索引对象分类
1.单列索引(表单个字段的索引)
2.多列索引(表多个字段的索引)
3.函数索引(对字段进行函数运算的索引)
建立函数索引的方法:
create index 收费日期索引 on GC_DFSS(trunc(sk_rq))
create index 完全客户编号索引 on yhzl(qc_bh||kh_bh)
在对函数进行了索引后,如果当前会话要引用应设置当前会话的query_rewrite_enabled为TRUE。
alter session set query_rewrite_enabled=true
注:如果对用户函数进行索引的话,那用户函数应加上 deterministic参数,意思是函数在输入值固定的情况下返回值也固定。例:
create or replace function trunc_add(input_date date)return date deterministic
as
begin
return trunc(input_date+1);
end trunc_add;
六.应用索引的扫描分类
1.INDEX UNIQUE SCAN(按索引唯一值扫描)
select * from zl_yhjbqk where hbs_bh='5420016000'
2.INDEX RANGE SCAN(按索引值范围扫描)
select * from zl_yhjbqk where hbs_bh>'5420016000'
select * from zl_yhjbqk where qc_bh>'7001'
3.INDEX FAST FULL SCAN(按索引值快速全部扫描)
select hbs_bh from zl_yhjbqk order by hbs_bh
select count(*) from zl_yhjbqk
select qc_bh from zl_yhjbqk group by qc_bh
七.什么情况下应该建立索引
1.表的主关键字,自动建立唯一索引
如zl_yhjbqk(用户基本情况)中的hbs_bh(户标识编号)
2.表的字段唯一约束
ORACLE利用索引来保证数据的完整性
如lc_hj(流程环节)中的lc_bh+hj_sx(流程编号+环节顺序)
3.直接条件查询的字段
在SQL中用于条件约束的字段
如zl_yhjbqk(用户基本情况)中的qc_bh(区册编号)
select * from zl_yhjbqk where qc_bh='7001'
4.查询中与其它表关联的字段,常常建立了外键关系
如zl_ydcf(用电成份)中的jldb_bh(计量点表编号)
select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh='540100214511'
5.查询中排序的字段
排序的字段如果通过索引去访问那将大大提高排序速度
select * from zl_yhjbqk order by qc_bh(建立qc_bh索引)
select * from zl_yhjbqk where qc_bh='7001' order by cb_sx(建立qc_bh+cb_sx索引,注:只是一个索引,其中包括qc_bh和cb_sx字段)
6.查询中统计或分组统计的字段
select max(hbs_bh) from zl_yhjbqk
select qc_bh,count(*) from zl_yhjbqk group by qc_bh
八.什么情况下应不建或少建索引
1.表记录太少
如果一个表只有5条记录,采用索引去访问记录的话,那首先需访问索引表,再通过索引表访问数据表,一般索引表与数据表不在同一个数据块,这种情 况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出,处理速度显然会比用索引快。
如表zl_sybm(使用部门)一般只有几条记录,除了主关键字外对任何一个字段建索引都不会产生性能优化,实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引,而是自动执行全表访问。如:
select * from zl_sybm where sydw_bh='5401'(对sydw_bh建立索引不会产生性能优化)
2.经常插入、删除、修改的表
对一些经常处理的业务表应在查询允许的情况下尽量减少索引,如zl_yhbm,gc_dfss,gc_dfys,gc_fpdy等业务表。
3.数据重复且分布平均的表字段
假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。
4.经常和主字段一块查询但主字段索引值比较多的表字段
如gc_dfss(电费实收)表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况,如果将所有的字段 都建在一个索引里那将会增加数据的修改、插入、删除时间,从实际上分析一笔收款如果按收费序号索引就已经将记录减少到只有几条,如果再按后面的几个字段索 引查询将对性能不产生太大的影响。
九.如何只通过索引返回结果
一个索引一般包括单个或多个字段,如果能不访问表直接应用索引就返回结果那将大大提高数据库查询的性能。对比以下三个SQL,其中对表zl_yhjbqk的hbs_bh和qc_bh字段建立了索引:
1 select hbs_bh,qc_bh,xh_bz from zl_yhjbqk where qc_bh='7001'
执行路径:
SELECT STATEMENT, GOAL = CHOOSE 11 265 5565
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 5565
INDEX RANGE SCAN DLYX 区册索引 1 265
平均执行时间(0.078秒)
2 select hbs_bh,qc_bh from zl_yhjbqk where qc_bh='7001'
执行路径:
SELECT STATEMENT, GOAL = CHOOSE 11 265 3710
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 3710
INDEX RANGE SCAN DLYX 区册索引 1 265
平均执行时间(0.078秒)
3 select qc_bh from zl_yhjbqk where qc_bh='7001'
执行路径:
SELECT STATEMENT, GOAL = CHOOSE 1 265 1060
INDEX RANGE SCAN DLYX 区册索引 1 265 1060
平均执行时间(0.062秒)
从执行结果可以看出第三条SQL的效率最高。执行路径可以看出第1、2条SQL都多执行了TABLE ACCESS BY INDEX ROWID(通过ROWID访问表) 这个步骤,因为返回的结果列中包括当前使用索引(qc_bh)中未索引的列(hbs_bh,xh_bz),而第3条SQL直接通过QC_BH返回了结果, 这就是通过索引直接返回结果的方法。
十.如何快速新建大数据量表的索引
如果一个表的记录达到100万以上的话,要对其中一个字段建索引可能要花很长的时间,甚至导致服务器数据库死机,因为在建索引的时候 ORACLE要将索引字段所有的内容取出并进行全面排序,数据量大的话可能导致服务器排序内存不足而引用磁盘交换空间进行,这将严重影响服务器数据库的工 作。解决方法是增大数据库启动初始化中的排序内存参数,如果要进行大量的索引修改可以设置10M以上的排序内存(ORACLE缺省大小为64K),在索引 建立完成后应将参数修改回来,因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。
猜你喜欢
- PHP有效的时间戳典型范围是格林威治时间 1901 年 12 月 13 日 20:45:54 到 203
- 问题描述:使用 SQL 2005 w/ SP2 的汇出汇入精灵将数据从 Access 汇入到 SQL2005 发生了错误,但使用在SQL 2
- 1、DOMWEB标准现在可真是热门中热门,不过下面讨论的是一个不符合标准的document.all[]。DOM--DOCUMENTOBJEC
- 阅读上一章:Chapter 4 引用互动性一直是互联网的重点,让使用者与网站能够交换信息,彼此沟通.表单使我们能够有组织的,使用同一方式的从
- 如何准确地获得一个整数?当然可以,Format就是做这个的“专家”:function NumbersOnlyNoDecimals(
- 此文章主要向大家讲述的是SQL Server数据库的分布式数据库系统的实际目标,通俗的讲就是研制分布式数据库系统的目的、动机,其中主要包括的
- 当一个页面上有一百个表单项,你是怎么获取上面的值勤的?这是一段简单的代码,你试试这段代码,试过后,欢迎留言说一下你的想法?index.asp
- 最近对 Range 和 Selection 比较感兴趣。基本非 IE 的浏览器都支持 DOM Level2 中的 Range,而 IE 中仅
- 近日,朋友写一个关于成绩管理的系统,其中遇到一个小问题。如果按照SQLSERVER的ORDER 排序时,比如遇到两个100分,结果必然是名次
- 在印刷排版中“point”是一个绝对的单位,它等于 1/72 英寸。可以用尺子丈量的,物理的英寸。但在CSS中pt的含义
- ..:: 巧用CSS制作艺术字 ::..如果灵活应用CSS各种滤镜的特点并加以组合,我们可以得到许多意想不到的效果。这是一些效果示范,供各位
- 一、可以使用以下步骤获取两个以逗号分割的字符串的并集:使用explode函数将两个字符串转换为数组,以便可以对其执行操作。使用array_m
- 长期以来一直以为iframe跟div一样都是块级元素,直到今天在一个群中看到一位朋友问到iframe怎么居中的时候,测试了下发现原来我一直对
- 一、设计说明设计这个自动化的目的是想要交替、重复地使用固定的几个分区(分区编号01~05)来保存数据,当最后一个分区就是快满的时候,我们会把
- 1.文档对象模型(DOM)DOM是HTML和XML文档的编程基础,它定义了处理执行文档的途径。编程者可以使用DOM增加文档、定位文档结构、填
- 如今,互联网越来越走近我们的生活,网上冲浪也渐渐成为我们生活不可缺少的一部分。网络世界五彩缤纷,涌现出大量优秀精美的网页。大量网络信息的呈现
- 在用户体验这个行业,经常会听到,可用性,可访问性这样专业的名词,但是,事实上在很多产品实现过程里都忽略了这一点!WHY?举个很简单的例子,用
- 在做视觉设计时,如何高效地使用图标是一门学问:该使用什么样的图标?图标该放在哪里?大小如何?图标的使用是否帮助用户更好更快的理解内容,亦或是
- 在实用美术中,常有"远看色彩近看花,先看颜色后看花,七分颜色三分花"的说法。这也就说明,在任何设计中,色彩对视觉的刺激起
- Jabob Nielsen提出的10个可用性原则中有一个原则-防止出错(Error Prevention)。官网中有对这一原则的具体解释,这