第二章：oracle中的索引

1,索引

与表关联的可选结构

加快SQL语句的执行

减少磁盘I/O

CREATE INDEX 语句用于创建索引

在逻辑上和物理上独立于表中的数据

ORCALE自动维护索引

2,唯一索引

确保在定义索引的列中没有重复的值

ORACLE自动为主键列和唯一键列创建唯一索引

CREATE UNIQUE INDEX 语句用于创建唯一索引

//创建唯一索引的语法 CREATE UNIQUE INDEX index_name on table_name(column_name);

//例：

SQL> create unique index idx_stuId on student(studentid);

3:组合索引

在表的多个列上创建的索引

也称为“连接索引”

组合索引中的列可以按任意顺序排列

对于在WHERE子句中包含多个列的查询，可以提高数据访问速度

//

创建组合索引的语法

CREATE INDEX index_name on table_name(cloumns_list);

//例:

SQL> create index idx_empNoAndDeptNo on emp(empNo,deptNo);

//在WHERE子句中同时已这两列为条件时，将引用到这个索引

SQL> select * from emp where empNo=7899 and deptNo=10;

4:反向键索引

适合于只做添加不做修改的列

反转索引列中的没一个字节（如：001，002 分别反转为 100，200）

将数据插入操作分布在整个索引上

在创建索引时使用REVERSE关键字

//创建反向键索引的语法 CREATE INDEX index_name on table_name(column_name) REVERSE;

//例:

SQL> create index idx_studentId on student(studentId) REVERSE;

5,位图索引

适合于有大量重复数据的列，（例如员工表的部门编号列，部门编号大量重复）

为低基数列创建

BITMAP INDEX 语句用于创建位图索引

优点：减少响应时间，降低空间占用

//创建位图索引的语法 CREATE BITMAP INDEX index_name on table_name(column_name);

//例:

SQL> create bitmap index idx_deptNo on emp(deptNo);

6,索引组织表

表的数据存储在与其关联的索引中

对于数据的修改只会导致对索引的更新

基于主键进行搜索(所以表中必须要定义主键列)

//创建索引组织表的语法

CREATE TABLE table_name

(

colName colDataType,

……

CONSTRAINT pk_id PRIMARY KEY (table_pkcolumn)

) ORGANIZATION INDEX;

//例:

SQL> create table citys

2 (

3 nCityCode number(10),

4 vCityName varchar2(50),

5 constraint pk_citycode primary key (nCityCode)

6 ) organization index;

7,

普通表与索引组织表的对比

普通表 索引组织表

ROWID唯一地标识行 主键唯一的标识行

有隐式的ROWID列 没有隐式的ROWID列

基于ROWID的访问 基于主键的访问

顺序扫描返回所有的行 完全索引扫描返回所有行，并按主键顺序排列

普通表可以存储在簇中 索引组织表不能存储在簇中

支持分发、复制、分区 不支持分发、复制、分区

8,基于函数的索引

基于一个或多个列上的函数或表达式创建的索引

表达式中不能包含聚合函数（SUM、COUNT、AVG、MIN、MAX）

不能在LOB、REF或潜逃表列上创建

//例:创建基于函数的索引

SQL> conn system/manager; //不清楚为什么scott帐户没有权限创建基于函数的索引

SQL> create index idx_cityName on scott.citys(lower(vCityName));

//在今后的SELECT语句WHERE子句中 使用lower(vCityName) 条件时，就自动引用了上面的索引

9,键压缩索引

将索引键拆分为前缀项和后缀项

在一个索引块中，通过后缀项共享前缀项即可达到压缩的目的

节省磁盘空间

//创建键压缩索引的语法 CREATE INDEX index_name table_name(前缀项列,后缀项列) COMPRESS 压缩的列序号;

//例: job(职位)列中有大量重复数据，则压缩job(职位)列，即第1列

SQL> create index idx_emp on emp(job,ename) compress 1;

10,分区索引

索引存储在不同的分区中

分区索引的类型

本地前缀索引

本地无前缀索引

全局索引

全局前缀索引

全局无前缀索引