pl/sql query optimization with function call in where clause

Question

I am trying to optimize a query where I am using a function() call in the where clause.

The function() simply changes the timezone of the date.

When I call the function as part of the SELECT, it executes extremely fast (< 0.09 sec against table of many hundreds of thousands of rows)

select 
  id, 
  fn_change_timezone (date_time, 'UTC', 'US/Central') AS tz_date_time,
  value 
from a_table_view
where id = 'keyvalue'
and date_time = to_date('01-10-2014','mm-dd-yyyy')

However, this version runs "forever" [meaning I stop it after umpteen minutes]

select id, date_time, value 
from a_table_view
where id = 'keyvalue'
and fn_change_timezone (date_time, 'UTC', 'US/Central')  = to_date('01-10-2014','mm-dd-yyyy')

(I know I'd have to change the date being compared, its just for example)

So my question is two-fold:

If the function is so fast outside of the where clause, why is it so much slower than say using TRUNC() or other functions (obviously trunc() doesnt do a table lookup like my function - but still the function is very very fast outside the where clause)
What are alternate ways of accomplishing this outside of the where clause ?

I tried this as an alternative, which did not seem any better, it still ran until I stopped the query:

select
  tz.date_time,
  v.id, 
  v.value
from 
  (select
    fn_change_timezone(to_date('01/10/2014-00:00:00', 'mm/dd/yyyy-hh24:mi:ss'), 'UTC',     'US/Central') as date_time
    from dual
    ) tz
  inner join   
 (
  select 
    id, 
    fn_change_timezone (date_time, 'UTC', 'US/Central') AS v_date_time,
    value 
  from a_table_view
  where id = 'keyvalue'
  ) v ON
    v.tz_date_time = tz.date_time

Hopefully I am explaining the issue well.

Is your function created as an "invariant" or "constant" function? That tells the optimizer it doesn't need to reinvoke the function for every set of identical parameters. — Andy Lester, Apr 04 '14 at 17:56
I do not understand those terms in relation to Oracle functions. Google was not helpful when I searched. — bbaley, Apr 04 '14 at 18:05
Sorry, my mistake, you want to look at "deterministic functions". http://www.dba-oracle.com/plsql/t_plsql_deterministic.htm — Andy Lester, Apr 04 '14 at 18:06
If I understand correctly - the function is NOT defined with the DETERMINISTIC keyword, and, it will alwyas return the same value based on the same input value. it simply does a table lookup, and performs a from_tz() with some cast()'ing — bbaley, Apr 04 '14 at 18:11
Can you redefine the function to be DETERMINISTIC? If so, that should help the optimizer. Also, if the function is doing table lookups behind the scenes, that's going to make things slow, too. — Andy Lester, Apr 04 '14 at 18:21
unfortunately no. I did try putting the functionality of the call into the where clause in place of the actual function call - which seems to perform "ok".... so I am guessing the optimizer is able to figure it out this way.... but it makes for a really ugly query.... — bbaley, Apr 04 '14 at 18:35
As you found out, function calls in `where` clauses disable indexes and are not performant. Yes, try to make it `DETERMINISTIC`, but if the function is just a SQL call to a lookup table, then I would write that SQL into your query and not encapsulate it in a function. Pure SQL is always faster. Try making a **view** to contain the SQL logic and join to or select from that as way of simplifying your query. — WoMo, Apr 04 '14 at 23:24

score 2 · Answer 1 · answered Apr 06 '14 at 22:17

There are at least four potential issues with using functions in the WHERE clause:

Functions may prevent indexes. A function-based index can solve this issue.
Functions may prevent partition pruning. Hard-coding values or maybe virtual column partitioning are possible solutions, although neither is likely helpful in this case.
Functions may run slowly. Even if the function is cheap, it is often very expensive to switch between SQL and PL/SQL. Some possible solutions are DETERMINISTIC, PARALLEL_ENABLE, function result caching, defining the logic in purely SQL, or with 12c defining the function in SQL.
Functions may cause bad cardinality estimates. It's hard enough for the optimizer to guess the result of normal conditions, adding procedural code makes it even more difficult. Using ASSOCIATE STATISTICS it is possible to provide some information to the optimizer about the cost and cardinality of the function.

Without more information, such as an explain plan, it is difficult to know what the specific issue is with this query.

understood, that function calls in the where clause are a bad I deal and not easily optimized. thats why I am looking for alternatives. unfortunately I cannot change the function to be deterministic, so likely I will have to find another way to elimnate the function call. — bbaley, Apr 07 '14 at 20:06
Without the explain plan it's hard to tell which option will help. You may not even necessarily need to change the function, perhaps just change how Oracle calls the function (indirectly through option #4). — Jon Heller, Apr 07 '14 at 21:27

score 1 · Answer 2 · answered Apr 04 '14 at 22:14

Function calls in the WHERE clause are a Bad Thing. The problem is that the function may be called for every row in the table, which may be many more than the selected set. This can be a real performance killer (don't ask me how I know :-). In the first version with the function call in the SELECT list the function will only be called when a row has been chosen and is being added to the result set - in the second version the function may well be called for every row in the table. Also, depending on the version of Oracle you're using there may be significant overhead to calling a user function from SQL, but I think this penalty has been largely eliminated in versions since 10g.

Best of luck.

Share and enjoy.

pl/sql query optimization with function call in where clause

2 Answers2