【Oracle笔记】索引的建立、修改、删除

一、概念和作用

  在oracle索引是一种供服务器在表中快速查找一个行的数据库结构。合理使用索引能够大大提高数据库的运行效率。
  在数据库中建立索引主要有以下作用。
　　（1）快速存取数据。
　　（2）既可以改善数据库性能，又可以保证列值的唯一性。
　　（3）实现表与表之间的参照完整性
　　（4）在使用orderby、groupby子句进行数据检索时，利用索引可以减少排序和分组的时间。

二、为什么能够提高查询速度

  索引就是通过事先排好序，从而在查找时可以应用二分查找等高效率的算法。
  一般的顺序查找，复杂度为O(n)，而二分查找复杂度为O(log2n)。当n很大时，二者的效率相差及其悬殊。

三、建立索引

  目的：提高对表的查询速度；对表有关列的取值进行检查。

CREATE [unique] INDEX [user.]index
ON [user.]table (column [ASC | DESC] [,column
[ASC | DESC] ] ... )
[CLUSTER [scheam.]cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]
Advanced

例如：

create index big_index on big_data_tbl(id);

其中：

• schema：ORACLE模式，缺省即为当前帐户

• index：索引名

• table：创建索引的基表名

• column：基表中的列名，一个索引最多有16列，long列、long raw列不能建索引列

• DESC、ASC：缺省为ASC即升序排序

• CLUSTER：指定一个聚簇（Hash cluster不能建索引）

• INITRANS、MAXTRANS：指定初始和最大事务入口数

• Tablespace：表空间名

• STORAGE：存储参数，同create table 中的storage.

• PCTFREE：索引数据块空闲空间的百分比(不能指定pctused)

• NOSORT：不（能）排序（存储时就已按升序，所以指出不再排序）

  注意：

• 一个基表不能建太多的索引；

• 空值不能被索引；

• 只有唯一索引才真正提高速度,一般的索引只能提高30%左右。

四、修改索引

  修改索引的主要任务是修改已存在索引的存储参数适应增长的需要或者重新建立索引。

ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n] 
REBUILD 
[STORAGE n]

其中：
  REBUILD是根据原来的索引结构重新建立索引，实际是删除原来的索引后再重新建立。

提示：
  DBA经常用REBUILD来重建索引可以减少硬盘碎片和提高应用系统的性能。

五、删除索引

  当不需要时可以将索引删除以释放出硬盘空间。命令如下：

DROP INDEX [schema.]indexname

注：当表结构被删除时，有其相关的所有索引也随之被删除。

六、索引建立原则总结

1. 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引。
2. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）。
3. 小表不要建立索引。
4. 对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引。
5. 列中有很多空值，但经常查询该列上非空记录时应该建立索引。
6. 经常进行连接查询的列应该创建索引。
7. 使用create index时要将最常查询的列放在最前面。
8. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引。
9. 限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）。