Bar Solutions

Subscribe to Bar Solutions feed
The beginning of knowledge is the discovery of something we do not understand. [Frank Herbert]
Updated: 14 hours 57 min ago


Sun, 2001-12-02 18:00

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:

.code, .code pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .code pre { margin: 0em; } .code .rem { color: #ff0000; } .code .kwrd { color: #008080; } .code .str { color: #0000ff; } .code .op { color: #0000c0; } .code .preproc { color: #cc6633; } .code .asp { background-color: #ffff00; } .code .html { color: #800000; } .code .attr { color: #ff0000; } .code .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .code .lnum { color: #606060; }

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

Oracle PL/SQL Programming 6th edition by Steven Feuerstein

Sun, 2001-12-02 18:00

opp6_catI have reviewed the latest edition of Oracle PL/SQL Programming. This is not a book, at least for me, that I would read front to back. I use it mostly as a reference, but the great thing about this book is that you can also use this book to learn programming PL/SQL from scratch.

The book is nicely ordered into different parts, like Program structure and how to use SQL in PL/SQL (which by the way to should minimize as much as possible).  Besides all the information on how to use the PL/SQL language, there are also reference parts with for instance the Metacharacters for Regular Expressions or the number format models. Something you will probably not learn by heart if you don’t use it regularly.

Although Oracle release 12.1 doesn’t bring major changes to the PL/SQL language, there are some areas where it has been changed and improved. This doesn’t mean by the way this book shouldn’t be used when you are running an older version of Oracle. It is an invaluable reference when you are programming for Oracle version 11 or even 10. Even though Oracle doesn’t support the older versions anymore, you can still use some of the techniques in this book. Maybe you’ll learn about possibilities that you cannot use yet, because of the version you are on, but it may give you an idea on how to implement it anyway or information on why you should upgrade to a newer version. If you database supports Conditional Compilation (Page 1064) which is available since version 10G Release 2 you could already start implementing the new techniques and if your organization upgrades to a newer version you will use it right away. For instance the Function Result Cache (Pages 853 – 868) would be a great candidate for this.

This is a rather big book (one of the biggest I have on this subject), but it’s also one of the best. A good way to get your work done is know stuff is possible and find out the details when you want to use it. Even though I have been working with the language for more than 14 years, there a still some areas where I need to get the extra information and that’s where this book comes is really handy.