19. Februar 2008

Bind Variablen: Wirklich immer benutzen? (Was ist "Bind Variable Peeking")?

English title: Always use bind variables: Really? (about "Bind Variable Peeking")

Heute geht mit dem Thema effizientes SQL - aus der Sicht des Entwicklers - weiter:
Today we'll continue with efficient SQL - from the developers' point of view:
Im letzten Post habe ich den Performance-Effekt der Verwendung von Bindevariablen vorgestellt. Wie das Testprogramm zeigte, ist dieser Effekt ganz erheblich - wenn eine Applikation viele "gleichartige" SQL-Statements absetzt, hat die Verwendung (oder Nicht-Verwendung) von Bindevariablen signifikante Auswirkungen auf die Performamce.
The last post illustrated the performance impact of using bind variables. As the results of little test program demonstrated this impact can be quite significant. If an application frequently issues similar SQL statements the developer can improve the performance just by using bind variables.
Heute möchte ich auf die Frage eingehen, ob man wirklich immer Bindevariablen nutzen sollte (Ihr könnt es euch denken: Natürlich nicht). Das Thema Bind Variable Peeking wird dabei ein wichtiger Schwerpunkt sein. Zunächst jedoch nochmal ein paar Worte zur Abarbeitung von SQL-Kommandos in der Datenbank:
  1. Parse: Das Statement wird syntaktisch und semantisch geprüft und es wird ein Ausführungsplan erstellt
  2. Bind: Die Platzhalter der Bindevariablen werden durch die konkreten Werte ersetzt
  3. Execute: Das Statement wird ausgeführt.
Today I'd like to elaborate a bit on the question whether bind variables are the best choice in every case (and as you can imagine: of course not). Bind Variable Peeking will play an important role here. But beforehand some words on the phases of SQL execution in the database:
  1. Parse: The statement syntax is being checked as well as its semantics (tables and columns are present?); furthermore the execution plan is being generated
  2. Bind: The bind variables are replaced with the "real" values
  3. Execute: questions?
Der Ausführungsplan wird also während des "Parse" im ersten Schritt erstellt. Der Optimizer schaut dazu nach ...
  • ... ob Indizes da sind ...
  • ... wieviele Zeilen da sind ...
  • ... ob die Query selektiv ist oder nicht ...
... und anderes mehr. Insbesondere die Selektivität ist ganz entscheidend für die Frage, ob und wie Indizes genutzt werden - je weniger selektiv eine Query (oder Subquery) ist, desto wahrscheinlicher ist ein Full Table Scan.
The execution plan is therefore generated during the "parse" step. The optimizer looks ...
  • ... for existing indexes ...
  • ... how many rows the involved tables contain ...
  • ... whether the query is selective is or not ...
... and many other things. The selectivity is very important for the decision whether to use an index or not. The more selective a query or subquery is the more probably is index usage.
Doch wie kann der Optimizer die "Selektivität" bestimmen, wenn er die Werte der Bindevariablen gar nicht kennt ... ?
But how can the optimizer determine the "selectivity" when it does not know the values of the bind variables...?
Naja, zunächst ist diese Frage nur dann relevant, wenn es ein extremes Ungleichgewicht in der Verteilung der Daten der jeweiligen Spalte gibt (wie im folgenden Beispiel). Bei Gleichverteilung ist die optimale Strategie vom konkreten Wert der Bindevariable unabhängig. Zunächst erstellen wir mit folgendem Skript eine Tabelle basierend auf der Tabelle CUSTOMERS im Schema SH. Dieser Tabelle wird eine neue Spalte STATUS hinzugefügt. 1% der Kunden werden den Status "A" und alle anderen die Status "B" oder "C" erhalten.
Now, firstly this question is only relevant when there is a skew in the data distribution of that particular column. If the data was distributed equally the execution plan would not depend on bind variable values - each possible value would lead to the same plan. But a skew in the data distribution changes the picture. In this example we'll first create a table based on the CUSTOMERS table in the sample schema SH. We'll then add a column named STATUS to that table and finally about 1% of the table rows will get the value "A" - the other 99% will get the values "B" or "C".
drop table customers
/

create table customers as select * from sh.customers
where rownum < 5000
/

alter table customers add (status char(1))
/

declare
  type number_array is table of number index by binary_integer;
  type char_array is table of varchar2(1) index by binary_integer;
  v_id_array  number_array;
  v_status_array char_array;

  v_random_val number;
begin
  select cust_id bulk collect into v_id_array from customers;
  for i in v_id_array.first..v_id_array.last loop
    v_random_val := dbms_random.value(0,100);
    if v_random_val < 1 then 
      v_status_array(i) := 'A';
    elsif v_random_val > 1 and v_random_val < 10 then
      v_status_array(i) := 'B';
    else 
      v_status_array(i) := 'C';
    end if;
  end loop;
  forall i in v_id_array.first..v_id_array.last 
    update customers set status = v_status_array(i)
    where cust_id = v_id_array(i);
end;
/
sho err 
Nun wird die neue Spalte STATUS indiziert (IDX_STATUS). Schließlich werden die Statistiken erneuert (DBMS_STATS.GATHER_SCHEMA_STATS). Dabei stellt der Parameter method_opt sicher, dass ein Histogramm für die Spalte STATUS gebildet wird - mit einem solchen Histogramm "weiss" der Optimizer um die (ungleiche) Verteilung der Daten.
Now the new column STATUS gets indexed (IDX_STATUS). The statistics get refreshed using DBMS_STATS.GATHER_SCHEMA_STATS. Since we know about the distribution skew in the STATUS column we'll force the database to create histograms for it (parameter method_opt). Now the optimizer "knows" about the data distribution and it will consider it when deciding about the execution strategy.
create index idx_status on customers (status)
/

exec dbms_stats.gather_schema_stats(ownname => user, method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY'); 
Wie geht die Datenbank nun vor, wenn eine SQL Abfrage mit der Spalte STATUS in der WHERE-Klausel hereinkommt...?
What will the database do if a SQL using the STATUS column in its WHERE clause arrives ...?
select * from customers where status = 'A' 
Wenn keine Bindevariablen verwendet werden, "sieht" die Datenbank den Wert "A" sofort und sie kann anhand der Histogramme nachschauen, wieviele Zeilen die Abfrage zurückliefern wird. Die Entscheidung, ob Index oder Full Table Scan nun besser sind, ist dann ein Leichtes. Im letzten Beispiel haben wir jedoch gesehen, dass man besser Bindevariablen verwenden sollte - was passiert dann ...?
If bind variables were not used the database would "see" the value (here: "A") immediately when parsing the SQL statement - the optimizer can use the histograms to determine the amount of rows the query will return. The decision whether to do a full table scan or an index lookup is now very easy. But in the last post we have seen that bind variables lead to better performance - so the developer uses bind variables. What now ...?
select * from customers where status = :1 
Die Datenbank "sieht" den Wert nun zur Parse-Zeit noch nicht. Allerdings muss der Optimizer einen Ausführungsplan erstellen - zur Lösung des Problems zieht er das Binding gewissermaßen vor (Bind Variable Peeking). Dieses Bind Variable Peeking wird pro SQL-Kommando aber nur einmal durchgeführt - wenn das gleiche SQL-Kommando erneut hereinkommt, wird der beim ersten Mal erstellte Ausführungsplan wiederverwendet. Und dies kann zu "seltsamen" Ergebnissen führen ... dazu zurück zum Beispiel:
Now the database cannot "see" the bind variable's value when parsing the SQL - it only sees ":1". To solve the problem it "peeks" into to bind variable (bind variable peeking) - but this is done only once; when the SQL is "new" to the database. The next time this SQL text arrives at the database it will pick up the already generated execution plan. And this can lead to unexpected results ...
Also: Die Tabelle wurde wie oben beschrieben erstellt und die Applikation nutzt Bindevariablen. Nach dem Hochfahren der Datenbank kommt also das SQL herein ...
Back to the example: The table was created as described above - the application does use bind variables. So the arriving SQL looks like this ...
select * from customers where status = :1 
Wie gerade beschrieben, führt die Datenbank das Bind Variable Peeking durch; wir gehen davon aus, dass in diesem Fall nach dem Status "A" gefragt wird. Anhand des Histogramms stellt der Optimizer fest, dass nur 1% der Zeilen selektiert werden und er entscheidet sich für ...
The database "peeks" into the bind variable as explained and in this case it finds the value "A". The table statistics (histogram) tells the optimizer that this value matches 1% of the rows and therefore it choses as execution strategy ...
SQL_ID  cfrfax3f40swx, child number 0
-------------------------------------
select * from customers where status = :1

Plan hash value: 2427356980

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |       |       |    40 (100)|          |
|   1 |  SORT AGGREGATE              |            |     1 |     6 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS  |    38 |   228 |    40   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_STATUS |    38 |       |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   3 - access("STATUS"=:1) 
... den Indexzugriff - und das ist gut so. Nun geht es weiter. Über die Applikation sucht der nächste Endanwender nach allen Kunden mit Status "C". Als SQL kommt wieder herein ...
... index lookup - which is good. Now the application issues the next SQL - the end user now queries all customers with status "C", but the database does just see ...
select * from customers where status = :1 
Nun kennt die Datenbank das SQL-Statement aber bereits - es wurde ja schonmal ausgeführt. Also findet kein Bind Variable Peeking statt - der beim erstem Mal erstellte Ausführungsplan wird wiederverwendet - die Datenbank macht für dieses SQL-Statement nun immer den Indexzugriff. Aber eigentlich wäre der Full Table Scan besser ...
Now the database knows this SQL already - the SQL text is stored in the SQL cache. So it does not peek the bind variable's value and picks up the already existing execution plan. The database uses again the index - as long this SQL text stays in the SQL area it will always use the existing execution plan. But a full table scan would (of course) be better ...
Anderes Szenario: Die Datenbank wird durchgestartet und der erste Endanwender, der über die Anwendung auf die Datenbank kommt, sucht diesmal nach Kunden mit Status "C". Der Optimizer kennt das Statement noch nicht, also macht er ein Bind Variable Peeking - und kommt zu folgendem Ausführungsplan:
Another scenario: We'll bounce the database (or issue ALTER SYSTEM FLUSH SHARED_POOL) and now the first query is for customers with status "C". The optimizer again peeks into the bind variable and based on the histogram information it decides to this execution plan:
SQL_ID  bz2rfvv5145f5, child number 0
-------------------------------------
select * from customers where status = :1

Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |    40 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |  4494 | 26964 |    40   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - filter("STATUS"=:1) 
"Full Table Scan" (logisch - schließlich haben 90% der Kunden den Status "C"). Nun kommt der nächste Anwender und sucht nach den Kunden mit Status "A" ... und Ihr könnt euch den Rest denken: Das SQL ist ja nun bekannt, die Datenbank verwendet den bereits erstellten Ausführungsplan wieder und wird nun alle diese SQL-Abfragen mit Full Table Scans ausführen ... nicht so gut ...
Full table scan - this is obvious since the query targets 90% of the table rows. Now an execution plan with a full table scan is assigned to that SQL text - and the next user who queries the "A" customers will get his query executed with a full table scan.
Das Problem ist hier, dass wir eigentlich gerne je nach abgefragtem Status unterschiedliche Ausführungspläne hätten. Durch die Verwendung der Bindevariable ist das SQL-Statement textlich jedoch stets dasselbe. Mit Bind Variable Peeking wird für die erste Abfrage ein Ausführungsplan berechnet und dann beibehalten. Für ein SQL-Statement (der SQL-Text ist entscheidend) gibt es im SQL-Cache der Datenbank nur einen aktiven Ausführungsplan.
The problem is that it would be best if the execution plan changed with the bind variable's value. But due to the bind variable the SQL text is always the same. Bind variable peeking allows the database to optimize the execution based on the value of the first bind. But it will then keep this execution plan for all other bind variable values. The SQL cache will contain only one active execution plan for one SQL text.
Dies ist also ein Fall, in dem es besser ist, das SQL per Stringverkettung zusammenzusetzen und keine(!) Bindevariablen zu verwenden. Denn dann liegen in der Datenbank für die verschiedenen Status textlich verschiedene SQL-Abfragen (mit unterschiedlichen Ausführungsplänen) vor. Bindevariablen sind zwar in den meisten Fällen, aber eben nicht "per-se" die beste Lösung:
  • Bindevariablen sollten nicht genutzt werden, wenn je nach Inhalt der Bindevariablen unterschiedliche Ausführungspläne erwartet werden
  • Bindevariablen sollten genutzt werden, wenn der Ausführungsplan von den Inhalten der Bindevariablen unabhängig ist und ohnehin stets gleich berechnet würde.
Oder anders gesagt: Als Entwickler sollte man sicherstellen, dass die Datenbank zur Parse-Zeit alle Informationen hat, die sie zur Erstellung eines guten Ausführungsplans braucht. Wenn Bindevariablen solche Informationen verstecken würden, sollte man sie weglassen.
In such cases it is better to construct the SQL via string concatenation instead of using bind variables: Because then each queries status value leads to "its own" SQL text in the databases SQL cache. And each SQL text is assigned its own execution plan. Bind variables are in most cases but not in all cases the better choice:
  • Bind variables should not be used if the optimal execution plan changes with the variable's value
  • Bind variables should be used if the optimal execution plan is independent of the variable's value
. Expressed otherwise: The developer should make sure that database has all information it needs to generate the optimal plan at parse time. If bind variables hide this information the developer is better off with string concatenation.
Und wer Oracle11g nutzt, liest am besten gleich im nächsten Posting weiter, denn 11g bringt Lösungen für genau dieses Problem mit.
Oracle11g users are recommended to instantly go to the next posting. Oracle11g has some new features exactly for dealing with those problems ...

11. Februar 2008

Geht es auch schneller? Gedanken und Tipps zum SQL Tuning - für Entwickler

English title: Can it be faster? Some thoughts on optimizing SQL - for developers!

Heute und in den folgenden Ausgaben möchte ich mich dem Thema effizientes SQL widmen - und zwar aus der Sicht desjenigen, der das SQL in die Anwendungen einbaut: des Entwicklers!
Zu Beginn also etwas zum Thema Bindevariablen: Interessant ist ja, dass eigentlich nur die "Datenbankleute" einen Unterschied zwischen statischem und dynamischem SQL machen. Für den JDBC-Entwickler gibt es diesen Unterschied gar nicht; dort wird das SQL immer per Stringverkettung zusammengesetzt und dann an die Datenbank übergeben (executeQuery(), prepareStatement()). Eine Ausnahme ist SQLJ, aber das wird (soweit ich es beobachten kann) kaum genutzt.
Es kommt also darauf an, wie man das SQL zusammensetzt und vor allem, wie man dabei mit den Literalen umgeht:
  • Entweder verkettet man sie "in das SQL" hinein ... dann wird das SQL vom Entwickler fertig zusammengesetzt und an die Datenbank übergeben - die führt es sofort aus.
    select * from sh.sales where cust_id = 4711
  • Oder man verwendet Bind-Variablen (in der Java-Welt auch PreparedStatements genannt). In diesem Fall verwendet man zunächst Platzhalter für die Literale ...
    select * from sh.sales where cust_id = ?
    ... oder ...
    select * from sh.sales where cust_id = :1
    ... und übergibt das SQL ohne Parameter an die Datenbank. Im nächsten Schritt werden die Platzhalter dann mit Werten belegt, dann führt die Datenbank das Statement aus.
Der Unterschied zwischen den beiden Varianten ist erheblich, wie das folgende kleine Java-Programm zeigt ...
This and the following posts are dedicated to efficient SQL - and this from the developer's (and not the DBA's) point of view. Many SQL tuning papers are written from the DBA's point of view - but it's the developer which embeds SQL into his application.
Let's start with bind variables: It's interesting that only database guys make a difference between static and dynamic SQL. The Java/JDBC developer does not care about this - every SQL is concatenated as a string and then passed to the database via executeQuery() or prepareStatement. An exception is SQLJ but as of my experience this is used rarely.
So the question ist how to concatenate the SQL and particularly how to handle the literals:
  • The literals can be built directly into the SQL statement which is then passed to the database for execution.
    select * from sh.sales where cust_id = 4711
  • The other option is to use variables for the literals ...
    select * from sh.sales where cust_id = ?
    ... or ...
    select * from sh.sales where cust_id = :1
    . So the statement passed to the database contains just the variables - the binding to the actual values is the next step - finally the statement gets executed by the database.
The chosen option has a huge performance impact, as the following little java program shows ...
import java.sql.*;

public class Bindtest {
  public static void main(String args[]) throws Exception {
    Connection        con = null;
    Statement         stmt = null;
    PreparedStatement pstmt = null;
    ResultSet         rs = null;

    long lStartTime;
    int  iCount = (Integer.parseInt(args[0]));

    String sSql = "";

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

    System.out.println("1. Test: Stringverkettung");
    System.out.println("**************************");
   
    lStartTime = System.currentTimeMillis();
    stmt = con.createStatement();
    for (int i=0;i<iCount;i++) {
      sSql = "select * from sh.sales where cust_id = " + ((int)(Math.random() * iCount));
      rs = stmt.executeQuery(sSql);
      while (rs.next()) {
        // do nothing
      }
      if (i % ((int)(iCount / 80)) == 0) {
        System.out.print(".");
      }
      rs.close();
    }
    stmt.close();
    System.out.println();
    System.out.println("Zeit: " + (System.currentTimeMillis() - lStartTime) + "ms.");

    System.out.println("2. Test: Bindevariablen");
    System.out.println("************************");

    lStartTime = System.currentTimeMillis();
    pstmt = con.prepareStatement("select * from sh.sales where cust_id = ?");
    for (int i=0;i<iCount;i++) {
      pstmt.setInt(1, ((int)(Math.random() * iCount)));
      rs = pstmt.executeQuery();
      while (rs.next()) {
        // do nothing
      }
      if (i % ((int)(iCount / 80)) == 0) {
        System.out.print(".");
      }
      rs.close();
    }
    pstmt.close();
    System.out.println();
    System.out.println("Zeit: " + (System.currentTimeMillis() - lStartTime) + "ms.");
    con.close();
  }
}
    
 
Das Programm zeigt den Performance-Unterschied zwischen SQL-Abfragen mit Bindevariablen und einfacher Stringverkettung, indem einzele Zeilen aus der Tabelle SH.SALES wiederholt abgerufen werden. Dabei wird massiv SQL an die Datenbank abgesetzt. Ihr müsst den Connection String an eure Datenbank anpassen, um das Programm nutzen zu können; außerdem muss das SH-Schema vorhanden sein. Das Programm muss mit einem Parameter aufgerufen werden; dieser gibt an, wie oft die Tabelle SH.SALES abgefragt werden soll.
The java program shows the performance difference between SQL queries using bind variables and simple string concatenation. For this it queries single rows from the table SH.SALES. Before running this program make sure that the JDBC connection string matches your database and the username and password (here both sh) are correct. The Oracle sample schema SH must be present. The main method takes one argument - how often the SH.SALES table should be queried.
$ java Bindtest 5000

1. Test: Stringverkettung / Simple String concatenation
********************************************************
.................................................................................
Zeit: 38326ms.

2. Test: Bindevariablen / Bind variables
*****************************************
.................................................................................
Zeit: 18186ms.
Die Ergebnisse zeigen sehr deutlich, was die Bindevariablen bringen; in diesem Beispiel ist es Faktor 3. Das liegt einfach daran, dass im Falle der einfachen Stringverkettung jedes SQL-Kommando für die Datenbank neu ist - und daher führt die Datenbank für jedes SQL ein Parsing durch; sie prüft also, ob die Tabellen und Spalten vorhanden sind, ob die Zugriffsrechte OK sind und schließlich erstellt sie einen Ausführungsplan (welcher immer gleich aussehen dürfte). Das kostet für das einzelne Kommando nicht viel, aber es kostet etwas - und bei Last im Mehrbenutzerumfeld macht sich dieser Unterschied auch bemerkbar.
Man sieht hieran sehr schön, dass Bindevariablen (oder PreparedStatements) nicht zum Thema "schönerer Code", sondern zur Pflicht beim Umgang mit der Datenbank gehören (und das besonders, wenn die Maschine unter Last steht).
Und das gilt für alle Programmiersprachen; Java ist hier lediglich beispielhaft aufgeführt. Ihr erreicht die gleichen Ergebnisse mit C/C++, PHP, Perl, sogar mit PL/SQL und EXECUTE IMMEDIATE.
Those results illustrate the effect of using bind variables (in this example it's factor 3). The reason is that for the simple string concatenation every SQL is new for the database - so the database does a parsing for every singe SQL; it checks whether the tables and columns are present, whether the user has access privileges and finally it creates the execution plan (which probably looks the same for each SQL call). A single parse call is not very expensive but in a multi user scenario with high load on the machine this will get significant. So this example shows that using bind variables is not just beautifying code, it's essential for good query performance while the database is under load.
And this is true for all programming environments - java was used just as an example here. C/C++, PHP or Perl will deliver the same results - this is even true for PL/SQL and EXECUTE IMMEDIATE.

6. Februar 2008

MD5-Prüfsummen aus BLOBs oder CLOBs berechnen

English title: Get MD5 Checksum from a LOB

Es gibt auch in der Datenbank viele Anwendungsfälle, in denen man Prüfsummen berechnen muss. Meist werden MD5-Prüfsummen verwendet. So kann man damit Duplikate erkennen oder die Integrität einer heruntergeladenen Datei überprüfen. Und wenn die Datei aus der Datenbank kommen soll, muss diese Prüfsumme auch in der Datenbank berechnet werden. Doch wie kann man eine MD5-Checksumme in der Datenbank berechnen?
Eine erste Idee ist es, nach vorhandenen PL/SQL-Prozeduren oder Funktionen zu suchen, man findet auch recht schnell DBMS_UTILITY.GET_HASH_VALUE. Allerdings funktioniert diese nur mit VARCHAR2-Variablen, womit wir auf 32Kb beschränkt wären.
Die Lösung lautet Java in der Datenbank. Java bietet im Package java.security standardmäßig Methoden zum Berechnen von MD5-Prüfsummen an - und die können wir auch in der Datenbank nutzen. Der folgende Code erstellt eine entsprechende Java-Klasse und die passenden PL/SQL-Wrapper zur Übergabe von BLOB oder CLOB-Datentypen.
Calculating checksums can be an important task also for the PL/SQL developer. A checksum (MD5 is used in most cases) can be used to determine whether a specific LOB already exists in the database. Another application is to provide a checksum to the end user in order to verify whether the integrity of a downloaded file. And if the file is being downloaded from the database ... the checksum has to be calculated in the database as well
A first idea is to search for available PL/SQL procedures or functions - but the only available function DBMS_UTILITY.GET_HASH_VALUE accepts only VARCHAR2 as input parameter - so we cannot use it for large objects (CLOB, BLOB).
The solution is again Java in the database. The package java.security contains methods for calculating MD5 checksums - by default. So we can use these methods within the database. All we then need is a PL/SQL wrapper which maked the java methods available to the SQL or PL/SQL engine. And this code follows here:
create or replace java source named "MD5" as 
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Clob;
import java.sql.Blob;
 
public class MD5 {
  public static String getMD5HashFromClob(Clob inhalt) throws Exception{
    String sChecksum = null;

    if (inhalt != null) {
      try {
        String data = inhalt.getSubString(1L, (int)inhalt.length());
        MessageDigest md5 = MessageDigest.getInstance("MD5");
        md5.update(data.getBytes());
        byte result[] = md5.digest();
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < result.length; i++) {
            String s = Integer.toHexString(result[i]);
            int length = s.length();
            if (length >= 2) {
                sb.append(s.substring(length - 2, length));
            } else {
                sb.append("0");
                sb.append(s);
            }
        }
        sChecksum = sb.toString();
      } catch (NoSuchAlgorithmException e) {
        // do nothing
      }
    }
    return sChecksum;
  } 

  public static String getMD5HashFromBlob(Blob inhalt) throws Exception{
    String sChecksum = null;

    if (inhalt != null) {
      try {
        MessageDigest md5 = MessageDigest.getInstance("MD5");
        md5.update(inhalt.getBytes(1L, (int)inhalt.length()));
        byte result[] = md5.digest();
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < result.length; i++) {
            String s = Integer.toHexString(result[i]);
            int length = s.length();
            if (length >= 2) {
                sb.append(s.substring(length - 2, length));
            } else {
                sb.append("0");
                sb.append(s);
            }
        }
        sChecksum = sb.toString();
      } catch (NoSuchAlgorithmException e) {
        // do nothing
      }
    }
    return sChecksum;
  }
}
/
sho err

alter java source "MD5" compile
/
sho err

CREATE OR REPLACE FUNCTION get_md5_CLOB(inhalt CLOB) RETURN VARCHAR2 DETERMINISTIC
AS LANGUAGE JAVA
name 'MD5.getMD5HashFromClob(java.sql.Clob) return java.lang.String';
/

CREATE OR REPLACE FUNCTION get_md5_BLOB(inhalt BLOB) RETURN VARCHAR2 DETERMINISTIC
AS LANGUAGE JAVA
name 'MD5.getMD5HashFromBlob(java.sql.Blob) return java.lang.String';
/

Vielen Dank übrigens an Michael Seiwert aus Hamburg für die Idee und auch Hilfe bei der Umsetzung
Credits to Michael Seiwert from Hamburg for the initial idea and for significant parts of code

Beliebte Postings