16. Dezember 2008

Subquery oder Inline View? Die SQL WITH Klausel und deren Ausführung

English title: Subquery or inline view: The WITH clause and its execution

Als letztes Posting für dieses Jahr möchte ich ein wenig was über die WITH-Klausel erzählen; ich selbst nutze sie ziemlich häufig und auch sehr gerne - aber immer wieder bekomme ich dann Fragen wie: "Was ist denn das ...?". Zunächst ein Beispiel - damit ist alles leichter ...
My last posting for this year will be about the SQL WITH clause. I use it very frequently but also get often questions like "What's that?. We'll start with an example:
with dept_sal as (
  select d.deptno, d.dname, sum(e.sal) sum_sal
  from dept d join emp e on (d.deptno = e.deptno)
  group by d.deptno, d.dname
)
select deptno, dname, sum_sal
from dept_sal 
where sum_sal > 2000
Wie man sieht, ist die WITH-Klausel ein etwas andere Form der Subquery; die "Subquery" erhält mit der WITH-Klausel einen Namen und kann unter diesem wie eine View nachfolgend beliebig wiederverwendet werden. Daher wird die Subquery innerhalb einer WITH-Klausel auch Inline View genannt. Und natürlich gehen auch mehrere WITH-Klauseln in einer Abfrage:
The WITH clause is, as you can see, another form of a subquery. The subquery gets a name (which is valid only for this particular SQL query) and can be reused under this name as often as the developer wants. Since this is very similar to a view the query inside a WITH clause is also called an inline view.
with dept_sal as (
  select d.deptno, d.dname, sum(e.sal) sum_sal
  from dept d join emp e on (d.deptno = e.deptno)
  group by d.deptno, d.dname
), dept_sal_mit_avg as (
  select 
    deptno, dname, sum_sal,
    avg (sum_sal) over (order by dname) dept_sum_sal_avg
  from dept_sal
)
select deptno, dname, sum_sal
from dept_sal_mit_avg 
where sum_sal > dept_sum_sal_avg
/
Ein direkter Vorteil der WITH-Klausel gegenüber einer "normalen" Subquery ist, dass man die Inline Views mehrfach wiederverwenden kann. Eine Subquery müsste man mehrfach in die Abfrage hineinschreiben. Persönlich bin ich der Ansicht, dass komplexere Abfragen mit Inline Views übersichtlicher bleiben als mit Subqueries - aber dazu gibt es bestimmt auch andere Ansichten.
One direct advantage of the WITH clause over a traditional subquery is that it can be used multiple times. A subquery would have to created as view objects or the actual query would have to be repeated. And my personal thinking is that complex queries are much better readable with inline views instead of subqueries.
Schauen wir uns nun noch an, wie der Optimizer mit einer WITH-Klausel umgeht. Das ist vor allem dann interessant, wenn man deren größten Vorteil nutzen, eine Inline View also mehrfach in einer Abfrage nutzen möchte. Dazu ein Beispiel.
Now let's have a look how the optimizer deals with the inline view. This is particular interesting when we use the inline view multiple times and the inline view query is expensive. The following example illustrates this:
set serveroutput on

create or replace function doit return number as
begin
  dbms_output.put_line('function called.');
  return 1;
end;
/

with do_func as (
  select doit val from dual
)
select val from do_func, emp
/
Das Beispiel ruft die Funtion doit in einer Inline View als SELECT .. FROM DUAL auf. Diese Inline View wird anschließend mit der Tabelle EMP per Join zusammengeführt. Nun ist die Frage: Wie oft wird doit ausgeführt. Die Codezeile mit DBMS_OUTPUT sagt es uns.
The function doit (which just writes a line with DBMS_OUTPUT) is being used in an inline view as SELECT ... FROM DUAL. Now this inline view gets joined with the EMP table (14 rows).Now the question: How often gets the function called?

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
:
:
function called.
Der Ausführungsplan:
Execution Plan:
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    14 |     5   (0)| 00:00:01 |
|   2 |   FAST DUAL        |      |     1 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------
Die Ausgabe function called sehen wir 14 mal; der Optimizer hat die Inline View also mit der "Hauptabfrage" gemerged und führt die Funktion entsprechend 14 mal aus. Man sieht es im Ausführungsplan: Nested Loops. Wenn die Funktion nun sehr teuer ist, ist genau dies u.U. nicht erwünscht - die Syntax der WITH-Klausel legt ja den Eindruck nahe, dass die Funktion nur einmal ausgeführt wird. Und das kann man auch erreichen: Im Prinzip kommen hierfür zwei Hints in Betracht: NO_MERGE, der in 11g neue RESULT_CACHE und das undokumentierte MATERIALIZE. Probieren wir zuerst NO_MERGE.
We see function called 14 times. The optimizer merged the inline view query with the "main" query and executed it using nested loops. Therefore the function is called for each row of the EMP table. If the function is very expensive this is not a very good execution strategy - and the syntax of the WITH clause leads to the assumption that the function is executed only one time. We'll try Optimizer hints to improve the behaviour: NO_MERGE, the in Oracle11g introduced RESULT_CACHE and the undocumented MATERIALIZE. First we try NO_MERGE.
with do_func as (
  select doit val from dual
)
select /*+ NO_MERGE (d) */ d.val from do_func d, emp e
/

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
function called.
Der Ausführungsplan:
Execution Plan:
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    14 |   182 |     5   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    14 |   182 |     5   (0)| 00:00:01 |
|   2 |   VIEW               |      |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    FAST DUAL         |      |     1 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL  | EMP  |    14 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Die Funktion wurde nun nur noch zweimal ausgeführt. Das ist schon besser, aber man stellt sich schon die Frage, ob man die Datenbank nicht dazu bringen kann, die Funktion einmal auszuführen, sich das Ergebnis zu merken und dann weiterzuarbeiten. Und in Oracle11g kann man dies mit dem Result Cache erreichen. Wenn wir mit einer PL/SQL-Funktion arbeiten, muss diese allerdings als deterministisch deklariert sein. Deterministisch meint, dass gleiche Eingabeparameter zum gleichen Ergebnis führen. Ist das nicht der Fall, so bleibt nur der weiter hinten folgende undokumentierte MATERIALIZE-Hint.
The function is now called twice. This is much better but there is still the question whether it is possible that the optimizer executes the query once, "memorize" the result and continue. And this can be achieved in Oracle11g using the result cache. But when using a PL/SQL function, it is important that this is declared deterministic. Deterministic means that equal input parameters lead to equal function results. If your function is not deterministic then the undocumented MATERIALIZE hint might be a solution.
create or replace function doit return number deterministic as
begin
  dbms_output.put_line('function called.');
  return 1;
end;
/

with do_func  as (
  select /*+ result_cache */ doit val from dual
)
select /*+ result_cache */ d.val from do_func d, emp e
/

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
Die Funktion wird einmal ausgeführt. Der Ausführungsplan:
The function is being executed one. The Execution Plan:
----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                            |    14 |   182 |     5   (0)| 00:00:01 |
|   1 |  RESULT CACHE         | d0r3jha88bjgjgrpt9rgv2aw98 |       |       |            |          |
|   2 |   MERGE JOIN CARTESIAN|                            |    14 |   182 |     5   (0)| 00:00:01 |
|   3 |    VIEW               |                            |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |     RESULT CACHE      | 3q99fnp78n4wr4nbdpj223u8b9 |       |       |            |          |
|   5 |      FAST DUAL        |                            |     1 |       |     2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL  | EMP                        |    14 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=1; dependencies=(SCOTT.EMP, SCOTT.DOIT); 
       name="with do_func  as (select /*+ result_cache */ doit val from dual)
             select/*+ result_cache */  d.val from do_func d, emp e"

   4 - column-count=1; dependencies=(SCOTT.DOIT); attributes=(single-row); 
       name="select /*+ result_cache */ doit val from dual"
Das Ergebnis der Inline View wurde im Result Cache gespeichert (im Ausführungsplan unter 4 erkennbar). Aber was ist, wenn die Funktion gar nicht deterministisch ist - also gleiche Eingabeparameter durchaus (vielleicht zu einer anderen Zeit) zu anderen Ergebnissen führen können)? Wenn die Abhängigkeiten in der Datenbank bekannt sind (bspw. das Funktionsergebnis hängt von den Inhalten einer Tabelle ab) könnte man die Abhängigkeiten deklarieren:
The inline view results were placed into the result cache ("4" in the execution plan). But what if the actual function is not deterministic? If the dependencies are known in the database (e.g. table contents) they can be declared in the function code as follows:
create or replace function doit return number result_cache relies_on ([table]) as
:
Wenn die Funktion nicht deterministisch ist und die Abhängigkeiten nicht bekannt sind (bspw. Zufallswerte, Abhängigkeit von der aktuellen Zeit), dann kann der Result Cache nicht genutzt werden. In diesen Fällen und in Oracle10g kann der undokumentierte MATERIALIZE hint eine Lösung sein.
If the function dependencies are not known in the database (random values, result depends on the current timestamp) then the result cache cannot be used. In those cases and in Oracle10g the undocumented MATERIALIZE hint might be a solution.
with do_func as (
  select /*+ MATERIALIZE */ doit val from dual
)
select d.val from do_func d, emp e
/

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
Man sieht: Die Funktion wurde genau einmal ausgeführt. Schauen wir und den Ausführungsplan an:
The function is executed once. Look at the execution plan:
----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |    14 |   182 |     7   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           | EMP                         |       |       |            |          |
|   3 |    FAST DUAL               |                             |     1 |       |     2   (0)| 00:00:01 |
|   4 |   MERGE JOIN CARTESIAN     |                             |    14 |   182 |     5   (0)| 00:00:01 |
|   5 |    VIEW                    |                             |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6604_40E25BF1 |     1 |    13 |     2   (0)| 00:00:01 |
|   7 |    BUFFER SORT             |                             |    14 |       |     5   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL      | EMP                         |    14 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Der Optimizer "merkt" sich das Ergebnis wirklich in einer temporären Tabelle. Ihr könnt auch nachsehen ... (als SYS)
The optimizer "memorizes" the query result in a temporary table. You can describe it (as SYS).
SQL> desc SYS_TEMP_0FD9D6604_40E25BF1
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 C0                                                 NUMBER
Gerade bei sehr teuren Funktionen (Beispiele wären Funktionen, welche Daten von extern laden) kann dies sehr nützlich sein. Beachtet aber bitte, dass der Hint MATERIALIZE von Oracle nicht dokumentiert ist; man müsste ihn also in jedem neuen Release testen ... Die Nutzung eines undokumentierten Hints sollte stets die "Ultima Ratio" sein.
This is extemely useful for long running or expensive subquries. But keep in mind that Oracle does not document this hint - so you have to test it in new releases. The usage of an undocumented hint should always the the "ultima ratio" - the choice when other things fail.
So - das war's für dieses Jahr. In zwei Wochen iss 'rum - und nächstes Jahr sieht man sich wieder. Bis dahin schöne Feiertage, kommt gut ins Neue Jahr und lasst den Rechner ein paar Tage aus.
That's it ... for 2008. In two weeks the year is over - Season's greetings and in 2009 we'll see each other again.

11. Dezember 2008

IN-Klauseln, dynamisches SQL und Bindevariablen

English title: IN conditions, dynamic SQL and Bind Variables

Auf der DOAG-Konferenz diskutierte ich mit jemandem, wie man IN-Klauseln mit Bindevariablen (für die Java-Programmierer: PreparedStatements) implementieren kann. Das Problem dabei ist, dass die Anzahl der Werte in der IN-Klausel nicht immer gleich ist. Für den Java-Programmierer kann ein Abfrage mit vier Argumenten so aussehen:
At the "DOAG conference" two weeks ago I discussed with somebody how an IN condition within a SQL WHERE clause could be used with bind variables (for with Java words: with PreparedStatements). And the challenge is that the number of arguments is not each time the same. A query with four arguments might look like the following ...
select [col1], [col2], [col3] from [table] where [col4] in (?, ?, ?, ?)
Wenn für die nächste Abfrage nun fünf Werte in die IN-Klausel gesetzt werden sollen, muss ein neues Statement erzeugt und geparst werden - in der Datenbank kann der vorhandene Cursor also nicht wiederverwendet werden; auf Java-Seite muss man neue Objekte erzeugen.
The next query now might have five arguments - so the Java programmer cannot reuse the first query statement. A new object has to be constructed which means that the current cursor in the database can also not be reused.
Ein Lösungsansatz ist, mit temporären Tabellen zu arbeiten; die Werte dort mit SQL INSERT-Kommandos hineinzuschreiben und dann anstelle der konkreten Werte eine Subquery in der eigentlichen Abfrage zu verwenden. Es gibt jedoch auch noch einen anderen Ansatz: Anstelle der temporären Tabelle kann man auch einen Objekttypen nehmen: Abhängig von dem Datentypen, der in der IN-Klausel verwendet werden soll, erzeugt man sich also zunächst einen "Array-Typen":
One possible solution is the usage of temporary tables. So the developer can create a temporary table, insert the arguments into that table and use a subquery in the actual query. But (if you don't like temporary tables) there is also another solution: You also can use object types for this. So first we create an array type suitable for our query.
CREATE TYPE QUERY_ARRAY AS TABLE OF [NUMBER | VARCHAR2(x) | DATE] 
Die SQL-Abfrage muss nun so umgestellt werden:
Next we rearrange the SQL query:
SELECT * FROM emp WHERE empno in (SELECT * FROM TABLE(?))
Wir verwenden wieder eine Subquery; allerdings nicht mit einer temporären Tabelle, sondern mit einer Variablen innerhalb der Funktion TABLE(). Diese Variable muss vom soeben neu erstellten Typ QUERY_ARRAY sein. Dann stellt sich natürlich sofort die Frage, wie man einen solchen Typen (von Java aus) erzeugt ... Hier ist ein Beispiel:
We use (as we would have done with a temporary table) a subquery to embed the array of arguments into the SQL query. The TABLE() function contains a variable of the just created data type QUERY_ARRAY and makes the contents accessible to the SQL engine. This leads immediately to the next question: How are those user-defined data types representated in JDBC code ...? Here is some example code.
import java.sql.*;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

/*
 * This program queries multiple rows from the EMP
 * table using the IN clause.
 *
 * It always uses THE SAME STATIC SQL Query - the number
 * of arguments for the IN clause does not matter.
 *
 * SELECT * FROM emp WHERE empno in (SELECT * FROM TABLE(?))
 *
 * Prerequisite: 
 * - An object type NUMBER_ARRAY must exist in the DB Schema
 *   CREATE TYPE QUERY_ARRAY AS TABLE OF NUMBER   
 *   
 */ 

public class JdbcDynamicInQuery {
  static String sConn = "jdbc:oracle:thin:@192.168.2.140:1521:orcl";
  static String sUser = "scott";
  static String sPass = "tiger";
  static String sSql =  "SELECT * FROM emp WHERE empno in (SELECT * FROM TABLE(?))";

  public static void main(String args[]) throws Exception {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection con = DriverManager.getConnection(sConn, sUser, sPass);
    con.setAutoCommit(false);

    // Those following lines generate the JDBC representation of the
    // object type QUERY_ARRAY 
    ArrayDescriptor dbArrayDesc = ArrayDescriptor.createDescriptor("QUERY_ARRAY", con);
    ARRAY dbArray = new ARRAY(dbArrayDesc, con, args);

    // prepare the Statement
    PreparedStatement pstmt = con.prepareStatement(sSql);

    // Use the setArray method to pass the Array from JDBC to the database
    pstmt.setArray(1, dbArray);
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) {
      System.out.println(rs.getString("ENAME"));
    }
    rs.close();
    pstmt.close();
    con.close();
  }
}
Die Klasse oracle.sql.ARRAY repräsentiert einen vom Benutzer in der Datenbank definierten Array-Typen. Da der Name dieses Typen frei wählbar ist, wird der oracle.sql.ArrayDescriptor benötigt, um ein solches Objekt zu erzeugen. Die Inhalte kommen aus einem gewöhnlichen Java-Array (hier: args). Ihr könnt das Beispielprogramm testen, indem Ihr eine beliebige Anzahl EMPNOs als Argumente übergebt ...
The java class oracle.sql.ARRAY represents user-defined array data type. Since the name of this data type is also user-defined JDBC needs the oracle.sql.ArrayDescriptor to create an instance of the oracle.sql.ARRAY class. The actual contents come from an ordinary java array (here: args). You can test the sample program after compiling as follows: Just provide a random amount of EMPNOs as command line arguments ...
$ java JdbcDynamicInQuery 7844 7839 7822
... und es werden stets alle in der IN-Klausel berücksichtigt. Es wird dabei stets ein- und dieselbe SQL-Abfrage verwendet. Übrigens: Das ist mit PL/SQL und EXECUTE IMMEDIATE genauso nutzbar - hier ist das Erzeugen der Variable sogar noch leichter:
... and you'll see that all of them are considered in the IN condition. But the SQL statement is always the same. BTW: This is also (of course) usable with PL/SQL and EXECUTE IMMEDIATE - the construction of the array variable is even much simpler.
set serveroutput on

declare
  type cursor_t is ref cursor;

  v_array  query_array;
  v_cursor cursor_t;  

  v_ename  SCOTT.EMP.ENAME%TYPE;
begin
  v_array := query_array(7844, 7839);
  open v_cursor for 'select ename from scott.emp where empno in (select column_value from table(:1))' using v_array;
  loop
    fetch v_cursor into v_ename;
    exit when v_cursor%notfound;
    dbms_output.put_line(v_ename);
  end loop;
  close v_cursor;
end;
/

4. Dezember 2008

PL/SQL Packages intialisieren

English title: PL/SQL initializing code ...

Wusstet Ihr, dass man in einem PL/SQL-Package auch Initialisierungscode hinterlegen kann; man braucht also nicht unbedingt eine Prozedur oder Funktion "init" - dazu gibt es im Package Body ein vorgesehenes Konstrukt:
Did you know that you can add initializing code to your PL/SQL packages? You don't need a special "init" procedure or function - there is a special language feature in the package body:
create or replace package test_init is
  g_max_salary number;
  function return_global_var return number;
end test_init;
/

create or replace package body test_init is
  function return_global_var return number is
  begin
    return g_max_salary;
  end return_global_var;

  begin
    select max(sal) into g_max_salary from emp;
end test_init;
/
Der rote Code ist der Initialisierungscode; er wird bei der ersten Nutzung des PL/SQL-Paketes aufgerufen. Als Beispiel rufe ich einfach nur die globale Variable ab:
The code is the initialization code; it is being executed on the first usage of the package. The following example just gets the global package variable:
SQL> var v_sal number
SQL> exec :V_SAL := test_init.g_max_salary

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> print

     V_SAL
----------
      5000

Man sieht es recht selten, deshalb war mir gar nicht bewußt, dass das geht ... Man lernt halt nie aus ...
Until I've heard about this a few weeks ago I did not realize that this is possible ... Live and learn ...

Beliebte Postings