基本的索引原理

1、本章内容并不是针对数据库专家或是那些想快速找到答案的读者。本章主要是讨论基本的索引原理(也可能是仅有的一章)。对初学者来说，最困难的就是如何找到那些可以填补最主要差距的信息，以及如何了解Oracle的索引功能。本章就是服务于这个目的。尽管市场上有大量面向中高级用户的书籍，但面向初学者的资料却非常少，而且需求量往往很高。Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几小时甚至几天的进程在几分钟内得以完成，这样会使您颇有成就感

2、。本章将讨论每个索引选项，然后指出每个选项的优缺点。本章主要内容：       基本的索引概念       查找被索引的表以及具有连接索引的表       组合索引的使用方法       Oracle ROWID       基于函数的索引的使用方法    &

3、#160;  如何避免比较不匹配的数据类型，造成索引取消       作为索引策略的集群因子       使用INDEX_STATS视图       索引的二元高度(Binary height)       使用直方图       快速全局扫描    

4、;   使用索引跳跃式扫描特性的方法       B树索引的解释       使用位图索引的时机       使用HASH索引的时机       使用索引顺序表的时机       使用反转键索引的时机       使用基

5、于函数的索引的时机       本地和全局分区索引2.1  基本的索引概念当从表中访问数据时，Oracle提供了两个选择：从表中读取每一行(即全表扫描)，或者通过ROWID一次读取一行。当访问大型表的少量行时，您可能想使用索引。例如，如果只访问大型表中5%的行，并且使用索引标识读取的块，则可以执行较少的I/O。如果没有使用索引，则要读取表中所有的块。索引改进性能的程度部分取决于数据的选择性以及在表的块之间分布数据的方式。如果数据非常具有选择性，则表中将只有很少的行匹配索引值(例如护照号码)。Oracle将能够快速查询匹配索引

6、值的ROWID的索引，并且可以快速查询少量的相关表块。如果数据不是非常具有选择性(例如国家名)，则索引可能返回许多ROWID，导致从表中查询许多单独的块。如果数据非常具有选择性，但是相关的行在表中的存储位置并不互相靠近，则会进一步减少索引的益处。如果匹配索引值的数据分散在表的多个块中，则必须从表中选择多个单独的块以满足查询。在一些情况中，您会发现当数据分散在表的多个块中时，最好是不使用索引，而是执行全表扫描。执行全表扫描时，Oracle使用多块读取以快速扫描表。基于索引的读取是单块读取，因此在使用索引时的目标是减少解决查询所需的单个块的数量。通过使用Oracle中的一些可用选项，比如分区、并行

7、DML、并行查询操作以及使用db_file_multiblock_read_count进行更大的I/O操作，全表扫描和索引查找之间的平衡点发生了改变。硬件更为快速，磁盘可以在磁盘上的高速缓存中缓存更多的信息，内存也变得更为廉价。与此同时，Oracle已经增强的索引特性，包括了跳跃式扫描索引和其他减少检索数据所需时间的操作。技巧：当升级Oracle版本时，确保测试应用程序的查询以确定查询的执行路径是否仍然使用在升级之前使用的索引。查看执行路径是否改变，并且查看这种改动的效果是更好还是更差。索引通常能提高查询的性能。SELECT语句、UPDATE和DELETE命令的WHERE子句的性能(当访问的行

8、较少时)可以从索引中获益。一般来说，增加索引会降低INSERT语句的性能(因为需要同时对表和索引进行插入)。如果未索引列，则索引列的UPDATE操作将会减慢执行速度，因为数据库必须管理对表和索引的改动。此外，大量行的DELETE操作将会由于表中存在索引而减慢执行速度。用于删除表中一半数据的DELETE语句同时需要删除所有这些行的索引(这种情况是非常耗时的)。通常，表中的每个索引都会使对表执行的INSERT操作变慢两倍；使用两条索引通常会使插入操作变慢一倍(然而，一个由两部分组成的单一索引并不比只有一个部分组成的单一索引差很多)。索引列的UPDATE和DELETE操作同样也会变慢。您需要根据对数

9、据操作性能的影响平衡索引对查询性能带来的益处。查询DBA_INDEXES视图可获得表上所有查询的清单。同样需要注意的是，可以通过访问USER_INDEXES视图检索模式的索引。查询ALL_INDEXES视图可以查看已经访问的所有表的索引。例如，在EMP表上创建了一些索引，EMP表是Oracle的一个演示表。create index emp_id1 on emp(empno, ename, deptno);create index emp_id2 on emp (sal);当发出这些命令时，数据库将在EMP表上创建两个单独的索引。每个索引将包含EMP表中的指定值以及匹配指定值的行的ROWID值。

10、如果需要查找Sal值为1000的EMP记录，优化器就会使用EMP_ID2索引查找该值，在索引中查找相关的ROWID，并且使用该ROWID在表中查找正确的行。下面的USER_INDEXES查询显示了EMP表上的新索引：select       table_name, index_namefrom         user_indexeswhere        table_name = &

11、#39;EMP' ;TABLE_NAME                            INDEX_NAME- -EMP                 

12、                    EMP_ID1EMP                             &

13、#160;       EMP_ID2输出显示了两个索引，但是没有显示每个索引中的列。为了获得给定表中被索引的特定列，可访问USER_IND_COLUMNS视图。同样需要注意的是，DBA可以通过访问DBA_IND_COLUMNS视图检索所有模式中被索引的列，而通过访问ALL_IND_COLUMNS视图则可以查看所有表的索引列。column index_name format a12column column_name format a8column table_name format a8select   

14、;  table_name, index_name, column_name, column_positionfrom       user_ind_columnsorder      by table_name, index_name, column_position;TABLE_NA  INDEX_NAME  COLUMN_N COLUMN_POSITION- - - -EMP      &#

15、160;  EMP_ID1       EMPNO                     1EMP         EMP_ID1       ENAME    

16、60;                 2EMP        EMP_ID1       DEPTNO                  

17、   3EMP         EMP_ID2       SAL                        1EMP表中有两个索引。首先，EMP_ID1是一个组合(concatenated)索引，它对Empno、Ename和

18、Deptno这几列进行索引。而第二个索引EMP_ID2只对Sal列进行索引。显示在程序清单中的Column_Position显示了组合索引中的列顺序，即按照Empno、Ename和Deptno的顺序。技巧：查询DBA_INDEXES和DBA_IND _COLUMNS可以检索到一个给定表的索引列表。对于您自己的模式，只能使用USER_INDEXES和USER_IND_COLUMNS来检索信息。2.2  组合索引当某个索引包含有多个已索引的列时，我们称这个索引为组合(concatenated)索引或是复合索引。虽然Oracle 9i引入的跳跃式扫描索引访问方法增强了优化器在使用组合索引时

19、的选择，但是您应该谨慎地选择索引中的列顺序。一般来说，索引的第一列应该是最有可能在WHERE子句中使用的列，并且也是索引中最具选择性的列。在引入跳跃式扫描功能之前，查询只能在WHERE子句中使用索引的第一列时使用索引。考虑如下程序清单中的示例，其中表EMP有一个组合索引，该索引包含了Empno、Ename和Deptno。注意第一部分是Empno，第二部分则是Ename，最后是Deptno。如果没有使用跳跃式扫描功能，除非在WHERE子句中对第一列(Empno)指定一个值，否则Oracle一般不会使用这个索引。select      job, &

20、#160;empnofrom        empwhere       ename = 'RICH'因为Ename不是索引的第一列，优化器可能会选择不使用该索引。随着在Oracle 9i中引入了跳跃式扫描功能，即使在WHERE子句中没有指定Empno值，优化器也可能会选择使用该索引。相反，优化器可能会选择索引的快速全局扫描或全表扫描。如果在WHERE子句中使用索引的第三列，也会产生相同的情况：select    job

21、,  empnofrom      empwhere     deptno = 30;在该程序清单中，WHERE子句指定了索引中第三列的值。优化器可能选择执行索引快速扫描访问、索引快速全局扫描或全表扫描。通过创建索引，您可以在执行查询时为数据库提供更多的选择。从而有希望改进整体的性能。注意，用户的代码没有改变，优化器可以识别该索引，并且根据每种替代方法的预期成本决定使用何种方法。在下面的示例中，使用了索引的一部分。将第一列Empno用作WHERE子句中的限制条件，以便Oracle可以使用该索引。

22、select    job,  empnofrom      empwhere     empno = 'RICH'两种最常见的索引扫描类型是唯一扫描和范围扫描。在唯一扫描中，数据库知道索引包含一个唯一值列表。在范围扫描中，数据库将根据查询标准从索引中返回多个值。在该示例中，emp_id1和emp_id2索引没有被创建为唯一索引。Oracle将在检索它们的值时执行范围扫描。在创建索引时，使用CREATE UNIQUE INDEX命令可以创建唯一索引。

23、在创建主键约束或UNIQUE约束时，Oracle将基于指定的列自动创建唯一索引(除非使用DISABLE子句创建约束)。如果创建多列的主键，Oracle将创建组合索引，其中的列按照在创建主键时指定的顺序排列。通过提供每个行的ROWID，类似于EMP_ID1和EMP_ID2的索引为Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的物理位置的指针。技巧：将Oracle的ROWID硬编码成特定代码时，一定要小心谨慎。因为不同版本的ROWID结构会有所不同，而且在将来的版本里可能还会有所改变。我建议不要对ROWID进行硬编码。2.3  限制索引限制索引是一些没有经验的开发

24、人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。后文的各小节将讨论一些常见的问题。Oracle优化器在后台工作，选择并使用可能最有效的数据检索方法。例如，在许多情况下不需要指定WHERE子句，从而Oracle可以使用索引。如果查询索引列的MIN或MAX值，Oracle将从索引(而不是表)中检索该值。同样，如果对索引列执行COUNT函数，Oracle可以使用索引而不是该列。在下面的小节中，您将看到WHERE子句的逻辑阻止Oracle使用索引的情况。2.3.1  使用不等于运算符(<>、!=)索引只能用于查找表中已有的数据。每当在WHERE子句中使用不等于运算

25、符时，都将无法使用所引用的列的索引。请考虑下文对CUSTOMERS表的查询，CUSTOMERS表中的CUST_RATING列有一个索引。下面的语句仍会执行一次全表扫描(因为大多数记录都可以被检索到)，即使列CUST_RATING上存在索引。select       cust_id,   cust_namefrom         customerswhere       

26、60;cust_rating <> 'aa'当分析表时，Oracle收集表中数据分布的相关统计信息。通过使用这种分析，基于成本的优化器就可以决定在WHERE子句中对一些值使用索引，而对其他的值不使用索引。在应用程序开发和测试期间，应该使用具有代表性的行集，从而可以模拟产品环境中实际的数据值分布。技巧：通过使用CREATE INDEX命令的COMPUTE STATISTICS子句，可以在一个步骤中创建索引并分析它们。也可以从产品数据库中导入统计信息以测试执行路径(参考10gR2 Database Performance Tuning Guide (Part Numbe

27、r B14211-01)的14.5.2节)。2.3.2  使用 IS NULL或IS NOT NULL在WHERE子句中使用 IS NULL或IS NOT NULL同样会限制索引的使用，因为NULL值并没有被定义。数据库中没有值等于NULL值；甚至NULL也不等于NULL。在SQL语句中使用NULL会有很多麻烦。如果被索引的列在某些行中存在NULL值，在索引中就不会有相应的条目(例外情况是位图索引，这是位图索引对于NULL搜索通常较为快速的原因)。一般情况下，下面的语句将造成执行全表扫描，即使Sal列被索引。select      &

28、#160;empno,  ename,  deptnofrom          empwhere        sal is null;如果要在上面的三列中禁用NULL值，可以在创建或修改表时使用NOT NULL。注意，如果表中已经包含数据，只有在表中每一行都有非NULL值或是使用ALTER TABLE命令的DEFAULT子句时，才可以为列设置NOT NULL属性。下面的程序清单显示了修改EMP表的Sal列以禁用NULL值：

29、alter  table  emp  modify(sal  not  null);注意，如果想尝试在Sal列中插入一个NULL值，会返回一个错误信息。技巧：在创建表时对列指定NOT NULL后会禁用NULL值，而且可以避免与使用NULL值相关的性能问题。下面的创建表语句为Deptno列提供了一个默认值。如果在执行INSERT操作时该列没有指定的值，就会使用默认值。如果指定了默认值，并且您确实需要使用NULL值，则需要在该列中插入NULL。create table employee(empl_id number(8) not null, fir

30、st_name varchar2(20) not null,last_name varchar2(20) not null, deptno number(4) default 10);insert into employee(empl_id, first_name, last_name)values (8100, 'REGINA', 'NIEMIEC');1 row created.select        *from        

31、;  employee;EMPL_ID FIRST_NAME             LAST_NAME                   DEPTNO- - - -8100   REGINA        

32、          NIEMIEC                         10insert into employeevalues (8200, 'RICH', 'NIEMIEC', NULL);1 row creat

33、ed.select       *from         employee;EMPL_ID FIRST_NAME              LAST_NAME               

34、     DEPTNO-  - - -8100   REGINA                   NIEMIEC                    &#

35、160;  108200   RICH                      NIEMIEC技巧：NULL值通常会限制索引。在创建表时对某一列指定NOT NULL或DEFAULT，对于避免可能出现的性能问题很有帮助。2.3.3  使用函数如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。一些常

36、见的函数，如TRUNC、SUBSTR、TO_DATE、TO_CHAR和INSTR等，都能改变列的值。因此，无法使用已被函数引用的索引和列。下面的语句会执行一次全表扫描，即使hire_date列上存在索引(只要它不是基于函数的索引)。select        empno, ename, deptnofrom          empwhere         trunc(hir

37、edate) = '01-MAY-01'把上面的语句改成如下所示的语句，这样就可以通过索引进行查找。select        empno, ename, deptnofrom          empwhere         hiredate > '01-MAY-01'and      

38、      hiredate < (TO_DATE('01-MAY-01') + 0.99999);技巧：通过改变所比较的列上的值，而不用改变列本身，就可以启用索引用。这样可避免全表扫描。关于基于函数的索引的更多详情，可查看本章后面的“基于函数的索引”一节。2.3.4  比较不匹配的数据类型比较不匹配的数据类型也是比较难于发现的性能问题之一。Oracle并不会对那些不匹配的数据报错 事实正好相反。例如，Oracle可以隐式地把VARCHAR2列的数据转换成要被比较的数值型数据类型。考虑如下的示例，其中accoun

39、t_number就是一个VARCHAR2类型。如果Account_Number列使用VARCHAR2数据类型，下面的语句将执行全表扫描，即便是索引account_number列：select      bank_name, address, city, state, zipfrom         bankswhere        account_number = 990354;Oracle可以自动把WH

40、ERE子句变成to_number(account_number)=990354这样就限制了索引的使用。这个查询的EXPLAIN PLAN仅显示通过“全表扫描”访问这个表(对编程人员来说通常都很迷惑)。对一些DBA或开发人员来说，这样的情况可能很少见，但在很多系统中，数字型值可以用零填充，然后指定为VARCHAR2类型。前面的语句可以改写成如下语句，这样可以正确地对这个字段使用单引号，以使用账号上的索引。select      bank_name, address, city, state, zipfrom    &

41、#160;   bankswhere       account_number = '000990354'作为选择，可以定义Account_Number列使用NUMBER数据类型，前提是前置的0不是该列的关键信息。技巧：不匹配数据类型之间的比较会让Oracle自动限制索引的使用。即便对这个查询执行EXPLAIN PLAN也不能让您明白为什么做了一次“全表扫描”。只有了解关于数据类型的知识才能帮助您解决这个问题。2.4  选择性Oracle根据查询和数据，提供了多种方法来判断使用索

42、引的价值。第一个方法就是判断索引中的唯一键或不同键的数量。可以对表或索引进行分析来确定不同键的数量。可以查询USER_INDEXES视图的Distinct_Keys列来研究分析的结果。比较一下唯一键的数量和表中的行数(如USER_INDEXES视图的Num_Rows列所示)，就可以判断索引的选择性。选择性越高，索引返回的行数就越少，该索引就越好。技巧：索引的选择性可以帮助基于成本的优化器来判断执行路径。索引的选择性越高，针对每个不同值返回的行数也越少。对于组合索引在索引中添加额外的列不会显著改善选择性，并且使用额外列的成本会超出收益。2.5  集群因子(Clustering Fact

43、or)集群因子是索引与它所基于的表相比较而得出的有序性度量，它用于检查在索引访问之后执行的表查找的成本(将集群因子与选择性相乘即可得到该操作的成本)。集群因子记录在扫描索引时将读取的块数量。如果使用的索引具有较大的集群因子，则必须访问更多的表数据块才可以获得每个索引块中的行(因为邻近行位于不同的块中)。如果集群因子接近于表中的块数量，则表示索引适当排序；但是，如果集群因子接近于表中的行数量，则表示索引没有适当排序。集群因子的计算简要介绍如下：(1) 按顺序扫描索引。(2) 将当前索引值指向的ROWID的块部分与以前的索引值进行比较(比较索引中的邻近行)。(3) 如果ROWID指向不同的TABL

44、E块，则增加集群因子(对整个索引执行该操作)。Clustering_Factor列位于USER_INDEXES视图中，该列反映了数据相对于已索引的列是否显得有序。如果Clustering_Factor列的值接近于索引中的树叶块(leaf block)的数目，表中的数据就是有序的。索引的树叶块存储索引值以及它们指向的ROWID。例如，CUSTOMERS表上Customer_Id列的值可以由序列生成器产生，而且是表CUSTOMERS上的主键。Customer_Id的索引的集群因子就有可能非常接近于树叶块数(表示有序)。当往数据库中添加客户数据时，它们就按照序列产生器所产生的序列值有序地存储在表中。

45、然而，因为整个表的客户名字排列是随机的，所以customer_name上的索引会有一个很高的集群因子。集群因子对执行范围扫描的SQL语句有一定的影响。如果集群因子很低(相对于树叶块的数量)，需要读取的表中块的数量就可以减少很多。这样也增加了相同的数据块已经存在于内存中的可能性。一个较高的集群因子(相对于树叶块的数量)会增加满足基于索引列的范围查询所需的数据块数目。技巧：可以使用表中数据的集群，这样可以提高执行范围扫描类型操作的语句的性能。如果要决定如何在语句中使用列，对列进行索引是最好的选择。2.6  二元高度(binary height)索引的二元高度对把ROWID返回给用户进程时

46、所要求的I/O数量起到关键作用。二元高度的每个级别都会增加一个额外的读取块，而且由于这些块不能按顺序读取，它们都要求一个独立的I/O操作。在图2-1中，我们检索一个二元高度为3的索引，这样会返回一行数据给用户，同时有4个块被读取：3个来自索引，一个来自表。随着索引的二元高度的增加，检索数据所要求的I/O数量也会随之增加。在对索引进行分析后，可以通过查询DBA_INDEXES的blevel列查看它的二元高度：图2-1  具有二元高度或blevel=3的索引(级别3是树叶块驻留的级别)EXECUTE DBMS_STATS.GATHER_INDEX_STATS ('SCOTT

47、9;,'EMP_ID1');PL/SQL procedure successfully completed.select      blevel, index_namefrom        dba_indexeswhere       index_name = 'EMP_ID1'BLEVEL   INDEX_NAME- -0   EMP_ID1技巧：对索引或者表进

48、行分析可以得到索引的二元高度。使用USER_INDEXES视图里的blevel列可以检查所有索引的二元高度。二元高度主要随着表中索引列的非NULL值数量以及索引列中值的范围狭窄程度而变化。索引上如果有大量被删除的行，它的二元高度也会增加。重建索引可能会降低二元高度。虽然这些步骤可以减少针对索引执行的I/O数量，但对性能的改进却很小。如果一个索引中被删除的行接近2025，重建索引会降低二元高度以及在一次I/O中所读取的空闲空间。技巧：一般来说，数据库块尺寸越大，索引的二元高度就越低。二元高度中的每个额外级别(blevel)在DML操作期间会增加额外的性能成本。2.7  使用直方图在分析

49、表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，可以在表的任何列上构建直方图。构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划：例如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80的记录。要创建直方图，首先要确定好它的尺寸。该尺寸与直方图所需的存储桶(bucket)数相关。每个存储桶包含列值

50、和行数的相关信息。EXECUTE DBMS_STATS.GATHER_TABLE_STATS('scott','company', METHOD_OPT => 'FOR COLUMNS SIZE 10 company_code');PL/SQL procedure successfully completed.前面的查询会在COMPANY表上创建一个带有10个存储桶的直方图，如图2-2所示。图中COMPANY_CODE列的所有值被分成10个存储桶。这个例子中有一个占大部分的company_code值(大约80，即1430)。同样如图中所示，

51、多数宽度均衡的存储桶都只有3行记录；有一个存储桶却有73行记录。在高度均衡图中，每个存储桶有相同数目的行，多数存储桶的终点都是1430，这也反映了数据的偏斜分布。Oracle的直方图是高度均衡的，而不是宽度均衡。也就是说，直方图里的所有存储桶都有相同的行数。存储桶的起点和终点取决于包含这些值的行数。宽度均衡的直方图则着重于确定每个存储桶的值的范围，然后统计出这个范围内的行数。这并不是一个理想的选择。图2-2  构建在Company_Code字段上的直方图(具有10个存储桶)技巧：如果表中的数据分布得较不均匀，直方图会为基于成本的优化器提供一个数据分布的均衡图(把数据平均分布到各个存储

52、桶)。在不是很偏斜的列上使用直方图并不会提高性能。 技巧：默认情况时，Oracle的直方图会产生75个存储桶。可以把SIZE的值指定在1254之间。2.8  快速全局扫描在索引的快速全局扫描期间，Oracle读取B树索引上的所有树叶块。这个索引可以按顺序读取，这样可以一次读取多个块。初始化文件中的DB_FILE_MULTIBLOCK _READ_COUNT参数可以控制同时被读取的块的数目。相比于全表扫描，快速全局扫描通常需要较少的物理I/O，并且允许更快速地处理查询。如果表查询中的所有列都被包括在索引里，而索引的前置列并不在WHERE条件中，就可以使用快速全局扫描(必须指定

53、第7章讲到的INDEX_FFS提示)。在下面的示例中用到了emp表。它有一个组合索引，包括列empno、ename和deptno。select     empno, ename, deptnofrom        empwhere      deptno = 30;由于SQL语句中的所有列都包括在索引中，因此可以执行快速全局扫描。通常在只查询索引连接键列的连接期间执行索引快速全局扫描。作为选择，Oracle可能执行索引的跳跃式扫描访问

54、；优化器应该考虑Deptno列的直方图(如果有可用的直方图)，并且确定哪个可用的访问路径可以产生最低的性能成本。技巧：如果索引相对于表的总体尺寸来说很小，快速全局扫描就可以使应用程序的性能陡增。如果表中有一个包含了大部分列的组合索引，索引可能要比真实的表要大，这样快速全局扫描反而会降低性能。2.9  跳跃式扫描本章前面的“组合索引”一节中介绍过，索引跳跃式扫描特性允许优化器使用组合索引，即便索引的第一列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描快得多，这是因为它只需要执行很少量的读取。例如，下面的查询显示了索引全扫描和跳跃扫描之间的区别。参考第6章，可以更好地了解什么是执

55、行计划或后面的程序清单中列出的统计数据。在该程序清单中，EMP5有成百上千的行。跟随查询的执行，该程序清单显示了查询花费的时间、它在数据库中的执行路径，以及显示处理该查询所需的逻辑读数量(一致的获取)和物理读数量的统计数据。create index skip1 on emp5(job,empno);Index created.select count(*)from     emp5where    empno = 7900;Elapsed: 00:00:03.13 (Result is a single rownot displayed)

56、60;Execution Plan0           SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5)1        0    SORT (AGGREGATE)2        1      INDEX (F

57、AST FULL SCAN) OF 'SKIP1' (NON-UNIQUE)Statistics6826    consistent gets6819    physical readsselect /*+ index(emp5 skip1) */ count(*)from     emp5where    empno = 7900;Elapsed: 00:00:00.56Execution Plan0     

58、; SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)1    0   SORT (AGGREGATE)2    1     INDEX (SKIP SCAN) OF 'SKIP1' (NON-UNIQUE)Statistics21   consistent gets17   physical reads如同该程序清单所示，第二个选项使用INDEX

59、 (SKIP SCAN)操作读取索引。该执行路径需要21个逻辑读，这些逻辑读又需要17个物理I/O操作。第一个选项执行INDEX (FAST FULL SCAN)操作，该操作需要更多数量的逻辑和物理I/O。为了让优化器选择跳跃式扫描，可能需要在查询中使用提示，如同该程序清单所示。提示影响了优化器，使其偏向您所指定的执行路径。技巧：对于那些有组合索引的大型表而言，索引跳跃式扫描特性可以提供一个快速访问，即使索引的第一列没有在限制条件中使用。2.10  索引的类型下面列出了本节要讨论的索引类型：       B树索引 

60、      位图索引       HASH索引       索引组织表索引       反转键(reverse key)索引       基于函数的索引       分区索引(本地和全局索引)     

61、;  位图连接索引2.10.1  B树索引B树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。B树索引最多可以包括32列。在图2-3的例子中，B树索引位于雇员表的last_name列上。这个索引的二元高度为3；接下来，Oracle会穿过两个树枝块(branch block)，到达包含有ROWID的树叶块。在每个树枝块中，树枝行包含链中下一个块的ID号。树叶块包含了索引值、ROWID，以及指向前一个和后一个树叶块的指针。Oracle可以从两个方向遍历这个二叉树。B树索引保存了在索引列

62、上有值的每个数据行的ROWID值。Oracle不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引，而其中列上包含NULL值，这一行就会处于包含NULL值的索引列中，且将被处理为空(视为NULL)。图2-3  B树索引创建过程技巧：索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了I/O量。2.10.2  位图索引位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到

63、中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列，但通常它们都只用于少量的列。例如，您的表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据Sex列的值查询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可用的位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。下面的程序清单给出了一个创建位图索引的例子：create bitmap index dept_idx2_bm on dept (deptno);Index created.技巧：对于有较低基数的

64、列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。位图索引示例下面来看一个示例表PARTICIPANT，该表包含了来自个人的调查数据。列Age_Code、Income_Leve

65、l、Education_Level和Marital_Status都包括了各自的位图索引。图2-4显示了每个直方图中的数据平衡情况，以及对访问每个位图索引的查询的执行路径。图中的执行路径显示了有多少个位图索引被合并，可以看出性能得到了显著的提高。图2-4  位图索引创建过程如图2-4所示，优化器依次使用4个单独的位图索引，这些索引的列在WHERE子句中被引用。每个位图记录指针(例如0或1)，用于指示表中的哪些行包含位图中的已知值。有了这些信息后，Oracle就执行BITMAP AND操作以查找将从所有4个位图中返回哪些行。该值然后被转换为ROWID值，并且查询继续完成剩余的处理工作。注

66、意，所有4个列都有非常低的基数，使用索引可以非常快速地返回匹配的行。技巧：在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。下面的查询可显示索引类型。B树索引作为NORMAL列出；而位图索引的类型值为BITMAP。select index_name, index_typefrom user_indexes;技巧：如果要查询位图索引列表，可以在USER _INDEXES视图中查询index_type列。建议不要在一些联机事务处理(OLTP)应用程序中使用位图索引。B树索引的索引值中包含ROW

67、ID，这样Oracle就可以在行级别上锁定索引。位图索引存储为压缩的索引值，其中包含了一定范围的ROWID，因此Oracle必须针对一个给定值锁定所有范围内的ROWID。这种锁定类型可能在某些DML语句中造成死锁。SELECT语句不会受到这种锁定问题的影响。位图索引有很多限制，如下所示：       基于规则的优化器不会考虑位图索引。       当执行ALTER TABLE语句并修改包含有位图索引的列时，会使位图索引失效。    &#

68、160;  位图索引不包含任何列数据，并且不能用于任何类型的完整性检查。       位图索引不能被声明为唯一索引。       位图索引的最大长度为30。技巧：不要在繁重的OLTP环境中使用位图索引2.10.3  HASH索引使用HASH索引必须要使用HASH集群。建立一个集群或HASH集群的同时，也就定义了一个集群键。这个键告诉Oracle如何在集群上存储表。在存储数据时，所有与这个集群键相关的行都被存储在一个数据库块上。如果数据都存储在同一个数据库块

69、上，并且将HASH索引作为WHERE子句中的确切匹配，Oracle就可以通过执行一个HASH函数和I/O来访问数据 而通过使用一个二元高度为4的B树索引来访问数据，则需要在检索数据时使用4个I/O。如图2-5所示，其中的查询是一个等价查询，用于匹配HASH列和确切的值。Oracle可以快速使用该值，基于HASH函数确定行的物理存储位置。HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH值)。这种冲突是非常

70、消耗资源的。冲突会造成用来存储额外行的缓冲溢出，然后造成额外的I/O。如果不同HASH值的数目已经被低估，您就必须在重建这个集群之后改变这个值。ALTER CLUSTER命令不能改变HASH键的数目。HASH集群还可能浪费空间。如果无法确定需要多少空间来维护某个集群键上的所有行，就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间，HASH集群可能就不是最好的选择。如果应用程序经常在集群表上进行全表扫描，HASH集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量，全表扫描可能非常消耗资源。图2-5  使用HASH索引的例子在实现HASH集群之前一定要小心。

71、您需要全面地观察应用程序，保证在实现这个选项之前已经了解关于表和数据的大量信息。通常，HASH对于一些包含有序值的静态数据非常有效。技巧：HASH索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。2.10.4  索引组织表索引组织表会把表的存储结构改成B树结构，以表的主键进行排序。这种特殊的表和其他类型的表一样，可以在表上执行所有的DML和DDL语句。由于表的特殊结构，ROWID并没有被关联到表的行上。对于一些涉及精确匹配和范围搜索的语句，索引组织表提供了一种基于键的快速数据访问机制。基于主键值的UPDATE和DELETE语句的性能也同样得以提高，这是因为行在

72、物理上有序。由于键列的值在表和索引中都没有重复，存储所需要的空间也随之减少。如果不会频繁地根据主键列查询数据，则需要在索引组织表中的其他列上创建二级索引。不会频繁根据主键查询表的应用程序不会了解到使用索引组织表的全部优点。对于总是通过对主键的精确匹配或范围扫描进行访问的表，就需要考虑使用索引组织表。技巧：可以在索引组织表上建立二级索引。2.10.5  反转键索引当载入一些有序数据时，索引肯定会碰到与I/O相关的一些瓶颈。在数据载入期间，某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题，可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。为了解决这个问题

73、，Oracle还提供了一种反转键索引的方法。如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反。这样，数据1234、1235和1236就被存储成4321、5321和6321。结果就是索引会为每次新插入的行更新不同的索引块。不能对位图索引和索引组织表进行反转键处理。技巧：如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。不可以将反转键索引与位图索引或索引组织表结合使用。2.10.6  基于函数的索引可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。例如，下面的查询就不能使用JOB列上的索引，除非它是基于函数的索引：select   *from      empwhere    UPPER(job) = 'MGR'下面的查询使用JOB列上的索引