Number of evaluations of function in where clause of query

Question

Let's have a look at this select:

SELECT table1.x,table2.y 
FROM   table1 
JOIN   table2 ON (table1.z = table2.z)
WHERE  table1.id = myfunction(12324);

How many times will be myfunction called in Oracle database? Parameter of this function is a contstant number value, it is not dependent on row values.

I know some databases will evaluate this function only once, but I couldn't find what will happen in Oracle.

Answer 1

If you don't mark the function as DETERMINISTIC, it will fire for every row in the table you are comparing it to, so that'd be the # of rows in table1.

If you mark it as DETERMINISTIC, it should fire once regardless of which table you are comparing it to because it caches the result and sees that the parameter never changes. If instead of a literal you were to pass in a column value, it would execute once per distinct parameter value.

This is true whether you use it in the WHERE clause or the SELECT clause.

If you can't mark it as deterministic, you can force it with a materialized CTE:

  WITH fx AS (SELECT /*+ materialize */ myfunction(12324) fxval FROM dual)
    SELECT table1.x,table2.y 
    FROM   table1
    JOIN   table2 ON (table1.z = table2.z)
    WHERE  table1.id = (SELECT fxval FROM fx)

To test, simply add a dbms_output.put_line('executing...'); line in your function and try various queries with it (limiting your rows to something reasonably small like in the single digits, or the dbms output will take forever to dequeue). You can then inspect the # of dbms output calls to see how many times it actually fired.