20. Mai 2008

Large Objects und Trigger ...

English title: LOBs and triggers ... some thoughts ...

Ein Trigger auf eine Tabelle ist ja nichts Besonderes - eine LOB-Spalte (CLOB, BLOB) auch nicht. Setzt man aber beides zusammen ein, so kann man ein seltsames Verhalten beobachten: Irgendwie feuert der Trigger niemals zur richtigen Zeit ...
Creating a trigger on a table is not a very complex thing to do - a lob column (CLOB, BLOB) also. But combining these can lead to some strange behaviour: In some way the trigger does never fire at the right time ...
Dazu (wie immer) ein Beispiel. Eine Tabelle mit einer LOB-Spalte und ein Trigger, welcher die Größe des LOB in eine andere Spalte schreibt, werden erzeugt. Zur Sicherheit wird im Trigger zusätzlich die Spalte TRIGGER_FIRED auf Y gesetzt.
An example illustrates this: We'll create a table with a CLOB column and a trigger which determines the LOB's length and stores this into another column: LAENGE. To be sure about the firing of the trigger we also set another column (TRIGGER_FIRED) explicitly to Y.
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class lobWriter {

  public static void main(String args[]) throws Exception {
    PreparedStatement pstmtInsert = null;
    PreparedStatement pstmtSelect = null;
    ResultSet rsLobLocator = null;
    CLOB lobLocator = null;

    int iLobId = 1;

    Writer lobWriter = null;


    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection con = DriverManager.getConnection(
      "jdbc:oracle:thin:@192.168.2.140:1521:orcl",
      "scott",
      "tiger"
    );
    con.setAutoCommit(false);
   
    // LOB-Locator in die Tabelle einfügen
    // Insert a lob locator into the table
    pstmtInsert = con.prepareStatement ( 
      "insert into lobtable (id, dokument) values (?, empty_clob())"
    );

    // Den LOB-Locator abrufen 
    // Retrieve the lob locator
    pstmtSelect = con.prepareStatement (
      "select dokument from lobtable where id = ? for update"
    );
  
    pstmtInsert.setInt(1, iLobId); 
    pstmtInsert.execute();

    pstmtSelect.setInt(1, iLobId);
    rsLobLocator = pstmtSelect.executeQuery();
    if (rsLobLocator.next()) {
      lobLocator = ((OracleResultSet)rsLobLocator).getCLOB(1); 
    }

    // LOB befüllen
    // populate lob via streaming
    lobWriter = lobLocator.setCharacterStream(0L);
    for (int i=0;i<1000;i++) {
        lobWriter.write("Dies ist ein Text ... zum " + i + "ten!\n");
    } 
   
    // Ressourcen schließen
    // Close the handles
    lobWriter.flush();
    lobWriter.close();
    rsLobLocator.close();
    pstmtSelect.close();
    pstmtInsert.close();
    con.commit();
    con.close();
  }
}
Der Java-Code macht ein INSERT nur auf die Spalten ID und DOKUMENT. Schauen wir uns die Tabelleninhalte anschließend mal an ...
Have a look at the SQL INSERT being actually performed by the JDBC code. It only populates the column ID and LOB column DOKUMENT. All other columns are being populated by the trigger. After that we have a look at the table contents ...
select id, dokument, laenge len_table, dbms_lob.getlength(dokument) len_lob, trigger_fired 
from lobtable

        ID DOKUMENT                                  LEN_TABLE    LEN_LOB T
---------- ---------------------------------------- ---------- ---------- -
         1 Dies ist ein Text ... zum 0ten!                   0      33890 Y
           Dies ist ein Text ... zum 1ten!
           Dies ist ein Tex
Und dieses SQL offenbart das ganze Dilemma. Dass der Trigger gefeuert wurde, ist an der Spalte TRIGGER_FIRED klar erkennbar. Allerdings wurde eine Länge von 0 geschrieben - wie die Funktion DBMS_LOB.GETLENGTH erkennen lässt, ist der LOB aber klar größer. Ganz offensichtlich hat der Trigger zum falschen Zeitpunkt gefeuert.
And this SQL shows the problem: The column TRIGGER_FIRED proves that the trigger has actually fired. But the column LAENGE contains zero (0), so it seems that the Trigger could not access to LOB contents. But the lob contents were written correctly, as the function DBMS_LOB.GETLENGTH indicates. Onviously, the trigger fired too early.
Tatsächlich feuert der Trigger unmittelbar nach dem DML-Kommando, also dem SQL Insert. Wenn man in den Java-Code schaut, wird dort jedoch zunächst ein leerer Lob (empty_clob()) eingefügt. Anschließend "holt" sich das Java-Programm den LOB-Locator mit einem SELECT ... FOR UPDATE und füllt ihn mittels Java Streaming. Für die Datenbank ist das "Füllen" des LOB mit Java Streaming allerdings kein DML mehr. Verfolgt man diesen Weg, um LOB-Inhalte in die Datenbank zu laden, so können vorhandene Trigger nicht auf die später per Streaming geladenen LOB-Inhalte zugreifen ...
The trigger actually fires directly after the DML issued by the JDBC code. Since that DML created an empty LOB using the empty_clob() function the trigger saw only this empty lob. The actual lob contents were then written via Java Streaming - but this streaming isn't DML for the database so no trigger fires any more. And for that reason the trigger has only seen the empty lob - therefore it has correctly determined a zweo length. When the actual contents reached the database the trigger has already finished.
Und wie löst man das Problem? Mit temporären LOBs - wir ändern den Java-Code ein wenig um:
And how to solve that problem? With temporary LOBs - just change the java code a little bit:
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class lobWriter2 {

  public static void main(String args[]) throws Exception {
    PreparedStatement pstmtInsert = null;
    ResultSet rsLobLocator = null;
    CLOB lobLocator = null;

    int iLobId = 1;

    Writer lobWriter = null;

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection con = DriverManager.getConnection(
      "jdbc:oracle:thin:@192.168.2.140:1521:orcl",
      "scott",
      "tiger"
    );
    con.setAutoCommit(false);

    // temporären LOB erzeugen und befüllen
    // create temporary lob and populate it
   
    lobLocator = CLOB.createTemporary(con, true, CLOB.DURATION_SESSION);
    lobWriter = lobLocator.setCharacterStream(0L);
    for (int i=0;i<1000;i++) {
        lobWriter.write("Dies ist ein Text ... zum " + i + "ten!\n");
    } 
    lobWriter.flush();
    lobWriter.close();

    // LOB in die Tabelle einfügen
    // Insert the LOB into the table
    pstmtInsert = con.prepareStatement ( 
      "insert into lobtable (id, dokument) values (?, ?)"
    );

    pstmtInsert.setInt(1, iLobId); 
    ((OraclePreparedStatement)pstmtInsert).setCLOB(2, lobLocator);
    pstmtInsert.execute();

    // WICHTIG: temporären LOB freigeben
    // IMPORTANT: free the temporary lob
    lobLocator.freeTemporary();

    pstmtInsert.close();
    con.commit();
    con.close();
  }
}
Nun wird zunächst ein temporärer LOB erzeugt, dieser wird befüllt und erst dann findet das SQL INSERT statt. Zum DML-Zeitpunkt ist der LOB auf Datenbankseite also vollständig zusammengestellt; wenn der Trigger feuert, "sieht" er also die Inhalte und die Spalte LAENGE in der Tabelle wird richtig vom Trigger gesetzt. Wir machen die Testabfrage nochmal:
This code creates a temporary lob, populates it and after that the SQL INSERT takes place. Now the LOB contents are present at DML time; the trigger can see the whole LOB and determine its length correctly. To check we'll perform our test query once more:
select id, dokument, laenge len_table, dbms_lob.getlength(dokument) len_lob, trigger_fired 
from lobtable

        ID DOKUMENT                                  LEN_TABLE    LEN_LOB T
---------- ---------------------------------------- ---------- ---------- -
         1 Dies ist ein Text ... zum 0ten!               33890      33890 Y
           Dies ist ein Text ... zum 1ten!
Mehr zum Thema temporäre LOBs (welche übrigens im TEMP-Tablespace gespeichert werden) findet Ihr in der Dokumentation. Wichtig ist bei temporären LOBs vor allem, dass Ihr sie freigebt, wenn Ihr fertig seid - dazu hatte ich in einem früheren Posting auch schon ein wenig was geschrieben.
You'll find more information on temporary LOBs (which are acually stored in the TEMP tablespace) in the Oracle Documentation. Most important is the correct free'ing of temporary lobs - an earlier posting contains some information about this.

6. Mai 2008

Den "jüngsten" Datensatz selektieren: Mit analytischen Funktionen

English title:

Eine recht gängige Aufgabe ist es, aus einer Tabelle mit mehreren Zeilen pro "Geschäftsvorfall" jeweils nur einen bestimmten zu selektieren. Enthält eine Tabelle bspw. mehrere Versionen eines Datensatzes mit einer aufsteigenden Versionsnummer, so muss häufig die jeweils letzte Version selektiert werden. Da ich schon recht häufig danach gefragt wurde: Hier eine Lösungsvariante mit Hilfe von analytischen Funktionen.
It's a quite common requirement to select only the last (or the first) row of a table containing multiple rows per "entity". An example for this is a table containing multiple versions of an entity. In most cases only the last version has to be selected. In the recent time I was asked very often how to achieve this - so here is an example using analytic functions.
Als Beispiel sollen anhand der Tabelle EMP pro Abteilung (DEPTNO) die zuletzt eingestellten (HIREDATE) Mitarbeiter selektiert werden (das wäre analog zum Selektieren der jüngsten Version eines Datensatzes):
The example is about (table EMP) selecting for each department (DEPTNO) the person employed most recently (HIREDATE). This is similar to selecting the most recent version of an entity:
Zunächst stellen wir das Datum des zuletzt eingestellten Mitarbeiters neben alle anderen ...
In the first step the HIREDATE of the most recently employed person per department is selected beyond the other columns of interest.
select
  deptno,
  ename,
  hiredate,
  max(hiredate) over (partition by deptno) last_date
from emp
Wir verwenden die Funktion MAX aber interessanterweise ohne GROUP BY. Wie das Maximum gebildet werden soll, steht in der OVER-Klausel der analytischen Funktion. Und zwar werden die Zeilen nach DEPTNO aufgeteilt (PARTITION BY). das Ergebnis sieht dann so aus ...
The MAX function is used here but without a GROUP BY clause. So this is the analytic variant of the MAX function. The OVER clause determines how the maximum has to be computed. In this example the rows get partiotioned by department (PARTITION BY). The intermediate result then looks like this:
    DEPTNO ENAME      HIREDATE LAST_EMP
---------- ---------- -------- --------
        10 CLARK      09.06.81 23.01.82
        10 KING       17.11.81 23.01.82
        10 MILLER     23.01.82 23.01.82
        20 JONES      02.04.81 23.05.87
        20 FORD       03.12.81 23.05.87
        20 ADAMS      23.05.87 23.05.87
        20 SMITH      17.12.80 23.05.87
        20 SCOTT      19.04.87 23.05.87
        30 WARD       22.02.81 03.12.81
        30 TURNER     08.09.81 03.12.81
        30 ALLEN      20.02.81 03.12.81
        30 JAMES      03.12.81 03.12.81
        30 BLAKE      01.05.81 03.12.81
        30 MARTIN     28.09.81 03.12.81
... und der Rest ist einfach ...
... finalizing is then very easy ...
with tab as (
 select 
  deptno,
  ename,
  hiredate,
  max(hiredate) over (partition by deptno) last_employed_date
 from emp
)
select 
 deptno,
 ename,
 hiredate 
from tab where last_employed_date = hiredate
/

    DEPTNO ENAME      HIREDATE
---------- ---------- --------
        10 MILLER     23.01.82
        20 ADAMS      12.01.83
        30 JAMES      03.12.81

3 Zeilen ausgewählt.
Mit der WITH-Klausel stellen wir das obige Zwischenergebnis als Inline-View bereit und selektieren daraus mit der WHERE-Klausel nur die Zeilen, deren HIREDATE gleich dem HIREDATE des zuletzt eingestellten Mitarbeiters ist. Und ein Blick auf den Ausführungsplan ...
The WITH clause provides the above intermediate result as inline view. From this inline view only those rows get selected where the HIREDATEequals the HIREDATE of the least employed person per department. And a look at the execution plan...
Plan hash value: 4130734685

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   532 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |      |    14 |   532 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |      |    14 |   238 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   238 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("LAST_EMPLOYED_DATE"="HIREDATE")
... zeigt, dass es hierfür sogar eine eigene Operation gibt - die Datenbank macht einen sogenannten Window Sort. Im Normalfall sollte eine analytische Funktion also stets besser, mindestens aber genauso effizient sein wie etwas Selbstprogrammiertes (Ausnahmen bestätigen die Regel).
... shows that there is an own operation for this analytic function: The window sort. Using analytic functions is therefore at least as good as writing one's own code - in most cases better (as always: exceptions confirm the rule).

Beliebte Postings