PL/SQL vs SQL

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

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

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

Using Table Functions

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

Overview of table functions

Table functions are functions that produce a collection or rows (either a nested table or a varray) that can be queried like a physical database table. You use a table function like the name of a database table, in the FROM clause of a query. The difference with a physical database table in the FROM clause of the query is that you need to use the TABLE() operator to tell the SQL engine that it should treat whatever comes out of the function as if it were a normal table.

Continue reading

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

Typical

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

After a presentation by Lucas Jellema I decided to try something with types in Oracle. One of the issues posed in this

table_EMP

presentation was that the type cannot self-reference. Neither direct nor indirect.

 

A table like the emp table cannot be expressed as an object type. The table has a column mgr which is a reference to another employee.

type_EMP

So I tried something like this:

TYPE emp_t AS OBJECT
( empno number(4)
, ename varchar2(10)
, job varchar2(9)
, mgr emp_t
, hiredate DATE
, sal number(7,2)
, comm number(7,2)
, deptno number(2)
)

 

This results in the following error:

Warning: Type created with compilation errors

Errors for TYPE DEMO.EMP_T:
LINE/COL ERROR
-------- ------------------------------------------------------------------------------------
1/1      PLS-00318: type "EMP_T" is malformed because it is a non-REF mutually recursive type
0/0      PL/SQL: Compilation unit analysis terminated

So, self referencing is not possible. But you can create hierarchical sets of types, where you extend one type in a child type. But the type could not include a self-reference, neither direct nor indirect.

Time to try it out. Don’t ever take for granted what anyone says, including me, always try it out.

type_PERS_T_EMP_T

My script was the following. I have a person type with all the information needed for this person and then, ‘below’ that I have an employee type:

TYPE pers_t AS OBJECT
( empno number(4)
, ename varchar2(10)
, job varchar2(9)
, hiredate DATE
, sal number(7,2)
, comm number(7,2)
, deptno number(2)
) NOT FINAL
TYPE emp_t FORCE UNDER pers_t
(
  mgr pers_t
)

And the output for these statements is:

Type created
No errors for TYPE DEMO.PERS_T
Type created
No errors for TYPE DEMO.EMP_T

Now, I know you can create objects in Oracle which cannot be called easily. Think about a package, with an overloaded function that has ambiguity in its parameters. Steven Feuerstein has a great article on this. Oracle will let you compile this package, but you can never call these programs. So, it’s time to test if it works. I created an anonymous block to try this:

DECLARE
  TYPE emps_tt IS TABLE OF emp_t INDEX BY PLS_INTEGER;
  l_emps emps_tt;
  l_mgr emp_t;
  l_emp emp_t;
  l_indx pls_integer;
BEGIN
  FOR rec IN (SELECT e.*
    FROM emp e
  CONNECT BY PRIOR e.empno = e.mgr
  STARt WITH e.mgr IS NULL) loop
    -- Check if it is the PRESIDENT
    IF rec.mgr IS NULL THEN
      l_mgr := emp_t(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
    ELSE
      l_mgr := l_emps(rec.mgr);
    END IF;
    l_emp := emp_t( rec.empno
                  , rec.ename
                  , rec.job
                  , rec.hiredate
                  , rec.sal
                  , rec.comm
                  , rec.deptno
                  , l_mgr);
    l_emps(rec.empno) := l_emp;
  END loop;
  l_indx := l_emps.first;
  LOOP
    EXIT WHEN l_indx IS NULL;
    dbms_output.put_line(l_emps(l_indx).mgr.empno || ' ' ||
                         l_emps(l_indx).mgr.ename || ' => ' ||
                         l_emps(l_indx).empno || ' ' ||
                         l_emps(l_indx).ename
                        );
    l_indx := l_emps.next(l_indx);
  END loop;
END;

And, to my surprise, it worked.

So, you cannot self-reference objects (which makes sense because the object being referenced doesn’t exist when you reference it), but you can reference parent objects.

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

APEX 5.0 Early Adopter

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

The long awaited Early Adopter installation is finally available at http://apexea.oracle.com. You can request a workspace here to play around with the new version. I have just been playing around with it for about an hour an from what I’ve seen it’s a big change to what we’re used to since APEX 4.

Not only has the look an feel changed to flat ApplicationBuilder  ApplicationBuilder, but I think the biggest visible change is the new application builder. Where APEX 4.0 introduced the treeview builder, which was a big change from the component view we were used to before 4.0. I liked the treeview a lot better after just using it a little while. Now APEX 5.0 gives us an all new Page Designer. The interface looks a lot like the interfaces you will find in the current IDE’s like JDeveloper etc.

It takes a bit of searching to find everything I used in APEX 4 but I think I will get used to the new interface very soon and I will probably start wondering how I could have ever lived without it soon enough.

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

Webinar on Edition Based Redefinition

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

On February 13th I will be giving a webinar on Edition Based Redefinition hosted by Red Gate Software.
I will be talking about how EBR can be used. It will not be an in-depth session, rather an overview of how EBR works or actually what you should do so you can use it. I am aiming for a presentation of about 30 minutes. This should leave some time to try and answer some questions.

image

More info and registration here:
http://www.red-gate.com/products/oracle-development/deployment-suite-for-oracle/education/webinars/webinar-oracle-edition-based-redefinition-patrick-barel

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page