oracle基本的索引概念.doc

本章内容并不是针对数据库专家或是那些想快速找到答案的读者。本章主要是讨论基本的索引原理(也可能是仅有的一章)。对初学者来说，最困难的就是如何找到那些可以填补最主要差距的信息，以及如何了解Oracle的索引功能。本章就是服务于这个目的。尽管市场上有大量面向中高级用户的书籍，但面向初学者的资料却非常少，而且需求量往往很高。
Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几小时甚至几天的进程在几分钟内得以完成，这样会使您颇有成就感。本章将讨论每个索引选项，然后指出每个选项的优缺点。
本章主要内容：
●       基本的索引概念
●       查找被索引的表以及具有连接索引的表
●       组合索引的使用方法
●       Oracle ROWID
●       基于函数的索引的使用方法
●       如何避免比较不匹配的数据类型，造成索引取消
●       作为索引策略的集群因子
●       使用INDEX_STATS视图
●       索引的二元高度(Binary height)
●       使用直方图
●       快速全局扫描
●       使用索引跳跃式扫描特性的方法
●       B树索引的解释
●       使用位图索引的时机
●       使用HASH索引的时机
●       使用索引顺序表的时机
●       使用反转键索引的时机
●       使用基于函数的索引的时机
●       本地和全局分区索引
2.1  基本的索引概念
当从表中访问数据时，Oracle提供了两个选择：从表中读取每一行(即全表扫描)，或者通过ROWID一次读取一行。当访问大型表的少量行时，您可能想使用索引。例如，如果只访问大型表中5%的行，并且使用索引标识读取的块，则可以执行较少的I/O。如果没有使用索引，则要读取表中所有的块。
索引改进性能的程度部分取决于数据的选择性以及在表的块之间分布数据的方式。如果数据非常具有选择性，则表中将只有很少的行匹配索引值(例如护照号码)。Oracle将能够快速查询匹配索引值的ROWID的索引，并且可以快速查询少量的相关表块。如果数据不是非常具有选择性(例如国家名)，则索引可能返回许多ROWID，导致从表中查询许多单独的块。
如果数据非常具有选择性，但是相关的行在表中的存储位置并不互相靠近，则会进一步减少索引的益处。如果匹配索引值的数据分散在表的多个块中，则必须从表中选择多个单独的块以满足查询。在一些情况中，您会发现当数据分散在表的多个块中时，最好是不使用索引，而是执行全表扫描。执行全表扫描时，Oracle使用多块读取以快速扫描表。基于索引的读取是单块读取，因此在使用索引时的目标是减少解决查询所需的单个块的数量。
通过使用Oracle中的一些可用选项，比如分区、并行DML、并行查询操作以及使用db_file_multiblock_read_count进行更大的I/O操作，全表扫描和索引查找之间的平衡点发生了改变。硬件更为快速，磁盘可以在磁盘上的高速缓存中缓存更多的信息，内存也变得更为廉价。与此同时，Oracle已经增强的索引特性，包括了跳跃式扫描索引和其他减少检索数据所需时间的操作。

技巧：
当升级Oracle版本时，确保测试应用程序的查询以确定查询的执行路径是否仍然使用在升级之前使用的索引。查看执行路径是否改变，并且查看这种改动的效果是更好还是更差。
索引通常能提高查询的性能。SELECT语句、UPDATE和DELETE命令的WHERE子句的性能(当访问的行较少时)可以从索引中获益。一般来说，增加索引会降低INSERT语句的性能(因为需要同时对表和索引进行插入)。如果未索引列，则索引列的UPDATE操作将会减慢执行速度，因为数据库必须管理对表和索引的改动。此外，大量行的DELETE操作将会由于表中存在索引而减慢执行速度。
用于删除表中一半数据的DELETE语句同时需要删除所有这些行的索引(这种情况是非常耗时的)。通常，表中的每个索引都会使对表执行的INSERT操作变慢两倍；使用两条索引通常会使插入操作变慢一倍(然而，一个由两部分组成的单一索引并不比只有一个部分组成的单一索引差很多)。索引列的UPDATE和DELETE操作同样也会变慢。您需要根据对数据操作性能的影响平衡索引对查询性能带来的益处。查询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值。如果需要查找Sal值为1000的EMP记录，优化器就会使用EMP_ID2索引查找该值，在索引中查找相关的ROWID，并且使用该ROWID在表中查找正确的行。
下面的USER_INDEXES查询显示了EMP表上的新索引：
select       table_name, index_name
from         user_indexes
where        table_name = 'EMP' ;
TABLE_NAME                            INDEX_NAME
------------------------------ ------------------------------
EMP                                     EMP_ID1
EMP                                     EMP_ID2
输出显示了两个索引，但是没有显示每个索引中的列。为了获得给定表中被索引的特定列，可访问USER_IND_COLUMNS视图。同样需要注意的是，DBA可以通过访问DBA_IND_COLUMNS视图检索所有模式中被索引的列，而通过访问ALL_IND_COLUMNS视图则可以查看所有表的索引列。
column index_name format a12
column column_name format a8
column table_name format a8
select     table_name, index_name, column_name, column_position
from       user_ind_columns
order      by table_name, index_name, column_position;
TABLE_NA  INDEX_NAME  COLUMN_N COLUMN_POSITION
-------- ------------ -------- ---------------
EMP         EMP_ID1       EMPNO                     1
EMP         EMP_ID1       ENAME                      2
EMP        EMP_ID1       DEPTNO                     3
EMP         EMP_ID2       SAL                        1
EMP表中有两个索引。首先，EMP_ID1是一个组合(concatenated)索引，它对Empno、Ename和Deptno这几列进行索引。而第二个索引EMP_ID2只对Sal列进行索引。显示在程序清单中的Column_Position显示了组合索引中的列顺序，即按照Empno、Ename和Deptno的顺序。

技巧：
查询DBA_INDEXES和DBA_IND _COLUMNS可以检索到一个给定表的索引列表。对于您自己的模式，只能使用USER_INDEXES和USER_IND_
COLUMNS来检索信息。
2.2  组合索引
当某个索引包含有多个已索引的列时，我们称这个索引为组合(concatenated)索引或是复合索引。虽然Oracle 9i引入的跳跃式扫描索引访问方法增强了优化器在使用组合索引时的选择，但是您应该谨慎地选择索引中的列顺序。一般来说，索引的第一列应该是最有可能在WHERE子句中使用的列，并且也是索引中最具选择性的列。
在引入跳跃式扫描功能之前，查询只能在WHERE子句中使用索引的第一列时使用索引。考虑如下程序清单中的示例，其中表EMP有一个组合索引，该索引包含了Empno、Ename和Deptno。注意第一部分是Empno，第二部分则是Ename，最后是Deptno。如果没有使用跳跃式扫描功能，除非在WHERE子句中对第一列(Empno)指定一个值，否则Oracle一般不会使用这个索引。
select      job,  empno
from        emp
where       ename = 'RICH';
因为Ename不是索引的第一列，优化器可能会选择不使用该索引。随着在Oracle 9i中引入了跳跃式扫描功能，即使在WHERE子句中没有指定Empno值，优化器也可能会选择使用该索引。相反，优化器可能会选择索引的快速全局扫描或全表扫描。
如果在WHERE子句中使用索引的第三列，也会产生相同的情况：
select    job,  empno
from      emp
where     deptno = 30;
在该程序清单中，WHERE子句指定了索引中第三列的值。优化器可能选择执行索引快速扫描访问、索引快速全局扫描或全表扫描。通过创建索引，您可以在执行查询时为数据库提供更多的选择。从而有希望改进整体的性能。注意，用户的代码没有改变，优化器可以识别该索引，并且根据每种替代方法的预期成本决定使用何种方法。
在下面的示例中，使用了索引的一部分。将第一列Empno用作WHERE子句中的限制条件，以便Oracle可以使用该索引。
select    job,  empno
from      emp
where     empno = 'RICH';
两种最常见的索引扫描类型是唯一扫描和范围扫描。在唯一扫描中，数据库知道索引包含一个唯一值列表。在范围扫描中，数据库将根据查询标准从索引中返回多个值。在该示例中，emp_id1和emp_id2索引没有被创建为唯一索引。Oracle将在检索它们的值时执行范围扫描。在创建索引时，使用CREATE UNIQUE INDEX命令可以创建唯一索引。
在创建主键约束或UNIQUE约束时，Oracle将基于指定的列自动创建唯一索引(除非使用DISABLE子句创建约束)。如果创建多列的主键，Oracle将创建组合索引，其中的列按照在创建主键时指定的顺序排列。
通过提供每个行的ROWID，类似于EMP_ID1和EMP_ID2的索引为Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的物理位置的指针。

技巧：
将Oracle的ROWID硬编码成特定代码时，一定要小心谨慎。因为不同版本的ROWID结构会有所不同，而且在将来的版本里可能还会有所改变。我建议不要对ROWID进行硬编码。
2.3  限制索引
限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。后文的各小节将讨论一些常见的问题。
Oracle优化器在后台工作，选择并使用可能最有效的数据检索方法。例如，在许多情况下不需要指定WHERE子句，从而Oracle可以使用索引。如果查询索引列的MIN或MAX值，Oracle将从索引(而不是表)中检索该值。同样，如果对索引列执行COUNT函数，Oracle可以使用索引而不是该列。在下面的小节中，您将看到WHERE子句的逻辑阻止Oracle使用索引的情况。
2.3.1  使用不等于运算符(<>、!=)
索引只能用于查找表中已有的数据。每当在WHERE子句中使用不等于运算符时，都将无法使用所引用的列的索引。请考虑下文对CUSTOMERS表的查询，CUSTOMERS表中的CUST_RATING列有一个索引。下面的语句仍会执行一次全表扫描(因为大多数记录都可以被检索到)，即使列CUST_RATING上存在索引。
select       cust_id,   cust_name
from         customers
where        cust_rating <> 'aa';
当分析表时，Oracle收集表中数据分布的相关统计信息。通过使用这种分析，基于成本的优化器就可以决定在WHERE子句中对一些值使用索引，而对其他的值不使用索引。在应用程序开发和测试期间，应该使用具有代表性的行集，从而可以模拟产品环境中实际的数据值分布。

技巧：
通过使用CREATE INDEX命令的COMPUTE STATISTICS子句，可以在一个步骤中创建索引并分析它们。也可以从产品数据库中导入统计信息以测试执行路径(参考10gR2 Database Performance Tuning Guide (Part Number 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       empno,  ename,  deptno
from          emp
where        sal is null;
如果要在上面的三列中禁用NULL值，可以在创建或修改表时使用NOT NULL。注意，如果表中已经包含数据，只有在表中每一行都有非NULL值或是使用ALTER TABLE命令的DEFAULT子句时，才可以为列设置NOT NULL属性。下面的程序清单显示了修改EMP表的Sal列以禁用NULL值：
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, first_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          employee;
EMPL_ID FIRST_NAME             LAST_NAME                   DEPTNO
---------- -------------------- -------------------- ----------
8100   REGINA                  NIEMIEC                         10
insert into employee
values (8200, 'RICH', 'NIEMIEC', NULL);
1 row created.
select       *
from         employee;
EMPL_ID FIRST_NAME              LAST_NAME                    DEPTNO
----------  -------------------- -------------------- ----------
8100   REGINA                   NIEMIEC                       10
8200   RICH                      NIEMIEC

技巧：
NULL值通常会限制索引。在创建表时对某一列指定NOT NULL或DEFAULT，对于避免可能出现的性能问题很有帮助。
2.3.3  使用函数
如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。一些常见的函数，如TRUNC、SUBSTR、TO_DATE、TO_CHAR和INSTR等，都能改变列的值。因此，无法使用已被函数引用的索引和列。下面的语句会执行一次全表扫描，即使hire_date列上存在索引(只要它不是基于函数的索引)。
select        empno, ename, deptno
from          emp
where         trunc(hiredate) = '01-MAY-01';
把上面的语句改成如下所示的语句，这样就可以通过索引进行查找。
select        empno, ename, deptno
from          emp
where         hiredate > '01-MAY-01'
and            hiredate < (TO_DATE('01-MAY-01') + 0.99999);

技巧：
通过改变所比较的列上的值，而不用改变列本身，就可以启用索引用。这样可避免全表扫描。
关于基于函数的索引的更多详情，可查看本章后面的“基于函数的索引”一节。
2.3.4  比较不匹配的数据类型
比较不匹配的数据类型也是比较难于发现的性能问题之一。Oracle并不会对那些不匹配的数据报错—— 事实正好相反。例如，Oracle可以隐式地把VARCHAR2列的数据转换成要被比较的数值型数据类型。考虑如下的示例，其中account_number就是一个VARCHAR2类型。
如果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
这样就限制了索引的使用。这个查询的EXPLAIN PLAN仅显示通过“全表扫描”访问这个表(对编程人员来说通常都很迷惑)。对一些DBA或开发人员来说，这样的情况可能很少见，但在很多系统中，数字型值可以用零填充，然后指定为VARCHAR2类型。前面的语句可以改写成如下语句，这样可以正确地对这个字段使用单引号，以使用账号上的索引。
select      bank_name, address, city, state, zip
from        banks
where       account_number = '000990354';
作为选择，可以定义Account_Number列使用NUMBER数据类型，前提是前置的0不是该列的关键信息。

技巧：
不匹配数据类型之间的比较会让Oracle自动限制索引的使用。即便对这个查询执行EXPLAIN PLAN也不能让您明白为什么做了一次“全表扫描”。只有了解关于数据类型的知识才能帮助您解决这个问题。
2.4  选择性
Oracle根据查询和数据，提供了多种方法来判断使用索引的价值。第一个方法就是判断索引中的唯一键或不同键的数量。可以对表或索引进行分析来确定不同键的数量。可以查询USER_INDEXES视图的Distinct_Keys列来研究分析的结果。比较一下唯一键的数量和表中的行数(如USER_INDEXES视图的Num_Rows列所示)，就可以判断索引的选择性。选择性越高，索引返回的行数就越少，该索引就越好。

技巧：
索引的选择性可以帮助基于成本的优化器来判断执行路径。索引的选择性越高，针对每个不同值返回的行数也越少。对于组合索引在索引中添加额外的列不会显著改善选择性，并且使用额外列的成本会超出收益。
2.5  集群因子(Clustering Factor)
集群因子是索引与它所基于的表相比较而得出的有序性度量，它用于检查在索引访问之后执行的表查找的成本(将集群因子与选择性相乘即可得到该操作的成本)。集群因子记录在扫描索引时将读取的块数量。如果使用的索引具有较大的集群因子，则必须访问更多的表数据块才可以获得每个索引块中的行(因为邻近行位于不同的块中)。如果集群因子接近于表中的块数量，则表示索引适当排序；但是，如果集群因子接近于表中的行数量，则表示索引没有适当排序。集群因子的计算简要介绍如下：
(1) 按顺序扫描索引。
(2) 将当前索引值指向的ROWID的块部分与以前的索引值进行比较(比较索引中的邻近行)。
(3) 如果ROWID指向不同的TABLE块，则增加集群因子(对整个索引执行该操作)。
Clustering_Factor列位于USER_INDEXES视图中，该列反映了数据相对于已索引的列是否显得有序。如果Clustering_Factor列的值接近于索引中的树叶块(leaf block)的数目，表中的数据就是有序的。索引的树叶块存储索引值以及它们指向的ROWID。
例如，CUSTOMERS表上Customer_Id列的值可以由序列生成器产生，而且是表CUSTOMERS上的主键。Customer_Id的索引的集群因子就有可能非常接近于树叶块数(表示有序)。当往数据库中添加客户数据时，它们就按照序列产生器所产生的序列值有序地存储在表中。然而，因为整个表的客户名字排列是随机的，所以customer_name上的索引会有一个很高的集群因子。
集群因子对执行范围扫描的SQL语句有一定的影响。如果集群因子很低(相对于树叶块的数量)，需要读取的表中块的数量就可以减少很多。这样也增加了相同的数据块已经存在于内存中的可能性。一个较高的集群因子(相对于树叶块的数量)会增加满足基于索引列的范围查询所需的数据块数目。

技巧：
可以使用表中数据的集群，这样可以提高执行范围扫描类型操作的语句的性能。如果要决定如何在语句中使用列，对列进行索引是最好的选择。
2.6  二元高度(binary height)
索引的二元高度对把ROWID返回给用户进程时所要求的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','EMP_ID1');
PL/SQL procedure successfully completed.
select      blevel, index_name
from        dba_indexes
where       index_name = 'EMP_ID1';
BLEVEL   INDEX_NAME
---------- ------------------------------
0   EMP_ID1

技巧：
对索引或者表进行分析可以得到索引的二元高度。使用USER_INDEXES视图里的blevel列可以检查所有索引的二元高度。
二元高度主要随着表中索引列的非NULL值数量以及索引列中值的范围狭窄程度而变化。索引上如果有大量被删除的行，它的二元高度也会增加。重建索引可能会降低二元高度。虽然这些步骤可以减少针对索引执行的I/O数量，但对性能的改进却很小。如果一个索引中被删除的行接近20％~25％，重建索引会降低二元高度以及在一次I/O中所读取的空闲空间。

技巧：
一般来说，数据库块尺寸越大，索引的二元高度就越低。二元高度中的每个额外级别(blevel)在DML操作期间会增加额外的性能成本。
2.7  使用直方图
在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，可以在表的任何列上构建直方图。
构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划：例如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。
要创建直方图，首先要确定好它的尺寸。该尺寸与直方图所需的存储桶(bucket)数相关。每个存储桶包含列值和行数的相关信息。
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)。同样如图中所示，多数宽度均衡的存储桶都只有3行记录；有一个存储桶却有73行记录。在高度均衡图中，每个存储桶有相同数目的行，多数存储桶的终点都是1430，这也反映了数据的偏斜分布。
Oracle的直方图是高度均衡的，而不是宽度均衡。也就是说，直方图里的所有存储桶都有相同的行数。存储桶的起点和终点取决于包含这些值的行数。宽度均衡的直方图则着重于确定每个存储桶的值的范围，然后统计出这个范围内的行数。这并不是一个理想的选择。

图2-2  构建在Company_Code字段上的直方图(具有10个存储桶)

技巧：
如果表中的数据分布得较不均匀，直方图会为基于成本的优化器提供一个数据分布的均衡图(把数据平均分布到各个存储桶)。在不是很偏斜的列上使用直方图并不会提高性能。

技巧：
默认情况时，Oracle的直方图会产生75个存储桶。可以把SIZE的值指定在1~254之间。
2.8  快速全局扫描
在索引的快速全局扫描期间，Oracle读取B树索引上的所有树叶块。这个索引可以按顺序读取，这样可以一次读取多个块。初始化文件中的DB_FILE_MULTIBLOCK _READ_COUNT参数可以控制同时被读取的块的数目。相比于全表扫描，快速全局扫描通常需要较少的物理I/O，并且允许更快速地处理查询。
如果表查询中的所有列都被包括在索引里，而索引的前置列并不在WHERE条件中，就可以使用快速全局扫描(必须指定第7章讲到的INDEX_FFS提示)。在下面的示例中用到了emp表。它有一个组合索引，包括列empno、ename和deptno。
select     empno, ename, deptno
from        emp
where      deptno = 30;
由于SQL语句中的所有列都包括在索引中，因此可以执行快速全局扫描。通常在只查询索引连接键列的连接期间执行索引快速全局扫描。作为选择，Oracle可能执行索引的跳跃式扫描访问；优化器应该考虑Deptno列的直方图(如果有可用的直方图)，并且确定哪个可用的访问路径可以产生最低的性能成本。

技巧：
如果索引相对于表的总体尺寸来说很小，快速全局扫描就可以使应用程序的性能陡增。如果表中有一个包含了大部分列的组合索引，索引可能要比真实的表要大，这样快速全局扫描反而会降低性能。
2.9  跳跃式扫描
本章前面的“组合索引”一节中介绍过，索引跳跃式扫描特性允许优化器使用组合索引，即便索引的第一列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描快得多，这是因为它只需要执行很少量的读取。例如，下面的查询显示了索引全扫描和跳跃扫描之间的区别。参考第6章，可以更好地了解什么是执行计划或后面的程序清单中列出的统计数据。在该程序清单中，EMP5有成百上千的行。
跟随查询的执行，该程序清单显示了查询花费的时间、它在数据库中的执行路径，以及显示处理该查询所需的逻辑读数量(一致的获取)和物理读数量的统计数据。
create index skip1 on emp5(job,empno);
Index created.
select count(*)
from     emp5
where    empno = 7900;
Elapsed: 00:00:03.13 (Result is a single row…not displayed)

Execution Plan
0           SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5)
1        0    SORT (AGGREGATE)
2        1      INDEX (FAST FULL SCAN) OF 'SKIP1' (NON-UNIQUE)
Statistics
6826    consistent gets
6819    physical reads
select /*+ index(emp5 skip1) */ count(*)
from     emp5
where    empno = 7900;
Elapsed: 00:00:00.56
Execution Plan
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)
1    0   SORT (AGGREGATE)
2    1     INDEX (SKIP SCAN) OF 'SKIP1' (NON-UNIQUE)
Statistics
21   consistent gets
17   physical reads
如同该程序清单所示，第二个选项使用INDEX (SKIP SCAN)操作读取索引。该执行路径需要21个逻辑读，这些逻辑读又需要17个物理I/O操作。第一个选项执行INDEX (FAST FULL SCAN)操作，该操作需要更多数量的逻辑和物理I/O。
为了让优化器选择跳跃式扫描，可能需要在查询中使用提示，如同该程序清单所示。提示影响了优化器，使其偏向您所指定的执行路径。

技巧：
对于那些有组合索引的大型表而言，索引跳跃式扫描特性可以提供一个快速访问，即使索引的第一列没有在限制条件中使用。
2.10  索引的类型
下面列出了本节要讨论的索引类型：
●       B树索引
●       位图索引
●       HASH索引
●       索引组织表索引
●       反转键(reverse key)索引
●       基于函数的索引
●       分区索引(本地和全局索引)
●       位图连接索引
2.10.1  B树索引
B树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。B树索引最多可以包括32列。
在图2-3的例子中，B树索引位于雇员表的last_name列上。这个索引的二元高度为3；接下来，Oracle会穿过两个树枝块(branch block)，到达包含有ROWID的树叶块。在每个树枝块中，树枝行包含链中下一个块的ID号。
树叶块包含了索引值、ROWID，以及指向前一个和后一个树叶块的指针。Oracle可以从两个方向遍历这个二叉树。B树索引保存了在索引列上有值的每个数据行的ROWID值。Oracle不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引，而其中列上包含NULL值，这一行就会处于包含NULL值的索引列中，且将被处理为空(视为NULL)。

图2-3  B树索引创建过程

技巧：
索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了I/O量。
2.10.2  位图索引
位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列，但通常它们都只用于少量的列。
例如，您的表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据Sex列的值查询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可用的位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。
下面的程序清单给出了一个创建位图索引的例子：
create bitmap index dept_idx2_bm on dept (deptno);
Index created.

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

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

技巧：
在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。
下面的查询可显示索引类型。B树索引作为NORMAL列出；而位图索引的类型值为BITMAP。
select index_name, index_type
from user_indexes;

技巧：
如果要查询位图索引列表，可以在USER _INDEXES视图中查询index_type列。
建议不要在一些联机事务处理(OLTP)应用程序中使用位图索引。B树索引的索引值中包含ROWID，这样Oracle就可以在行级别上锁定索引。位图索引存储为压缩的索引值，其中包含了一定范围的ROWID，因此Oracle必须针对一个给定值锁定所有范围内的ROWID。这种锁定类型可能在某些DML语句中造成死锁。SELECT语句不会受到这种锁定问题的影响。
位图索引有很多限制，如下所示：
●       基于规则的优化器不会考虑位图索引。
●       当执行ALTER TABLE语句并修改包含有位图索引的列时，会使位图索引失效。
●       位图索引不包含任何列数据，并且不能用于任何类型的完整性检查。
●       位图索引不能被声明为唯一索引。
●       位图索引的最大长度为30。

技巧：
不要在繁重的OLTP环境中使用位图索引
2.10.3  HASH索引
使用HASH索引必须要使用HASH集群。建立一个集群或HASH集群的同时，也就定义了一个集群键。这个键告诉Oracle如何在集群上存储表。在存储数据时，所有与这个集群键相关的行都被存储在一个数据库块上。如果数据都存储在同一个数据库块上，并且将HASH索引作为WHERE子句中的确切匹配，Oracle就可以通过执行一个HASH函数和I/O来访问数据—— 而通过使用一个二元高度为4的B树索引来访问数据，则需要在检索数据时使用4个I/O。如图2-5所示，其中的查询是一个等价查询，用于匹配HASH列和确切的值。Oracle可以快速使用该值，基于HASH函数确定行的物理存储位置。
HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH值)。这种冲突是非常消耗资源的。冲突会造成用来存储额外行的缓冲溢出，然后造成额外的I/O。如果不同HASH值的数目已经被低估，您就必须在重建这个集群之后改变这个值。ALTER CLUSTER命令不能改变HASH键的数目。
HASH集群还可能浪费空间。如果无法确定需要多少空间来维护某个集群键上的所有行，就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间，HASH集群可能就不是最好的选择。
如果应用程序经常在集群表上进行全表扫描，HASH集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量，全表扫描可能非常消耗资源。

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

技巧：
HASH索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。
2.10.4  索引组织表
索引组织表会把表的存储结构改成B树结构，以表的主键进行排序。这种特殊的表和其他类型的表一样，可以在表上执行所有的DML和DDL语句。由于表的特殊结构，ROWID并没有被关联到表的行上。
对于一些涉及精确匹配和范围搜索的语句，索引组织表提供了一种基于键的快速数据访问机制。基于主键值的UPDATE和DELETE语句的性能也同样得以提高，这是因为行在物理上有序。由于键列的值在表和索引中都没有重复，存储所需要的空间也随之减少。
如果不会频繁地根据主键列查询数据，则需要在索引组织表中的其他列上创建二级索引。不会频繁根据主键查询表的应用程序不会了解到使用索引组织表的全部优点。对于总是通过对主键的精确匹配或范围扫描进行访问的表，就需要考虑使用索引组织表。

技巧：
可以在索引组织表上建立二级索引。
2.10.5  反转键索引
当载入一些有序数据时，索引肯定会碰到与I/O相关的一些瓶颈。在数据载入期间，某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题，可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。
为了解决这个问题，Oracle还提供了一种反转键索引的方法。如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反。这样，数据1234、1235和1236就被存储成4321、5321和6321。结果就是索引会为每次新插入的行更新不同的索引块。
不能对位图索引和索引组织表进行反转键处理。

技巧：
如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。
不可以将反转键索引与位图索引或索引组织表结合使用。
2.10.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。
看一下使用基于函数的索引的巨大好处，考虑如下的示例，这个示例中有一个包含一百万行的SAMPLE表。
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.10.7  分区索引
分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。B树和位图索引都可以被分区，而HASH索引不可以被分区。可以有好几种分区方法：表被分区而索引未被分区；表未被分区而索引被分区；表和索引都被分区。不管采用哪种方法，都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性。
有两种类型的分区索引：本地分区索引和全局分区索引。每个类型都有两个子类型，有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引，就必须是本地索引。把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。
在使用分区后的表和索引时，Oracle还支持并行查询和并行DML(可以在第11章中看到更详细的内容)。这样就可以同时执行多个进程，从而加快处理这条语句。
1. 本地分区索引(通常使用的索引)
可以使用与表相同的分区键和范围界限来对本地索引分区。每个本地索引的分区只包含了它所关联的表分区的键和ROWID。本地索引可以是B树或位图索引。如果是B树索引，它可以是唯一或不唯一的索引。
这种类型的索引支持分区独立性，这就意味着对于单独的分区，可以进行增加、截取、删除、分割、脱机等处理，而不用同时删除或重建索引。Oracle自动维护这些本地索引。本地索引分区还可以被单独重建，而其他分区不会受到影响。
有前缀的索引
有前缀的索引包含了来自分区键的键，并把它们作为索引的前导。例如，让我们再次回顾participant表。在创建该表后，使用survey_id和survey_date这两个列进行范围分区，然后在survey_id列上建立一个有前缀的本地索引，如图2-6所示。这个索引的所有分区都被等价划分，就是说索引的分区都使用表的相同范围界限来创建。

技巧：
本地的有前缀索引可以让Oracle快速剔除一些不必要的分区。也就是说没有包含WHERE条件子句中任何值的分区将不会被访问，这样也提高了语句的性能。
无前缀的索引
无前缀的索引并没有把分区键的前导列作为索引的前导列。若使用有同样分区键(survey_id和survey_date)的相同分区表，建立在survey_date列上的索引就是一个本地的无前缀索引，如图2-7所示。可以在表的任一列上创建本地无前缀索引，但索引的每个分区只包含表的相应分区的键值。
        
图2-6  分区的、有前缀的索引                 图2-7  无前缀的本地索引
如果要把无前缀的索引设为唯一索引，这个索引就必须包含分区键的子集。在这个例子中，我们必须把包含survey和(或)survey_id的列进行组合(只要survey_id不是索引的第一列，它就是一个有前缀的索引)。

技巧：
对于一个唯一的无前缀索引，它必须包含分区键的子集。
2. 全局分区索引
全局分区索引在一个索引分区中包含来自多个表分区的键。一个全局分区索引的分区键是分区表中不同的或指定一个范围的值。在创建全局分区索引时，必须定义分区键的范围和值。全局索引只能是B树索引。Oracle在默认情况下不会维护全局分区索引。如果一个分区被截取、增加、分割、删除等，就必须重建全局分区索引，除非在修改表时指定ALTER TABLE命令的UPDATE GLOBAL INDEXES子句。
有前缀的索引
通常，全局有前缀索引在底层表中没有经过对等分区。没有什么因素能限制索引的对等分区，但Oracle在生成查询计划或执行分区维护操作时，并不会充分利用对等分区。如果索引被对等分区，就必须把它创建为一个本地索引，这样Oracle可以维护这个索引，并使用它来删除不必要的分区，如图2-8所示。在该图的3个索引分区中，每个分区都包含指向多个表分区中行的索引条目。

图2-8  分区的、全局有前缀索引

技巧：
如果一个全局索引将被对等分区，就必须把它创建为一个本地索引，这样Oracle可以维护这个索引，并使用它来删除不必要的分区。
无前缀的索引
Oracle不支持无前缀的全局索引。
2.10.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条件的行。
位图连接索引的使用一般会受到限制：只可以索引维度表中的列。用于连接的列必须是维度表中的主键或唯一约束；如果是复合主键，则必须使用连接中的每一列。不可以对索引组织表创建位图连接索引，并且适用于常规位图索引的限制也适用于位图连接索引。
2.11  快速重建索引
执行ALTER INDEX语句中的REBUILD选项，可以使用已有索引而不是整个表快速重建索引：
alter   index   cust_idx1 rebuild parallel
tablespace    cust_tblspc1
storage (pctincrease 0);
Index altered.
我们可以同时修改STORAGE子句，并且可以使用parallel选项。

技巧：
利用ALTER INDEX语句中的REBUILD选项，可以使用已有索引而不是表来快速重建索引。在执行这个操作时必须要有足够的空间来保存所有的索引。

技巧：
还可以在重建索引时使用REBUILD ONLINE选项，以允许对表或分区进行DML操作。但不能对位图索引或那些强制参照完整性约束的索引指定REBUILD ONLINE。
2.12  技巧回顾
●       在升级Oracle版本时，确保测试应用程序的查询以确定查询的执行路径是否仍然使用在升级之前使用的索引。查看执行路径是否改变，并且查看这种改动的效果是更好还是更差。
●       查询DBA_INDEXES和DBA_IND_COLUMNS以检索一个表的索引列表。使用USER_INDEXES和USER_IND_COLUMNS检索唯一模式的信息。
●       避免将Oracle的ROWID硬编码为特定编码。不同版本的ROWID结构会有所不同，而且可能在将来的版本里还会有所改变。我建议不要对ROWID进行硬编码。
●       通过使用CREATE INDEX命令的COMPUTE STATISTICS子句，可以在一个步骤中创建并分析索引。
●       对表的列使用默认值子句，这样就可以禁止使用NULL值，并且消除与使用NULL值关联的性能问题。
●       通过使用函数(例如TO_DATE或TO_CHAR)修改所比较的列值而不是列本身，就可以使用索引，在对列本身使用函数时就会限制使用这些索引。
●       比较不匹配的数据类型可能会导致Oracle内部限制对索引的使用。即使对查询使用EXPLAIN PLAN也不能解释为什么会执行全表扫描。
●       索引的选择性可以帮助基于成本的优化器判断一条执行路径。索引选择性越高，返回的行数就越少。可以通过建立组合/复合(多列)索引来提高选择性。
●       通常，数据库块越大，索引的二元高度越小。
●       blevel中的每个额外级别都会额外增加DML的执行成本。
●       表中的数据集群可以用来提高执行范围扫描类型操作的语句的执行性能。通过判断语句中是如何使用列的，可以在对列进行索引时提供很多好处。
●       对索引或表进行分析可以得到索引的二元高度。使用USER_INDEXES视图中的blevel列可以检查索引的二元高度。
●       如果索引中被删除的行数达到了20％~25％，就必须重建索引，这样可以减少二元高度和在一次I/O过程中读取的空闲空间量。
●       如果表中的数据是偏斜的，直方图可以为基于成本的优化器提供一个分布图。在那些不偏斜的列上使用直方图并不会提高性能，反而可能降低性能。
●       默认情况下，Oracle在一个直方图里创建了75个存储桶。这个数目可以指定为1~254。
●       对于有组合索引的大型表来说，索引跳跃式扫描特性可以进行快速访问，即使是索引的前导列没有出现用在限制条件中。
●       已索引列的值存储在索引中。因此，您可以建立一个组合(复合)索引，这样查询可以直接访问这些索引，而不必从表中检索数据，同时也就减少了I/O。
●       对于低基数的列可以使用位图索引。性别列就是一个典型的例子，性别只能是男或女(基数只为2)。
●       查询USER_INDEXES视图可以查询位图索引列表。
●       不要在高OLTP的环境中使用位图索引；一定要记住位图索引的限制。
●       在实现HASH集群之前一定要记住相关的注意事项。您需要仔细地检查应用程序，保证在实现这个选项之前已经知道尽可能多的关于表和数据的信息。通常，HASH选项对于一些包含较有序的值的静态数据非常有效。
●       HASH索引在限制条件指定了一个确定值时而不是一个范围的值时最为有用。
●       考虑对那些经常通过在主键上指定值或范围扫描来访问数据的表使用索引组织表。
●       如果只有有限的磁盘数，同时还有大量的载入操作要执行，反转键索引就是可行的方案。
●       对于优化器中所使用的基于函数的索引，必须把初始参数QUERY _ REWRITE_ ENABLED设为TRUE(默认值为FALSE)。
●       有前缀的本地索引可以帮助Oracle快速剔除不必要的分区。若分区没有包含任何一个WHERE子句中所使用的值，就可以不用访问分区，这样提高了语句的执行性能。
●       要使无前缀索引唯一，它必须包含分区键的子集。
●       在修改分区表时指定ALTER TABLE命令的UPDATE GLOBAL INDEXES子句。默认情况下，在改变分区表时需要重建全局索引。
●       如果一个全局索引将被对等分区，必须把它创建为本地索引，这样Oracle可以维护这个索引，并使用它来删除不必要的分区。
●       使用位图连接索引改进数据仓库环境中连接的性能。
●       在ALTER INDEX语句中使用REBUILD选项可以使用已有索引而不是表来快速重建索引。
●       可以在重建索引时使用REBUILD ONLINE选项，以允许对表或分区进行DML操作。但不能在位图索引或那些强制参照完整性约束的索引上指定REBUILD ONLINE选项。

评论

1 楼 yanghoho6 2013-07-24  

很好， 学习了，