首先是一种比较明显的情况:
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的时候就能注意到,否则后期去查会比较麻烦。