现在的位置: 首页 > 数据库 > 正文

Oracle索引详解[转]

2014年10月31日 数据库 ⁄ 共 7096字 暂无评论 ⁄ 阅读 1,343 次
文章目录

索引创建语法

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) 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

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

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

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

索引缺点

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

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

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

应该建索引的列

1)在经常需要搜索的列上,可以加快搜索的速度;

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

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

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

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

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

但是,这些特点都过于概念化,要想判断某列上是否应该建立索引需要根据表的数据量,结果集的数据量,表的胖瘦(列的多少)等等因素综合判断。

不应该建索引的列

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

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

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

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

索引失效

在SQL中有很多操作会导致索引无法使用,以下为常见问题:

1) 使用不等于操作符(<>、!=),通过把不等于操作符改成OR条件,就可以使用索引,以避免全表扫描。如下:下面的查询即使在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';

2) 使用IS NULL或IS NOT NULL

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

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);

4)隐式转换,不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行ExplainPlan也不能让您明白为什么做了一次“全表扫描”。

如例: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';

查询索引

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

二元高度(Binary height)

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

索引分类

B树索引

在创建索引时B树索引是默认的索引类型,B树索引可以是一个列的(简单)索引,也可以是组合/复合(多个列)的索引,B树索引最多可以包括32列。B树索引每个结点都是数据块;大多都是物理上一层、两层或三层不定,逻辑上三层;叶子块数据是排序的,从左向右递增。

oracle-index1

位图索引

位图索引非常适合于决策支持系统(Decision Support System,DSS)和数据仓库,它们不应该用于通过事务处理应用程序访问的表。尽管位图索引最多可达30个列,但通常它们都只用于少量的列。

位图索引不同于B树索引,它不存储ROWID值,也不存储键值,主要用于低基数列(如性别,只有男和女两种值)上创建索引。在为表中的低基数列创建位图索引时,系统将对表进行一次全面扫描,为低基数列的各个取值构建“图表”(相当于为每个值增加一列),扫描的同时还将创建位图记录,记录中各行的顺序与它在表中的顺序相同。在位图索引的图表中,1表示“是,该值存在于这一行中“,0表示”否,该值不存在于这一行中“,虽然1和0不能作为指向行的指针,但是,由于图表中1和0的位置与表行的位置是相对应的。如果给定起始和终止ROWID,则可以计算出表中的物理位置。

oralce-index2

特点:

  • 适合与决策支持系统;
  • 做UPDATE代价非常高;
  • 非常适合OR操作符的查询;
  • 基数比较少的时候才能建位图索引;

使用限制:

  • 基于规则的优化器不会考虑位图索引;
  • 当执行ALTER TABLE语句并修改包含有位图索引的列时,会使位图索引失效;
  • 位图索引不包含任何列数据,并且不能用于任何类型的完整性检查;
  • 位图索引不能被声明为唯一索引;
  • 位图索引的最大长度为30;

基于函数的索引

基于函数的索引只是常规的B树索引,只不过它存放的数据是由表中的数据应用函数后得到的,而不是直接存放表中的数据本身。

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索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。

oracle-index3

分区索引

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

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

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

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

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

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

有前缀的索引

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

oracle-index4

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

无前缀的索引

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

oracle-index5

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

全局分区索引

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

有前缀的索引

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

oracle-index6

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

位图连接索引

位图连接索引是基于两个表的连接的位图索引,在数据仓库环境中使用这种索引改进连接维度表和事实表的查询的性能。创建位图连接索引时,标准方法是连接索引中常用的维度表和事实表。当用户在一次查询中结合查询事实表和维度表时,就不需要执行连接,因为在位图连接索引中已经有可用的连接结果。通过压缩位图连接索引中的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条件的行。

参考文章地址:

http://blog.csdn.net/iteye1011/article/details/17263241

http://ccbbkk.iteye.com/blog/2111755

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?