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合并我们需要的数据,直接访问我们所需的行。