Oracle之SQL优化-索引的基本原理（一)

Oracle之SQL优化-索引的基本原理（一)。

1、索引的基本概念：

(1)创建索引的目的：

以索引小的IO换取表的大IO。

何时创建索引：

当访问的数据块少于表中20%的数据时，建议使用索引。

(2)索引的副作用

会使insert、delete速度变慢(索引个数多的话速度就会慢)。

对于update语句，需要先判断是否要修改索引列。

(3)单字段索引和组合索引

在Oracle9i之前，只有使用到索引的前导引用时才可以使用组合索引。

现在可以通过索引的跳跃式扫描来使用非前导引用的组合索引。

(4)Oracle的rowid

rowid是直接指向单行的线路图，不同的版本rowid的结构不同，不能对rowid进行硬编码。

具体可看上面的原理图。

2、使用索引时需要注意

(1)条件中使用不等于操作（<>,!=),将不会走索引，而是走全表扫描。

(2)条件中使用IS NULL 或者IS NOT NULL，也不会走索引，走全表扫描。字段可以使用缺省值。

(3)条件中对字段使用函数，也不会走索引，除非是函数索引。

(4)比较不匹配的数据类型时，oracle可以帮我们自动做数据类型的转换，但是oracle还是建议我们最好

使用转换函数（to_char()、to_date()、to_number()等）做显示的转换。

3、索引的选择性

(1)选择性越高，索引返回的数据就越少。

(2)索引的群集因子越高，表中的数据按照这个索引字段越有序，表中返回的数据块就越少。

(3)索引的二元高度的每个级别需要单独的io,如果索引中被删除的行接近20~30%，需要重建索引。

降低二元高度；数据库快的尺寸越大，索引的二元高度就越小。

(4)索引的空间

索引的空间被重复利用的概率很小，有时索引的空间比表大。

4、索引的扫描方式

(1)全局快速扫描索引(fast full scan)：

使用hint提示中的INDEX_FFS。索引相对于表的总体大小来说很小。如果在连接中只查询索引的

连接键列时，通常都会执行快速全索引扫描。

(2)跳跃式扫描索引(skip scan)：

允许优化器使用组合索引，即使索引的前导列没有出现在where子句里面。比全索引扫描要快得多。

(3)索引的全局扫描(full scan);

(4)索引的范围扫描(range scan)。

5、索引的类型及特点

(1)B——索引

如果索引的值都在索引中，就可以不访问表，从而减少IO量。

(2)位图索引

适用于DSS系统，他使用较少基数（唯一键数目）列访问非常大的表。

B-树索引和位图索引的比较：

B-树索引的索引值中包含rowid，这样oracle就可以在行级别上锁定索引。

位图索引被存储为压缩的索引值，其中包含一个范围的rowid,因此oracle必须针对一个给定值锁

定所有范围内的rowid。这种锁定可能在某些dml语句中造成死锁。

位图索引的限制：

a、基于代价的优化

b、当执行alter table 语句，并修改包含位图索引的列时，位图索引会失效。

c、不能用于任何类型的完整性检查。

d、不能被声明为唯一索引。

注意：不要在繁重的oltp环境中使用位图索引。

创建位图索引

 create bitmap index emp_sex on employees(sex)
 tablespace users;     

(3)Hash 索引

使用hash 索引必须使用hash群集。

(4)索引编排表

又称为索引组织表，只有索引段，没有数据段。

(5)反转键索引

数据1234就被存储为4321。不能对位图索引和索引编排表进行反转键处理。

此类型的索引可能比b-索引要慢2.5~3倍。

一般适用于磁盘数很少而插入很多的情况。

创建反序索引

 create unique index order_reinx on orders(order_num,order_date)
 tablespace users reverse;

(6)基于函数的索引

数据库的参数:QUERY_REWRITE_ENABLED = TRUE

创建函数索引(函数索引即可以是普通的B树索引，也可以是位图索引)

 create index emp_substr_empno on employees(substr(empno,1,2)) 
 tablespace users;

(7)分区索引

本地索引：有前缀和无前缀。

全局索引：有前缀和无前缀。

6、索引的快速重建

锁表的情况

 alter index index_name rebuild; 

不锁表的情况

 alter index indx_name rebuild online;     

注：

B树索引：在B树的叶节点中存储索引字段的值与ROWID。

唯一索引和不唯一索引都只是针对B树索引而言。

复合索引：Oracle最多允许包含32个字段的复合索引。