查询中的 Oracle 和 SQLServer 函数评估
假设我在 Oracle 中的 select 或 where 子句上有一个函数调用,如下所示:
select a, b, c, dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3)
from my_table
可以为 MS SQLServer 构建类似的示例。
每种情况下的预期行为是什么?
HASH 函数是否会为表中的每一行调用一次,或者 DBMS 会足够聪明,只调用该函数一次,因为它是一个具有常量参数并且没有 side- 的函数效果?
多谢。
Let's say I have a function call on a select or where clause in Oracle like this:
select a, b, c, dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3)
from my_table
A similar example can be constructed for MS SQLServer.
What's the expected behavior in each case?
Is the HASH function going to be called once for each row in the table, or DBMS will be smart enough to call the function just once, since it's a function with constant parameters and no side-effects?
Thanks a lot.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
Oracle 的答案是视情况而定。 将为所选的每一行调用该函数,除非该函数被标记为“确定性”,在这种情况下,该函数只会被调用一次。
输出:
因此在第一种情况下 StringLen() 函数被调用了 3 次。 现在,当使用 StringLen2() 执行时,它被标记为确定性:
结果:
因此 StringLen2() 函数仅被调用一次,因为它被标记为确定性。
对于未标记为确定性的函数,您可以通过修改查询来解决此问题:
The answer for Oracle is it depends. The function will be called for every row selected UNLESS the Function is marked 'Deterministic' in which case it will only be called once.
Output:
So the StringLen() function was called three times in the first case. Now when executing with StringLen2() which is denoted deterministic:
Results:
So the StringLen2() function was only called once since it was marked deterministic.
For a function not marked deterministic, you can get around this by modifying your query as such:
对于 SQL Server,它将针对每一行进行评估。
通过运行该函数一次并分配给变量并在查询中使用该变量,您的情况会好得多。
For SQL server, it will be evaluated for every single row.
You will be MUCH better off by running the function once and assigning to a variable and using the variable in the query.
简短的回答......这取决于。
如果函数正在访问数据,ORACLE 不知道每一行是否相同,因此,它需要查询每一行。 例如,如果您的函数只是一个始终返回相同值的格式化程序,那么您可以打开缓存(将其标记为确定性),这可能允许您只执行一次函数调用。
您可能想要研究的是 ORACLE WITH 子查询:
我从 此处 获取了引用的文本,其中有很多示例。
short answer....it depends.
If the function is accessing data ORACLE does not know if it is going to be the same for each row, therefore, it needs to query for each. If, for example, your function is just a formatter that always returns the same value then you can turn on caching (marking it as Deterministic) which may allow for you to only do the function call once.
Something you may want to look into is ORACLE WITH subquery:
I got the quoted text from here, which has plenty of examples.