Let's say we have tables Blog and Post. Blog is one to many to Post, Blog.id = Post.b_id

And we have this query

SELECT p.*, b.*, func(b.id) AS funcres 
FROM Blog b, Post p 
WHERE b.id = p.b_id;

In this query function func will be called as many times as rows are found (total number of Post).

Is there a way to call func only once per Blog ?

The only way is working for me is

SELECT 
    p.*, b.*, 
    (SELECT func(b.id) FROM Blog b_ WHERE b_.id = b.id) AS funcres 
FROM 
    Blog b, Post p 
WHERE 
    b.id = p.b_id;

but this looks strange...

Let's say we have tables Blog and Post. Blog is one to many to Post, Blog.id = Post.b_id

And we have this query

SELECT p.*, b.*, func(b.id) AS funcres 
FROM Blog b, Post p 
WHERE b.id = p.b_id;

In this query function func will be called as many times as rows are found (total number of Post).

Is there a way to call func only once per Blog ?

The only way is working for me is

SELECT 
    p.*, b.*, 
    (SELECT func(b.id) FROM Blog b_ WHERE b_.id = b.id) AS funcres 
FROM 
    Blog b, Post p 
WHERE 
    b.id = p.b_id;

but this looks strange...

• sql
• oracle-database

Share Improve this question Follow edited Mar 7 at 17:02 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 7 at 15:33 IlyaIlya 29.8k1919 gold badges119119 silver badges161161 bronze badges 9

• 1 A minimal reproducible example is a great start when asking for SQL assistance. Also, are you using Postgresql or Oracle, those aren't fully compatible. – jarlh Commented Mar 7 at 15:34
• What does this function do, and why do you care if it runs multiple times? – Charlieface Commented Mar 7 at 15:39
• The dbms optimizer decides the actual execution order. Don't try to fool it, it won't make things better. – jarlh Commented Mar 7 at 15:42
• 4 Tip of the day: Always use modern, explicit JOIN syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! – jarlh Commented Mar 7 at 15:46
• 1 Then restrict this question to Oracle and create another one for Postgres. – Adrian Klaver Commented Mar 7 at 16:14

 |  Show 4 more comments

4 Answers 4

Sorted by: Reset to default 3

Evaluate the function before the join and use ROWNUM (in a seemingly innocuous filter that gets Oracle to evaluate and enumerate the rows) to force Oracle to materialize the sub-query rather than pushing the predicates to the outer query:

SELECT p.*,
       b.*
FROM   (SELECT b.*,
               func(b.id) AS funcres
        FROM   Blog b
        WHERE  ROWNUM > 0
       ) b
       INNER JOIN Post p 
       ON b.id = p.b_id;

Alternatively, use the /*+ NO_MERGE */ hint in the inner query or the /*+ no_push_pred(a) */ hint in the outer query.

Another option that doesn't require a rewrite at all is simply to mark the function as deterministic:

create or replace func(id IN integer)
return integer 
deterministic -- add this
as
begin
  dbms_output.put_line('called!');
  return id + 1;
end;

Now use your existing SQL as-is:

SELECT p.*, b.*, func(b.id) AS funcres 
FROM Blog b, Post p 
WHERE b.id = p.b_id;

You should see "called!" only once per distinct Blog.id value, even if you see that same value in more than one row because of a one-to-many join to another table. If you were to change b.id to rownum you'd see it go back to executing the function for every single row (since in that case every row would pass a different argument value to the function).

Explanation: Oracle has an optimization that avoids calling a function more than once during a single SQL execution if it sees you are calling it with the exact same arguments already used, but only if it is marked as deterministic, which means you a promising that for a given set of input values, the result will always be the same. Obviously, to be truly deterministic, a function cannot read from a table, or use SYSDATE, or anything else that might change the result over time. But Oracle will trust you (even if you lied, and it isn't actually deterministic). Telling it that it is giving Oracle permission to skip calling it if it already knows what value it would return.

The advantage of this approach is that it doesn't require use of hints to try to coerce the optimizer into the correct step order to accomplish the same thing without this optimization. That might be a better choice.

Use inline query blocks to control order of execution. Oracle may collapse them (view merging), so to prevent that, use the no_merge hint. Also on the outside chance (if Post is tiny and Blog is large) that Oracle decides to do a nested loops join, prevent it from starting with Post using the leading hint:

SELECT /*+ leading(b) */ *
  from (select /*+ no_merge */ b.*,func(b.id) as funcres FROM Blog b) b, 
       Post p 
 WHERE b.id = p.b_id;

What about a Common Table Expression ?

WITH b AS (
    SELECT /*+ MATERIALIZE */ b.id, b.column1, b.column2, func(b.id) AS func_result
    FROM Blog b
)
SELECT p.*, b.*
FROM b
JOIN Post p ON b.id = p.b_id;

Update: I fet to add the MATERIALIZE hint, which will create a global temporary table that will prevent the merge. Alternatively, the NO_MERGE hint should work too.

EDIT:

Also, it might be worth of mention that, in Oracle Database, if func is a PL/SQL function with the DETERMINISTIC attribute and the Result Cache has the right size, you could just add the /*+ RESULT_CACHE */ hint to your original query, and the function would be actually executed only once per id while, the following times, the result would be retrieved from the Result Cache.