Optimize a SQL query : call a function or do a join?

Question

What is the fastest in an ORACLE database ?

Call a function inside a select statement to retrive a single value for each row

SELECT field1, field2, F_GET_LIBELLE_STATUT( field2 ) FROM table1 WHERE ...

with the simple function :

create or replace
FUNCTION "F_GET_LIBELLE_STATUT" (v_statut NUMBER) RETURN VARCHAR2 IS
tmpVar VARCHAR2(40);
BEGIN
   select libelle into tmpVar from t_statut_ope where id_statut = v_statut;
   RETURN tmpVar;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       RETURN '';
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END f_get_libelle_statut;

Or doing a join in the select statement ?

Select a.field1, a.field2, b.libelle
FROM table1 a LEFT JOIN t_statut_ope b ON b.id_statut = a.field2
WHERE ...

And is it the same answer if I call a lot of functions (ten or more) in the select and in the clause condition ?

Thanks for your answer.

Answer 1

Anything which can be done in SQL should be done in SQL (*).

A join will always be faster than calling that function because set-based operations are always faster than row by agonizing row. Then you have the overhead of moving between SQL and PL/SQL. Plus the overhead of handling those NO_DATA_FOUND exceptions, which aren't truly exceptions because gaps are expected and tolerated. Finally, the optimizer will pick a better plan for the pure SQL option because you have given it all the information it needs.

---

(*) I answered this question way back when and I answered it focused on the example given, and I stand by that answer. If we can get the data we need using SQL against tables (via joins, views, inline views, subqueries) then we should do that. But I want to drill down into the underlying question: why does Oracle support the use of functions in queries? Because sometimes we need to do things we can't do in SQL.

Here are use cases for calling functions that execute SQL (I am thinking primarily of functions invoked through table() calls ) instead of using a join (or other SQL constructs such as inline view, WITH clause) :

1. Query is dynamic. Dynamic SQL requires PL/SQL so a function is yer only man here.
2. Row generation. Using PL/SQL to split the input into multiple strings (such as CSV tokenizing) or perhaps to spawn data, not from a table. Occasionally still valid but regex support and the nifty CONNECT BY LEVEL <= N trick have binned the more common usages.
3. The data is encapsulated behind a PL/SQL API so a function is all we can call.
4. For some bizarre reason we can only get the performance we need by using PL/SQL to implement a filter or a look-up. Apparently. To be honest I can't ever remember a case where I had to do this and it worked (although I've had a few cases where turning a pipelined function into a subquery or inline view improved performance). Maybe I've led a sheltered life. Certainly, I would welcome benchmarked citations for counter-examples.