为什么oracle自定义函数这么慢？即使是简单的返回值？

Question

就比如说select func(col) from table和select col from table比较。（相差100倍左右）
func(num number)
begin
return num;
end;

Answer 1

如果纯粹看您给的例子，那返回的都是单一数值，区别是单纯遍历语句结果，前者是通过函数。我想您是想通过这个例子来问，如何提高函数在QUERY中的效率问题，因为你例子本身没有实际应用价值，只可能是你为解决问题的实验了。不知是否正确。

因为不知道你的具体工作情况，我只能提供多种可能，有的方法也许受权限，和你工作的内容限制，无法实现。我个人经验来看。
1. 尽量把函数放在你Login的schema下，对权限的检测，如果是自身的schema会提高速度。
2. 如果是你要生成report,你看看有没有可能，或者权限，生成Materialized view, 而不直接在他的Table或者View里面找。 Materialized View的方法有时候在数据仓库中会用来提高速度，当然 前提是你有这个权限。
3.就这个语句来看，我个人觉得没有必要从索引来找。你对自己的数据肯定比我们要熟悉的多，过多的索引反而会是速度变慢。觉得要么从表，要么从函数去入手。祝好运

Answer 2

这个取决于你SQL的where条件之后会有多少条数据。即便你函数里紧紧只是返回参数本身，但毕竟函数被调用执行的时间不可忽略。举例如果你搜到结果是1000条，那么这个函数就要被打开执行1000次，肯定会影响效率。加上业务表本身数据量如果大，又没索引的帮助，这个吞吐量是很吓人的。

追问

我做的这个经常需要返回百万级的数据，我用了自定义索引，所以索引方面耗时基本和select col 差不多，时间基本上耗在调用函数上，我主要是想知道有什么改进方式没有。

追答

我有个疑问。。是不是只能让oracle所在的服务器去做事。。我更倾向于用代码结合结果集去做事。。
还有一个逻辑问题，如果给用户看的，那么怎么着也要分个页吧，这样这种效率问题就迎刃而解了，因为分页后数据也就百来十条，，逐个调用function也不会太卡，如果分页SQL中就包含了这个调用，那只能说明数据结构设计的不太合理了，有较真的意思。。。
如果是后台处理或自己人用的，那不出错的情况下，能用就得了吧。。。
本来大数据量的问题基本都是分批处理的，不仅仅是慢，还有一个时间周期内数据的有效性问题，很多情况下必须在N之内处理，否则先前处理的都可能不对了。。

追问

我给您先说下这个需求，func实际上是一个字段授权审计函数，用于检验字段访问用户是否有访问权限。而数据库中的数据是一旦录入只要没错误，是不会更改的也不会删。现在就是加上func后，就至少慢了2个数量级，所以不管是返回结果多还是少，都存在性能问题：结果少的话并发量一大就会卡，结果多（比如count（func（col）））就更不用说了。您觉得这还有改进的空间没有？

追答

记录级权限啊。。。
count（func（col））这种东西确实不好处理，但你可以关注下像大的通信公司的网站，你登陆后默认的也只是给你当月的数据，搜时间间隔也强制了日期区间的大小，这就变相解决了你的问题。你查话费，也不至于登陆就直接看从办卡到今天的总计吧。。思路还是加个默认的where条件使得结果集先变小很多。
至于角色权限。。没看到你表结构不太好发表意见。一般是人挂钩角色，角色再挂钩记录组，记录组再挂钩记录，这样逐层分解，自然一张表的相关数据就会少很多。具体还是要看你业务的表设计，说不定也有很出彩的地方。
我的意见还是把数据访问压力分摊给数据库服务器和工程所在的服务器，也就是一方面给数据库查，但查出后再用代码加工，最大利用两方的性能，不然一方面数据库查得都快抽筋了，工程项目就呆呆的等然后把数据打出来，太蛋疼了。。。SQL写得不是太差，一次查10万条，和查100次1000条区别很大的，毕竟即使方法是同步非并发的，电脑循环100次×每次1000条对CPU来说纯粹就是底层运算而已，速度不慢的。。

Answer 3

你是如何做对比的?

追问

更新了问题补充，麻烦看下。

Answer 4

一般不会出现这种情况，请详细介绍一下具体情况

追问

更新了问题补充，麻烦看下。

追答

不应该是这儿的问题，你的where怎么写的?

如果col建了索引，而where中 col=xxx能使用索引，但fun(col)一般不能使用索引，不过好像可以对fun(col)建立索引

追问

嗯，我建过自定义索引，所以不是慢在这。