Oracle Index

来源：互联网发布：批处理软件安装顺序编辑：程序博客网时间：2024/05/16 23:47

一、概述

建立索引时，将在表空间自动地建立一个索引段，索引段空间分配和保留空间的使用受到下列控制：

索引段范围的分配受常驻该索引段的存储参数控制；其数据块中未用空间可受该段的PCTFREE参数设置所控制。

二、创建索引的两种方式

1.自动创建：在定义主键或唯一性约束时系统会自动在相应的字段上创建unique索引。

2.手动创建：用户可以在其它列上创建非唯一索引，以加速查询。如果创建unique索引则也会自动实施unique约束。

三、创建索引的原则

创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表中数据的策略。

1、在select操作占大部分的表上创建索引；

2、在where子句中出现最频繁的列上创建索引；

3、在选择性高的列上创建B-TREE索引（补充索引选择性，最高是1，eg：primary key）；

4、复合索引的主列应该是最有选择性的和where限定条件最常用的列，并以此类推第二列……。

5、小于5M的表，最好不要使用索引来查询，表越小，越适合用全表扫描。

四、使用索引的原则

1、查询结果是所有数据行的5%以下时，使用B-TREEindex查询效果最好；

2、where条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引。因为当sql语句所查询的列，全部都出现在复合索引中时，

此时由于Oracle只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多；

3、索引利于select，但对经常insert，delte尤其update的表，会降低效率。

4、where子句中的这个字段，必须是复合索引的第一个字段；eg：一个索引是按f1, f2, f3的次序建立的，

若where子句是f2 = :var2,则因为f2不是索引的第1个字段，无法使用该索引，9i后引用index skip scan解决了这个问题

5、where子句中的这个字段，不应该参与任何形式的计算：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

6、应尽量熟悉各种操作符对Oracle是否使用索引的影响：以下这些操作会显式（explicitly）地阻止 Oracle 使用索引：

is null;is not null;notin;!=;like;numeric_col+0;date_col+0;char_col||' ';to_char;to_numberto_date等。

五、索引特点

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

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

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

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

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

六、应该建索引列的特点：

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

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

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

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

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

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

七、不应该建索引列的特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。

相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加B-TREE索引。这是因为，由于这些列的取值很少，

例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加B-TREE索引，并不能明显加快检索速度。

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

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。

当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

八、索引不足

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

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

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

九、创建索引的语法

CREATEUNIUQE | 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 | GLOBALPARTITION<partition_setting>

相关说明

1） UNIQUE | BITMAP：指定UNIQUE为唯一值的B-Tree索引， 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：可以在分区表和未分区表上对创建的索引进行分区

另外

createindex index_name1 on student(sid,sname);

createindex index_name2 on student(sname,sid);

这两种索引方式是不一样的

索引index_name1对Select * from student where sid=1; 这样的查询语句有效

索引index_name2对Select * from student where sname=?; 这样的查询语句有效

因此建立复合索引的时候，字段的组合顺序是非常重要的。一般情况下，需要经常访问的字段放在组合字段的前面

Oracle9i引入跳跃式扫描，解决了上述问题，即使Oracle不走索引也可以加hint。

十、索引的存储

索引和表都是独立存在的。在为索引指定表空间的时候，不要将被索引的表和索引指向同一个表空间，这样可以避免产生IO冲突。

使Oracle能够并行访问存放在不同硬盘中的索引数据和表数据，更好的提高查询速度。

十一、删除索引

dropindex PK_DEPT1;

十二、索引的失效

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

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

select cust_Id,cust_name from customerswhere cust_rating <> 'aa';

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

select cust_Id,cust_name from customers wherecust_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';

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

selectempno,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 frombanks where account_number = 990354;

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

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

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

十三、管理索引

1）先插入数据后创建索引

向表中插入大量数据之前最好不要先创建索引，因为如果先建立索引。那么在插入每行数据的时候都要更改索引。这样会大大降低插入数据的速度。

2）设置合理的索引列顺序

3）限制每个表索引的数量

4）删除不必要的索引

5）为每个索引指定表空间

6）经常做insert，delete尤其是update的表最好定期exp/imp表数据，整理数据，降低碎片（缺点：要停应用，以保持数据一致性，不实用）；

有索引的最好定期rebuild索引（rebuild期间只允许表的select操作，可在数据库较空闲时间提交），以降低索引碎片，提高效率

十四、注意的地方

1.建立索引将占用额外的数据库空间，更重要的是增删改操作的时候，索引的排序也必须改变，加大的维护的成本。

2.如果经常查询x=?和y=?，那推荐使用组合index(x,y)，这种情况下组合索引的效率是远高于两个单独的索引的。

3.同时在用组合索引的时候，大家一定要注意一个细节：建立组合索引index(x,y,z)的时候，那在查询条件中出现x,xy,xyz,yzx都是可以用到该组合索引，但是y,yz,z是不能用到该索引的。

十五、索引的启用与警用

启动索引监视：alter index index_name monitoring usage;

停止索引监视：alter index index_name nomonitoringusage;

十六、索引的重建

Oracle index rebuild online与 rebuild及 drop index后重建？

当我们对索引进行 rebuild时,如果不加online选项,oracle则直接读取原索引的数据;

当我们添加 online选项时,oracle是直接扫描表中的数据,维护索引段数据的一致性就是从索引开始创建到索引创建完成这段时间的数据改变的同步。

从索引开始 rebuild online的那一刻起,oracle会先创建一个SYS_JOURNAL_xxx的系统临时日志表,结构类似于物化视图日志表mlog$_表,

通过内部触发器,记录了开始 rebuildonline索引时表上所发生的改变的记录,当索引已经创建好之后,

新数据将直接写入索引,只需要把SYS_JOURNAL_xxx日志表中的改变维护到索引中即可,也就是最小化对当前业务的影响。

在 rebulid index online的时候走的是 full table scan，这时候需要排序；

在 rebulid index走的index ffs，而ffs搜索的顺序是根据leafblock的物理存储顺序相关，也需要排序。在rebuild index时候还是需要用到temp空间来排序的。

总之，优先用rebuild online，实在不行也要在空闲的时候用rebuil，非必要不用drop index/create index

ALTERINDEX index_name REBUILD;

ALTERINDEX index_name REBUILD ONLINE;

alter index index_name rebuild tablespace tablespace_name --重建时重设存储的表空间

全局非分区索引

CREATEINDEX month_ix ON sales (sales_month);

等同于

CREATEINDEX month_ix ON sales (sales_month) GLOBAL;

局部索引的基表必须是分区表

在Oracle中可以创建组合索引，即同时包含两个或两个以上列的索引。在组合索引的使用方面，Oracle有以下特点：

1、当使用基于规则的优化器（RBO）时，只有当组合索引的前导列出现在SQL语句的where子句中时，才会使用到该索引；

2、在使用Oracle9i之前的基于成本的优化器（CBO）时，只有当组合索引的前导列出现在SQL语句的where子句中时，才可能会使用到该索引，这取决于优化器计算的使用索引的成本和使用全表扫描的成本，Oracle会自动选择成本低的访问路径；

3、从Oracle9i起，Oracle引入了一种新的索引扫描方式——索引跳跃扫描（index skip scan），这种扫描方式只有基于成本的优化器（CBO）才能使用。这样，当SQL语句的where子句中即使没有组合索引的前导列，并且索引跳跃扫描的成本低于其他扫描方式的成本时，Oracle就会使用该方式扫描组合索引；

4、 Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示（hint），我们可以帮助Oracle优化器作出更好的选择。

十七、索引类型

1、B树索引（B-Tree Index）

创建索引的默认类型，结构是一颗二叉树，采用的是平衡B树算法：右子树节点的键值大于等于父节点的键值，左子树节点的键值小于等于父节点的键值。

B树索引可是单列索引，也可是复合(也称组合)索引索引，最多可以包括32列。

树叶块包含了索引值、ROWID，以及指向前一个和后一个树叶块的指针。Oracle可以从两个方向遍历这个二叉树。B树索引保存了在索引列上有值的每个数据行的ROWID值。

Oracle不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引，而其中列上包含NULL值，这一行就会处于包含NULL值的索引列中，且将被处理为空(视为NULL)。

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

B-tree 特点：

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

不能用包含OR操作符的查询；(and可以)

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

典型的树状结构；

每个结点都是数据块；

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

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

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

例子表temp_liutao字段A有索引，B/C没有索引，则下列OR语句无法使用A的索引，除非A\B列均有索引。

select/*+index(t ind_temp_liutao_a)*/ *

from temp_liutao t

where t.a = 'a1' or t.b = 'b1' and t.c='c1';

2、位图索引(BitMap Index)

如果表中的某些字段取值范围比较小，比如职员性别、分数列ABC级等。只有两个值。这样的字段如果建B树索引没有意义，不能提高检索速度。这时我们推荐用位图索引

位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。

它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列，但通常它们都只用于少量的列。

因位图索引合并机制的存在，且index skip scan不能用于位图索引的组合形式，故可以说位图索引并适合用组合索引。

例如，您的表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为 2。

如果有多个可用的位图索引， Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。

创建3个单列位图索引

下面体现合并的处理

Bitmap t特点：

适合与决策支持系统；

做UPDATE代价非常高；

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

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

技巧：当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户 )操作中加载表 (插入操作 )方面通常要比 B树做得好。

当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。

技巧：位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。

技巧：建议不要在一些联机事务处理(OLTP)应用程序中使用位图索引。B树索引的索引值中包含 ROWID，这样 Oracle就可以在行级别上锁定索引。

位图索引存储为压缩的索引值，其中包含了一定范围的 ROWID，因此 Oracle必须针对一个给定值锁定所有范围内的 ROWID。这种锁定类型可能在某些DML语句中造成死锁。 SELECT语句不会受到这种锁定问题的影响。

位图索引的使用限制：

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

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

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

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

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

3、基于函数的索引

如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。

建立它们之前必须先考虑下面一些问题：

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

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

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

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

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

4、HASH索引