Oracle索引

概念及作用 ： 在oracle中，索引是一种供服务器在表中快速查找一个行的数据库结构。在数据库中建立索引主要有以下作用。 　　（1）快速存取数据。 　　（2）既可以改善数据库性能，又可以保证列值的唯一性。 　　（3）实现表与表之间的参照完整性。 　　（4）在使用orderby、groupby子句进行数据检索时，利用索引可以减少排序和分组的时间。 　　（5）减少I/O操作。 　　（5）消除磁盘排序。   Oracle索引（index）最大的作用是用来优化数据库查询的效率，提升数据库的查询性能。就好比书的目录一样，可以通过目录来直接定位所需内容存在的页数，大大提高检索效率。

创建索引:  注意： index的存储也是需要存储空间的，oracle里逻辑存储空间就是表空间，索引肯定要为index指定表空间的。 如果创建时不指定，就会使用默认表空间users。 一般建议将index和table分开，这样避免IO竞争。可以提高性能。不过也要看业务需求和硬件环境。 　单一索引  :  Create Index <Index-Name> On <Table_Name>(Column_Name);           例如：                        Create Index index_sid On Student (Studentid);  —>在student表的Studentid列建立索引。      唯一索引             用create unique index语句来创建唯一索引，     例：             create unique index dept_unique_idx on dept(dept_no) tablespace tablespace_name;   　复合索引: Create Index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建立索引。 　　select * from emp where deptno=66 and job='sals' ->走索引。 　　select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引 　　select * from emp where deptno=66 ->走索引。 　　select * from emp where job='sals' ->进行全表扫描、不走索引。 　　如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引，将会进行全表扫描。      基于函数的索引             常用与UPPER、LOWER、TO_CHAR(date)等函数分类上      例：             create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;      位图索引             对基数较小，且基数相对稳定的列建立索引时，首先应该考虑位图索引，     例：             create bitmap index idx_bitm on class (classno) tablespace tablespace_name;      与约束相关的索引            可以用using index字句，为与unique和primary key约束相关的索引，     例：            alter table table_name            add CONSTRAINT PK_primary_keyname primary key (field_name)  ----> 添加约束，主键            using index tablespace tablespace_name；

什么时候创建索引： 1、Oracle 数据库会为表的主键和包含唯一约束的列自动创建索引，所以在建立唯一约束时，可以考虑该列是否必要建立。是否经常要作为查询条件。   2、如果某个表的数据量较大（十几二十万以上），某列经常作为where的查询条件，并且检索的出来的行数经常是小于总表的5%，那该列可以考虑建立索引。   3、对于两表连接的字段，应考虑建立索引。若经常在某表的一个字段进行Order By ，则建议建立索引。      (为了改善多表关联，索引列用于联结)   4、不应该在小表上建立索引。小表之间查询的数据会比建立索引的查询速度更快，但是在某些字段，如性别：只有男、女和未知三种数据时，可以考虑位图索引，可以增加查询效率。   5、经常进行DML操作，即经常进行增删改的操作的表，创建表索引时就要权衡一下，因为建索引会导致进行DML操作时速度变慢。所以可以根据实际情况，选择某些字段建立索引，而不能盲目乱建。   6、列中有许多空值，不适合建立索引；Date型列一般适合基于函数的索引（列中的值相对比较唯一）。

索引副作用:        建立索引不仅仅要浪费空间来存储索引表，当数据量较少时，直接查询数据比经过查询索引表再定位到表数据的速度更快。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。 不合理的索引将严重影响系统资源，主要表现在CPU和I/O上； 插入、更新、删除数据产生大量db file sequential read锁等待；

索引的类别： 逻辑上：           Single column 单行索引；Concatenated 多行索引；Unique 唯一索引；          NonUnique 非唯一索引；Function-based函数索引；Domain 域索引    物理上：           Partitioned 分区索引 ；NonPartitioned 非分区索引    B-tree：           Normal 正常型B树；Rever Key 反转型B树 ；Bitmap 位图索引 b-tree索引： Oracle数据中最常见的索引，就是b-tree索引，create index创建的normal就是b-tree索引，没有特殊的必须应用在哪些数据上。 bitmap位图索引： 位图索引经常应用于列数据只有几个枚举值的情况，比如我们经常开发中应用的代码字段。这个时候使用bitmap位图索引，查询效率将会最快。 函数索引： 比如经常对某个字段做查询的时候经常是带函数操作的，那么此时建一个函数索引就有价值了。例如：trim（列名）或者substr(列名)等等字符串操作函数，这个时候可以建立函数索引来提升这种查询效率。 hash索引： hash索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。创建hash索引必须使用hash集群，相当于定义了一个hash集群键，通过这个集群键来告诉oracle来存储表。因此，需要在创建HASH集群的时候指定这个值。存储数据时，所有相关集群键的行都存储在一个数据块当中，所以只要定位到hash键，就能快速定位查询到数据的物理位置。 reverse反向索引： 这个索引不经常使用到，但是在特定的情况下，是使用该索引可以达到意想不到的效果。如：某一列的值为{10000,10001,10021,10121,11000,....}，假如通过b-tree索引，大部分都密集发布在某一个叶子节点上，但是通过反向处理后的值将变成{00001,10001,12001,12101,00011,...}，很明显的发现他们的值变得比较随机，可以比较平均的分布在各个叶子节点上，而不是之前全部集中在某一个叶子节点上，这样子就可大大提高检索的效率。 分区索引和分区表的全局索引： 这两个索引是应用在分区表上面的，前者的分区索引是对分区表内的单个分区进行数据索引，后者是对分区表的全表进行全局索引。