9i10g11g编程艺术——索引

索引"/>

9i10g11g编程艺术——索引

1、B*树索引

如果在一个数字列上有一个索引，那么从概念上来讲这个结构可能如图：
B*树索引中不存在非唯一条目。在一个非唯一索引中，oracle会把rowid作为一个额外的列追加到键上，使得键唯一。例如，如果有一个CREATE INDEX I ON T(X,Y)索引，从概念上讲，它就是CREATE UNIQUE INDEX I ON T(X,Y,ROWID)。在一个唯一索引中，根据你定义的唯一性，oracle不会再向索引键增加rowid。在非唯一索引中，你会发现，数据会首先按索引键值排序（依索引键的顺序），然后按rowid升序排序。而在唯一索引中，数据只按索引键值排序。
1、索引键压缩 反复创建同一个索引（create index t_idx on t(owner,object_type,object_name)）四次，分别使用不压缩、compress 1、compress 2、compress 3来创建，然后分析索引，查看分析数据： u1@ORCL> select what, height, lf_blks, br_blks, 2         btree_space, opt_cmpr_count, opt_cmpr_pctsave 3    from idx_stats 4  /
WHAT              HEIGHT    LF_BLKS    BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE ------------- ---------- ---------- ---------- ----------- -------------- ---------------- noncompressed          3        351          3     2830680              2               28 compress 1             3        314          3     2533572              2               19 compress 2             2        253          1     2030004              2                0 compress 3             3        393          3     3164940              2               35
可以看到，compress 1索引的大小大约是无压缩索引的89%（通过比较BTREE_SPACE得出）。叶子块数大幅下降。更进一步，使用compress 2时，节省的幅度更为显著。所得到的索引大约是无压缩索引的72%，而且由于数量减少，这些数据能放在单个的块上，相应的索引的高度就从3降为2。实际上，利用列OPT_CMPR_PCTSAVE（这代表最优的节省压缩百分比或期望从压缩得到的节省幅度）的信息，我们可以猜测出compress 2索引的大小； u1@ORCL> select 2830680*(1-0.28) from dual;
2830680*(1-0.28) ---------------- 2038089.6
不过，再看看compress 3会怎么样。如果压缩3列，所得到的索引实际上会更大；是原来索引大小的110%。这是因为：每删除一个重复的前缀，能节省N个副本的空间，但是作为压缩机制的一部分，这回在叶子块上增加4字节的开销。把object_name列增加到压缩键后，则使得这个键几乎是唯一的。在这种情况下，则说明没有重复的副本可以提取。因此，最后的结果是：我们只是向每个索引键条目增加了4个字节，而不能提取出任何重复的数据。IDX_STATS中的OPT_CMPR_COUNT列真是精确无比，确实给出了可用的最佳压缩数，OPT_CMPR_PCTSAVE则指出了可以得到多大的节省幅度。
这种压缩不是免费的。这里需要做的是对增加的CPU时间和减少的IO时间作出权衡。还会增加块竞争的可能性。如果你现在已经在大量用CPU时间，再增加压缩索引只能适得其反，这回减慢处理的速度。另一方面，如果目前的IO操作很多，使用压缩索引就能加快处理速度。
2、反向键索引 反向键索引主要用于缓解忙索引右侧的缓冲区忙等待。反向键索引的缺点之一是：能用常规索引的地方不一定能用反向键索引。例如，在回答以下谓词时，X上的反向索引就没用： where X > 5  存储之前，数据不是按X在索引中排序，而是按REVERSE(X)排序，因此，对X>5的区间扫描不能所用这个索引。另一方面，有些区间扫描确实可以在反向键索引上完成。如果是(X,Y)上有一个串联索引，以下谓词就能够利用反向键索引，并对它执行区间扫描： where X = 5  这是因为，首先将X的字节反转，然后再将Y的字节反转。oracle并不是将(X||Y)的字节反转，而是会存储(REVERSE(X) || REVERSE(Y))，这说明，X=5的所有值会存储在一起，所以oracle可以对这个索引执行区间扫描来找到所有这些数据。
3、降序索引 u1@ORCL> create index t_idx on t(owner,object_type,object_name);
索引已创建。
u1@ORCL> select owner, object_type from t 2   where owner between 'T' and 'Z' and object_type is not null 3   order by owner DESC, object_type ASC; --------------------------------------------------------------------------- | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |       |  3328 | 93184 |    30   (4)| 00:00:01 | |   1 |   SORT ORDER BY |       |  3328 | 93184 |    30   (4)| 00:00:01 | |*  2 |   INDEX RANGE SCAN| T_IDX |  3328 | 93184 |    29   (0)| 00:00:01 | ---------------------------------------------------------------------------
可以看见上面sql其中一些列是按升序排序(ASC)，另外一些列按降序排序(DESC)，此时这种降序索引就能派上用场了，例如： u1@ORCL> create index desc_t_idx on t(owner desc,object_type asc);
索引已创建。
u1@ORCL> select owner, object_type from t 2   where owner between 'T' and 'Z' and object_type is not null 3   order by owner DESC, object_type ASC; ------------------------------------------------------------------------------- | Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |            |  3328 | 93184 |     3   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| DESC_T_IDX |  3328 | 93184 |     3   (0)| 00:00:01 | -------------------------------------------------------------------------------
4、什么情况下应该使用B*树索引 访问表中的行：通过读取索引来访问表中的行。此时你希望访问表中很少的一部分（只占一个很小的百分比）。 u1@ORCL> select owner, status from t where owner = USER; ------------------------------------------------------------------------------------- | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |       |    11 |   242 |    12   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T     |    11 |   242 |    12   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | T_IDX |    11 |       |     3   (0)| 00:00:01 | -------------------------------------------------------------------------------------
回答一个查询：索引包含了足够的信息来回答整个查询，我们根本不用去访问表。在这种情况下，索引则用作一个较瘦版本的表。 u1@ORCL> select count(*) from t where owner = user;
--------------------------------------------------------------------------- | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |       |     1 |    17 |     3   (0)| 00:00:01 | |   1 |  SORT AGGREGATE   |       |     1 |    17 |            |          | |*  2 |   INDEX RANGE SCAN| T_IDX |    11 |   187 |     3   (0)| 00:00:01 | ---------------------------------------------------------------------------
在此，只使用了索引来回答查询，现在访问多少行都没有关系，因为我们只会使用索引。重要的是，要了解两个概念之间的区别。如果必须完成TABLE ACCESS BY INDEX ROWID，就必须确保只访问表中很少的一部分行。对于第二种类型的查询，即答案完全可以在索引中找到，情况就完全不同了。 u1@ORCL> select count(*) from t;
---------------------------------------------------------------------------- | Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |            |     1 |    50   (2)| 00:00:01 | |   1 |  SORT AGGREGATE       |            |     1 |            |          | |   2 |   INDEX FAST FULL SCAN| DESC_T_IDX | 65114 |    50   (2)| 00:00:01 | ----------------------------------------------------------------------------
上面在索引上执行了一个快速全面扫描，从而更快的回答这类查询。快速全面扫描是指，此时索引更像是一个表。如果采用快速全面扫描，将不再按索引条目的顺序来得到行。
下面来看这样一个简化的例子，假设我们通过索引读取一个瘦表，而且要读取表中20%的行。若这个表中有100 000行，其中20%就是20 000行。如果行大小大约是80字节，在一个块大小为8KB的数据库中，每个块上则有大约100行。这说明，这个表大约1000个块。了解了这些情况，计算起来就非常容易了。我们要通过索引读取20 000行：这说明，大约是20 000个TABLE ACCESS BY INDEX ROWID操作。谓为此要处理20 000个表块来执行这个查询。不过整个表才只有大约1000个块！最后会把表中的每一个块平均读取和处理20次。即使把行的大小提高一个数量级，达到每行800字节，这样每块有10行，现在表中就有10 000个块。要通过索引访问20 000行，仍要求我们把没一个块平均读取2次。在这种情况下，全表扫描就比使用索引高效得多，因为每个块只会命中一次。如果查询使用这个索引来访问数据，效率都不会高，除非对于800字节的行，平均只访问表中不到5%的数据（这样一来，就只会访问大约5000个块），如果是80字节的行，则访问的数据应当只占更小的百分比（大约0.5%或更少）。
物理组织 首先我们创建一个表，这个表主要安其主键排序: u1@ORCL> create table colocated ( x int, y varchar2(80) );
表已创建。
u1@ORCL> begin 2      for i in 1 .. 100000 3      loop 4          insert into colocated(x,y) 5          values (i, rpad( dbms_random.random,75,'*') ); 6      end loop; 7  end; 8  /
PL/SQL 过程已成功完成。
u1@ORCL> alter table colocated add constraint colocated_pk primary key(x);
表已更改。
u1@ORCL> exec dbms_stats.gather_table_stats( user, 'COLOCATED');
PL/SQL 过程已成功完成。
这个表正好满足前面的描述，即在块大小为8KB的一个数据库中，每块有大约100行。仍取这个表，但有意使它“无组织”。在colocated表中，我们创建了一个Y列，它带有一个前导随机数，现在利用这一点使得数据无组织，即不再按主键排序： u1@ORCL> create table disorganized as select x,y from colocated order by y;
表已创建。
u1@ORCL> alter table disorganized add constraint disorganized_pk primary key (x);
表已更改。
u1@ORCL> exec dbms_stats.gather_table_stats( user, 'DISORGANIZED');
PL/SQL 过程已成功完成。
可以证明，这两个表是一样的，但这两个表的性能缺有着天壤之别。

2、基于函数的索引

只对部分行建立索引 考虑有一个很大的表，其中有一个NOT NULL列，名为temporary，它有两个可取值：Y或N，默认值为N。增加新行时，这个值为N，指示这一行未得到处理，等到处理了这一行后，则会将其更新为Y来指示已处理。我们可能想对这个列建立索引，从而能快速的获取值为N的记录，但是这里有数百万行，而且几乎所有行的值都为Y。所得到的B*树索引将会很大，如果我们把值从N更新为Y，维护这样一个大索引的开销也相当高。 u1@ORCL> select temporary, cnt, 2         round( (ratio_to_report(cnt) over ()) * 100, 2 ) rtr 3    from ( 4  select temporary, count(*) cnt 5    from big_table 6   group by temporary 7         ) 8  /
T        CNT        RTR - ---------- ---------- Y     997647      99.76 N       2353        .24
u1@ORCL> create index processed_flag_idx on big_table(temporary); 索引已创建。
u1@ORCL> analyze index processed_flag_idx validate structure; 索引已分析
u1@ORCL> select name, btree_space, lf_rows, height from index_stats; NAME                           BTREE_SPACE    LF_ROWS     HEIGHT ------------------------------ ----------- ---------- ---------- PROCESSED_FLAG_IDX                14528892    1000000          3
可以看到，在表的1 000 000条记录中，只有0.24%的数据应当加索引。如果使用传统索引，会发现这个索引有1 000 000个条目，占用了差不多14MB的空间，高度为3。通过这个索引获取任何数据都会带来3个IO才能到达叶子块。这个索引不仅很宽，还很高。要得到第一个未处理的记录，必须至少执行4个IO。 怎么改变这种情况呢？我们要让索引更小一些，而且要更易于维护。其实，我们真正想做的是，只对感兴趣的记录建立索引（即该列值为N的记录）。我们可以通过使用函数索引来做得这一点，我们可以编写一个函数，如果不想对某个给定行加索引，则这个函数就返回NULL。B*索引对于完全为NULL的键没有相应的条目。 u1@ORCL> drop index processed_flag_idx; 索引已删除。
u1@ORCL> create index processed_flag_idx on big_table( case temporary when 'N' then 'N' end ); 索引已创建。
u1@ORCL> analyze index processed_flag_idx validate structure; 索引已分析
u1@ORCL> select name, btree_space, lf_rows, height from index_stats; NAME                           BTREE_SPACE    LF_ROWS     HEIGHT ------------------------------ ----------- ---------- ---------- PROCESSED_FLAG_IDX                   48008       2353          2
这就有很大不同，这个索引只有大约40KB，而不是14MB。高度也有所降低。与先前那个更高的索引相比，使用这个能少执行一个IO。

3、位图索引

在B*树索引结构中，索引键和表中的行存在着对应关系。在位图索引中，可能只有很少的索引条目，每个索引条目指向多行。而在传统的B*树索引中，一个索引条目就指向一行。

表11-6显示了第8、10和13行的值为ANALYST，而第4、6和7行的值为MANAGER。在此还显示了所有行都不为null（位图索引可以存储null条目；如果索引中没有null条目，这说明表中没有null行）。如果我们想统计值为MANAGER的行数，位图索引就能很快的完成这个任务。如果我们想找出JOB为CLERK或MANAGER的所有行，只需要根据索引合并它们的位图，如表11-7所示。

表11-7清楚的显示出，第1、4、6、7、11、12和14行满足我们的要求。oracle为每个键值存储位图，使得每个位置表示底层表中的一个rowid。
什么情况下应该使用位图索引 u1@ORCL> Select count(*) 2    from T 3   where gender = 'M' 4     and location in (1, 10, 30) 5     and age_group = '41 and over';
----------------------------------------------------------------------------------------------- | Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |               |     1 |    13 |     5   (0)| 00:00:01 | |   1 |  SORT AGGREGATE               |               |     1 |    13 |            |          | |   2 |   BITMAP CONVERSION COUNT     |               |     1 |    13 |     5   (0)| 00:00:01 | |   3 |    BITMAP AND                 |               |       |       |            |          | |*  4 |     BITMAP INDEX SINGLE VALUE | GENDER_IDX    |       |       |            |          | |   5 |     BITMAP OR                 |               |       |       |            |          | |*  6 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          | |*  7 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          | |*  8 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          | |*  9 |     BITMAP INDEX SINGLE VALUE | AGE_GROUP_IDX |       |       |            |          | -----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("GENDER"='M') 6 - access("LOCATION"=1) 7 - access("LOCATION"=10) 8 - access("LOCATION"=30) 9 - access("AGE_GROUP"='41 and over')
这个例子展示出了位图索引的强大能力。oracle能看到location in (1, 10, 30)，知道要读取这3个位置上的索引，并能在位图中对这些“位”执行逻辑OR。然后将得到的位图与gender = 'M'和age_group = '41 and over'的相应位图执行逻辑AND。再统计“1”的个数，这就得到了答案。
u1@ORCL> select /*+ index(t) */ * from t 2   where ((gender = 'M' and location = 20) or 3         (gender = 'F' and location = 22)) 4     and age_group = '18 and under';
------------------------------------------------------------------------------------------------ | Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT               |               |   246 |  8118 |   105   (0)| 00:00:02 | |   1 |  TABLE ACCESS BY INDEX ROWID   | T             |   246 |  8118 |   105   (0)| 00:00:02 | |   2 |   BITMAP CONVERSION TO ROWIDS  |               |       |       |            |          | |   3 |    BITMAP AND                  |               |       |       |            |          | |*  4 |     BITMAP INDEX SINGLE VALUE  | AGE_GROUP_IDX |       |       |            |          | |   5 |     BITMAP OR                  |               |       |       |            |          | |   6 |      BITMAP AND                |               |       |       |            |          | |*  7 |       BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          | |*  8 |       BITMAP INDEX SINGLE VALUE| GENDER_IDX    |       |       |            |          | |   9 |      BITMAP AND                |               |       |       |            |          | |* 10 |       BITMAP INDEX SINGLE VALUE| GENDER_IDX    |       |       |            |          | |* 11 |       BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          | ------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("AGE_GROUP"='18 and under') 7 - access("LOCATION"=22) 8 - access("GENDER"='F') 10 - access("GENDER"='M') 11 - access("LOCATION"=20)
有计划显示：这里执行逻辑OR的两个条件是通过AND适当位图逻辑计算得到的，然后再对这些结果执行逻辑OR得到一个位图。再加上另一个AND条件（以满足 age_group = '18 and under'），我们就找到了满足所有条件的结果。由于这一次要请求具体的行，所以oracle会把位图中的各个“1”和“0”转换为rowid，来获取源数据。
位图连接索引 位图联结索引确实有一个先决条件。联结条件必须联结到另一个表中的主键或唯一键。 通常都是在一个表上创建索引，而且只使用这个表的列。位图联结索引则打破了这个规则，它允许使用另外某个表的列对一个给定表建立索引。 u1@ORCL> create bitmap index emp_bm_idx 2  on emp( d.dname ) 3  from emp e, dept d 4  where e.deptno = d.deptno 5  /
索引已创建。
使用传统索引的话，以下查询中DEPT表和EMP表都必须访问。我可以使用DEPT.NAME上的一个索引来查找SALES行，并获取SALES的DEPTNO值，然后使用EMP.DEPTNO上的一个索引来查找匹配的行，但是如果使用一个位图联结索引，就不需要这些了。利用位图联结索引，我们能对DEPT.NAME列建立索引，但这个索引不是指向DEPT表，而是指向EMP表。 u1@ORCL> select count(*) 2    from emp, dept 3   where emp.deptno = dept.deptno 4     and dept.dname = 'SALES';
------------------------------------------------------------------------------------------ | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT            |            |     1 |    13 |     1   (0)| 00:00:01 | |   1 |  SORT AGGREGATE             |            |     1 |    13 |            |          | |   2 |   BITMAP CONVERSION COUNT   |            | 10000 |   126K|     1   (0)| 00:00:01 | |*  3 |    BITMAP INDEX SINGLE VALUE| EMP_BM_IDX |       |       |            |          | ------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("EMP"."SYS_NC00009$"='SALES')
可以看到，要回答这个特定的问题，我们不必真正去访问EMP表或DEPT表，答案全部来自索引本身。 u1@ORCL> select /*+ index(emp) */ emp.* 2    from emp, dept 3   where emp.deptno = dept.deptno 4     and dept.dname = 'SALES';
------------------------------------------------------------------------------------------- | Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |            | 10000 |   849K|   131K  (1)| 00:26:20 | |   1 |  TABLE ACCESS BY INDEX ROWID | EMP        | 10000 |   849K|   131K  (1)| 00:26:20 | |   2 |   BITMAP CONVERSION TO ROWIDS|            |       |       |            |          | |*  3 |    BITMAP INDEX SINGLE VALUE | EMP_BM_IDX |       |       |            |          | -------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("EMP"."SYS_NC00009$"='SALES')
我们还能避免访问DEPT表，使用EMP上的索引就能从DEPT合并我们需要的数据，直接访问我们所需的行。