ORACLE index索引介绍

1、ORACLE index索引介绍2008-06-05 22:21概述 索引在各种关系型数据库系统中都是举足轻重的组成部分，其对于提高检索数据的速度起至关重要的作用。在Oracle中，索引基本分为以下几种：B*Tree索引，反向索引，降序索引，位图索引，函数索引，interMedia全文索引等。Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进程在几分钟得以完成，这样会使您立刻成为一位英雄。这篇文章就将简单的

2、讨论每个索引选项。主要有以下内容：1 基本的索引概念查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。2 组合索引当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中对第一列（empno）指定一个值，否

3、则就不能使用这个索引键进行一次范围扫描。特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！3 ORACLE ROWID通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用，可以在任何表中使用和指定rowid列。4 限制索引限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题：4.1 使用不等于操作符（、!=）下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。select

4、 cust_Id,cust_namefrom customerswherecust_rating aa;把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。select cust_Id,cust_namefrom customerswherecust_rating aa;特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。4.2 使用IS NULL 或IS NOT NULL使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议

5、开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。4.3 使用函数如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询不会使用索引（只要它不是基于函数的索引）select empno,ename,deptnofrom empwheretrunc(hiredate)=01-MAY-81;把上面的语句改成下面的语句，这样就可以通过索引进行查找。select empno,ename,deptnofrom empw

6、herehiredate20 and column select number,dump(1,16) from dual union all select number,dump(2,16) from dual 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)

7、,16) from dual2 union all select number,dump(reverse(2),16) from dual3 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个索引码基本上不会出现在同一个叶块里，所以减少了争用。不过反向索引又一个缺点就

8、是不能在所有使用常规索引的地方使用。在范围搜索中其不能被使用，例如，where columnvalue,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。降序索引 降序索引是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

9、 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 ind

10、ex 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 Byte

11、s=400) 我们看到排序过程消失了，这是因为创建降序索引时Oracle已经把数据都按降序排好了。 另外一个需要注意的地方是要设置init.ora里面的compatible参数为8.1.0或以上，否则创建时desc关键字将被忽略。位图索引 位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。 位图索引可以是简单的（单列）也可以是连接的（多列

12、），但在实践中绝大多数是简单的。在这些列上多位图索引可以与AND或OR操作符结合使用。位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。位图索引的格式如表26-1所示。 表26-1 位图索引的格式 行值 1 2 3 4 5 6 7 8 9 10Male 1 0 0 0 0 0 0 0 1 1Female 0 1 1 1 0 0 1 1 0 0Null 0 0 0 0 1 1 0

13、 0 0 0如果搜索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来访问数据块函数索引基于函数的索引也是8i以来的新产物，它有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序的逻辑上提高了查询性能。使用基于函数的索引有几个先决

14、条件：1）必须拥有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）权限。2）必须使用基于成本的优化器，基于规则的优化器将被忽略。3）必须设置以下两个系统参数：QUERY_REWRITE_ENABLED=TRUEQUERY_REWRITE_INTEGRITY=TRUSTED可以通过alter system set,alter session set在系统级或线程级设置，也可以通过在init.ora添加实现。这里举一个基于函数的索引的例子：SQL create index test.ind_fun on test.testindex(upper(a);

15、索引已创建。SQL insert into testindex values(a,2);已创建 1 行。SQL commit;提交完成。SQL select /*+ RULE*/* FROM test.testindex where upper(a)=A;A B- -a 2Execution Plan- 0SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX(优化器选择了全表扫描)-SQL select * FROM test.testindex where upper(a)=A;A B- -a 2

16、Execution Plan- 0SELECT 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索引)各种索引的创建方法 （1）*Tree索引。 Create index indexname on tablename(columnnamecolumnna

17、me.) （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) 注

18、意：创建索引后分析要索引才能起作用。 analyze index indexname compute statistics;各种索引使用场合及建议（1）B*Tree索引。常规索引，多用于oltp系统，快速定位行，应建立于高cardinality列（即列的唯一值除以行数为一个很大的值，存在很少的相同值）。（2）反向索引。B*Tree的衍生产物，应用于特殊场合，在ops环境加序列增加的列上建立，不适合做区域扫描。（3）降序索引。B*Tree的衍生产物，应用于有降序排列的搜索语句中，索引中储存了降序排列的索引码，提供了快速的降序搜索。（4）位图索引。位图方式管理的索引，适用于OLAP（在线分析）和D

19、SS（决策处理）系统，应建立于低cardinality列，适合集中读取，不适合插入和修改，提供比B*Tree索引更节省的空间。（5）函数索引。B*Tree的衍生产物，应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。索引什么时候不工作首先要声明两个知识点：（1）RBO&CBO。Oracle有两种执行优化器，一种是RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于sql语句写法选择执行路径的；另一种是CBO（Cost Based Optimizer）基于规则的优化器，这种优化器是Oracle根据

20、统计分析信息来选择执行路径，如果表和索引没有进行分析，Oracle将会使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能选择错误执行路径，不过CBO是Oracle发展的方向，自8i版本来已经逐渐取代RBO.（2）AUTOTRACE。要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径，我们能看到Oracle内部是怎么执行sql的，这是一个非常好的辅助工具，在sql调优里广泛被运用。我们来看一下怎么运用AUTOTRACE： 由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立

21、了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 t

22、raceonly提供了只查看统计信息而不查询数据的功能。SQL set autotrace onSQL select * from test; A- 1Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF TESTStatistics- 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 recei

23、ved via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)rows processedSQL set autotrace traceonlySQL select * from test.test;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF TESTStatistics- 0 recursive calls 0 db block gets 0 consi

24、stent 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 processedHints是Oracle提供的一个辅助用法，按字面理解就是提示的意思，确实它起得作用也是提示优化器按它所提供的关键字来选择执行路径，特别适用于sql调整的时候。使用方法如下：DELETE|INSERT|SE

25、LECT|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

26、 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 1Execution Plan- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX 2 1 INDEX (RANGE SC

27、AN) OF IND_COLA (NON-UNIQUE)（使用了索引ind_cola）SQL select /*+RULE */* FROM test.testindex where a=1;（数据类型不匹配的情况）A B- -1 1Execution Plan- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX（优化器选择了全表扫描）（2）条件列包含函数但没有创建函数索引。SQL select /*+ RULE */* FROM test.testindex where upper(a)=

28、 A;（使用了函数upper()在列a上）;A B- -a 2Execution 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.

29、testindex where upper(a)=A;A B- -a 2Execution 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 2Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5) 1 0 TABLE A

30、CCESS (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 2Execution

31、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模式下选择的行数比例

32、过大，优化器采取了全表扫描。SQL select * from test.testindex where a=1;A B- -1 2Execution 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 begin3 for i in 1 . 100 loop4 insert in

33、to test.testindex values (to_char(i),i);5 end loop;6 end;7 /PL/SQL 过程已成功完成。SQL commit;提交完成。SQL select count(*) from test.testindex;COUNT(*)-102SQL select * from test.testindex where a=1;A B- -1 11 2Execution PlanSELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)1 0 INDEX (RANGE SCAN) OF IND_C

34、OM (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 21 110 1011 1112 1213 1314 1415 1516 1617 1718 1819 19100 100已选择13行。Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1

35、3 Bytes=52) 1 0 TABLE ACCESS (FULL) OF TESTINDEX (Cost=1 Card=13 Bytes=52)(表一共102行，选择比例为13/10210%,优化器选择了全表扫描)增加表行数SQL declare i number;2 begin3 for i in 200 . 1000 loop4 insert into test.testindex values (to_char(i),i);5 end loop;6 end;7 /PL/SQL 过程已成功完成。SQL commit;提交完成。SQL select count(*) from test.

36、testindex;COUNT(*)-903SQL select * from test.testindex where a like 1%;A B- -1 21 110 1011 1112 1213 1314 1415 1516 1617 1718 1819 19100 1001000 1000已选择14行。Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52) 1 0 TABLE ACCESS (FULL) OF TESTINDEX (Cost=1 Card=13 Bytes=52)(表一共

37、903行，选择比例为14/903 analyze table test.testindex compute statistics for table for all indexed columns for all indexes;表已分析。SQL select * from test.testindex where a like 1%;A B- -1 21 110 10100 1001000 100011 1112 1213 1314 1415 1516 1617 1718 1819 19已选择14行。Execution Plan- 0 SELECT STATEMENT Optimizer=C

38、HOOSE (Cost=4 Card=24 Bytes=120) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost=4 Card= 24 Bytes=120) 2 1 INDEX (RANGE SCAN) OF IND_COLA (NON-UNIQUE) (Cost=2 Ca rd=24)（经过分析后优化器选择了正确的路径，使用了ind_cola索引）analyze index 时validate structure和compute statistics的区别？分析命令 2009-09-17 10:09 阅读34评论0 字号： 大大 中中 小小