使用where子句中的函数的Oracle性能 [英] Oracle performance using functions in where clause

问题描述

在一个存储过程(它的日期参数名为'paramDate')中，我有一个类似的查询

In a stored procedure (which has a date parameter named 'paramDate' ) I have a query like this one

select id, name
from customer
where period_aded = to_char(paramDate,'mm/yyyy')

Oracle会将每一行的paramDate转换为字符串吗?

will Oracle convert paramDate to string for each row?

我确定Oracle不会，但是有人告诉我Oracle会. 实际上，我认为如果函数的参数是约束条件(查询中没有固定值或计算值)，结果应该始终相同，这就是Oracle应该只执行一次此转换的原因. 然后我意识到有时我会在多个函数中执行DML语句，这也许会导致结果值发生变化，即使每一行的值都没有变化.

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.

这应该意味着我应该在将这些值添加到查询之前进行转换.

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.

反正再次...

Oracle将执行一次to_char还是Oracle将对每一行执行一次?

Will oracle execute that to_char once or will Oracle do it for each row?

感谢您的回答

推荐答案

我认为通常不是这种情况，因为它会阻止使用索引.

I do not think this is generally the case, as it would prevent an index from being used.

至少对于内置功能，Oracle应该能够弄清楚它只能评估一次. (有关用户定义的功能，请参见下文).

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).

在这种情况下，正在使用索引(并且并非对每一行都评估该函数):

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:

1. 从Oracle 10.2开始，您可以将函数定义为DETERMINISTIC.

1. Since Oracle 10.2, you can define the function as DETERMINISTIC.

在较旧的版本上，您可以重新措辞以使用标量子查询缓存":

On older versions you can re-phrase it to use "scalar subquery caching":

选择计数(*) 来自员工 WALE SALARY =(从双选SELECT getValue(1));

SELECT COUNT(*) FROM EMPLOYEES WHERE SALARY = (SELECT getValue(1) FROM DUAL);

这篇关于使用where子句中的函数的Oracle性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！