Mit Oracle 12c ist es jetzt möglich, für ein VARCHAR2, NVARCHAR2 oder RAW Feld eine Zeichenlänge von 32767 Byte (und nicht CHAR!) anzugeben. Doch warum sollte man das wirklich wollen? Für umfangreiche Texte eignet sich doch der Datentyp CLOB respektive NCLOB viel besser! Durch die Möglichkeit, einzelne „Chunks“ zu ändern, muss dabei nicht für jeden Update die gesamte Zeichenkette gelesen werden und mit der SecureFile Option, die übrigens in Oracle 12c Standard für die Speicherung von CLOB und BLOB Daten ist, ist auch die Verwaltung von umfangreichen Texten viel einfacher.
Man stelle sich doch nur einmal vor, eine Spalte wird als VARCHAR2(32767) definiert und komplett mit Zeichen gefüllt. Bei der Standardkonfiguration einer Oracle Datenbank ist ein Block gerade mal 8k groß, d.h. es müssen mindestens 5 Blöcke alloziert werden (da die 8k nicht komplett zur Verfügung stehen), um diese Zeichenkette zu speichern – sicherlich nicht sehr effektiv.
Aber was spricht jetzt dafür, solche Spalten zu definieren?
Im Dezember 2012 habe ich eine Datenbank Migration von US7ASCII nach Unicode durchgeführt (siehe auch Blog: Umlaute unter US7ASCII – geht doch gar nicht! Oder doch? ), dabei standen wir tatsächlich vor dem Problem, dass dort Spalten mit VARCHAR2(4000) verwendet worden waren. Die Umstellung nach Unicode führte dazu, dass einige Felder abgeschnitten wurden, weil Umlaute, das Euro-Symbol und andere Sonderzeichen die Grenze des VARCHAR2-Feldes von 4000 BYTE sprengte.
Wir standen jetzt vor folgender Entscheidung:
- Umstellung von VARCHAR2 auf CLOB: das hätte zunächst einmal einen großen Aufwand für die Umstellung der Anwendungen mit sich gebracht und außerdem wäre die Datenbank extrem angewachsen. Der Grund ist, dass CLOB bei einer Unicode Datenbank einem Format ähnlich wie UCS-2 gespeichert werden, d.h. jedes Zeichen belegt mindestens 2 Byte (siehe z.B. Oracle Globalization Support Guide 11g Seite 6-15: „Data in CLOB columns is stored in a format that is compatible with UCS-2 when the database character set is multibyte, such as UTF8 or AL32UTF8. This means that the storage space required for an English document doubles when the data is converted.“ – Das gilt natürlich auch für einen deutschen Text 😉
- Das Risiko eingehen und die Texte nach 4000 Byte abschneiden und später die Texte korrigieren.
Wir haben uns nach einiger Beratung für den zweiten Ansatz entschieden und wären sicherlich, bei der Verfügbarkeit von Oracle 12c, auf dieses Release gewechselt.
Benutzung von VARCHAR2(32767)
Zunächst einmal wird man wahrscheinlich scheitern, wenn man in einer Oracle 12c Datenbank versucht, die folgende Tabelle anzulegen:
SQL> create table meine_texte (
2 id number(10) generated by default as identity,
3 meintext varchar2(10000 CHAR));
meintext varchar2(10000 CHAR))
*
FEHLER in Zeile 3:
ORA-00910: Angegebene Länge zu groß für den Datentyp
Der Grund hierfür ist, dass auch Oracle12c standardmäßig nur eine maximale Spaltenlänge von 4000 Byte für VARCHAR2 zulässt und für den erweiterten Modus migriert werden muss.
Dafür wird die Datenbank / Instanz herunter gefahren und anschließend mit STARTUP UPGRADE wieder gestartet. Nach dem „OPEN MIGRATE“ kann der Parameter MAX_STRING_SIZE=EXTENDED gesetzt werden. Als letztes muss dann das Data Dictionary auf die Verwendung von 32k Spaltenlänge umgestellt werden.
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP UPGRADE SQL> ALTER DATABASE OPEN MIGRATE; -- Nur Pluggable Databases SQL> ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED; SQL> @?/rdbms/admin/utl32k.sql
Damit ist die Migration abgeschlossen und dem CREATE TABLE steht nichts mehr im Wege.
Allerdings ist das Ganze eine Einbahnstraße, d.h. ein Zurücksetzen von MAX_STRING_SIZE=STANDARD ist nicht möglich. Dies ist vor allen Dingen bei Pluggable Databases wichtig, da die Umstellung für die CDB erfolgt. Wenn also eine Pluggable Database anschließend in eine andere CDB gehängt wird, muss diese ebenfalls migriert sein.
Der Grund für die Migration ist, dass intern VARCHAR2 Felder mit einer Länge von mehr als 4000 Byte als CLOB abgespeichert werden, allerdings nicht, wie bei einem „normalen“ CLOB als UCS-2 sondern im Standard Datenbank Zeichensatz. Somit wird weniger Speicher benötigt. Allerdings bedeutet das auch, dass die Tabelle jetzt aus mehreren Segmenten besteht, der Tabelle und entsprechenden CLOB Feldern. Das sollte man bedenken, wenn man sich die Größe der Tabelle ansieht: in DBA_TABLES (bzw. USER_ oder ALL_) wird nur das Tabellensegment berücksichtigt, das kann schon mal irreführend sein.
Aufgrund der generellen Limitierung für Indizes (Oracle Database Reference: Total Size of indexed colums = 75% of the database block size minus some Overhead) kann es sein, dass es nicht möglich ist, einen Index auf die Spalte zu legen. Aber wie schon der Titel sagt: „Sinn oder Unsinn“ eine Spalte mit einer Größe von 32k zu indizieren.
Viel Spaß beim Ausprobieren und beim produktiven Einsatz wünsche ich und wie immer bin ich sehr an Feedback interessiert.