Dateien als BLOBs in Tabellen zu speichern, ist nichts besonderes für einen APEX-Entwickler; APEX unterstützt dies seit einiger Zeit aus dem Stand - und wer seine Prozesse zum Up- und Download von Dateien individuell umsetzen möchte, findet zahlreiche Möglichkeiten dazu. Zu diesem Thema sind bereits vor einiger Zeit Community-Tipps erschienen.

In diesem Tipp beschäftigen wir uns mit Large Objects, also BLOB und CLOB-Datentypen. APEX selbst wird heute keine besondere Rolle spielen; es geht eher um Tabellen, LOB-Segmente und Speichereinstellungen für diese. Sie erfahren, welche Optionen und Möglichkeiten Ihnen bei einer Tabelle zur Verfügung stehen, wie Sie den Platzverbrauch Ihrer Large Objects bestimmen können und wie man man mit Large Objects in PL/SQL umgeht.

Large Objects

Werden Dateien in Tabellenzeilen gespeichert, so kommen hierfür Large Objects zum Einsatz. Werden Binärdaten gespeichert, so verwendet man den Datentypen BLOB, (Binary Large Object) bei Zeichendaten den Datentypen CLOB (Character Large Object). Ein Large Object wird von der Datenbank anders gepeichert als normale Tabellenspalten: Letztere werden zu Zeilen zusammengefasst und dann in Datenbankblöcken abgelegt - Oracle speichert Daten zeilenorientiert. Large Objects werden dagegen in eigene LOB-Segmente ausgelagert, wenn sie mehr als 4000 Byte lang sind (für Experten: Mit der Klausel DISABLE STORAGE IN ROW kann man das Auslagern auch für kleinere LOBs erzwingen). Die Tabellenzeile selbst enthält nur noch einen Lob Locator.

Ein LOB-Segment kann auch in einem anderen Tablespace (und damit auf anderen Platten) liegen, als die eigentliche Tabelle. Dieses Verfahren stellt sicher, dass das Vorhandensein großer LOB-Datenmengen die Performance der anderen Tabellenspalten nicht wesentlich beeinflusst. Ein einzelner LOB in der Datenbank kann sehr groß werden: Das theoretische Limit liegt bei 2³² - 1, multipliziert mit der Blockgröße des Tablespace, in dem das LOB-Segment liegt. De-Facto bedeutet das 8 Terabyte, bei einer Blockgröße von 2KB, und 128 Terabyte bei einer Blockgröße von 32KB.

Neben dem Tablespace können für ein Large Object auch andere physikalische Parameter wie Buffer-Cache-Eigenschaften oder das Redolog-Verhalten individuell festgelegt werden.

Es gibt immer noch ältere Datentypen für Large Objects: "LONG" und "LONG RAW" (letzterer nicht zu verwechseln mit dem RAW-Datentypen). Diese sollte man auf keinen Fall noch verwenden, auch wenn sie prinzipiell noch funktionieren. Sie stammen aus der Zeit, bevor es BLOB und CLOB gab und haben mit ganze Reihe Nachteile: Der wichtigste ist, dass ein LONG oder LONG RAW nicht in ein eigenes LOB-Segment ausgelagert, sondern mit den "normalen" Tabellendaten vermischt wird. Auch wenn die LONG bzw. LONG RAW Spalte gar nicht selektiert wird, müssen dennoch alle Blöcke gelesen werden. Dazu kommen weitere Einschränkungen wie dass nur eine LONG bzw. LONG RAW Spalte erlaubt ist und noch andere mehr. Zusammenfassend kann man nur sagen: Finger weg von LONG und LONG RAW.

Was sind Securefiles?

Securefiles ist der Name der LOB-Speichertechnologie, die mit Oracle11g eingeführt wurde. Es geht also nicht um einen neuen Datentypen; auch Securefiles werden als BLOB oder CLOB angelegt. Lediglich in der Storage-Klausel wird bestimmt, dass die Securefile-Technologie zum Speichern eingesetzt werden soll. Die folgenden Kommandos legen eine Tabelle mit einer LOB-Spalte an; einmal als Securefile, einmal als herkömmliches Basicfile.

create table dateien_tab (
  id     number(10) primary key,
  name   varchar2(200),
  datei  blob
) lob (datei) store as securefile
/

create table dateien_alt_tab (
  id     number(10) primary key,
  name   varchar2(200),
  datei  blob
)
/

Wie bereits erwähnt, hat diese Storage-Klausel keinerlei Auswirkungen auf die Anwendung. Das jüngste Datenbankrelease Oracle12c verwendet (sofern der Datenbankadministrator den entsprechenden Parameter DB_SECUREFILE nicht umgestellt hat), Securefiles standardmäßig für neu angelegte LOB-Spalten. Auch in Oracle11g ist die Verwendung von Securefiles gegenüber den herkömmlichen "Basicfiles" sehr zu empfehlen - die Verarbeitung der Large Objects ist dann spürbar effizienter. Zusätzliche Lizenzkosten fallen bei der einfachen Verwendung von Securefiles nicht an - das spielt erst bei den Zusatzfunktionen wie Verschlüsselung, Compression oder Deduplication eine Rolle. Informationen darüber, ob ein LOB für eine Tabelle als Basicfile oder Securefile gespeichert wird, welche Funktionen eingeschaltet wurden und welcher Tablespace verwendet wird, findet man in der Data Dictionary View USER_LOBS.

SQL> select table_name, column_name, securefile, in_row, compression, encryption  
  2  from user_lobs 
  3  where table_name like 'DATEI%';

TABLE_NAME         COLUMN_NAME SECUREFILE IN_ROW COMPRESSION ENCRYPTION
------------------ ----------- ---------- ------ ----------- ----------
DATEIEN_ALT_TAB    DATEI       NO         YES             NO         NO
DATEIEN_TAB        DATEI       YES        YES            YES         NO

2 Zeilen ausgewählt.

LOBs in PL/SQL

Die Datentypen CLOB und BLOB können bekanntlich auch in PL/SQL verwendet werden; die Unterstützung ist in den letzten Releases immer besser geworden. Mit BLOBs arbeitet man in PL/SQL-Prozeduren meist nur "als Ganzes"; ein typisches Szenario ist das Auslesen aus einer Tabelle und die Ausgabe mit WPG_DOCLOAD an den Browser des Anwenders im Rahmen eines APEX-Prozesses.

declare
  l_blob blob;
begin
  select datei into l_blob
  from dateien_tab where id = :P1_ID;
  
  owa_util.mime_header('application/octet-stream', false);
  htp.p('Content-Length: '||dbms_lob.getlength(l_blob));
  owa_util.http_header_close;
  wpg_docload.download_file(l_blob);
end;

Mit einem CLOB sieht es oft anders aus: Oft setzt man als PL/SQL Programmierer Zeichenketten zusammen, und wenn das Ergebnis länger als 32.767 Byte sein könnte, reicht ein VARCHAR2-Datentyp nicht mehr aus; ein CLOB muss her. PL/SQL macht es dem Entwickler aber sehr einfach: auch für CLOBs kann man mit den ganz normalen Zeichenketten-Operatoren arbeiten; sie werden intern auf die nötigen CLOB-Operationen übersetzt. Im folgenden PL/SQL-Code unterscheidet sich das Arbeiten mit einem CLOB scheinbar in keinster Weise von der mit einem VARCHAR2.

declare
  l_clob clob := '';
begin
  for i in 1..20000 loop
   l_clob := l_clob || ('**** Row Number ' || lpad(i, 5, '0') || ' ****' || chr(10));
  end loop;
  dbms_output.put_line(substr(l_clob, 200000, 108));
end;         

"Unter der Haube" sieht es aber ganz anders aus: Der Code wird völlig anders ausgeführt: Es werden automatisch temporäre LOBs generiert. Ein temporärer LOB ist nötig, sobald ein CLOB in PL/SQL größer als 32.767 Byte wird; PL/SQL kann diese Objekte nicht wie VARCHAR2-Datentypen behandeln; schließlich könnten sie auch mehrere Gigabyte groß werden. Die Stringoperationen selbst werden auf Funktionen des PL/SQL Pakets DBMS_LOB übersetzt: WRITEAPPEND und SUBSTR.

Die folgende PL/SQL Prozedur verwendet das PL/SQL Paket DBMS_LOB explizit; sie tut genau das gleiche wie oben stehende, ist mit 0,6 Sekunden aber fast fünf mal so schnell wie die andere Variante mit 3 Sekunden.

declare
  l_clob clob := '';
  l_str  varchar2(200);
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in 1..20000 loop
    l_str := '**** Row Number ' || lpad(i, 5, '0') || ' ****' || chr(10);
    dbms_lob.writeappend(l_clob, length(l_str), l_str);
  end loop;
  dbms_output.put_line(dbms_lob.substr(l_clob, 108, 200000));
end;

Arbeitet man mit LOBs und PL/SQL, kann es sich also durchaus lohnen, temporäre LOBs selbst zu verwalten und das Paket DBMS_LOB anstelle einfacher PL/SQL-Stringoperatoren zu nutzen. Ein ausführlicheres Beispiel zu diesem Thema findet sich auf dem Blog SQL und PL/SQL in Oracle.

Größe einer Tabelle bestimmen: GET_SPACE_INFO

Da die LOB-Segmente separate Speichereinheiten sind, muss man diese separat berücksichtigen, wenn man wissen möchte, wieviel Platz eine Tabelle verbraucht. Eine für diese Fälle hilfreiche PL/SQL-Funktion ist GET_SPACE_INFO, welche 2009 auf dem Blog SQL und PL/SQL in Oracle veröffentlicht wurde. Wenn schon einige Zeilen mit Large Objects gespeichert sind, kann man die Größe der Tabelle und der zugehörigen Speichersegmente (auch Indizes) wie folgt bestimmen.

select segment_name, alloc_bytes, free_bytes from table(get_space_info('EINE_TABELLE'));

SEGMENT_NAME                     ALLOC_BYTES FREE_BYTES
-------------------------------- ----------- ----------
EINE_TABELLE                           65536          0
SYS_LOB0000110999C00004$$            3145728     974848
SYS_IL0000110999C00004$$               65536      32768
PK_EINETABELLE                         65536      32768

4 Zeilen ausgewählt.

Die Spalte ALLOC_BYTES gibt dabei an, wieviel Platz die Tabelle im Moment auf der Platte tatsächlich beansprucht; die freien Abschnitte innerhalb dieser allokierten Bereiche, die zum Beispiel durch das Löschen einzelner Zeilen entstehen können, werden in der Spalte FREE_BYTES gelistet. Dieser Funktion ist ein gutes Handwerkszeug, um den Erfolg unterschiedlicher Maßnahmen zur effizienteren Speicherung zu beurteilen.

Securefile Compression

Seit Oracle11g bietet Oracle die Möglichkeit an, Securefile LOBs automatisch und völlig transparent für die Anwendung zu komprimieren. Festgelegt wird das wiederum in der Storage-Klausel der Tabelle.

create table dateien_tab (
  id     number(10) primary key,
  name   varchar2(200),
  datei  blob
) lob (datei) store as securefile (compress {LOW | MEDIUM | HIGH})
/

Hinter dem STORE AS SECUREFILE können Sie mit dem Schlüsselwort COMPRESS festlegen, dass die Securefiles komprimiert werden sollen. Optional können Sie den Algorithmus mit den Schlüsselworten LOW, MEDIUM und HIGH parametrisieren. LOW bedeutet dabei, dass der Algorithmus möglichst schnell sein soll, HIGH bedeutet, dass er möglichst gut komprimieren soll. MEDIUM ist der Default und versucht einen Kompromiß.

Das Schlüsselwort DEDUPLICATE schaltet die automatische Eliminierung von Dubletten ein. In dem Fall bildet die Datenbank Checksummen über alle gespeicherten LOBs. Wird eine neue Zeile erzeugt, wird für diese wiederum eine Checksumme gerechnet - ist eine solche in der Datenbank bereits vorhanden, so ist das neue Large Object zunächst ein Kandidat für eine Dublette. Nun vergleicht die Datenbank die Kandidaten genau; und wenn dieses Large Object tatsächlich schon einmal vorhanden ist, so wird es nicht erneut gespeichert. Dann wird vielmehr eine Referenz auf das bereits vorhandene Objekt abgelegt. Natürlich werden diese Referenzen intelligent verwaltet; tatsächlich gelöscht wird ein Large Object mit mehreren Referenzen erst dann, wenn alle referenzierenden Tabellenzeilen gelöscht wurden.

Die Nutzung der automatischen Securefile Compression erfordert eine Lizenz der Advanced Compression Option. Ohne diese Lizenz dürfen Sie weder das Schlüsselwort COMPRESS noch DEDUPLICATE nutzen.

Um die Large Objects einer vorhandenen Tabelle zu komprimieren, reicht es nicht aus, ein ALTER TABLE abzusetzen; die Daten müssen tatsächlich bewegt werden. Am einfachsten ist es, eine neue Tabelle mit eingeschalteter Securefile-Komprimierung zu erzeugen und die BLOBs und CLOBs per INSERT AS SELECT in diese zu kopieren. Wer dies im laufenden Betrieb erldigen muss, kann mit dem PL/SQL-Paket DBMS_REDEFINITION arbeiten.

Ob es sich nun lohnt, die Kompression für Securefiles zu nutzen, hängt stark von den gespeicherten Daten ab. Bei der Deduplication ist der Fall klar - ein solches Feature sollte man dann nutzen, wenn viel Plattenplatz durch Dubletten verschwendet wird; eine Datenbank, in der Email-Anhänge gespeichert werden, wäre ein klassischer Kandidat. Bei der Kompression liegt der Fall nicht so einfach: Hier muss man sich die Daten konkret und auch eigene Tests machen. Dabei ist folgende Vorgehensweise empfehlenswert.

BLOB <-> CLOB <-> BLOB

Hin und wieder kommt man in die Gelegenheit, dass ein BLOB vorliegt, man aber mit einem CLOB arbeiten muss - oder umgekehrt. Dann geht es darum, das eine in das andere zu übersetzen - wofür die Datenbank zwei etwas umständliche Prozuduren im Paket DBMS_LOB anbietet: CONVERTTOCLOB und CONVERTTOBLOB. Das folgende Beispiel selektiert einen CLOB aus einer Tabelle und wandelt diesen BLOB um. Dabei werden einige Parameter, teilweise auch als OUT und IN OUT-Parameter benötigt, so dass der Code etwas umfangreicher wird.

declare
  l_table_clob  clob;
  l_target_blob blob;
  l_dstoff      pls_integer := 1;
  l_srcoff      pls_integer := 1;
  l_lngctx      pls_integer := 0;
  l_warning     pls_integer;
begin
  select clob_content into l_table_clob
  from clob_table where id = :P1_ID;
 
  dbms_lob.converttoblob(
    -- Das ist noch einfach: Quell-CLOB und Ziel-BLOB
    dest_lob      => l_target_blob,
    src_clob      => l_table_clob,
    -- Offsets: Für den Fall, dass man einen Teil-BLOB umwandeln möchte
    amount        => dbms_lob.lobmaxsize,
    dest_offset   => l_dstoff,
    src_offset    => l_srcoff,
    -- Wichtig, wenn Umlaute enthalten sind: Wie kodieren?
    blob_csid     => nls_charset_id('AL32UTF8'),
    -- Context-Wert für weitere Konvertierungen
    lang_context  => l_lngctx,
    -- Ausgabe bei Encoding-Fehlern
    warning       => l_warning
  );

  -- Code für Arbeit mit dem BLOB kommt hier
end;

Findige Programmierer bauen sich damit eine einfach zu benutzende PL/SQL-Funktion, die den einen Datentypen entgegennimmt, den anderen zurückgibt und keine weiteren Parameter hat. Danach wird das Leben leichter.

Large Objects und das Dateisystem

Während CLOB und BLOB-Datentypen in Tabellen gespeichert werden, kennt Oracle zusätzlich noch den BFILE-Datentypen - ein BFILE ist ein "Pointer" auf eine im Dateisystem des Datenbankservers liegende Datei. Um ein BFILE zu erzeugen, braucht man ein Directory-Objekt und einen Dateinamen; Zeiger auf die einzelnen Dateien können dann als BFILE-Objekte in Tabellen abgelegt werden. Möchte man die Daten aus der Datei lesen, kann mit DBMS_LOB.LOADBLOBFROMFILE oder DBMS_LOB.LOADCLOBFROMFILE gearbeitet werden. Das Schreiben in ein BFILE ist nicht möglich - BFILEs sind Read Only.

Der BFILE-Datentyp wird nur selten verwendet: Meist kommt er nur in administrativen Skripts zum Einsatz, als Datentyp einer Tabellenspalte findet man ihn kaum vor. Wer eine tiefergehende Integration zwischen Large Objects und Dateien im Filesystem des Datenbankservers braucht, findet in diesem Community Tipp mehr Informationen.

Fazit

Large Objects machen dem APEX-Entwickler normalerweise keine Probleme; BLOB und CLOB-Datentypen können in vielen Fällen verwendet werden wie alle anderen Datentypen auch. Als praktische Tipps für APEX, SQL und PL/SQL-Entwickler lassen sich zusammenfassend festhalten:

• Die Datentypen LONG und LONG RAW nicht mehr verwenden - aber das wussten Sie schon, oder?
• Securefiles für CLOB- und BLOB-Spalten sind zu empfehlen. Ein CREATE TABLE-Kommando sollte für die LOB-Spalten also die Klausel STORE AS SECUREFILE enthalten.
• Setzt man in PL/SQL einen CLOB zusammen, sollte man die ohnehin nötigen temporären LOBs nicht von PL/SQL verwalten lassen, sondern dies selbst erledigen und die Methoden des Pakets DBMS_LOB verwenden. Als Lohn erhält man eine typischerweise bessere Performance.

Zurück zur Community-Seite