索引( 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 会否使用索引。
from:http://www.cndw.com/tech/data/2006051166664.asp
分享到:
相关推荐
)所以,当你拥有这三个数据库的时候,打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 在查询中不让记录...