如何优化Oracle在where条件中用了自定义函

Oracle的自定义函数，提供了对逻辑的封装能力，便于我们对代码进行管理。然而当这个函数出现在where语句中，它却很可能给我们的SQL语句带来严重的效率问题。因为：
1、Oracle的优化器无法对函数进行优化，只能逐行执行（这就是为什么我们常发现，把函数里面的语句拷出来，就会执行得很快的原因）
2、函数并非标准SQL所包含的东西，因此Oracle在执行函数时，会频繁在SQL上下文和PL/SQL上下文之间切换。当数据量大时，就会增加CPU和内存的消耗，降低语句执行的效率。
因此，自定义函数，就像一把双刃剑一样，摆在了我们面前。对此，我们有以下几种对策：
1、不用函数
2、借用Oracle的缓存机制
3、函数索引
1）不用函数。不用函数确实能解决问题，但如果一段逻辑会被很多个SQL语句用到，这就会给我们的代码管理带来了很大的麻烦。因此这是万不得已的下下策。当然，没必要使用函数的地方，可以尽量避免。
2）借用Oracle的缓存机制。
Oracle对子查询，是会做缓存处理的。因此我们可以把函数写在一个子查询中，如把“my_func(id)”变成“(select my_func(id) from dual)”。这样做可以通过缓存，减少函数被调用的次数，从而提高效率。通常用函数对大数据量进行过滤时，此方法都可大量降低函数被调用的次数（可从Oracle的执行统计看出）
另一种方法，就是对函数强行开启结果缓存。方法是在函数的return类型之后，加上“result_cache”标记。
3）使用函数索引。首先该函数必须被标记为deterministic，即在函数的return类型之后，加上“deterministic”标记。它表示当函数的输入值是确定时，返回结果必定是唯一的。此后，在表的列上新建索引，索引列不是写列名，而是写“my_func(id)”这样即可。
通常地，我推荐先尝试“子查询缓存”这种方法。因为它不仅对函数、表本身没有作任何修改，而且效果也比较明显。我曾经试过对一条很复杂的SQL做了这种处理，结果执行效率提高了60倍之多！如果缓存效果不明显时，可以再尝试下函数索引。不过我发现同样的SQL，我只加函数索引的话，效果并不如“子查询缓存”那样立竿见影。
注意，以上提到的，都只是一些调优的手段而已，并非一定能解决问题。所以可以的话，我们还是要尽量避免把函数放在where语句中。