Geek Answers Handbook

Oracle performance using functions in the where clause

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

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 would not, but I was told that Oracle would. In fact, I thought that if the function parameter was a constraint (not obtained or not calculated value inside the query), the result should be the same, and therefore Oracle should perform this conversion only once. Then I realized that I sometimes executed DML sentences in several functions, and perhaps this could lead to a change in the resulting value, even if it does not change for each row.

This should mean that I have to convert such values ​​before adding them to the query.

In any case, perhaps well-known functions (built-in) are evaluated once, or even my functions will also be.

Anyway, again ...

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

thank you for your responses

+4
source share
3 answers

I do not think this is a general case, as this will prevent the use of the index.

At least for built-in functions, Oracle should be able to understand that it can only evaluate it once. (For custom functions, see below.)

Here is an example using an index (and the function is not evaluated for each 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 custom features, check out this article . It mentions two ways to ensure that your function is called only once:

  • With Oracle 10.2, you can define a function as DETERMINISTIC.

  • In older versions, you can reuse it to use "scalar caching of subqueries":

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

+8
source

Worry about tochchar is not calling with me. However, in your pl / sql, you could

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

Ct

+1
source

Looking at the entries for the DETERMINISTIC keyword ( here is one , here is another option ), it was suggested to give the developer the opportunity to tell Oracle that the function will return the same value for the same input parameters. Therefore, if you want your functions to be called only once, and you can guarantee that they will always return the same value for the same input parameters , you can use the DETERMINISTIC keyword.

As for the built-in functions like to_char , I put aside those who are better versed in Oracle internals to give you direction.

+1
source

More articles:


All Articles