Oracle索引

1、	索引介绍
索引是用于加速数据存取的数据对象，是对数据表中一个或多个列进行排序的结构。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。
2、	问题：为什么需要索引？
select * from scott.emp where empno=’8379’   --遍历emp表？
制约程序规模增大的两个瓶颈：网络和数据库，需要用更好的方式组织、管理数据。
3、	索引分类
?	按索引列个数
(1)	单列索引
基于单个列所建立的索引，定义格式如下：
create index索引名on表名(列名);
(2)	复合索引
基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同。
create index 索引名 on 表名(列名1, 列名2,…… ) ;
?	按索引列的唯一性
(1)	唯一索引
索引列上的取值唯一。
create unique index索引名on表名(列名1, 列名2,…… ) ;
(2)	非唯一索引
索引列上的取值不唯一。
create index索引名on表名(列名1, 列名2,…… ) ;
?	按索引数据的存储方式
(1)	b*树索引
?	特点：所有的叶子节点都在同一层，也就是无论查找哪一条数据，需要执行的i/o数据是一样的，适合进行区间扫描。
?	定义语法：
create [unique] index [方案名.]索引名 on  [方案名.]表名
(列名1[asc|desc] [,列名2[asc|desc]] …) 
[tablespace 表空间名] 
[pctfree] 
[initrans] 
[maxtrans] 
[storage clause] 
[logging|nologging] 
(2)	位图索引
?	特点：对索引列有少数不同值的大表，特别适合用位图索引，由于位图索引的更新代价更大，所以适合很少更新键值的表。
?	定义语法：
create bitmap index [方案名.]索引名 on  [方案名.]表名
(列名1[asc|desc] [,列名2[asc|desc]] …) 
[tablespace 表空间名] 
[pctfree] 
[initrans] 
[maxtrans] 
[storage clause] 
[logging|nologging]
(3)	逆向索引
?	特点：适合建在递增或递减的列上，从而降低批量插入数据时造成的索引块竞争；但是无法进行区间扫描。
?	定义语法：
create [unique] index [方案名.]索引名 on  [方案名.]表名
(列名[asc|desc]) reverse
[tablespace 表空间名] 
[pctfree] 
[initrans] 
[maxtrans] 
[storage clause] 
[logging|nologging]
(4)	函数索引
?	特点：适合于查询对表中字段的引用中使用了函数的情况。
?	举例：select * from scott.emp where upper(ename) = 'smith';
4、	索引的使用原则
(1)	在大表上建立索引才有意义
(2)	在where子句或是连接条件上经常引用的列上建立索引
5、	索引缺点分析
(1)	建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。
(2)	更新数据的时候，系统必须要有额外的时间同时对索引进行更新，以维持数据和索引的一致性。
6、	合并与重建索引
随着对表的不断更新，表中的索引会产生越来越多的存储碎片，要对碎片进行整理有两种方式：重建索引或合并索引。 
?	合并索引：将b树索引中的叶节点存储碎片进行合并。 
alter index 索引名 coalesce deallocate unused; 
注：不加deallocate unused则合并后仍保留多于的空间。 
?	重建索引：即进行重新建立，而且可以修改之前的各种参数；可将索引移入另外的表空间；由于重建的过程中移除了逻辑删除的索引，可以提高空间利用率。 
alter index索引名rebuild 重新配置参数; 
注：在改变属性时还可以修改reverse参数，改为反向，或反向改为正向。
7、	删除索引
?	作用
(1)	 不再需要的索引；
(2)	 通过一段时间观察，发现很少使用该索引；
(3)	 该索引无效，必须在重建之前删除；
(4)	 该索引包含过多存储碎片，需要在重建之前删除该索引；
(5)	索引没有提供所期望的性能改善；
(6)	在批量载入数据之前，先删除索引然后重建。
?	语法：
drop index 方案名.索引名;
8、	案例
--执行系统预定义的存储过程来创建plan_table表
sql>@ f:\app\administrator\product\11.2.0\dbhome_1\rdbms\admin\utlxplan.sql
sql> grant all on plan_table to public;
--执行系统预定义的存储过程来创建角色plustrace并赋予查询动态性能视图的权限
sql>@f:\app\administrator\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql
sql>set autotrace on exp
sql>create table student
(
sno char(9) primary key,
sname char(20) unique,
ssex char(2),
sage smallint,
sdept char(20)
);
sql> insert into student values('201010121', '李勇', '男',20, 'is');
sql>insert into student values('201010122', '刘晨', '男',19, 'is');
sql>insert into student values('201010123', '张力', '男',20, 'ma');
sql>insert into student values('201010124', '李颖', '女',20, 'is');
--对比以下三条查询语句的执行计划，观察table access上的区别
sql>select * from student where sno='201010122'; 
sql>select * from student where sname='刘晨';
sql>select * from student where sage=19;
sql>set autotrace off
--查询student表上的索引信息，现在有两个索引
sql>select index_name, index_type, table_name, uniqueness from user_indexes where table_name='student';  
--假设index_name分别是sys_c0011054和sys_c0011053
--下面进行b*树索引监控
sql>alter index sys_c0011054 monitoring usage; 
--sys_c0011054为student表上一个索引的index_name
sql>select * from student where sno='201010122';
sql>select * from student where sname='刘晨';
sql>select * from v$object_usage;
--创建位图索引
sql>create bitmap index bidx_sex on student(ssex);
--下面进行位图索引监控
sql>alter index bidx_sex monitoring usage; 
sql>select * from student where ssex ='男';
sql>select * from v$object_usage;
说明：关闭索引监控需执行语句为
sql>alter index sys_c0011054 nomonitoring usage;
其中，sys_c0011054为待关闭监控的索引名。
9、	显示索引列
创建索引时，需要提供相应的表列。通过查询数据字典dba_ind_columns可以显示所有的索引的表列信息。通过查询数据字典all_ind_columns可以显示当前用户可访问的所有的索引表列信息。通过查询数据字典user_ind_columns可以显示当前用户索引的表列信息。例如：
sql> select column_name, column_length from user_ind_columns where index_name='bidx_sex';
10、显示索引段位置及大小
建立索引时，oracle会为索引分配相应的索引字段，索引数据被存放在索引段中，并且段名与索引名完全相同。例如：
sql> select tablespace_name, segment_type, bytes from user_segments where segment_name='bidx_sex';
11、显示函数索引
建立函数索引时，oracle会将函数索引的信息存放到数据字典中。通过查询数据字典dba_ind_expressions可以显示数据库所有函数索引所对应的函数或表达式。通过查询数据字典user_ind_expressions可以显示当前用户函数索引所对应的函数或表达式。
例如：
sql> create index fun_ind_sdept on student(upper(sdept));
sql> select column_expression from user_ind_expressions where index_name='fun_ind_sdept';
12、索引使用的限制情况
(1) 使用不等于操作符（<>、!=） 
下面的查询即使在sno列有一个索引，查询语句仍然执行一次全表扫描。
sql>  select * from student where sno<>'1';
把上面的语句改成如下的查询语句，在采用基于规则的优化器而不是基于代价的优化器时，将会使用索引。
sql>  select * from student where sno < '1' or sno > '1';
注：通过把不等于操作符改成or条件，就可以使用索引，以避免全表扫描。
(2) 使用is null 或is not null
使用is null 或is not null同样会限制索引的使用。因为null值并没有被定义。 在sql语句中使用null会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成not null。 如果被索引的列在某些行中存在null值，就不会使用这个索引，除非索引是一个位图索引。 
(3) 比较不匹配的数据类型
比较不匹配的数据类型也是比较难于发现的性能问题之一。
注意下面查询的例子，sno是一个char类型，在sno字段上有索引。下面的语句将执行全表扫描。        
sql>  select * from student where sno=1;
oracle可以自动把where子句变成to_number(sno)=1，这样就限制了索引的使用,改成下面的查询就可以使用索引：
sql>  select * from student where sno='1';
(4) 当使系统数据字典或视图 
系统的数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降。