9i10g11g编程艺术——索引

来源：互联网发布：2016淘宝客推广编辑：程序博客网时间：2024/05/22 02:16

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;

---------------------------------------------------------------------------

---------------------------------------------------------------------------

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

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

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

| 5 | BITMAP OR | | | | | |

-----------------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

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

| 5 | BITMAP OR | | | | | |

| 6 | BITMAP AND | | | | | |

| 9 | BITMAP AND | | | | | |

------------------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("EMP"."SYS_NC00009$"='SALES')

我们还能避免访问DEPT表，使用EMP上的索引就能从DEPT合并我们需要的数据，直接访问我们所需的行。