Oracle数据库关于索引的使用详细介绍

索引是由oracle维护的可选结构，为数据提供快速的访问。准确地判断在什么地方需要使用索引是困难的，使用索引有利于调节检索速度。 当建立一个索引时，必须指定用于跟踪的表名以及一个或多个表列。一旦建立了索引，在用户表中建立、更改和删除时， oracle就自动地维护索引。创建索引时，下列准则将帮助用户做出决定：

　　1) 索引应该在sql语句的"where"或"and"部分涉及的表列(也称谓词)被建立。假如

　　personnel表的"firstname"表列作为查询结果显示，而不是作为谓词部分，则不论其值是什么，该表列不会被索引。

　　2)用户应该索引具有一定范围的表列，索引时有一个大致的原则：如果表中列的值占该表中行的2 0 %以内，这个表列就可以作为候选索引表列。假设一个表有36 000行且表中一个表列的值平均分布(大约每12000行)，那么该表列不适合于一个索引。然而，如果同一个表中的其他表列中列值的行在1 0 0 0～1 5 0 0之间(占3 %～4 % )，则该表列可用作索引。

　　3)如果在s q l语句谓词中多个表列被一起连续引用，则应该考虑将这些表列一起放在一个索引内， o r a c l e将维护单个表列的索引(建立在单一表列上)或复合索引(建立在多个表列上)。复合索引称并置索引。

　　1 主关键字的约束

　　关系数据库理论指出，在表中能唯一标识表的每个数据行的一个或多个表列是对象的主关键字。由于数据字典中定义的主关键字能确保表中数据行之间的唯一性，因此，在o r a c l e 8 i数据库中建立表索引关键字有助于应用调节。另外，这也减轻了开发者为了实现唯一性检查，而需要各自的要求。

　　提示使用主关键字索引条目比不使用主关键字索引检索得快。

　　假设表p e r s o n把它的i d表列作为主关键字，用下列代码设置约束：

alter table person add constraint person_pk primary key (id) using index storage (initial 1m next 1m

pctincrease 0) tablespace prd_indexes ;

　　处理下列s q l语句时：

select last_name ,first_name ,salary from person where id = 289 ;

　　在查找一个已确定的“ i d”表列值时， o r a c l e将直接找到p e r s o n _ p k。如果其未找到正确的索引条目，o r a c l e知道该行不存在。主关键字索引具有下列两个独特之处：

　　1.1因为索引是唯一的， 所以o r a c l e知道只有一个条目具有设定值。如果查找到了所期望的条目，则立即终止查找。

　　1.2一旦遇到一个大于设定值的条目，索引的顺序搜索可被终止;

　　2 order by中用索引

　　order by 子句只在两种严格的条件下使用索引.

　　order by中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.

　　order by中所有的列必须定义为非空.

　　where子句使用的索引和order by子句中所使用的索引不能并列.

　　例如:

　　表dept包含以下列:

dept_code pk

not null

dept_desc not null

dept_type null

　　非唯一性的索引(dept_type)

　　低效: (索引不被使用)

select dept_code

from dept

order by dept_type

explain plan:

sort order by

table access full

　　高效: (使用索引)

select dept_code

from dept

where dept_type > 0

explain plan:

table access by rowid on emp

index range scan on dept_idx

　　3 避免改变索引列的类型.

　　当比较不同数据类型的数据时, oracle自动对列进行简单的类型转换.

假设 empno是一个数值类型的索引列.

select …

from emp

where empno = ‘123'

　　实际上,经过oracle类型转换, 语句转化为:

select …

from emp

where empno = to_number(‘123')

　　幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.

　　现在,假设emp_type是一个字符类型的索引列.

select …

from emp

where emp_type = 123

　　这个语句被oracle转换为:

select …

from emp

where to_number(emp_type)=123

　　因为内部发生的类型转换, 这个索引将不会被用到! 为了避免oracle对你的sql进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, oracle会优先转换数值类型到字符类型.

　　4 需要当心的where子句

　　某些select 语句中的where子句不使用索引. 这里有一些例子.

　　在下面的例子里, ‘!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.

　　不使用索引:

select account_name

from transaction

where amount !=0;

　　使用索引:

select account_name

from transaction

where amount >0;

　　下面的例子中, ‘||'是字符连接函数. 就象其他函数那样, 停用了索引. 不使用索引:

select account_name,amount

from transaction

where account_name||account_type='amexa';

　　使用索引:

select account_name,amount

from transaction

where account_name = ‘amex'

and account_type=' a';

　　下面的例子中, ‘+'是数学函数. 就象其他数学函数那样, 停用了索引.

　　不使用索引:

select account_name, amount

from transaction

where amount + 3000 >5000;

　　使用索引:

select account_name, amount

from transaction

where amount > 2000 ;

　　下面的例子中,相同的索引列不能互相比较,这将会启用全表扫描.

　　不使用索引:

select account_name, amount

from transaction

where account_name = nvl(:acc_name,account_name);

　　使用索引:

select account_name, amount

from transaction

where account_name like nvl(:acc_name,'%');

　　如果一定要对使用函数的列启用索引, oracle新的功能: 基于函数的索引(function-based index) 也许是一个较好的

　　方案.

create index emp_i on emp (upper(ename)); /*建立基于函数的索引*/

select * from emp where upper(ename) = ‘blacksnail'; /*将使用索引*/

　　5 怎样监控无用的索引

　　oracle 9i以上，可以监控索引的使用情况，如果一段时间内没有使用的索引，一般就是无用的索引

　　语法为：

　　开始监控：alter index index_name monitoring usage;

　　检查使用状态：select * from v$object_usage;

　　停止监控：alter index index_name nomonitoring usage;

　　当然，如果想监控整个用户下的索引，可以采用如下的脚本：

set heading off

set echo off

set feedback off

set pages 10000

spool start_index_monitor.sql

select 'alter index '||owner||'.'||index_name||' monitoring usage;'

from dba_indexes

where owner = user;

spool off

set heading on

set echo on

set feedback on

------------------------------------------------

set heading off

set echo off

set feedback off

set pages 10000

spool stop_index_monitor.sql

select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'

from dba_indexes

where owner = user;

spool off

set heading on

set echo on

set feedback on

一．索引介绍 

1.1 索引的创建语法： 

create uniuqe | bitmap index <schema>.<index_name> 

      on <schema>.<table_name> 

           (<column_name> | <expression> asc | desc, 

            <column_name> | <expression> asc | desc,...) 

     tablespace <tablespace_name> 

     storage <storage_settings> 

     logging | nologging 

    compute statistics 

     nocompress | compress<nn> 

     nosort | reverse 

     partition | global partition<partition_setting> 

相关说明 

1） unique | bitmap：指定unique为唯一值索引，bitmap为位图索引，省略为b-tree索引。 

2）<column_name> | <expression> asc | desc：可以对多列进行联合索引，当为expression时即“基于函数的索引” 

3）tablespace：指定存放索引的表空间(索引和原表不在一个表空间时效率更高) 

4）storage：可进一步设置表空间的存储参数 

5）logging | nologging：是否对索引产生重做日志(对大表尽量使用nologging来减少占用空间并提高效率) 

6）compute statistics：创建新索引时收集统计信息 

7）nocompress | compress<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值) 

8）nosort | reverse：nosort表示与表中相同的顺序创建索引，reverse表示相反顺序存储索引值 

9）partition | nopartition：可以在分区表和未分区表上对创建的索引进行分区 

1.2 索引特点： 

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高的性能。 

1.3 索引不足： 

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 

1.4 应该建索引列的特点： 

1）在经常需要搜索的列上，可以加快搜索的速度； 

2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 

3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 

4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 

5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 

6）在经常使用在where子句中的列上面创建索引，加快条件的判断速度。 

1.5 不应该建索引列的特点： 

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 

第三，对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。 

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。 

1.6 限制索引 

限制索引是一些没有经验的开发人员经常犯的错误之一。在sql中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题： 

   1.6.1  使用不等于操作符（<>、!=）      

   下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。     

   select cust_id,cust_name from customers where  cust_rating <> 'aa';        

把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。        

  select cust_id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa'; 

  特别注意：通过把不等于操作符改成or条件，就可以使用索引，以避免全表扫描。 

   1.6.2 使用is null 或is not null 

   使用is null 或is not null同样会限制索引的使用。因为null值并没有被定义。在sql语句中使用null会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成 not null。如果被索引的列在某些行中存在null值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。 

   1.6.3 使用函数 

   如果不使用基于函数的索引，那么在sql语句的where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。 下面的查询不会使用索引（只要它不是基于函数的索引） 

select empno,ename,deptno from emp  where  trunc(hiredate)='01-may-81'; 

   把上面的语句改成下面的语句，这样就可以通过索引进行查找。 

select empno,ename,deptno from emp where  hiredate<(to_date('01-may-81')+0.9999); 

  1.6.4 比较不匹配的数据类型       

也是比较难于发现的性能问题之一。 注意下面查询的例子，account_number是一个varchar2类型,在account_number字段上有索引。 

下面的语句将执行全表扫描： 

select bank_name,address,city,state,zip from banks where account_number = 990354; 

  oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引： 

select bank_name,address,city,state,zip from banks where account_number ='990354'; 

特别注意：不匹配的数据类型之间比较会让oracle自动限制索引的使用,即便对这个查询执行explain plan也不能让您明白为什么做了一次“全表扫描”。 

1.7 查询索引 

查询dba_indexes视图可得到表中所有索引的列表，注意只能通过user_indexes的方法来检索模式(schema)的索引。访问user_ind_columns视图可得到一个给定表中被索引的特定列。 

1.8 组合索引 

当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在 oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、 ename和deptno。在oracle9i之前除非在where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。 

   特别注意：在oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！ 

1.9 oracle rowid 

通过每个行的rowid，索引oracle提供了访问单行数据的能力。rowid其实就是直接指向单独行的线路图。如果想检查重复值或是其他对rowid本身的引用，可以在任何表中使用和指定rowid列。 

1.10 选择性 

   使用user_indexes视图，该视图中显示了一个distinct_keys列。比较一下唯一键的数量和表中的行数，就可以判断索引的选择性。选择性越高，索引返回的数据就越少。 

1.11 群集因子(clustering factor) 

  clustering factor位于user_indexes视图中。该列反映了数据相对于已建索引的列是否显得有序。如果clustering factor列的值接近于索引中的树叶块(leaf block)的数目，表中的数据就越有序。如果它的值接近于表中的行数，则表中的数据就不是很有序。 

1.12 二元高度(binary height) 

  索引的二元高度对把rowid返回给用户进程时所要求的i/o量起到关键作用。在对一个索引进行分析后，可以通过查询dba_indexes的b- level列查看它的二元高度。二元高度主要随着表的大小以及被索引的列中值的范围的狭窄程度而变化。索引上如果有大量被删除的行，它的二元高度也会增加。更新索引列也类似于删除操作，因为它增加了已删除键的数目。重建索引可能会降低二元高度。 

1.13 快速全局扫描 

  从oracle7.3后就可以使用快速全局扫描(fast full scan)这个选项。这个选项允许oracle执行一个全局索引扫描操作。快速全局扫描读取b-树索引上所有树叶块。初始化文件中的 db_file_multiblock_read_count参数可以控制同时被读取的块的数目。 

1.14 跳跃式扫描 

  从oracle9i开始，索引跳跃式扫描特性可以允许优化器使用组合索引，即便索引的前导列没有出现在where子句中。索引跳跃式扫描比全索引扫描要快的多。 

下面的比较他们的区别： 

sql> set timing on 

sql> create index tt_index on tt(teamid,areacode); 

索引已创建。 

已用时间:  00: 02: 03.93 

sql> select count(areacode) from tt; 

count(areacode) 

--------------- 

7230369 

已用时间:  00: 00: 08.31 

sql> select /*+ index(tt tt_index )*/ count(areacode) from tt; 

count(areacode) 

--------------- 

7230369 

已用时间:  00: 00: 07.37 

1.15 索引的类型 

b-树索引    位图索引   hash索引     索引编排表  

反转键索引 基于函数的索引  分区索引   本地和全局索引 

二． 索引分类 

oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进程在几分钟得以完成，这样会使您立刻成为一位英雄。下面就将简单的讨论每个索引选项。 

下面讨论的索引类型： 

b树索引(默认类型) 

位图索引 

hash索引 

索引组织表索引 

反转键(reverse key)索引 

基于函数的索引 

分区索引(本地和全局索引) 

位图连接索引 

2.1  b树索引 (默认类型) 

b树索引在oracle中是一个通用索引。在创建索引时它就是默认的索引类型。b树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。b树索引最多可以包括32列。 

在下图的例子中，b树索引位于雇员表的last_name列上。这个索引的二元高度为3；接下来，oracle会穿过两个树枝块(branch block)，到达包含有rowid的树叶块。在每个树枝块中，树枝行包含链中下一个块的id号。 

树叶块包含了索引值、rowid，以及指向前一个和后一个树叶块的指针。oracle可以从两个方向遍历这个二叉树。b树索引保存了在索引列上有值的每个数据行的rowid值。oracle不会对索引列上包含null值的行进行索引。如果索引是多个列的组合索引，而其中列上包含null值，这一行就会处于包含null值的索引列中，且将被处理为空(视为null)。 

                         

技巧：索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了i/o量。 

b-tree 特点： 

  适合与大量的增、删、改（oltp） 

不能用包含or操作符的查询； 

适合高基数的列（唯一值多） 

典型的树状结构； 

每个结点都是数据块； 

大多都是物理上一层、两层或三层不定，逻辑上三层； 

叶子块数据是排序的，从左向右递增； 

在分支块和根块中放的是索引的范围； 

2.2  位图索引 

位图索引非常适合于决策支持系统(decision support system，dss)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列，但通常它们都只用于少量的列。 

例如，您的表可能包含一个称为sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据sex列的值查询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可用的位图索引，oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。 

bitmapt 特点： 

适合与决策支持系统； 

做update代价非常高； 

非常适合or操作符的查询； 

基数比较少的时候才能建位图索引； 

技巧：对于有较低基数的列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于b树索引来说小了很多。因为这些索引是低基数的b树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。 

当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比b树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。 

示例 

下面来看一个示例表participant，该表包含了来自个人的调查数据。列age_code、income_level、education_level和marital_status都包括了各自的位图索引。下图显示了每个直方图中的数据平衡情况，以及对访问每个位图索引的查询的执行路径。图中的执行路径显示了有多少个位图索引被合并，可以看出性能得到了显著的提高。 

                      

如上图图所示，优化器依次使用4个单独的位图索引，这些索引的列在where子句中被引用。每个位图记录指针(例如0或1)，用于指示表中的哪些行包含位图中的已知值。有了这些信息后，oracle就执行bitmap and操作以查找将从所有4个位图中返回哪些行。该值然后被转换为rowid值，并且查询继续完成剩余的处理工作。注意，所有4个列都有非常低的基数，使用索引可以非常快速地返回匹配的行。 

技巧：在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。 

下面的查询可显示索引类型。 

sql> select index_name, index_type from user_indexes; 

index_name         index_type 

------------------------------ ---------------------- 

tt_index            normal 

ix_custaddr_tp    normal 

b树索引作为normal列出；而位图索引的类型值为bitmap。 

技巧：如果要查询位图索引列表，可以在user _indexes视图中查询index_type列。 

建议不要在一些联机事务处理(oltp)应用程序中使用位图索引。b树索引的索引值中包含rowid，这样oracle就可以在行级别上锁定索引。位图索引存储为压缩的索引值，其中包含了一定范围的rowid，因此oracle必须针对一个给定值锁定所有范围内的rowid。这种锁定类型可能在某些dml语句中造成死锁。select语句不会受到这种锁定问题的影响。 

位图索引的使用限制： 

基于规则的优化器不会考虑位图索引。 

当执行alter table语句并修改包含有位图索引的列时，会使位图索引失效。 

位图索引不包含任何列数据，并且不能用于任何类型的完整性检查。 

位图索引不能被声明为唯一索引。 

位图索引的最大长度为30。 

技巧：不要在繁重的oltp环境中使用位图索引 

2.3  hash索引 

使用hash索引必须要使用hash集群。建立一个集群或hash集群的同时，也就定义了一个集群键。这个键告诉oracle如何在集群上存储表。在存储数据时，所有与这个集群键相关的行都被存储在一个数据库块上。如果数据都存储在同一个数据库块上，并且将hash索引作为where子句中的确切匹配，oracle就可以通过执行一个hash函数和i/o来访问数据——而通过使用一个二元高度为4的b树索引来访问数据，则需要在检索数据时使用4个i/o。如下图所示，其中的查询是一个等价查询，用于匹配hash列和确切的值。oracle可以快速使用该值，基于hash函数确定行的物理存储位置。 

hash索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建hash集群之前就要知道。需要在创建hash集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的hash值)。这种冲突是非常消耗资源的。冲突会造成用来存储额外行的缓冲溢出，然后造成额外的i/o。如果不同hash值的数目已经被低估，您就必须在重建这个集群之后改变这个值。 

alter cluster命令不能改变hash键的数目。hash集群还可能浪费空间。如果无法确定需要多少空间来维护某个集群键上的所有行，就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间，hash集群可能就不是最好的选择。如果应用程序经常在集群表上进行全表扫描，hash集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量，全表扫描可能非常消耗资源。 

在实现hash集群之前一定要小心。您需要全面地观察应用程序，保证在实现这个选项之前已经了解关于表和数据的大量信息。通常，hash对于一些包含有序值的静态数据非常有效。 

技巧：hash索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。 

                         

2.4  索引组织表 

索引组织表会把表的存储结构改成b树结构，以表的主键进行排序。这种特殊的表和其他类型的表一样，可以在表上执行所有的dml和ddl语句。由于表的特殊结构，rowid并没有被关联到表的行上。 

对于一些涉及精确匹配和范围搜索的语句，索引组织表提供了一种基于键的快速数据访问机制。基于主键值的update和delete语句的性能也同样得以提高，这是因为行在物理上有序。由于键列的值在表和索引中都没有重复，存储所需要的空间也随之减少。 

如果不会频繁地根据主键列查询数据，则需要在索引组织表中的其他列上创建二级索引。不会频繁根据主键查询表的应用程序不会了解到使用索引组织表的全部优点。对于总是通过对主键的精确匹配或范围扫描进行访问的表，就需要考虑使用索引组织表。 

技巧：可以在索引组织表上建立二级索引。 

2.5  反转键索引 

当载入一些有序数据时，索引肯定会碰到与i/o相关的一些瓶颈。在数据载入期间，某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题，可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。 

为了解决这个问题，oracle还提供了一种反转键索引的方法。如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反。这样，数据1234、1235和1236就被存储成4321、5321和6321。结果就是索引会为每次新插入的行更新不同的索引块。 

技巧：如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。 

不可以将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。 

2.6  基于函数的索引 

可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。例如，下面的查询就不能使用job列上的索引，除非它是基于函数的索引： 

select * from emp where upper(job) = 'mgr'; 

下面的查询使用job列上的索引，但是它将不会返回job列具有mgr或mgr值的行： 

select * from emp where job = 'mgr'; 

可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式upper(job)创建索引，而不是直接在job列上建立索引，如： 

create index emp$upper_job on emp(upper(job)); 

尽管基于函数的索引非常有用，但在建立它们之前必须先考虑下面一些问题： 

能限制在这个列上使用的函数吗？如果能，能限制所有在这个列上执行的所有函数吗 

是否有足够应付额外索引的存储空间？ 

在每列上增加的索引数量会对针对该表执行的dml语句的性能带来何种影响？ 

基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，insert、update和delete语句的执行就会花费越多的时间。 

注意：对于优化器所使用的基于函数的索引来说，必须把初始参数query _rewrite _ enabled设定为true。

示例： 

select  count(*) from  sample where ratio(balance,limit) >.5; 

elapsed time: 20.1 minutes 

create index ratio_idx1 on sample (ratio(balance, limit)); 

select  count(*) from  sample where ratio(balance,limit) >.5; 

elapsed time: 7 seconds!!! 

2.7  分区索引 

分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免i/o问题)。b树和位图索引都可以被分区，而hash索引不可以被分区。可以有好几种分区方法：表被分区而索引未被分区；表未被分区而索引被分区；表和索引都被分区。不管采用哪种方法，都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性 

有两种类型的分区索引：本地分区索引和全局分区索引。每个类型都有两个子类型，有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引，就必须是本地索引。把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。 

在使用分区后的表和索引时，oracle还支持并行查询和并行dml。这样就可以同时执行多个进程，从而加快处理这条语句。 

2.7.1.本地分区索引(通常使用的索引) 

可以使用与表相同的分区键和范围界限来对本地索引分区。每个本地索引的分区只包含了它所关联的表分区的键和rowid。本地索引可以是b树或位图索引。如果是b树索引，它可以是唯一或不唯一的索引。 

这种类型的索引支持分区独立性，这就意味着对于单独的分区，可以进行增加、截取、删除、分割、脱机等处理，而不用同时删除或重建索引。oracle自动维护这些本地索引。本地索引分区还可以被单独重建，而其他分区不会受到影响。 

2.7.1.1 有前缀的索引 

有前缀的索引包含了来自分区键的键，并把它们作为索引的前导。例如，让我们再次回顾participant表。在创建该表后，使用survey_id和survey_date这两个列进行范围分区，然后在survey_id列上建立一个有前缀的本地索引，如下图所示。这个索引的所有分区都被等价划分，就是说索引的分区都使用表的相同范围界限来创建。 

                 

技巧：本地的有前缀索引可以让oracle快速剔除一些不必要的分区。也就是说没有包含where条件子句中任何值的分区将不会被访问，这样也提高了语句的性能。 

2.7.1.2 无前缀的索引 

无前缀的索引并没有把分区键的前导列作为索引的前导列。若使用有同样分区键(survey_id和survey_date)的相同分区表，建立在survey_date列上的索引就是一个本地的无前缀索引，如下图所示。可以在表的任一列上创建本地无前缀索引，但索引的每个分区只包含表的相应分区的键值。 

                         

如果要把无前缀的索引设为唯一索引，这个索引就必须包含分区键的子集。在这个例子中，我们必须把包含survey和(或)survey_id的列进行组合(只要survey_id不是索引的第一列，它就是一个有前缀的索引)。 

技巧：对于一个唯一的无前缀索引，它必须包含分区键的子集。 

2.7.2. 全局分区索引 

全局分区索引在一个索引分区中包含来自多个表分区的键。一个全局分区索引的分区键是分区表中不同的或指定一个范围的值。在创建全局分区索引时，必须定义分区键的范围和值。全局索引只能是b树索引。oracle在默认情况下不会维护全局分区索引。如果一个分区被截取、增加、分割、删除等，就必须重建全局分区索引，除非在修改表时指定alter table命令的update global indexes子句。 

2.7.2.1 有前缀的索引 

通常，全局有前缀索引在底层表中没有经过对等分区。没有什么因素能限制索引的对等分区，但oracle在生成查询计划或执行分区维护操作时，并不会充分利用对等分区。如果索引被对等分区，就必须把它创建为一个本地索引，这样oracle可以维护这个索引，并使用它来删除不必要的分区，如下图所示。在该图的3个索引分区中，每个分区都包含指向多个表分区中行的索引条目。 

         

                       

         分区的、全局有前缀索引 

技巧：如果一个全局索引将被对等分区，就必须把它创建为一个本地索引，这样oracle可以维护这个索引，并使用它来删除不必要的分区。 

2.7.2.2 无前缀的索引 

oracle不支持无前缀的全局索引。 

2.8  位图连接索引 

位图连接索引是基于两个表的连接的位图索引，在数据仓库环境中使用这种索引改进连接维度表和事实表的查询的性能。创建位图连接索引时，标准方法是连接索引中常用的维度表和事实表。当用户在一次查询中结合查询事实表和维度表时，就不需要执行连接，因为在位图连接索引中已经有可用的连接结果。通过压缩位图连接索引中的rowid进一步改进性能，并且减少访问数据所需的i/o数量。 

创建位图连接索引时，指定涉及的两个表。相应的语法应该遵循如下模式： 

create bitmap index fact_dim_col_idx on fact(dim.descr_col) from fact, dim 

where fact.joincol = dim.joincol; 

位图连接的语法比较特别，其中包含from子句和where子句，并且引用两个单独的表。索引列通常是维度表中的描述列——就是说，如果维度是customer，并且它的主键是customer_id，则通常索引customer_name这样的列。如果事实表名为sales，可以使用如下的命令创建索引： 

create bitmap index sales_cust_name_idx 

on  sales(customer.customer_name)  from sales, customer 

where  sales.customer_id=customer.customer_id; 

如果用户接下来使用指定customer_name列值的where子句查询sales和customer表，优化器就可以使用位图连接索引快速返回匹配连接条件和customer_name条件的行。 

位图连接索引的使用一般会受到限制： 

1）只可以索引维度表中的列。 

2）用于连接的列必须是维度表中的主键或唯一约束；如果是复合主键，则必须使用连接中的每一列。 

3）不可以对索引组织表创建位图连接索引，并且适用于常规位图索引的限制也适用于位图连接索引。