Blog Johannes

Blog Johannes

johannes blog ohne logo

Blog Johannes

Oracle 12.2 Collation

Veröffentlicht: 01. September 2017
Geschrieben von Johannes Ahrends

Wenn man den Begriff "Case Insensitive Database" im "New Feature" Buch von Oracle 12.2 liest, dann hat man zunächst das Gefühl, dass das vielleicht ein Schritt zurück in die Vergangenheit ist. Denn wir sind uns sicherlich alle einig, dass wir durchaus zwischen Groß- und Kleinschreibung unterscheiden wollen.

In der Realität geht es aber auch gar nicht darum, nur Großbuchstaben in den Tabellen zu speichern, sondern bei der Suche bzw. der Sortierung von Daten auf die Unterscheidung zu verzichten - "Collation" ist hier das Zauberwort, was Sortierung und Textvergleich bedeutet.

Jeder, der schon einmal eine Anwendung geschrieben hat, kennt das Problem der "richtigen" Schreibweise und damit der Notwendigkeit, bestimmte Konvertierungen bei Textvergleichen vorzunehmen. Am Einfachsten durch die Funktion "UPPER" bzw. "LOWER".

SQL> SELECT persid, vorname, nachname 
  FROM mitarbeiter
 WHERE upper(nachname) = 'MÜLLER'
 ORDER BY nachname, vorname; 
 
    PERSID VORNAME              NACHNAME
---------- -------------------- --------------------
    100242 Gerd                 müller
    100229 Andreas              Müller
    100483 Carla                Müller
    101000 Günther              Müller

 Das sieht soweit ganz gut aus - bis auf die Sortierung. "Gerd müller" wird vor "Andreas Müller" einsortiert, weil der Kleinbuchstabe vor dem Großbuchstaben sortiert wird. Das hätte man vielleicht nicht so erwartet, ist aber keine großartige Neuigkeit. Beheben kann man das z.B. durch die Einstellung der Session auf bestimmte NLS-Variablen, wie diese:

SQL> ALTER SESSION set NLS_SORT='XGERMAN_CI';
SQL> SELECT persid, vorname, nachname 
  FROM mitarbeiter
 WHERE upper(nachname) = 'MÜLLER'
 ORDER BY nachname, vorname; 
 
    PERSID VORNAME              NACHNAME
---------- -------------------- --------------------
    100229 Andreas              Müller
    100483 Carla                Müller
    100242 Gerd                 müller
    101000 Günther              Müller

Das sieht doch gut aus, oder?

Nicht unbedingt: die Verwendung der Funktion "UPPER" sowie die Änderung der NLS-Einstellung bewirken, dass Indizes nicht mehr benutzt werden können. Bei der Funktion "UPPER" leuchtet das noch ein, aber die NLS-EInstellung ist schwer zu erkennen, weil sie ja nicht direkt zum Statement gehört. Daher sei noch einmal darauf hingewiesen, dass es oft nicht ausreichend ist, mit Toad, SQL-Developer oder einem anderen Tool ein Statement mit Explain Plan zu analysieren, man muss auch die Umgebung kennen!

Zurück zum Thema. Die Einstellung von NLS_LANG bewirkt, dass die deutsche Sortierung (xgerman) unabhängig von Groß- und Kleinbuchstaben (CI = Case Insensitive) verwendet werden soll.

Column Level Collation

Diese Einstellung kann man ab Oracle 12.2 einer Tabelle auch direkt mitgeben, wie folgendes Beispiel zeigt:

SQL> ALTER TABLE mitarbeiter MODIFY nachname COLLATE XGERMAN_AI;
SQL> ALTER TABLE mitarbeiter MODIFY vorname COLLATE XGERMAN_AI;

SQL> SELECT persid, vorname, nachname
      FROM mitarbeiter
     WHERE nachname like 'Müller'
     ORDER BY nachname, vorname;

    PERSID VORNAME              NACHNAME
---------- -------------------- --------------------
    100229 Andreas              Müller
    100720 Anne                 Muller
    100025 Blaine               Muller
    100483 Carla                Müller
    100594 Elysee               Muller
    100242 Gerd                 müller
    101000 Günther              Müller

In diesem Beispiel wurde statt "CI" der Postfix "AI" (Accent Insensitive) benutzt, wodurch die Umlaute (Accent) ebenfalls ignoriert wurden. D.h. ob ich jetzt nach "Muller" oder "Müller" suche, es werden immer beide Versionen ausgegeben. Natürlich muss die Anwendung das auch tatsächlich wollen!

Was ist eigentlich mit dem schönen deutschen "ß"? Hier kommt jetzt das "X" von "XGERMAN" zum Tragen: Während bei "GERMAN" das "ß" als eigenständiger Buchstabe hinter dem "s" einsortiert wird, wird bei "XGERMAN" ein "ß" wie ein "ss" behandelt.

SQL> SELECT persid, vorname, nachname
      FROM mitarbeiter
     WHERE nachname = 'Weiss'
  ORDER BY nachname, vorname;    
 
    PERSID VORNAME              NACHNAME
    PERSID VORNAME              NACHNAME
---------- -------------------- --------------------
    100645 Randi                Weiß
    100949 Selina               Weiss

Und was ist mit den Indizes?

Für alle Indizes, die vorher schon existiert haben, gilt, dass es sich hierbei um eine Funktion handelt, d.h. die Indizes werden ignoriert. Für Indizes die anschließend angelegt werden, wird die Funktion integriert, d.h. der Index benutzt die zugrundeliegende Funktion. Daher steht der Nutzung von Collatins in diesem Umfeld nichts mehr im Wege.

Neben der direkten Änderung von Tabellenspalten gibt es auch die Möglichkeit, eine Default Collation für eine Tabelle oder ein ganzes Schema zu setzen. Allerdings gilt das nur für neu hinzugefügte Spalten (Tabelle) oder neue Tabellen (Schema), aber das ist vielleicht ein Thema für einen weiteren Blog.

An dieser Stelle möchte ich mich bei Tim Hall bedanken, der mich mit seinem Blog "Column-Level Collation and Case-Insensitive Database in Oracle Database 12c Release 2 (12.2)" dazu inspiriert hat, mich mit Collations zu beschäftigen - womit dann auch erwiesen ist, dass Engländer etwas für Brauereien übrig haben.

Hinweis

Falls Sie mehr über Collations und weiteren neuen Funktionen von Oracle 12.2 erfahren möchten: nehmen Sie an unserer Schulung "Oracle 12.2 New Features" im Rahmen der DOAG Konferenz und Ausstellung am 24. November in Nürnberg teil.

Johannes Ahrends

CarajanDB GmbH

Siemensstr. 25  50374 Erftstadt

Fon: +49 (2235) 170 91 83

Fax: +49 (2235) 170 79 78

Mail: info@carajandb.com

 

carajan-db-logo