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.

CREATE TABLE TMP_PERSON
(ID NUMBER
,VORNAME VARCHAR2(200)
,NACHNAME VARCHAR2(200)
,GEBURTSNAME VARCHAR2(200)
);

Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (1,'Karsten','Besserdich',null);
Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (2,'Tobias','Redmann',null);
Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (3,null,'KeinVorname',null);
Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (4,'Thomas','Müller','Geburtsname');
Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (4,'Ka3rsten','B3sserdich',null);
commit;

CREATE TABLE TMP_PERSON

(ID NUMBER

,VORNAME VARCHAR2(200)

,NACHNAME VARCHAR2(200)

,GEBURTSNAME VARCHAR2(200)

);

Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (1,'Karsten','Besserdich',null);

Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (2,'Tobias','Redmann',null);

Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (3,null,'KeinVorname',null);

Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (4,'Thomas','Müller','Geburtsname');

Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (4,'Ka3rsten','B3sserdich',null);

commit;

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!

CREATE OR REPLACE FUNCTION tmp_normalise (pi_string IN VARCHAR2) RETURN VARCHAR2 
DETERMINISTIC
IS
BEGIN
  RETURN UPPER(REGEXP_REPLACE(pi_string,'[^[:alpha:]]'));
END tmp_normalise;

CREATE OR REPLACE FUNCTION tmp_normalise (pi_string IN VARCHAR2) RETURN VARCHAR2

DETERMINISTIC

BEGIN

RETURN UPPER(REGEXP_REPLACE(pi_string,'[^[:alpha:]]'));

END tmp_normalise;

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.

Alter TABLE tmp_person add NORMIERT_NACHNAME GENERATED ALWAYS AS (tmp_normalise(nachname)) virtual;
Alter TABLE tmp_person add NORMIERT_VORNAME GENERATED ALWAYS AS (tmp_normalise(vorname)) virtual;
Alter TABLE tmp_person add NORMIERT_GEBURTSNAME GENERATED ALWAYS AS (tmp_normalise(geburtsname)) virtual;

Alter TABLE tmp_person add NORMIERT_NACHNAME GENERATED ALWAYS AS (tmp_normalise(nachname)) virtual;

Alter TABLE tmp_person add NORMIERT_VORNAME GENERATED ALWAYS AS (tmp_normalise(vorname)) virtual;

Alter TABLE tmp_person add NORMIERT_GEBURTSNAME GENERATED ALWAYS AS (tmp_normalise(geburtsname)) virtual;

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.

Create index tmp_person_norm_nachname on tmp_person (normiert_nachname);
Create index tmp_person_norm_vorname on tmp_person (normiert_vorname);
Create index tmp_person_norm_gebname on tmp_person (normiert_geburtsname);

Create index tmp_person_norm_nachname on tmp_person (normiert_nachname);

Create index tmp_person_norm_vorname on tmp_person (normiert_vorname);

Create index tmp_person_norm_gebname on tmp_person (normiert_geburtsname);

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.

Select * from tmp_person
where normiert_vorname = 'KARSTEN';

1 2	Select * from tmp_person where normiert_vorname = 'KARSTEN';

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

SCHREIBEN SIE EINEN KOMMENTAR

Sie sind hier: