By Patch ~ April 3rd, 2014. Filed under: Oracle, PL/SQL, SQL.

plsql_logoThere 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
  TYPE enames_aa IS TABLE OF emp.ename%type INDEX BY pls_integer;
  l_returnvalue VARCHAR2(32767) := '';
  l_enames enames_aa;
  SELECT e.ename
    BULK COLLECT INTO l_enames
    FROM emp e
   WHERE e.deptno = deptno_in
  IF l_enames.count > 0 THEN
    FOR indx IN 1 .. l_enames.count LOOP
      l_returnvalue := l_returnvalue || l_enames(indx) || ',';
  l_returnvalue := rtrim(l_returnvalue, ',');
  RETURN l_returnvalue;

and the query executed is something like this:

SELECT d.dname, getenames(d.deptno) enames
  FROM dept d

The result of this query is:

-------------- --------------------------------------------------------------------------------

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

Reader's Comments

  1. Paul | April 5th, 2014 at 12:35 am

    My guess is that a lot of people don’t know the “advanced” SQL features (especially the newer ones) such as LISTAGG, so they’ll still revert to what they know and hand-crank it in PLSQL.

  2. Patch | April 5th, 2014 at 8:18 am

    You are absolutely right Paul, but this shows how important code review is. Someone else might know the newer features. I think code review should take place on ‘old’ code when you migrate to a new database version.

  3. Sayan Malakshinov | April 5th, 2014 at 9:48 pm

    btw, there are some variants to do the same on versions <11G with sql only, e.g. with model clause or sys_connect_by_path.
    Example with model clause on 10.2:

  4. Sayan Malakshinov | April 5th, 2014 at 9:55 pm

    Oh, i’ve just remembered that i wrote about this recently:

Leave a Comment

Our partners: buy essay