Oracle索引分析与比较.doc

Oracle索引分析与比较Oracle索引分析与比较收藏此页 打印作者：itpub pentium 2006-08-18 网友评论 2 条内容导航：各种索引的简要解释 第1页： 各种索引的简要解释 第2页： 各种索引的创建方法 第3页： 索引的两个知识点 第4页： 索引不起作用的几种情况 第5页： CBO模式下优化器采取全表扫描。 【IT168 技术文档】索引在各种关系型数据库系统中都是举足轻重的组成部分，其对于提高检索数据的速度起至关重要的作用。在Oracle中，索引基本分为以下几种：B*Tree索引，反向索引，降序索引，位图索引，函数索引，interMedia全文索引等。本文主要就前6种索引进行分析，由于interMedia全文索引涉及的内容可以单独写一篇文章，所以不在此对其做分析。 首先给出各种索引的简要解释： b*tree index：几乎所有的关系型数据库中都有b*tree类型索引，也是被最多使用的。其树结构与二叉树比较类似，根据rid快速定位所访问的行。 反向索引：反转了b*tree索引码中的字节，是索引条目分配更均匀，多用于并行服务器环境下，用于减少索引叶的竞争。 降序索引：8i中新出现的索引类型，针对逆向排序的查询。 位图索引：使用位图来管理与数据行的对应关系，多用于OLAP系统。 函数索引：这种索引中保存了数据列基于function返回的值，在select * from table where function(column)=value这种类型的语句中起作用。 2 各种索引的结构分析 2.1 B*Tree索引B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址。 假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如where column 20 and column select number,dump(1,16) from dual 2 union all select number,dump(2,16) from dual 3 union all select number,dump(3,16) from dual; NUMBE DUMP(1,16) - - number Typ=2 Len=2: c1,2 （1） number Typ=2 Len=2: c1,3 （2） number Typ=2 Len=2: c1,4 （3） 再对比一下反向以后的情况： SQL select number,dump(reverse(1),16) from dual 2 union all select number,dump(reverse(2),16) from dual 3 union all select number,dump(reverse(3),16) from dual; NUMBE DUMP(REVERSE(1),1 - - number Typ=2 Len=2: 2,c1 （1） number Typ=2 Len=2: 3,c1 （2） number Typ=2 Len=2: 4,c1 （3） 我们发现索引码的结构整个颠倒过来了，这样1，2，3个索引码基本上不会出现在同一个叶块里，所以减少了争用。不过反向索引又一个缺点就是不能在所有使用常规索引的地方使用。在范围搜索中其不能被使用，例如，where columnvalue,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。 2.3 降序索引 降序索引是8i里面新出现的一种索引，是B*Tree的另一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序，在某些场合下降序索引将会起作用。举个例子，我们来查询一张表并进行排序： SQL select * from test where a between 1 and 100 order by a desc，b asc; 已选择100行。 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400) 1 0 SORT(ORDER BY)(Cost=2 Card=100 Bytes=400) 2 1 INDEX (RANGE SCAN) OF IND_BT (NON-UNIQUE) (Cost=2 Card=100 Bytes=400) 这里优化器首先选择了一个索引范围扫描,然后还有一个排序的步骤。如果使用了降序索引，排序的过程会被取消。 SQL create index test.ind_desc on test.testrev(a desc，b asc); 索引已创建。 SQL analyze index test.ind_desc compute statistics; 索引已分析 再来看下执行路径： SQL select * from test where a between 1 and 100 order by a desc，b asc; 已选择100行。 Execution Plan（SQL执行计划，稍后会讲解如何使用）。 - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400) 1 0 INDEX (RANGE SCAN) OF IND_DESC (NON-UNIQUE) (Cost=2 Card=100 Bytes=400) 我们看到排序过程消失了，这是因为创建降序索引时Oracle已经把数据都按降序排好了。另外一个需要注意的地方是要设置init.ora里面的compatible参数为8.1.0或以上，否则创建时desc关键字将被忽略。1 2.4 位图索引 位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。 位图索引可以是简单的（单列）也可以是连接的（多列），但在实践中绝大多数是简单的。在这些列上多位图索引可以与AND或OR操作符结合使用。位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。 如果搜索where gender=Male,要统计性别是”Male”的列行数的话，Oracle很快就能从位图中找到共3行即第1，9，10行是符合条件的；如果要搜索where gender=Male or gender=Female的列的行数的话，也很容易从位图中找到共8行即1，2，3，4，7，8，9，10行是符合条件的。如果要搜索表的值的话，那么Oracle会用内部的转换函数将位图中的相关信息转换成rowid来访问数据块。 2.5 函数索引 基于函数的索引也是8i以来的新产物，它有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序的逻辑上提高了查询性能。使用基于函数的索引有几个先决条件： （1）必须拥有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）权限。 （2）必须使用基于成本的优化器，基于规则的优化器将被忽略。 （3）必须设置以下两个系统参数： QUERY_REWRITE_ENABLED=TRUE QUERY_REWRITE_INTEGRITY=TRUSTED 可以通过alter system set,alter session set在系统级或线程级设置，也可以通过在init.ora添加实现。这里举一个基于函数的索引的例子： SQL create index test.ind_fun on test.testindex(upper(a); 索引已创建。 SQL insert into testindex values(a,2); 已创建 1 行。 SQL commit; 提交完成。 SQL select /*/*+ RULE*/* FROM test.testindex where upper(a)=A; A B - - a 2 Execution Plan - 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX (优化器选择了全表扫描) - SQL select * FROM test.testindex where upper(a)=A; A B - - a 2 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost=2 Card= 1 Bytes=5) 2 1 INDEX (RANGE SCAN) OF IND_FUN (NON-UNIQUE) (Cost=1 Car d=1)(使用了ind_fun索引) 3 各种索引的创建方法 （1）*Tree索引。 Create index indexname on tablename(columnnamecolumnname.) （2）反向索引。 Create index indexname on tablename(columnnamecolumnname.) reverse （3）降序索引。 Create index indexname on tablename(columnname DESCcolumnname.) （4）位图索引。 Create BITMAP index indexname on tablename(columnnamecolumnname.) （5）函数索引。 Create index indexname on tablename(functionname(columnname) 注意：创建索引后分析要索引才能起作用。 analyze index indexname compute statistics; 4 各种索引使用场合及建议（1）B*Tree索引。 常规索引，多用于oltp系统，快速定位行，应建立于高cardinality列（即列的唯一值除以行数为一个很大的值，存在很少的相同值）。 （2）反向索引。 B*Tree的衍生产物，应用于特殊场合，在ops环境加序列增加的列上建立，不适合做区域扫描。 （3）降序索引。 B*Tree的衍生产物，应用于有降序排列的搜索语句中，索引中储存了降序排列的索引码，提供了快速的降序搜索。 （4）位图索引。 位图方式管理的索引，适用于OLAP（在线分析）和DSS（决策处理）系统，应建立于低cardinality列，适合集中读取，不适合插入和修改，提供比B*Tree索引更节省的空间。 （5）函数索引。 B*Tree的衍生产物，应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。 5 附表（索引什么时候不工作） 首先要声明两个知识点： （1）RBO&CBO。 Oracle有两种执行优化器，一种是RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于sql语句写法选择执行路径的；另一种是CBO（Cost Based Optimizer）基于规则的优化器，这种优化器是Oracle根据统计分析信息来选择执行路径，如果表和索引没有进行分析，Oracle将会使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能选择错误执行路径，不过CBO是Oracle发展的方向，自8i版本来已经逐渐取代RBO. （2）AUTOTRACE。 要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径，我们能看到Oracle内部是怎么执行sql的，这是一个非常好的辅助工具，在sql调优里广泛被运用。我们来看一下怎么运用AUTOTRACE： 由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE。如果没有的话，请运行utlxplan.sql脚本（它在$ORACLE_HOME/rdbms/admin目录中）。 AUTOTRACE可以通过运行plustrce.sql脚本(它在$ORACLE_HOME/sqlplus/admin目录中)来设置，用sys用户登陆然后运行plustrce.sql后会建立一个PLUSTRACE角色，然后给相关用户授予PLUSTRACE角色，然后这些用户就可以使用AUTOTRACE功能了。 AUTOTRACE的默认使用方法是set autotrace on，但是这方法不总是适合各种场合，特别当返回行数很多的时候。Set autotrace traceonly提供了只查看统计信息而不查询数据的功能。 SQL set autotrace on SQL select * from test; A - 1 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF TEST Statistics - 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) rows processed SQL set autotrace traceonly SQL select * from test.test; Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF TEST Statistics - 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) rows processed Hints是Oracle提供的一个辅助用法，按字面理解就是提示的意思，确实它起得作用也是提示优化器按它所提供的关键字来选择执行路径，特别适用于sql调整的时候。使用方法如下： DELETE|INSERT|SELECT|UPDATE /*+ hint text hinttext. */ 具体可参考Oracle SQL Reference。有了前面这些知识点，接下来让我们来看一下什么时候索引是不起作用的。以下列出几种情况。 （1）类型不匹配时。 SQL create table test.testindex (a varchar(2),b number); 表已创建。 SQL create index ind_cola on test.testindex(a); 索引已创建。 SQL insert into test.testindex values(1,1); 已创建 1 行。 SQL commit; 提交完成。 SQL analyze table test.testindex compute statistics for all indexes; 表已分析。 SQL set autotrace on; SQL select /*/*+RULE */* FROM test.testindex where a=1;(使用基于rule的优化器，数据类型匹配的情况下) A B - - 1 1 Execution Plan - 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX 2 1 INDEX (RANGE SCAN) OF IND_COLA (NON-UNIQUE)（使用了索引ind_cola） SQL select /*/*+RULE */* FROM test.testindex where a=1;（数据类型不匹配的情况） A B - - 1 1 Execution Plan - 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX（优化器选择了全表扫描） （2）条件列包含函数但没有创建函数索引。 SQL select /*/*+ RULE */* FROM test.testindex where upper(a)= A;（使用了函数upper()在列a上）; A B - - a 2 Execution Plan - 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX（优化器选择全表扫描） - 创建基于函数的索引 SQL create index test.ind_fun on test.testindex(upper(a); 索引已创建。 SQL insert into testindex values(a,2); 已创建1行。 SQL commit; 提交完成。 SQL select /*/*+ RULE*/* FROM test.testindex where upper(a)=A; A B - - a 2 Execution Plan - 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX (在RULE优化器下忽略了函数索引选择了全表扫描) - SQL select * FROM test.testindex where upper(a) =A; A B - - a 2 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost=2 Card= 1 Bytes=5) 2 1 INDEX (RANGE SCAN) OF IND_FUN (NON-UNIQUE) (Cost=1 Car d=1)(CBO优化器使用了ind_fun索引) （3）复合索引中的前导列没有被作为查询条件。 创建一个复合索引 SQL create index ind_com on test.testindex(a,b); 索引已创建。 SQL select /*/*+ RULE*/* from test.testindex where a=1; A B - - 1 2 Execution Plan - 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 INDEX (RANGE SCAN) OF IND_COM (NON-UNIQUE)(条件列表包含前导列时使用索引ind_com) SQL select /*/*+ RULE*/* from test.testindex where b=1; 未选定行 Execution Plan - 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX(条件列表不包括前导列是选择全表扫描) - （4）CBO模式下选择的行数比例过大，优化器采取了全表扫描。 SQL select * from test.testindex where a=1; A B - - 1 2 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5) 1 0 TABLE ACCESS (FULL) OF TESTINDEX (Cost=1 Card=1 Bytes=5) （表一共2行，选择比例为50%,所以优化器选择了全表扫描） 下面增加表行数 SQL declare i number; 2 begin 3 for i in 1 . 100 loop 4 insert into test.testindex values (to_char(i),i); 5 end loop; 6 end; 7 / PL/SQL 过程已成功完成。 SQL commit; 提交完成。 SQL select count(*) from test.testindex; COUNT(*) - 102 SQL select * from test.testindex where a=1; A B - - 1 1 1 2 Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5) 1 0 INDEX (RANGE SCAN) OF IND_COM (NON-UNIQUE) (Cost=1 Card=1 Bytes=5) （表一共102行，选择比例为2/102=2%,所以优化器选择了索引扫描）1 （2）条件列包含函数但没有创建函数索引。 SQL select /*/*+ RULE */* FROM test.testindex where upper(a)= A;（使用了函数upper()在列a上）; A B - - a 2 Execution Plan - 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX（优化器选择全表扫描） - 创建基于函数的索引 SQL create index test.ind_fun on test.testindex(upper(a); 索引已创建。 SQL insert into testindex values(a,2); 已创建1行。 SQL commit; 提交完成。 SQL select /*/*+ RULE*/* FROM test.testindex where upper(a)=A; A B - - a 2 Execution Plan - 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX (在RULE优化器下忽略了函数索引选择了全表扫描) - SQL select * FROM test.testindex where upper(a) =A; A B - - a 2 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost=2 Card= 1 Bytes=5) 2 1 INDEX (RANGE SCAN) OF IND_FUN (NON-UNIQUE) (Cost=1 Car d=1)(CBO优化器使用了ind_fun索引) （3）复合索引中的前导列没有被作为查询条件。 创建一个复合索引 SQL create index ind_com on test.testindex(a,b); 索引已创建。 SQL select /*/*+ RULE*/* from test.testindex where a=1; A B - - 1 2 Execution Plan - 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 INDEX (RANGE SCAN) OF IND_COM (NON-UNIQUE)(条件列表包含前导列时使用索引ind_com) SQL select /*/*+ RULE*/* from test.testindex where b=1; 未选定行 Execution Plan - 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX(条件列表不包括前导列是选择全表扫描) - （4）CBO模式下选择的行数比例过大，优化器采取了全表扫描。 SQL select * from test.testindex where a=1; A B - - 1 2 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5) 1 0 TABLE ACCESS (FULL) OF TESTINDEX (Cost=1 Card=1 Bytes=5) （表一共2行，选择比例为50%,所以优化器选择了全表扫描） 下面增加表行数 SQL declare i number; 2 begin 3 for i in 1 . 100 loop 4 insert into test.testindex values (to_char(i),i); 5 end loop; 6 end; 7 / PL/SQL 过程已成功完成。 SQL commit; 提交完成。 SQL select count(*) from test.testindex; COUNT(*) - 102 SQL select * from test.testindex where a=1; A B - - 1 1 1 2 Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5) 1 0 INDEX (RANGE SCAN) OF IND_COM (NON-UNIQUE) (Cost=1 Card=1 Bytes=5) （表一共102行，选择比例为2/102=2%,所以优化器选择了索引扫描）5）CBO模式下表很久没分析，表的增长明显，优化器采取了全表扫描。 SQL select * from test.testindex where a like 1%; A B - - 1 2 1 1 10 10 11 11 12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 100 100