Oracle之SQL优化-索引的基本原理（二）
Oracle之SQL优化-索引的基本原理（二）。
1、为什么使用索引？
(1)、原因
索引中只有一列，io小，所以较快；
索引中此列是排序的，二叉查找，提高查询速度。
(2)、原因分析
索引是对数据库表中一列或多列的值进行排序的一种结构。
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
当表中有大量记录时，若要对表进行查询有2中搜索方式：
第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，
这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；
第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）
快速找到表中对应的记录。
注：当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
2、什么情况下适合建立索引：
(1)表的主键、外键必须有索引。
(2)经常与其它表进行连接的表，在连接字段上应该建立索引。
(3)经常出现在WHERE子句中的字段，特别是大表的字段，应该建立索引。
(4)索引应该建在选择性高的字段上。
(5)索引应该建在小字段上，对于大的文本字段甚至超长字段，不适合建索引。
(6)复合索引的建立需要进行仔细分析。
(7)正确选择复合索引中的主列字段，一般是选择性较好的字段。
(8)如果单字段查询很少甚至没有，那么可以建立复合索引；否则考虑单字段索引。
(9)如果复合索引中包含的字段经常单独出现在WHERE子句中，那么分解为多个单字段索引。
(10)如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段。
(11)如果既有单字段索引，又有这几个字段上的复合索引，那么一般可以删除复合索引。
(12)频繁进行DML操作(insert、update、delete)的表，不要建立太多的索引。
(13)删除无用的索引，避免对执行计划造成负面影响。
3、索引创建的策略
(1)导入数据后再创建索引。
(2)不需要为很小的表创建索引。
(3)对于取值范围很小的字段（比如性别字段）应当建立位图索引。
(4)限制表中的索引的数目。
(5)为索引设置合适的PCTFREE值。
(6)存储索引的表空间最好单独设定。
4、如何对索引进行操作：
(1)、索引存储位置——数据库文件：
数据库文件中存储着用户数据（表、索引等）、数据字典、存储过程、函数和数据包的代码、
用来排序的临时数据以及回滚段数据等。
(2)、查看索引：
ORACLE数据字典视图的种类分别为：USER,ALL 和 DBA。
USER_*:有关用户所拥有的对象信息，即用户自己创建的对象信息；
ALL_*:有关用户可以访问的对象的信息，即用户自己创建的对象的信息加上
其他用户创建的对象但该用户有权访问的信息；
DBA_*:有关整个数据库中对象的信息
注：如果数据库中的索引数据很多，尽量避免查询ALL_INDEXES、DBA_INDEXES。
系统视图存放的是索引名称，对应的表和列等：
all_ind_columns/dba_ind_columns/user_ind_columns
select * from user_indexes where table='表名' ;
select * from user_ind_columns where index_name=upper('&index_name');
(3)清理索引碎片
a.合并索引(只是简单的将B树叶结点中的存储碎片合并在一起，并不会改变索引的物理组织结构）
alter index emp_pk coalesce;
b.重建索引(不仅能够消除存储碎片,还可以改变索引的全部存储参数设置，并且可以将索引移动到
其它的表空间中,重建索引实际上就是再指定的表空间中重新建立一个新的索引,然后删除原来的索引)
alter index emp_pk rebuild;
(4)删除索引
drop index emp_ename;
如果索引中包含损坏的数据块，或者包含过多的存储碎片，需要首先删除这个索引，然后再重建它。
如果索引是在创建约束时由oracle自动产生的,可以通过禁用约束或删除约束的方法来删除对应的索引。
在删除一个表时,oracle会自动删除所有与该表相关的索引。
5、常用到的一些索引操作
(1)查询一张表里面索引
select * from user_indexes where table_name=upper('tableName');
(2)查询被索引字段
select * from user_ind_columns where index_name=('indexName');
(3)给某一字段创建索引
create index index_name on table_name(col_name);
(4)查看用户下的索引
select * from user_indexes- -----查看当前用户下的所有索引
select * from user_indexes where table_name='A'; -----查看当前用户下表A的索引
drop index index_name去掉索引
select index_name,index_type,status,blevel from user_indexes where table_name = '？';
-----查看某一个表的所有索引
select table_name, index_name, column_name, column_position from user_ind_columns
where table_name='？'; ----查看索引的构成
(5)建索引
Create unique clustered index 索引名on 表名(字段1) --单索引 Create index 索引名 on 表名(字段1,字段2) -------复合索引