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

Solution 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);

Solution 2

The concern about to_char does not ring a bell with me. However, in your pl/sql, you could have

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

Solution 3

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 params you can use the keyword 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.