Oracle performance using functions in where clause

Question

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

will Oracle convert paramDate to string for each row?

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.

Anyway, again...

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

Thanks for your answers

Answer 1

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

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. Since Oracle 10.2, you can define the function as DETERMINISTIC.

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

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