When a user-written function is embedded in a statement,
the function may be called a large number of times. If the function
appears within the
select list, it is
called for each returned row. If it appears within the
where clause, it is called for each and every
row that has successfully passed the filtering criteria previously
evaluated. This may be a considerable number of times if the other
criteria are not very selective.
Consider what happens if that same function executes a query. The query is executed each time the function is called; in practice, the result is exactly the same as a correlated subquery, except that the function is an excellent way to prevent the cost-based optimizer from executing the main query more intelligently! Precisely because the subquery is hidden within the function, the database optimizer cannot take any account of this query. Moreover, the stored procedure is not as close to the SQL execution engine as is a correlated subquery, and it will consequently be even less efficient.
Now I shall present an example demonstrating the dangers of hiding
SQL code away inside a user-written function. Consider a table
flights that describes commercial flights,
with columns for flight number, departure time, arrival time, and the
usual three-letter IATA[*] codes for airports. The translation of those codes (over 9,000 of them) is stored in a reference table that contains the name of the city (or of the particular airport ...