Oracle列操作引起的全表扫描

首先是一种比较明显的情况：

select * from table where column + 1 = 2

这里对column进行了列操作，加1以后，与column索引里的内容对不上，导致column不走索引，走了全表扫描。

修改方式也很简单，把+1移到右边，即：

select * from table where column = 2-1

 

接下来是一种不可避免的列操作：

select * from table where upper(column) = 'ORACLE'

这里的upper不可避免，也无法用别的写法替代，怎么办？

这种情况只能建函数索引，即对upper(column）也建一个索引。

建函数索引是实在没办法的情况下，比如以上的例子。

 

然后是一些隐式转换引起的列操作，比如：

select * from table where column = 123 --这里的column是varchar2类型

执行的时候，会有个隐式的类型转换，也就是 to_number(column) = 123，这样column函数就不走索引，走了全表扫描。

所以应该避免这种隐式转换。

 

另外有一种隐式转换是这样的：

select * from table where column = '123' --这里的column是number类型

执行的时候，oracle会对‘123’做转换，即column = to_number('123')

这种情况没有什么坏处，column还是走了索引。

 

以上两个例子说明oracle是优先把字符串转成数字，虽然第二种情况无坏处，但实际情况下还是尽量避免隐式转换比较好。

 

实际情况中，单表的这种列操作容易发现，往往出问题的在多表连接，比如：

select a.id, b.id from table1 a, table2 b where a.column = b.column --这里a是大表，column类型为varchar2，b是小表，column类型为number

执行的时候，也会有个隐式转换，即 to_number(a.column) = b.column，导致a.column无法走索引。

修改方式为： select a.id, b.id from table1 a, table2 b where a.column = to_char(b.column)

将b.column做类型转换，虽然b.column无法走索引，但保证a.column可以走索引，因为a是大表，这样效率更高。

总之大表和小表连接时，如果有这种隐式转换，尽量让大表能够走索引。

 

以上这些问题，最好在写sql的时候就能注意到，否则后期去查会比较麻烦。