0%

oracle 11g 的索引

发表于 分类于 阅读次数： Valine：
本文字数： 2.6k 阅读时长 ≈ 2 分钟

1. 含义

索引是与表相关的一个可选结构，索引可用以提高 SQL 语句执行的性能，并较少磁盘 I/O。索引在逻辑上和物理上都独立于表的数据，oaracle 会自动维护索引。

2.分类

索引大体上可以分为：B树索引（平衡树索引）、位图索引。其中 B 树索引分为：唯一索引、组合索引、反向键索引、基于函数的索引。

3. 创建和分析

3.1 创建标准索引

首先创建一张普通表，具体 sql 如下所示：

1
2
3
4
create table student(
    sno number(11) not null,
    sname varchar2(50)
);

为 student 表的学号 sno 创建普通标准索引，具体如下：

1
create index stu_sno_idx on student(sno) [tablespace default]; -- 表空间可选

3.2 分析索引

索引只有经过分析之后，才能准确的查看索引的信息。因为对表的每次操作，oracle 都会自动维护索引。也就是索引的结构等信息都会变化。为了查看准确的索引信息，那么就需要索引进行分析。索引分析语法如下：

1
analyze index <index_name> validate structure;

分析完索引之后，可以查看index_stats表中的pct_used列的值，如果pct_used的值过低，说明在索引中存在碎片，可以重建索引，来提高pct_used的值，减少索引中的碎片。pct_used 一般最高值是 90%。

1
2
-- 查询名称为 STU_SNO_IDX 的索引的 pct_used 使用率
select t.pct_used from index_stats where name = 'STU_SNO_IDX';

4. 唯一索引

唯一索引确保在定义索引的列中没有重复值，Oracle 自动在表的主键列上创建唯一索引。使用CREATE UNIQUE INDEX语句创建唯一索引。同样的，我们在上述的 student 表创建唯一索引，具体如下所示：

1
create unique index stu_sno_idx on student(sno);

由于我们在 sno 列创建的是唯一索引，所以第三条 insert 是无法执行成功的：

1
2
3
insert into student values(1, 'lisi');
insert into student values(2, 'wangwu');
insert into student values(2, 'zhaoliu'); -- 插入失败

5. 组合索引

组合索引是在表的多个列上创建的索引，索引中列的顺序是任意的。如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度。创建示例如下：

1
2
-- 使用 sno 和 sname 创建组合索引
create index stu_sno_sname_idx on student(sno, sname);

6. 反向键索引

举个例子来说明反向键索引的用途。比如现在 student 表的学号列的值一般都是 16251104101，16251104102，16251104103…也就是前面大部分是固定的序列，后面几位是递增的序列。由于 B 树索引（相对于位图，B 树索引更为常用）的特性，在对上述规则的 sno 列上建立索引，可能会导致 B 树不平衡。

为了避免这种情况，可以反转 sno 列的值，也就是变为 30140115261，20140115261，10140115261…，这样便可以使数据均匀地分布在整个索引上。

可以使用关键字 REVERSE 来创建反向键索引，创建实例如下所示：

1
create index rev_sno_idx on student(sno) reverse;

7. 位图索引

位图索引适合创建在低基数列上，所谓的低基数列即类似于性别这种列，列上只有男或者女两种值。位图索引不直接存储ROWID，而是存储字节位到ROWID的映射。位图索引可以节省空间占用。如果索引列被经常更新的话，不适合建立位图索引。

总体来说，位图索引适合于数据仓库中，不适合日常的开发使用中。位图索引的创建示例如下所示：

1
2
-- 注意关键字 bitmap
create bitmap index stu_sgender_idx on student(sgender);

8. 基于函数的索引

基于一个或多个列上的函数或表达式创建的索引，且表达式中不能出现聚合函数。基于函数的索引不能在LOB类型的列上创建，且必须具有 QUERY REWRITE 权限。创建实例如下所示：

1
create index lowercase_idx on student(lower(sname));

这样我们在通过函数作为查询条件进行查询的时候，速度会更加快，例如：

1
select * from student where lower(sname) = 'lisi';

9. 重建索引

语法如下所示：

1
ALTER INDEX index_name REBUILD [ONLINE] [NOLOGGING] [COMPUTE STATISTICS];

其中：

  • ONLINE使得在重建索引过程中，用户可用对原来的索引进行修改；
  • NOLOGGING表示在重建过程中产生最少的重做条目redo Entry；
  • COMPUTE STATISTICS表示在重建过程中就生成了oracle 优化器所需的统计信息，避免了索引重建之后再进行analyze 或dbms_stats来收集统计信息。

10. 删除索引

删除示例如下所示：

1
drop index index_name;

11. 索引信息

与索引有关的数据字典视图有：

  • USER_INDEXES 用户创建的索引的信息
  • USER_IND_PARTITIONS 用户创建的分区索引的信息
  • USER_IND_COLUMNS 与索引相关的表列的信息

例如查询索引作用的列的信息：

1
2
3
SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
ORDER BY INDEX_NAME, COLUMN_POSITION;
------ 本文结束------