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:appAdministratorproduct11.2.0dbhome_1RDBMSADMINutlxplan.sql
SQL> grant all on plan_table to public;
--运行系统提前定义的存储过程来创建角色plustrace并赋予查询动态性能视图的权限
SQL>@F:appAdministratorproduct11.2.0dbhome_1sqlplusadminplustrce.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) 当使系统数据字典或视图 
系统的数据字典表都未被分析过。可能导致极差的“运行计划”。
可是不要擅自对数据字典表做分析，否则可能导致死锁。或系统性能下降。