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:

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

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

Answer 1

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

1. Functions may prevent indexes. A function-based index can solve this issue.
2. Functions may prevent partition pruning. Hard-coding values or maybe virtual column partitioning are possible solutions, although neither is likely helpful in this case.
3. 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.
4. 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.

Answer 2

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.