A few days ago I spoke with another developer about SQL embedded into PL/SQL code and how function calls are handled in the WHERE clause of SQL statements. There is sometimes confusion who (PL/SQL engine or SQL engine) is executing it, I thought it's a good idea to write a posting about it.
Last year I have already blogged about it in
Caution when using PL/SQL functions in a SQL statement, but that was in the context of writing a stand alone SQL statement for a report, ... but what actually happens if you have a SQL embedded into PL/SQL like the following example procedure code in a package
PROCEDURE processEmps
( pDepartmentId IN NUMBER
)
IS
BEGIN
FOR rEMP IN
( SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE SALARY > getAvgDeptSalary(pDepartmentId)
)
LOOP
NULL;
END LOOP;
END processEmps;
(Note: getAvgDeptSalary isn't a very good example, because you could also do that with SQL only, but it's just an example)Very simple. A function in the WHERE clause which uses the
procedure parameter pDepartmentId. What would you expect how the code/SQL is executed?
Do you expect the code is executed in the following order?
- Procedure processEmps is called
- Function getAvgDeptSalary gets executed by the PL/SQL engine, because the PL/SQL engine has already all input parameters for the function call. No values from the query are needed. The result gets stored into an internal temporary bind variable.
- Context switch to the SQL engine which executes the query on EMPLOYEES, restricted to SALARIES greater than the value in internal temporary bind variable.
Sorry you are wrong!
The hole SQL statement is handed over to the SQL engine, which then does a call to the getAvgDeptSalary function. That could be one time if you are lucky and the optimizer is clever enough, but it could also be for each row which is processed by the query!!! So it's the same processing as already described in my Caution when using PL/SQL functions in a SQL statement! You can also easily identify that behavior by yourself, because there are some restrictions in the usage of function calls in the WHERE clause:- You can only use public functions.
- You can't use the non-positional syntax (at least in < 11g) for the function parameters.
That's because the SQL engine can only use public PL/SQL functions and can't handle the non-positional syntax for parameters.
So if you have somewhere in your code such function calls (like the V or NV functions in Oracle APEX), be aware that this could be a performance bottleneck! Do the function call before the SQL statement and use a variable to restrict the query.
Labels: caution, performance, pl/sql, SQL
« ... Read full posting ... »
