Virtual Columns – Normierung und Suche

von Tobias Redmann

Virtual Columns gibt es seit Oracle 11g. Vor einiger Zeit konnte ich dieses „neue“ Feature wirklich sinnvoll einsetzen. Die Aufgabe bestand darin eine bestehende Tabelle um SUCHFELDER zu erweitern, in denen z.B. die Column NAME durch eine Normierungsfunktion verarbeitet wurde und dann in das SUCHFELD abgelegt werden sollte. Anschließend sollte eine Indizierung auf die neu erstellen Suchfelder duchgeführt werden.

Das Bedeutet, dass ich Speicherplatz für das Suchfeld sowie für den Index benötige, eine Datenmigration schreiben sollte um das SUCHFELD initial zu füllen und bestehende Applikationlogik anpassen musste (UPDATE und INSERT-Funktionalitäten). In Summe wären das locker ein paar Tage Aufwand gewesen. (Der Einsatz von Function-Based-Indexes wäre nicht möglich gewesen, die die „normalisierte“ Zeichenkette in der Tabelle sichtbar sein sollten.)

Ich habe mich dann für den Einsatz von Virtual Columns eingesetzt da ich während meiner Argumentation die vollständige Aufgabenstellung umgesetzt hatte. Die Vorteile liegen klar auf der Hand:

  • wenig Speicherplatz – nur für Indizes,
  • keine Anpassung der Applikationslogik für UPDATE und INSERT Operationen
  • keine initiale Datenmigration der neuen Suchfelder

Anwendungsbeispiel

In dem folgenden Beispiel zeige ich euch mal die Grundidee der Normierung und der Suche unter dem Einsatz von Oracle Virtual Columns.

Als erstes erstelle ich die Bestandstabelle TMP_PERSON und füge ein paar Testdaten ein.

Nun benötige ich eine Function welche eine einfache Zeichenkettennormierung durchführt. Diese kann je nach Anforderungen mehr oder weniger komplex ausfallen. Wichtig ist nur, dass die FUNCTION deterministisch ist!

Nun werden 3 virtuelle Columns in der Tabelle angelegt. Die zuvor erstellte Function tmp_normalise wird für die Normierung verwendet und ganz einfach in das ALTER-Statement eingebettet.

Klasse finde ich, dass ich eine nicht persistente Spalte – unsere Suchspalten – indizieren kann. Also pro Column ein Index oder einen kombinierten, wie schon zuvor angesprochen – es kommt auf die konkrete Anforderung an.

Nun können wie bei „normalen“ Spalten SELECT Statements auf die neu erstellten Suchfelder abgesetzt werden. Durch die Indizes erfolgt die Suche entsprechend performant. Um die Aktualisierung der normierten Werte braucht ihr euch nicht kümmern – ORACLE stellt natürlich sicher, dass alle Werte stimmen.
Wie bei einem Function-Based Index wird der Index DISABLED sofern ihr die Normierungsfunction neu kompiliert oder ändert. Dann hilft nur noch ein Index Rebuild.

Habt ihr auch schon Erfahrungen mit Virtual Columns machen können? Ich freue mich auf euer Feedback.

SCHREIBEN SIE EINEN KOMMENTAR