oracle性能优化之索引

1.索引的基本概念

一般索引可以提高查询性能。但是insert,delete,在索引列上的update等操作会变慢（因为要同时对数据和索引都操作）。所以需要平衡索引带来的查询性能的提升和对数据修改性能的影响。

查询一个表的索引，可以用USER_INDEXES 查询有哪些索引，以及用 USER_IND_COLUMNS 查询索引有哪些列。
例：

• select table_name,index_name from user_indexes where table_name = ‘XX;
• selelct table_name, index_name, column_name, column_position
  from user_ind_columns
  order by table_name,index_name, column_name, column_position;

2.不可视索引

可以通过不可视索引，来临时关闭某个索引来查询没有索引时的性能，但是这个索引仍然在维护，可以随时打开它。

• alter index idx1 INVISIBLE
• alter index idx1 VISIBLE
• create index ... INVISIBLE

3.组合索引

一般组合索引的第一列是最有可能在where子句中使用的列，也是索引中最具选择性的列。
在索引跳跃式扫描出现之前，组合索引只有在where子句中有出现前导列的时候才会被优化器使用。oracle 9i中增加了索引跳跃式扫描功能使得 在where子句的查询列是组合索引的第顺位之后的字段时依然可能使用索引。
索引扫描方式：

• 唯一扫描（UNIQUE SCAN）,适用于唯一索引，或者是主键索引
• 范围扫描(RANGE SCAN), 数据库根据查询条件从索引中返回多个符合条件的值。

oracle 索引查询，都是通过每行的ROWID去访问单行数据，ROWID就是指向单行数据的物理位置的指针。ROWID最好不要硬编码到代码中，因为不同版本的ROWID结构会有不同。

4.索引抑制

某些where子句的逻辑会阻止oracle 使用索引：

• where子句使用不等号,!= ,<>
• where 子句使用 is null 或is not null
• where 子句使用LIKE时，以通配符%开头(如果%放在结尾则可用索引)
• where 子句对存在索引的列使用函数的时候，比如TRUNC,SUBSTR,TO_DATE,TO_CHAR等。
• where子句比较不匹配的数据类型，做了隐式类型转换的时候。比如直接把varchar类型跟number类型，或者是date类型比较的时候。

5.索引的选择性

索引的选择性越高，意味着一个索引值返回的行数越少，索引就越好。索引选择性可以帮助基于成本的优化器来决定执行路径。
可以用多种方法判断索引的价值：
1.判断索引中的唯一键或不同键的数量
比较不同键的数量（USER_INDEXES视图的DISTINCT_KEYS）和表中行数（USER_INDEXES视图的NUM_ROWS），就可以知道索引的选择性。

6.集群因子

集群因子记录在扫描索引时，需要读取的数据块的数量。反映数据相对于已索引的列是否显得有序。如果集群因子接近于表中数据块的数量，表示索引对应的数据行的排列情况良好。但是如果集群因子接近于表中数据的行数量，说明排列情况不佳。
使用USER_INDEXES视图中的CLUSTERING_FACTOR查看。

综上，索引的选择性和集群因子相乘，即为该操作的成本。

7.二元高度

二元高度（B树级别，BLEVEL）是指一个索引从它的根块到其叶块的深度。

8 .创建直方图

构建直方图可以帮助优化器在表中的数据出现严重偏斜时做出更好的规划。

9.快速全扫描

相比于全表扫描，快速全扫描需要更少的物理I/O。当表查询中的所有列都包括在索引中，且索引的前导列并不在WHERE条件里，可以使用快速全扫描。

10. 跳跃式扫描

在组合索引中，索引的前导列并不在WHERE条件里时，可以用跳跃式扫描。该扫描比索引全扫描更快。
需要使用提示来使用索引的跳跃式扫描，提示可以影响优化器，使它偏向您所指定的路径。

11.索引的类型

1.B树索引

B树索引是oracle创建索引时的默认索引。可以是单列索引，也可以是组合索引，最多为32列。

2.位图索引

适用范围：
适用于大表上基数（不同值的数量）不高的列上建立位图索引，最多为30列。由于位图索引比B树索引小的多，所以可实现对表的快速访问，比如sex,基数只有2，男和女。

如果有多个位图索引，oracle可以合并从每个位图索引得到的结果。
创建位图索引sql:
create bitmap index idxname on XXX (aa,bb);
优点：
位图索引很少会向其中添加新的值，所以在批处理的插入中比B树要好。
缺点：

• 在联机事务处理中不建议使用位图索引，因为位图索引的索引值使用压缩格式存储。一个索引值会包含一系列的ROWID，因此oracle在更新一个定值时会锁定对应的所有ROWID，可能在DML过程造成死锁。
• 当执行ALTER TABLE 修改包含位图索引的列时，位图索引会失效
• 基于规则的优化器不会考虑位图索引。

3.哈希索引

适用范围：
哈希索引在限制条件是确定的值 而不是范围的情况下很有用。
优点：

• 哈希索引是访问数据库数据最快的方法，oracle可以基于哈希函数快速确定行的物理存储位置。
• 对包含有序值的静态数据非常有效。

缺点：

• 创建哈希集群之前必须知道集群键不同值的数量。否则可能造成集群的冲突（两个集群的键值用相同的哈希值）。
• 哈希集群可能浪费空间，因为需要为集群未来增长预留好空间，需要合理评估。同时，由于为未来增长预留好空间，全表扫描会很耗资源。

4.索引组织表

概念：
索引组织表会以表的主键进行排序，把表的组织结构改成B树结构。索引组织表提供了一种基于键的快速数据访问机制。
适用范围
对于总是通过对主键的精确匹配或范围扫描进行访问的表，可以考虑使用索引组织表。
优点：

• 可以在索引组织表上建立额外的索引。

5.反键索引

适用范围：
磁盘数量有限，同时还需要执行大量有序载入时用。 反键索引不能与wei‘tu‘suo‘y或索引组织表结合使用。

6.基于函数的索引

可以在表中创建基于函数的索引，否则在where子句中带有索引的列如果使用了函数都将不会使用。
但是要注意以下几点:

• 加索引列对dml产生的影响（变慢）
• 是否有足够的空间应付额外索引
• 这个索引列函数是否适用于所有情况

7.分区索引

分区索引就是把一个索引分成多个片段，减少需读取的索引大小，使得访问更快，也避免I/O竞争。
oracle还支持在分区的表和索引上的并行查询和并行DML
B树索引和位图索引可以分区，哈希索引不可以。
分区索引有两种类型：本地索引和全局索引。每种类型又分为有前缀和无前缀索引。

• 本地索引
  本地索引是指与表使用相同的分区键和范围界限的索引，本地索引的每个分区只包含了该表分区的键值和ROWID。oracle会自动维护这些本地索引，本地索引的分区也可以被单独重建。
• 全局索引
  全局分区索引在一个索引分区中包含来自多个表分区的键值。全局索引只能是B树索引。oracle默认不会自动维护全局索引。

12.快速重建索引

可使用ALTER INDEX中的REBUILD选项，使用已有索引而不是表来快速重建索引。
alter index idx1 rebuild parallel
tablespace cust_tbl
storage (xxx);

13.在线重建索引

可以直接在执行DML语句时创建或者重建索引。
CREATE INDEX index_name ON table (col1,col2) ONLINE;
ALTER INDEX index_name REBUILD ONLINE;

oracle性能优化之索引

原文：https://www.cnblogs.com/laiyaling/p/12803873.html