Oracle中的索引讲解

一、B-树索引

索引一般是B-Tree索引，在索引结构中存储着键值和键值的RowID，并且是一一对应的。

create [unique] index studentin student(id [desc]);

选项择索引字段的原则:

（ORACLE在UNIQUE和主键字段上自动建立索引 。）

• 在WHERE子句中最频繁使用的字段 。
• 联接语句中的连接字段。
• 选择高选择性的字段(即如果很少的字段拥有相同值,即有很多独特值,可以快速查找到所需数据的字段) .
• 在联机事务处理(OLTP)环境下,所由并发性非常高,索引经常被修改,可以建B-TREE索引，不应该建位图索引 。
• 不要在经常被修改的字段上建索引，可建函数索引。
• 不要在有用到函数的字段上建索引。
• B-Tree索引不包含null的数据。 
  可以建立一个“伪”复合索引解决。eg:

  create index my_ix on my_table(my_col,0);

  也可以用函数索引将不想索引，即使不是null也可以剔除。eg:

  create index a on table(decode(status,0,0))--只关心少数status为0的行。

二、复合索引

索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。例如，以下语句创建一个具有两列的复合索引：(复合索引列数量不超过32个)

CREATE INDEX name  ON employee (emp_lname, emp_fname)

复合索引的第一列称为前导列(leading column)。

复合索引字段排序的原则:

• WHERE子句中使用到的字段需要是复合索引的前导字段，若仅对后面的任意列执行搜索时，则应该创建另一个仅包含第二列的索引。
• 如果某个字段在WHERE子句中最频繁使用,则在建立复合索引时,考虑把这个字段排在第一位(在CREATE INDEX语句中)
• 如果所有的字段在WHERE子句中使用频率相同,则将低选择性列排在最前面,将选择性较强的列排在最后面
• 如果所有的字段在WHERE子句中使用频率相同,如果数据在物理上是按某一个字段排序的,则考虑将这个字段放在复合索引的第一位 。
• 在主键索引(复合主键)中列的顺序被强制为与列在表定义中出现的顺序相同，这与 PRIMARY KEY 约束中指定的列顺序无关.
• 索引列的排序方式必须与 ORDER BY 子句完全相同或完全相反。否则不能得到性能优化。

CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC)

在这种情况下，以下查询可以得到优化：

SELECT col1, col2, col3 from table1 ORDER BY col1 ASC, col2 DESC, col3 ASC

和

SELECT col1, col2, col3 from example ORDER BY col1 DESC, col2 ASC, col3 DESC

• 复合索引的前导字段is (not) null 可以使用索引。

三、位图索引

创建语法是在普通索引创建的语法中index前加关键字bitmap即可,例如:

create bitmap index t_ix_执行人 on t(执行人);

位图索引主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等)。

索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码。

位图索引存储数据的方式相对于B-Tree索引,占用的空间非常小,创建时不需要排序，定位存储，创建和使用非常快。

位图索引的特点：

• Bitmap索引允许键值为空，对位图索引列进行is（not） null查询时,则可以使用索引。
• Bitmap索引对表记录的高效访问。当使用count(XX),可以直接访问索引就快速得出统计数据。当根据位图索引的列进行and,or或 in(x,y,..)查询时,直接用索引的位图进行或运算。
• Bitmap索引对批量DML操作只需进行一次索引。
• 位图索引由于用位图反映数据,不同会话更新相同键值的同一位图段,insert、update、delete相互操作都会发锁定（未提交时）。
• 由于并发DML操作锁定的是整个位图段的大量数据行，所以位图索引主要是用于联机分析处理(OLAP)应用。

四、函数索引

比如执行如下一条SQL语句：

select * from emp where upper(ename) = 'KING'，

即使在ename上建立了索引，还是会全表扫描emp表，将里面的ename字段改成大写跟常量KING进行比较。 
如果我们建立一个基于函数的索引，比如：

create index emp_upper_idx on emp(upper(ename));

这个时候，我们只需要按区间扫描小部分数据，然后获取rowid取访问表中的数据，这个速度是比较快的。

函数索引的特点：

• 基于函数的索引，类似于普通的索引，只是普通的索引是建立在列上，而它是建立在函数上。当然这回对插入数据有一定影响，因为需要通过函数计算一下，然后生成索引。但是插入数据一般都是少量插入，而查询数据一般数据量比较大。为了优化查询速度，稍微降低点插入速度是可以承担的。
• 函数索引还有一个功能，只对部分行建立索引。 
  假设有一个很大的表，有一列叫做FLAG，只可能取Y和N。假设大部分数据是Y，小部分数据是N，我们需要将N修改成Y。

如果建立一个普通索引，这个索引会非常大，而且将N修改成Y的时候，维护这个索引开销会很大。

如果建立一个位图索引，但这是一个事务系统（OLTP），可能有很多人同时插入记录，或者进行修改。那么位图索引也不适合。所以，如果我们只是在值为N的行上建立索引，就比较好办了。 
只在值为N的行建立索引：

create index flag_index on big_table(case flag when 'N' then 'N' end);

这样一个索引大小会大大降低，而且维护成本也会很低的。前提是我们只对值为N的行感兴趣。

五、维护索引

• 字典表：all_indexes、user_indexes
• 重建索引：alter index dep_idx rebuild [online]
• 删除索引：drop index dep_idx;