深入探讨Oracle函数索引

        Oracle还是比较常用的，Oracle函数索引在某些情况下也是非常有用的。

一.Oracle函数索引

1.Oracle函数索引的好处：Exp: function(column1) 如果表中有1000行，即便column1列上建有索引，索引在此时也不被使用，function会执行1000次。

 

2.建立Oracle函数索引会使插入/更新性能稍有下降，但是查询速度有了极大的提高。另外如果更新的时候没有涉及到建立Oracle函数索引的这个列，那就不会产生额外的开销。

 

3.部分行建立索引：

        1)某一列只有很少的基数，例如只有Y和N

        2)通常只会查询where column1 = ‘N’

        3)并且值为N的行占很小的比例

        4)在此列上建立索引，由于很大的一部分索引空间是浪费的(等于Y的)，不会使用到的，所以我们可以在值为’N’的那些行上面建立索引。

        5)Create index I on T(decode(column1 ， ‘N’， ‘N’))

        6)利用的是B树索引特性，如果列值为null，在索引中就没有相应的条目。

 

4.使用Oracle函数索引来保证复杂的约束：(某个条件成立时，X，Y，Z必须唯一)

        1)如果项目表，项目有2种状态，ACTIVE和INACTIVE

        2)希望ACTIVE的项目必须有唯一的项目名，INACTIVE的项目没有此要求

        3)此时项目状态这列上我们不适合建立唯一键

        4)但是我们可以建立一个唯一索引

        5)Create unique index I on T(decode(STATUS ， ‘ACTIVE’ ， NAME))

        6)利用的还是B树索引特性，如果列值为null，在索引中就没有相应的条目。

 

二.Oracle函数索引索引的一些常见问题

1.B树索引特性，如果列值为null，在索引中就没有相应的条目。

        1)Create table T (x int ， y int)

        2)Create unique index I T(x ， y)

        3)Insert into T values(1 ， 1)

        4)Insert into T values(null ， 1)

        5)Insert into T values(1 ， null)

        6)Insert into T values(null ， null)

        7)此时索引中只有三行，(null ， null)不在索引行中

        8)Insert into T values(null ， null) --成功插入

        9)Insert into T values(1 ， null)--报错，违反index唯一性

        10) 所以我们可以看到ORACLE中null <> null (null代表N/A)

        11) Where x is null --这个查询无法使用索引，因为（null，null）不在索引中，如果ORACLE使用索引就会得到错误的答案

        12) 同样能够得出结论，如果在一个允许null的列上面建立索引，x is null也不会使用索引

        13) 可以使用索引的条件Create table T (x int ， y int not null);

 

2.外键建立索引是需要的

 

3.索引跳跃式扫描

        1)Create index I T(x ， y)

        2)Select * from T where x=5; --此时优化器可能不会使用索引

        3)Select x ， y from T where x=5;--可能使用索引，因为所需要的内容都在索引中

        4)索引跳跃式扫描—skip scan

        5)如果y只有2个基数，Y和N ，oracle会采用index

        6)Select * from T where x=5; --会经过如下处理

        7)Select * from T where x=5 and y=’Y’

           Union all

           Select * from T where x=5 and y=’N’;

 

4.如果索引建立在一个允许null的列上面， select * from T就会使用全表扫描，不使用索引，因为null的行并不在索引行中，所以不会使用index统计数目。

 

5.select * from T where x=5 等价于select * from T where to_number(x)=’5’，由于存在隐式转换，所以x上的索引不会得到使用

 

6.where trunc(date) = trunk(sysdate) 可以转换成 date >=trunc(sysdate) and date<trunk(sysdate+1)

 

7.定期分析表:如果发现Oracle 在有索引的情况下，没有使用索引，这并不是Oracle 的优化器出错。在有些情况下，Oracle 确实会选择全表扫描（Full Table Scan），而非索引扫描（Index Scan）。

 

8.有些情况下单列索引不如复合索引有效率！

 

文章来源：http://database.51cto.com/art/200911/164250.htm