Oracle索引--简单粗暴

Oracle的索引

Oracle索引（index）最大的作用是用来优化数据库查询的效率，提升数据库的查询性能。就好比书的目录一样，可以通过目录来直接定位所需内容存在的页数，大大提高检索效率。
创建索引就是对某些特定列中的数据进行排序或归类，生成独立的索引表。引用索引的过程中会使用到ROWID，而在Oracle内部通常就是使用ROWID来访问数据的，所以当表中的数据非常多时，引用索引带来的查询效率非常可观 。
（ROWID是oracle数据库的表中的每一行数据的唯一的标识符，表明了该行在oracle数据库中的物理具体位置）

创建索引

--唯一索引：键值不重复,不能为空，主键就是一种唯一索引
create unique index idx_emp_1 on emp(job);

--一般索引：键值可重复
create index idx_emp_2 on emp(ename);

--复合索引：绑定了多个列
create index idx_emp_3 on emp(ename,job);

删除索引

无法删除主键的索引，想要删除需要使用其他方法

--删除索引
drop index 索引名;

--删除主键的索引
alter table 表名 drop constraint 主键名;

修改索引

重建索引

重建索引可以减少硬盘碎片和提高数据库系统的性能，也可以通过整理碎片的方式提高性能

-- 重建索引
alter index 索引名称 rebuild;

整理碎片
alter index 索引名称 coalesce;

重命名索引名

alter index 索引名称 rename to 新索引名称；

补充

在实际应用中，把表和索引的存储空间分开到不同的表空间，减少磁盘的竞争，可以极大的提高性能

--将索引放入到指定的表空间中
create index 索引名 on 表名称(列名) tablespace 表空间名称；

利用PL\SQL Developer查看SQL语句对索引的使用情况

使用F5键可以查看使用情况

索引的优点：

大大加快检索数据的速度
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
查询语句汇总含有分组或者排序的语句时，速度更快
查询的过程中，使用索引，使用优化隐藏器，从而提高系统的性能

索引的不足：

创建和维护索引，比较耗费时间，随着数据量的增大而增大
创建索引，占一定的物理空间（聚簇索引，占用空间会更大）
在对表进行增删改的时候，索引相应的也需要进行动态的更新
索引有开销，一方面是需要更多的存储空间，另一方面是影响表的增加、删除、和修改的性能。

比较适合建立索引的列的特点：

经常需要搜索的列上
where子句后边经常出现的字段
经常需要根据范围进行搜索的列上，比如日期
主键本身唯一性索引，保持数据的唯一性
外键，提高表与表之间连接的速度
需要排序的列上

不适合建立索引的列的特点：

很少进行搜索的列上
列取值比较少的列上
blob类型的列上
修改频率比较高的列上

限制索引（建立了索引但无法使用）

使用不等于<> 、 != ，（不等于操作符一定会进行全表扫描）
使用is null 、 is not null （只要索引中出现一个null，那么这个索引就报废了。所以在建立索引的时候，一定要将准备建立索引的列设置为not null）
使用函数（where子句中含有trunc()、add_months()之类）的时候，sql优化器会自动忽略掉索引
where子句中，进行了数据类型不匹配的比较，比如(where row_num = ‘1’)的时候，生气了优化器会限制索引的使用