Oracle 索引

olop

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) 当使系统数据字典或视图
系统的数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降。