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

Thilo · Accepted Answer · 2009-07-13 01:44:43Z

8

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:

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

edited Jul 13, 2009 at 1:44

answered Jul 13, 2009 at 1:31

Thilo

264k107 gold badges527 silver badges674 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

l0b0 Over a year ago

+1, but "deterministic" is available in 9.2 - I'm using it there already.

deFreitas Over a year ago

Using a function in the column in where clause forced oracle to do not use the index, something like WHERE trunc(my_date_column) = trunc(sysdate) oracle 11. Reference

APC Over a year ago

@deFreitas - yeah but that's different from the example included in the answer. Applying a function to a table column will disable any index on that column. This is known behaviour, because the whole point of an index is that it indexes and makes searchable known values, and applying a function to a column changes its value. The exception is if the index is a function-based index: create index fbi on your_table(trunc(my_date_column)).

akf · Accepted Answer · 2009-07-13 01:45:28Z

1

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.

answered Jul 13, 2009 at 1:45

akf

39.6k8 gold badges89 silver badges96 bronze badges

Comments

FerranB · Accepted Answer · 2009-07-14 20:05:01Z

1

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

edited Jul 14, 2009 at 20:05

FerranB

37k18 gold badges70 silver badges87 bronze badges

answered Jul 13, 2009 at 1:09

Karl T.

5023 silver badges3 bronze badges

1 Comment

Nicolás Over a year ago

Thanks, I was trying to know if that is something that would improve the SP performance, that would be the only reason to go and change all the existing ones. Luckily it seems not to be necessary

Collectives™ on Stack Overflow

Oracle performance using functions in where clause

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related