第04章 数据库对象
所以只有经常作为查询条件的字段才建立索引。
4.2.2 索引分类
索引的类型有：按索引建立的对象有表索引、簇索引(在簇中介绍)； 按索引的结构有B树和位图索引。按索引内容有函数索引和反转索引。 1.B树索引
B树及改进的B+树和B*树，Oracle是采用的B*树，也是Oracle默认的索引类型。 B树索引结构示意如下：
B树索引的组成：
(1)根块：索引顶级块，包含下级节点(分支块)的入口信息，即索引字段值的分段和对应下级节点入口地址；
(2)分支块：中间节点块，包含下级节点(分支块或叶块)的入口信息，即索引字段值子分段和对应下级节点入口地址；
(3)叶块：末节点块，包含表行的入口信息，即索引列值和对应的rowid。 B树索引的查找：
B树的查询是二分查询，根据查询条件值，判断其所在左右子树，迅速查找其叶块，取得满足条件行的rowid。影响查询速度是B树的深度和宽度。
B树索引的特点：
(1)适用于大量异值列的大表，列的基数较高，即大部分值都不相同的字段； (2)可以维持平衡性(深度和宽度)；当表的数据增长时，有一定的稳定性； (3)可以采用多列索引(组合索引、连接索引)，可以升序(ASC)或降序(DESC)；
- 21 -
A Q P Z A G F P A 1 P 2 G 3 P 4 Q 5 S 6 Q T S Z T 7 Z 8 第04章 数据库对象
(4)可以生成反转索引(REVERSE)和唯一索引(UNIQUE)。 B树索引的缺点：
(1)不适合列的基数较低的索引；少于200个记录没有必要建立B树索引。 (2)不支持函数查询。如SUBSTR()等函数条件查询。 2.位图索引
当列值基数小，都在一个较小的集合内，如性别只有’男’、’女’，婚否只有’yes’、’no’，不适合建立B树索引，可以使用位图(BITMAP)索引。
位图索引的结构：位图索引是一个二维数组，行对应表的记录行，列对应索引字段的取值位图和ROWID。如员工表staff中如下：其中是否退休retire(Y、N)，性别sex(M[ale]、F[emale])
ROWID 1 2 3 4 STAFF_NO 202 203 302 302 SEX M F M F RETIRE Y Y N N 按SEX和RETIRE建立的位图索引结构示意如下：
ROWID 1 2 3 4 M 1 0 1 0 F Y N 0 1 0 1 1 0 0 0 1 1 0 1 位图索引的查找：
SQL>SELECT * FROM staff WHERE sex=’M’;
当查询男性M时，只需查询M=0的rowid。如果是多个位图字段的查询，如： SQL>SELECT * FROM staff WHERE sex=’M’ and retire=’Y’; 则需要对(M=1) and (Y=1)的逻辑运算。 位图索引的特点：
(1)占用空间较少，查询的速度快；如果几个列建立了位图索引，有专门的合并算法； (2)适合于基数少的列建立索引；
- 22 -
第04章 数据库对象
(3)适合于静态数据，如数据仓库的数据。 3.函数索引
如果建立索引的字段，在查询条件子句中使用了函数，则不会使用索引。如果经常使用字段的函数值作为查询条件，可以建立函数索引。函数索引的结构是B树索引。
函数索引的限制是：
(1)不能使用聚合函数，如SUM、AVG等；
(2)函数的返回值是可重复的，不能使用类似SYSDATE、USER等返回固定值的函数， 4.反转索引
索引字段值分布在一个比较密集的范围内，并且是顺序递增或递减，如果建立B树，则会使树的左右子树不均衡，导致树的层次急剧增加。可以使用反转索引。
反转索引是将索引字段值反向后建立的索引。反转索引结构还是B树结构，而位图索引不能使用反转索引。
4.2.3 建立索引
建立索引的要素有：
(1)索引所依附的表？小表不需要建立索引，除了定义主键自动建立的索引外。 (2)索引字段？只有经常用作查询条件的字段才需要建立索引。 (3)索引的名字？标识表和字段。 (4)建立什么类型的索引？ (5)索引存放的表空间？
除了定义主键和唯一键时，自动建立索引外，可以手工建立索引，其语法如下： CREATE [UNIQUE|BITMAP] INDEX [schema.]indexname ON [user.]tablename
({column[ASC|DESC]|column_expression},...) [CLUSTER [user.]cluster] [TABLESPACE tablespace]
[PCTFREE n] [INITRANS n] [MAXTRANS n] [STORAGE storage]
- 23 -
第04章 数据库对象
[REVERSE] [NOSORT]
//翻转索引
//已经排序，无需重新排序 //日志不记录 //在线建立 //产生统计信息
[LOGGING|NOLOGGING] [ONLINE]
[COMPUTE STATISTICS] 说明：
(1)UNIQUE是唯一索引，是B树索引，要求索引字段的值唯一。一般不单独建立唯一索引，而是在定义字段的唯一性约束自动建立。
(2)BITMAP是位图索引，位图索引不能和UNIQUE同时选。默认的为B树索引。 (3)indexname是建立索引的名字 (4)tablename是索引依附的表名
(5)column是索引字段，索引字段可以是多个字段，称为组合索引。位图索引最多30列，B树索引最多32列。
(6)ASC按升序建立索引(默认)，DESC按降序建立索引。 (7)column_expression是建立函数索引的表达式。 (8)cluster指明索引簇，关于簇索引，在”簇”一节中介绍。
(9)tablespace是索引存放的表空间，一般索引与表保存在不同的表空间。 (10)块控制参数中，不能指定PCTUSED参数。 (11)STORAGE指定存储参数。
(12)REVERSE建立反转索引，反转索引只能是B树索引，不能是位图索引。
(13)NOSORT数据行已经排好序，在建立索引时不必排序，可以节约索引建立时间，但不能和反转索引联用。
(14)LOGGING|NOLOGGING创建索引操作是否记录日志中。LOGGING是缺省方式。 (15)ONLINE在线索引，在建立索引时，允许进行对表进行DML操作。
(16)COMPUTE STATISTICS收集统计信息，以便优化程序选择执行计划。BITMAP、REVERSE、COMPUTE STATISTICS不能在线建立。
- 24 -
第04章 数据库对象
建立索引举例： 针对下表：
CREATE TABLE teacher(
id name sex age dno
NUMBER(20) VARCHAR2(10) CHAR(2) number(3), NUMBER(10), char(1)
CHECK(state IN (‘I’,’O’)),
NOT NULL, NOT NULL, DEFAULT ‘男’,
state
CONSTRAINT teacher_id_pk PRIMARY KEY(id) USING INDEX TABLESPACE indx )TABLESPACE USERS;
例1：建立B树索引：单列索引，指明表空间
CREATE INDEX teacher_name_index ON teacher(name)
TABLESPACE indx; 说明：
(1)索引名为：表名_字段名_index； (2)索引表空间一般与表所在表空间分离。 例2：建立B树索引的多索引字段组合索引
CREATE INDEX teacher_name_dept_index ON teacher(dept_id,name)
TABLESPACE indx; 例3：建立反转索引
CREATE INDEX teacher_age_index ON teacher(age)
TABLESPACE indx REVERSE; 例4：建立位图索引
CREATE BITMAP INDEX teacher_state_index ON teacher(state)
- 25 -