索引 ( index ) 是常见的数据库对象,它的设置好坏、使用是否得当,极大地影响数据库应用程序和 database 的性能。虽然有许多资料讲索引的用法, dba 和 developer 们也经常与它打交道,但笔者发现,还是有不少的人对它存在误解,因此针对使用中的常见问题,讲三个问题。此文所有示例所用的数据库是 oracle 8.1.7 ops on hp n series , 示例全部是真实数据,读者不需要注意具体的数据大小,而应注意在使用不同的方法后,数据的比较。本文所讲基本都是陈词滥调,但是笔者试图通过实际的例子, 来真正让您明白事情的关键。
第一讲、索引并非总是最佳选择
如果发现 oracle 在有索引的情况下,没有使用索引,这并不是 oracle 的优化器出错。在有些情况下, oracle 确实会选择全表扫描( full table scan ) , 而非索引扫描( index scan )。这些情况通常有:
1. 表未做 statistics, 或者 statistics 陈旧,导致 oracle 判断失误。
2. 根据该表拥有的记录数和数据块数,实际上全表扫描要比索引扫描更快。
对第 1 种情况,最常见的例子,是以下这句 sql 语句:
select count(*) from mytable;
在 未作 statistics 之前,它使用全表扫描,需要读取 6000 多个数据块(一个数据块是 8k ) , 做了 statistics 之后,使用的是 index (fast full scan) ,只需要读取 450 个数据块。但是, statistics 做得不好,也会导致 oracle 不使用索引。
第 2 种情况就要复杂得多。一般概念上都认为索引比表快,比较难以理解什么情况下全表扫描要比索引扫描快。为了讲清楚这个问题,这里先介绍一下 oracle 在评估使用索引的代价( cost )时两个重要的数据: cf(clustering factor) 和 ff(filtering factor).
cf: 所谓 cf, 通俗地讲,就是每读入一个索引块,要对应读入多少个数据块。
ff: 所谓 ff, 就是该 sql 语句所选择的结果集,占总的数据量的百分比。
大约的计算公式是: ff * (cf + 索引块个数 ) ,由此估计出,一个查询, 如果使用某个索引,会需要读入的数据块块数。需要读入的数据块越多,则 cost 越大, oracle 也就越可能不选择使用 index. (全表扫描需要读入的数据块数等于该表的实际数据块数)
其核心就是, cf 可能会比实际的数据块数量大。 cf 受到索引中数据的排列方式影响,通常在索引刚建立时,索引中的记录与表中的记录有良好的对应关系, cf 都很小;在表经过大量的插入、修改后,这种对应关系越来越乱, cf 也越来越大。此时需要 dba 重新建立或者组织该索引。
如果某个 sql 语句以前一直使用某索引,较长时间后不再使用,一种可能就是 cf 已经变得太大,需要重新整理该索引了。
ff 则是 oracle 根据 statistics 所做的估计。比如 , mytables 表有 32 万行,其主键 myid 的最小值是 1 ,最大值是 409654 ,考虑以下 sql 语句:
select * from mytables where myid>=1; 和
select * from mytables where myid>=400000
这两句看似差不多的 sql 语句,对 oracle 而言,却有巨大的差别。因为前者的 ff 是 100% , 而后者的 ff 可能只有 1% 。如果它的 cf 大于实际的数据块数,则 oracle 可能会选择完全不同的优化方式。而实际上,在我们的数据库上的测试验证了我们的预测 . 以下是在 hp 上执行时它们的 explain plan:
第一句:
sql> select * from mytables where myid>=1;
已选择 325917 行。
execution plan
----------------------------------------------------------
0 select statement optimizer=choose (cost=3132 card=318474 byt es=141402456)
1 0 table access (full) of 'mytables' (cost=3132 card=318474 byt es=141402456)
statistics
----------------------------------------------------------
7 recursive calls
89 db block gets
41473 consistent gets
19828 physical reads
0 redo size
131489563 bytes sent via sql*net to client
1760245 bytes received via sql*net from client
21729 sql*net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
325917 rows processed
第二句:
execution plan
----------------------------------------------------------
0 select statement optimizer=choose (cost=346 card=663 bytes=2 94372)
1 0 table access (by index rowid) of 'mytables' (cost=346 card=663
bytes=294372)
2 1 index (range scan) of 'pk_mytables' (unique) (cost=5 card=663)
statistics
----------------------------------------------------------
1278 recursive calls
0 db block gets
6647 consistent gets
292 physical reads
0 redo size
3544898 bytes sent via sql*net to client
42640 bytes received via sql*net from client
524 sql*net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
7838 rows processed
显而易见,第 1 句没有使用索引,第 2 句使用了主键索引 pk_mytables. ff 的巨大影响由此可见一斑。由此想到,我们在写 sql 语句时,如果预先估计一下 ff, 你就几乎可以预见到 oracle 会否使用索引。
第二讲、索引也有好坏
索 引有 b tree 索引, bitmap 索引, reverse b tree 索引, 等。最常用的是 b tree 索引。 b 的全称是 balanced , 其意义是,从 tree 的 root 到任何一个 leaf ,要经过同样多的 level. 索引可以只有一个字段( single column ) , 也可以有多个字段( composite ) , 最多 32 个字段, 8i 还支持 function-based index. 许多 developer 都倾向于使用单列 b 树索引。
除此之外呢?我们还是来看一个例子吧:
在 hp ( oracle 8.1.7 ) 上执行以下语句:
select count(1) from mytabs where coid>=130000 and issuedate >= to_date ('2001-07-20', 'yyyy-mm-dd') 。
一开始,我们有两个单列索引: i_mytabs1(coid), i_mytabs2(issuedate), 下面是执行情况:
count(1)
----------
6427
execution plan
----------------------------------------------------------
0 select statement optimizer=choose (cost=384 card=1 bytes=11)
1 0 sort (aggregate)
2 1 table access (by index rowid) of 't_mytabs' (cost=384 card
=126 bytes=1386)
3 2 index (range scan) of 'i_mytabs2' (non-unique) (cost=11
card=126)
statistics
----------------------------------------------------------
172 recursive calls
1 db block gets
5054 consistent gets
2206 physical reads
0 redo size
293 bytes sent via sql*net to client
359 bytes received via sql*net from client
2 sql*net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到,它读取了 7000 个数据块来获得所查询的 6000 多行。
现在,去掉这两个单列索引,增加一个复合索引 i_mytabs_test ( coid, issuedate), 重新执行,结果如下:
count(1)
----------
6436
execution plan
----------------------------------------------------------
0 select statement optimizer=choose (cost=3 card=1 bytes=11)
1 0 sort (aggregate)
2 1 index (range scan) of 'i_mytabs_test' (non-unique) (cost=3 card=126 bytes=1386)
statistics
----------------------------------------------------------
806 recursive calls
5 db block gets
283 consistent gets
76 physical reads
0 redo size
293 bytes sent via sql*net to client
359 bytes received via sql*net from client
2 sql*net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到,这次只读取了 300 个数据块。
7000 块对 300 块,这就是在这个例子中,单列索引与复合索引的代价之比。这个例子提示我们, 在许多情况下,单列索引不如复合索引有效率。
可以说,在索引的设置问题上,其实有许多工作可以做。正确地设置索引,需要对应用进行总体的分析。
第三讲、索引再好,不用也是白搭
抛开前面所说的,假设你设置了一个非常好的索引,任何傻瓜都知道应该使用它,但是 oracle 却偏偏不用,那么,需要做的第一件事情,是审视你的 sql 语句。
oracle 要使用一个索引,有一些最基本的条件:
1 , where 子句中的这个字段,必须是复合索引的第一个字段;
2 , where 子句中的这个字段,不应该参与任何形式的计算
具体来讲,假设一个索引是按 f1, f2, f3 的次序建立的,现在有一个 sql 语句 , where 子句是 f2 = : var2, 则因为 f2 不是索引的第 1 个字段,无法使用该索引。
第 2 个问题,则在我们之中非常严重。以下是从 实际系统上面抓到的几个例子:
select jobid from mytabs where isreq='0' and to_date (updatedate) >= to_date ( '2001-7-18', 'yyyy-mm-dd') ;
………
以上的例子能很容易地进行改进。请注意这样的语句每天都在我们的系统中运行,消耗我们有限的 cpu 和 内存资源。
除了 1 , 2 这两个我们必须牢记于心的原则外,还应尽量熟悉各种操作符对 oracle 是否使用索引的影响。这里我只讲哪些操作或者操作符会显式( explicitly )地阻止 oracle 使用索引。以下是一些基本规则:
1 , 如果 f1 和 f2 是同一个表的两个字段,则 f1>f2, f1>=f2, f1
2 , f1 is null, f1 is not null, f1 not in, f1 !=, f1 like ‘ %pattern% ' ;
3 , not exist
4 , 某些情况下, f1 in 也会不用索引;
对于这些操作,别无办法,只有尽量避免。比如,如果发现你的 sql 中的 in 操作没有使用索引,也许可以将 in 操作改成 比较操作 + union all 。笔者在实践中发现很多时候这很有效。
但 是, oracle 是否真正使用索引,使用索引是否真正有效,还是必须进行实地的测验。合理的做法是,对所写的复杂的 sql, 在将它写入应用程序之前,先在产品数据库上做一次 explain . explain 会获得 oracle 对该 sql 的解析( plan ) , 可以明确地看到 oracle 是如何优化该 sql 的。
如果经常做 explain, 就会发现,喜爱写复杂的 sql 并不是个好习惯,因为过分复杂的 sql 其解析计划往往不尽如人意。事实上,将复杂的 sql 拆开,有时候会极大地提高效率,因为能获得很好的优化。当然这已经是题外话了。
,欢迎访问网页设计爱好者web开发。
- 浏览: 8583 次
- 性别:
- 来自: 北京
相关推荐
)所以,当你拥有这三个数据库的时候,打patch的步骤是先development database再test database最后才在production database环境应用。 构架应用体系 如果你研究过Oracle Forms,使用过Application Server和...
5. Oracle Index 的三个问题 6. Oracle PL-SQL语言基础 7. Oracle的分布式管理 8. ORACLE的数据类型 9. Oracle数据库碎片整理 10.ORACLE性能调整1 11.ORACLE性能调整2 12.Oracle专家调优秘密 13.PL...
发现,还是有不少的人对它存在误解,因此针对使用中的常见问题,讲三个问题。此文所有示例所用的数 据库是Oracle8.1.7OPSonHPNseries,示例全部是真实数据,读者不需要注意具体的数据大小,而 应注意在使用不同的方法...
快速重建索引的一种有效的办法,因为使用现有索引项来重建新索引,如果客户操作时有其他用户在对这个表操作,尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题,alter index index_name ...
数据库高手给出的从九个不同方面介绍ORACLE数据库优化设计方案 一、数据库优化自由结构OFA(Optimal flexible Architecture) 二、充分利用系统全局区域SGA(SYSTEM GLOBAL AREA) 三、规范与反规范设计数据库 四、...
4、startup,等于以下三个命令 startup nomount alter database mount alter database open 5、startup restrict 约束方式启动 这种方式能够启动数据库,但只允许具有一定特权的用户访问 非特权用户访问时,会出现...
CREATE SESSON 题目12、数据库实例启动的三个阶段依次是( B)。 选择一项: a. MOUNT,OPEN,NOMOUNT b. NOMOUNT,MOUNT,OPEN c. NOMOUNT,OPEN,MOUNT d. OPEN,NOMOUNT,MOUNT 题目13、在重启数据库时除了必须...
§7.2 关于创建多个Oracle实例问题 93 §7.3 Oracle系统安装后的优化基础工作 94 §7.3.1 Oracle系统有关目录所有文件的保护 94 §7.3.2 避免新用户使用默认system系统表空间 94 §7.4 Oracle系统所在服务器的独立性...
其三、职业方向多:Oracle数据库管理方向、Oracle开发及系统架构方向、Oracle数据建模数据仓库等方向。 四、 如何学习 认真听课、多思考问题、多动手操作、有问题一定要问、多参与讨论、多帮组同学 五、 体系结构 ...
这个笔记是学习oracle数据库过程中整理出来的,比较详细,适合于初学者。 │ oracle与tomcat端口冲突.txt │ Oracle学习笔记.pdf │ Oracle学习笔记.wps │ 安装Oracle后myEclipse不能正常使用.txt │ 手工配置...
17.7 Oracle的锁机制 36 17.8事务不提交的后果 36 17.9回滚事务rollback 36 17.10保留点savepoint 36 十八、 数据库对象:视图view 37 18.1带子查询的create table 37 18.2带子查询的insert 37 18.3定义缺省值:...
第三个黄色子块:远程数据库实例名 3、添加一个环境变量,名为TNS_ADMIN,值为tnsnames.ora文件所在路径。比如我的本机为:D:/instantclient_11_2/network/admin 4、设置ORACLE的语言,添加环境变量NLS_LANG...
5. Oracle Index 的三个问题 6. Oracle PL-SQL语言基础 7. Oracle的分布式管理 8. ORACLE的数据类型 9. Oracle数据库碎片整理 10.ORACLE性能调整1 11.ORACLE性能调整2 12.Oracle专家调优秘密 13.PL_SQL单行...
,我们可以把此文件拷贝到该目录下,然后在dos下运行该工具,定位到我们的shp文件的位置,例如我们的shp文件名称是state.shp(shapefile包括至少三个文件state.shp state.dbf state.idx)在D:\data\目录下,我们在...
INSTR的第三个参数为1时,实现的是indexOf功能。 INSTR的第三个参数为-1时,实现的是lastIndexOf功能。 例如: INSTR(‘wenjin_文进_李_浩dsf’, ‘_’, -1, 1) 返回结果为:12 INSTR(‘wenjin_文进_李_浩dsf’, ‘_...
共享的语句必须满足三个条件: A. 字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同. 例如: SELECT * FROM EMP; 和下列每一个都不同 SELECT * from EMP; Select * From Emp; SELECT * ...
§10.1.1 Oracle与2000年问题 212 §10.1.2 如何正确插入日期数据 213 §10.1.3 在查询中只返回满足条件的部分记录 214 §10.1.4 快速大量删除数据Truncate 215 §10.1.5 Rowid的使用 215 §10.1.6 在查询中不让记录...