使用 where 子句中的函数的 Oracle 性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1117372/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle performance using functions in where clause
提问by Nicolás
In a stored procedure (which has a date parameter named 'paramDate' ) I have a query like this one
在一个存储过程（它有一个名为 'paramDate' 的日期参数）中，我有一个这样的查询
select id, name
from customer
where period_aded = to_char(paramDate,'mm/yyyy')
will Oracle convert paramDate to string for each row?
Oracle 会将 paramDate 转换为每一行的字符串吗？
I was sure that Oracle wouldn't but I was told that Oracle will. In fact I thought that if the parameter of the function was constraint (not got a fierld nor a calculated value inside the query) the result should be allways the same, and that's why Oracle should perform this conversion only once. Then I realized that I've sometimes executed DML sentences in several functions, and perhaps this could cause the resulting value to change, even if it does not change for each row.
我确信 Oracle 不会，但有人告诉我 Oracle 会。事实上，我认为如果函数的参数是约束（在查询中没有字段或计算值），结果应该总是相同的，这就是为什么 Oracle 应该只执行一次这种转换。然后我意识到我有时会在几个函数中执行 DML 语句，也许这可能会导致结果值发生变化，即使每一行都没有变化。
This should mean that I should convert such values before I add them to the query.
这应该意味着我应该在将它们添加到查询之前转换这些值。
Anyway, perhaps well 'known functions' (built in) are evaluated once, or even my functions would also be.
无论如何，也许众所周知的“已知函数”（内置）会被评估一次，甚至我的函数也会被评估。
Anyway, again...
无论如何，再次...
Will oracle execute that to_char once or will Oracle do it for each row?
oracle 会执行一次 to_char 还是 Oracle 会为每一行执行一次？
Thanks for your answers
谢谢你的回答
回答by Thilo
I do not think this is generally the case, as it would prevent an index from being used.
我认为通常情况并非如此，因为它会阻止使用索引。
At least for built-in functions, Oracle should be able to figure out that it could evaluate it only once. (For user-defined functions, see below).
至少对于内置函数，Oracle 应该能够弄清楚它只能评估一次。（对于用户定义的函数，请参见下文）。
Here is a case where an index is being used (and the function is not evaluated for every row):
这是使用索引的情况（并且不会为每一行评估该函数）：
SQL> select id from tbl_table where id > to_char(sysdate, 'YYYY');
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35 | 140 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| SYS_C004274 | 35 | 140 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID">TO_NUMBER(TO_CHAR(SYSDATE@!,'YYYY')))
For user-defined functions check out this article. It mentions two ways to ensure that your function gets called only once:
对于用户定义的函数，请查看这篇文章。它提到了两种方法来确保您的函数只被调用一次：
Since Oracle 10.2, you can define the function as DETERMINISTIC.
On older versions you can re-phrase it to use "scalar subquery caching":
SELECT COUNT(*) FROM EMPLOYEES WHERE SALARY = (SELECT getValue(1) FROM DUAL);
从 Oracle 10.2 开始，您可以将该函数定义为 DETERMINISTIC。
在旧版本上，您可以将其重新表述为使用“标量子查询缓存”：
SELECT COUNT(*) FROM EMPLOYEES WHERE SALARY = (SELECT getValue(1) FROM DUAL);
回答by Karl T.
The concern about to_char does not ring a bell with me. However, in your pl/sql, you could have
对 to_char 的关注并没有引起我的注意。但是，在您的 pl/sql 中，您可以拥有
create or replace procedure ........
some_variable varchar2(128);
begin
some_variable := to_char(paramDate,'mm/yyyy');
-- and your query could read
select id, name from customer where period_aded = some_variable;
.
.
.
end;
/
Kt
千吨
回答by akf
Looking at write-ups on the DETERMINISTIC keyword (here is one, here is another), it was introduced to allow the developer to tell Oracle that the function will return the same value for the same input params. So if you want your functions to be called only once, and you can guarantee they will always return the same value for the same input paramsyou can use the keyword DETERMINISTIC.
查看关于 DETERMINISTIC 关键字的文章（这里是一个，这里是另一个），引入它是为了允许开发人员告诉 Oracle 该函数将为相同的输入参数返回相同的值。因此，如果您希望您的函数只被调用一次，并且您可以保证它们始终为相同的输入参数返回相同的值，您可以使用关键字 DETERMINISTIC。
With regards to built-in functions like to_char, I defer to those who are better versed in the innards of Oracle to give you direction.
至于诸如 的内置函数to_char，我请那些更精通 Oracle 内部结构的人为您提供指导。
