There is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only the EMP and DEPT tables to protect the suspects and maybe innocent.
The function defined is something like this:
FUNCTION getenames(deptno_in IN emp.deptno%type) RETURN VARCHAR2
IS
TYPE enames_aa IS TABLE OF emp.ename%type INDEX BY pls_integer;
l_returnvalue VARCHAR2(32767) := '';
l_enames enames_aa;
BEGIN
SELECT e.ename
BULK COLLECT INTO l_enames
FROM emp e
WHERE e.deptno = deptno_in
ORDER BY e.ename ASC NULLS FIRST;
IF l_enames.count > 0 THEN
FOR indx IN 1 .. l_enames.count LOOP
l_returnvalue := l_returnvalue || l_enames(indx) || ',';
END LOOP;
END IF;
l_returnvalue := rtrim(l_returnvalue, ',');
RETURN l_returnvalue;
END;
and the query executed is something like this:
SELECT d.dname, getenames(d.deptno) enames FROM dept d
The result of this query is:
DNAME ENAMES -------------- ---------------------------------------------------------------------- ACCOUNTING CLARK,KING,MILLER RESEARCH ADAMS,FORD,JONES,SCOTT,SMITH SALES ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD OPERATIONS
The Oracle database performs a lot of (not with these demo tables, though) Context Switches between the SQL Engine and the PL/SQL Engine. And even worse, it performs Context Switches in the PL/SQL code back to the SQL Engine. The PL/SQL code has already been optimized using Bulk Processing, but still. The fastest way of doing something is by not doing it at all.
The SQL engine in Oracle 11G has been enhanced with functionality that does exactly what is now being done by the PL/SQL Function.
The query has to be rewritten a bit (well, actually a lot) but the result is just SQL so no more Context Switching:
SELECT d.dname, (SELECT listagg(e.ename, ',') WITHIN GROUP(ORDER BY e.ename)
FROM emp e
WHERE 1=1
AND e.deptno = d.deptno
) enames
FROM dept d
If you run this statement you’ll get the exact same result as before, but with execution time. With these demo tables it is probably not so obvious that this approach is faster, but if you have real-world examples with real-world data then you will probably benefit from the time spent in the query.
Bottom line is, the Oracle SQL Engine gets more powerful every release and it may be so powerful it can take care of PL/SQL solutions you came up with in the past. And even though it is hard to say goodbye to working code, using SQL can dramatically speed up the execution of your code.
This post has been cross-posted to blog.bar-solutions.com
