Ähnlichkeitssuche in SQL-Abfragen: Oracle TEXT für alle!
Das heutige Blog-Posting gehört eigentlich gar nicht hierher, sondern eher in das Blog oracle-text-de.blogspot.com, denn es geht um ein interessantes Feature in Oracle TEXT. Allerdings kann man die Ähnlichkeitssuche, um die es hier gehen soll, auch für "normale" Tabellen sehr gut nutzen - und daher möchte ich es dem (durchaus größeren) Leserkreis dieses Blogs ebenfalls vorstellen. Natürlich kann ich das Thema hier nur "anreißen" und ich werde jede Menge Begriffe, wie Section Groups, Datastores, Lexer-Preferences und einiges mehr, "mal eben" nebenbei erwähnen. Genauere Informationen dazu finden sich auf Deutsch im Blog oracle-text-de.blogspot.com oder in der Dokumentation zu Oracle TEXT:
Sucht man mit einem "normalen" SQL SELECT etwas in einer Tabelle (mit oder ohne Index), so ist dies stets eine "scharfe" Suche - es werden genau die Tabellenzeilen gefunden, die eben genau den gesuchten Text oder einen Teilstring (LIKE) enthalten. Doch was ist, wenn man auch falsch oder ähnlich geschriebene Treffer haben möchte? Gerade bei Namen ist es ja durchaus gängig, dass der Name anders geschrieben ist, als man denkt. Die Volltextengine ORACLE TEXT bietet hierfür schon seit langem spezielle Funktionen zur Ähnlichkeitssuche an. Der FUZZY Operator findet ganz allgemein ähnliche Wörter und die NDATA-Funktion ist speziell für die Suche nach Namen vorgesehen.
Doch wie soll man die Funktionen anwenden? Wie gesagt, brauchen wir zuerst einen Oracle TEXT Index. Der wird aber auf "Dokumente" in VARCHAR2-, CLOB oder BLOB-Spalten angelegt. Das haben wir aber nicht; hier liegen "ganz normale" Tabellen vor. Aber Oracle TEXT bietet hierfür einen Weg an, den ich in diesem Blog Posting vorstellen werde.
Als Beispiel bauen wir uns eine Tabelle, basierend auf dem Schema SH auf. Wenn Ihr Tabellen mit "richtigen" Daten hat, nehmt diese, denn den Daten in SH.CUSTOMERS merkt man recht schnell an, dass sie generiert wurden - so sind nur ca. 30 oder 40 wirklich verschiedene Vor- bzw. Nachnamen drin. Später beim Abfragen merkt man das dann sehr deutlich - man erwischt entweder direkt eine Masse von Treffern oder gar nichts.
create table kunden_tab as
select
cust_id id,
cust_first_name vorname,
cust_last_name nachname,
cust_street_address strasse,
cust_postal_code plz,
cust_city ort,
cust_credit_limit kreditlimit
from sh.customers
/
select * from kunden_tab where rownum <= 10
/
ID VORNAME NACHNAME STRASSE PLZ ORT
---------- --------------- --------------- ------------------------------ ---------- ---------------
49671 Abigail Ruddy 27 North Sagadahoc Boulevard 60332 Ede
3228 Abigail Ruddy 37 West Geneva Street 55406 Hoofddorp
6783 Abigail Ruddy 47 Toa Alta Road 34077 Schimmert
10338 Abigail Ruddy 47 South Kanabec Road 72996 Scheveningen
13894 Abigail Ruddy 57 North 3rd Drive 67644 Joinville
17449 Abigail Ruddy 67 East Mcintosh Avenue 83786 Nagoya
21005 Abigail Ruddy 77 Bradford Avenue 52773 Santos
24561 Abigail Ruddy 77 North Packard Avenue 37400 Yokohama
28116 Abigail Ruddy 87 West Coshocton Avenue 71349 Haarlem
31671 Abigail Ruddy 97 Sagadahoc Avenue 55056 Bolton
Wie schon gesagt: In diesen Daten soll nun gesucht werden, aber es wird eben auch eine Ähnlichkeitssuche gebraucht. Diese ist in Oracle TEXT enthalten, also müssen wir einen Oracle TEXT-Index auf diese Tabelle erzeugen. Und einen Index ist hier wörtlich gemeint, denn im Gegensatz zu "normalen" Datenbankindizes wird bei Oracle TEXT nicht ein Index auf jede zu durchsuchende Tabellenspalte erzeugt (das würde zu sehr schlechter Performance führen), sondern es wird ein Index auf eine Spalte gelegt, der aber Daten aus allen Tabellenspalten enthält. Das ist eine sehr wichtige Besonderheit von Oracle TEXT: Nur ein Textindex ist das Optimum - und dieser enthält alle zu durchsuchenden Daten - sobald man mehrere Indizes hat, verschlechtert sich die Abfrageperformance erheblich.
Hierfür kommt eine besonderes Feature von Oracle TEXT zum Einsatz: Der User Datastore. Oracle Text erlaubt es und, einen PL/SQL-Prozedur "zwischen" den Index und die Daten zu stellen. Der Index nimmt dann nicht einfach die Daten aus der Tabellenspalte, sondern er ruft mit jeder ROWID die PL/SQL-Prozedur auf, diese bereitet die Daten auf und übergibt sie an den Index. Mehr Details zum User Datastore finden sich wieder mal im Oracle TEXT Blog.
Dann wollen wir mal beginnen: Wir brauchen eine Prozedur, die anhand einer ROWID alle Daten aus unserer Tabelle KUNDEN_TAB als "XML"-Dokument bereitstellt. Beachtet, dass der Name dabei doppelt generiert wird - zunächst werden für Vor- und Nachname einzelne Tags und zusätzlich ein "gemeinsames" für den ganzen Namen erstellt. Das wird später für die verschiedenen Suchvarianten gebraucht.
create or replace procedure kunden_suche_uds(
rid in rowid,
tlob in out nocopy varchar2
) is
begin
for e in (
select id, vorname, nachname, vorname||' '||nachname name, strasse, plz, ort, kreditlimit
from kunden_tab
where rowid = rid
) loop
tlob :=
'<ID>' || e.id || '</ID>' ||
'<VORNAME>' || e.vorname || '</VORNAME>' ||
'<NACHNAME>' || e.nachname || '</NACHNAME>' ||
'<NAME>' || e.name || '</NAME>' ||
'<STRASSE>' || e.strasse || '</STRASSE>' ||
'<PLZ>' || e.plz || '</PLZ>' ||
'<ORT>' || e.ort || '</ORT>' ||
'<KREDITLIMIT>' || e.kreditlimit || '</KREDITLIMIT>';
end loop;
end kunden_suche_uds;
/
sho err
Ein Test (in SQL*Plus) ...
SQL> select rowid from kunden_tab where rownum = 1;
ROWID
------------------
AAAtizAAEAAAJxrAAA
1 Zeile wurde ausgewählt.
SQL> var tlob varchar2(4000)
SQL> exec kunden_suche_uds('AAAtizAAEAAAJxrAAA',:tlob);
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> print
TLOB
-------------------------------------------------------------
<ID>49671</ID><VORNAME>Abigail</VORNAME><NACHNAME>Ruddy</NAC
HNAME><NAME>Abigail Ruddy</NAME><STRASSE>27 North Sagadahoc
Boulevard</STRASSE><PLZ>60332</PLZ><ORT>Ede</ORT><KREDITLIMI
T>1500</KREDITLIMIT>
Puristen mögen einwenden, dass das hier gar kein XML-Dokument ist, denn es fehlen die "Root-Tags". Das ist Oracle TEXT aber egal, wohlgeformtes XML ist gar nicht nötig. Und da diese Prozedur später für jede ROWID aufgerufen wird, generieren wir nur, was auch wirklich gebraucht wird.
Als nächstes müssen wir die Prozedur für Oracle Text "registrieren". Das geschieht mit dem Einrichten einer sog. Datastore Preference. Damit das folgende Kommando läuft, braucht Ihr Execute-Privilegien auf CTX_DDL oder besser gleich die Rolle CTXAPP.
begin
ctx_ddl.drop_preference(
preference_name => 'kunden_ds'
);
end;
/
sho err
begin
ctx_ddl.create_preference(
preference_name => 'kunden_ds',
object_name => 'user_datastore'
);
ctx_ddl.set_attribute(
preference_name => 'kunden_ds',
attribute_name => 'procedure',
attribute_value => 'kunden_suche_uds'
);
end;
/
sho err
Als nächstes muss man Oracle TEXT noch sagen, wie die Inhalte zwischen den einzelnen "XML-Tags" behandelt werden sollen - denn da gibt es durchaus Unterschiede. Das wird mit einer Section Group Preference wie folgt gemacht.
begin
ctx_ddl.drop_section_group(
group_name => 'kunden_sg'
);
end;
/
begin
ctx_ddl.create_section_group(
group_name => 'kunden_sg',
group_type => 'XML_SECTION_GROUP'
);
/*
* Einfache "Field Sections" für die Suchelemente. Der letzte Parameter legt fest,
* ob der Section-Name bei Suchen angegeben werden muss ("false") oder ob es auch
* ohne geht ("true").
*/
ctx_ddl.add_field_section('kunden_sg', 'ID', 'ID', false);
ctx_ddl.add_field_section('kunden_sg', 'VORNAME', 'VORNAME', false);
ctx_ddl.add_field_section('kunden_sg', 'NACHNAME', 'NACHNAME', false);
ctx_ddl.add_field_section('kunden_sg', 'STRASSE', 'STRASSE', false);
ctx_ddl.add_field_section('kunden_sg', 'PLZ', 'PLZ', false);
ctx_ddl.add_field_section('kunden_sg', 'ORT', 'ORT', false);
/*
* Eigene "NDATA"-Section für spezielle Namenssuche
*/
ctx_ddl.add_ndata_section('kunden_sg', 'NAME', 'NAME');
/*
* "SDATA"-Section: Die Suche in KREDITLIMIT soll auch mit > und < möglich sein ...
*/
ctx_ddl.add_sdata_section('kunden_sg', 'KREDITLIMIT', 'KREDITLIMIT', 'NUMBER');
end;
/
sho err
Als nächstes stellen wir noch einige Details am Index ein. So möchten wir, dass Umlaute wie "ä", "ü", "ö", aber auch á, é, ê und andere auf ihre Grundformen (a,u,o,e) reduziert werden - das macht die Suche einfacher. Schließlich stellen wir sucher, dass der Index case-insensitiv angelegt wird.
begin
ctx_ddl.drop_preference('kunden_lx');
end;
/
sho err
begin
ctx_ddl.create_preference('kunden_lx', 'BASIC_LEXER');
ctx_ddl.set_attribute('kunden_lx', 'MIXED_CASE', 'NO');
ctx_ddl.set_attribute('kunden_lx', 'BASE_LETTER', 'YES');
ctx_ddl.set_attribute('kunden_lx', 'BASE_LETTER_TYPE', 'GENERIC');
end;
/
sho err
Dann ist es soweit - der Index wird nun tatsächlich erzeugt - wie jeder Index muss auch der Oracle TEXT-Index auf eine Tabellenspalte erzeugt werden. Da aber Daten mehrerer Tabellenspalten enthalten sind, können wir uns eine der Tabellenspalten aussuchen (oder man fügt mit ALTER TABLE ADD COLUMN) eine eigene Spalte hinzu.
create index ft_kunden_suche on kunden_tab (nachname)
indextype is ctxsys.context
parameters('
datastore kunden_ds
section group kunden_sg
lexer kunden_lx
stoplist ctxsys.empty_stoplist
memory 500M
')
/
Bevor wir mit dem Abfragen loslegen, kommt noch eine Kleinigkeit: Der Index ist ja nun auf die Tabellenspalte NACHNAME angelegt. Was ist, wenn ein SQL UPDATE auf die Spalte VORNAME gemacht wird ...? Davon würde der Index nichts mitbekommen, denn die indizierte Spalte ist vom SQL UPDATE ja nicht betroffen. Sie soll aber betroffen sein, denn mit der PL/SQL Prozedur haben wir da Daten aus VORNAME in den Index aufgenommen. Mit einem Trigger müssen wir also sicherstellen, dass ein SQL UPDATE auf die Tabelle KUNDEN_TAB stets die Spalte NACHNAME anspricht.
create or replace trigger trg_upd_kundentab before update on kunden_tab for each row begin :new.nachname := :new.nachname; end; /
Allerdings verhält sich ein Oracle TEXT-Index bei Änderungen an den Daten anders als ein normaler Index: Er ist asynchron - doch dazu später mehr. Nun können Abfragen auf den Index gemacht werden - hier einige Beispiele:
select id, nachname, vorname, ort from kunden_tab where contains(nachname, 'Bar within (NACHNAME)')>0 and rownum<=5; Es wurden keine Zeilen ausgewählt select id, nachname, vorname, ort from kunden_tab where contains(nachname, 'FUZZY(Bar) within (NACHNAME)')>0 and rownum<=5; ID NACHNAME VORNAME ORT ---------- ------------------------- ------------------------- --------------------------- 37905 Barr Annie Bedford 41460 Barr Annie Relecq-Kerhuon 45015 Barr Annie Rhineland 7792 Barr Annie Vidalia 43347 Barr Annie Gerald select id, nachname, vorname, ort, kreditlimit from kunden_tab where contains(nachname, 'FUZZY(Bar) within (NACHNAME) and SDATA(KREDITLIMIT > 2000) ' )>0 and rownum<=5; ID NACHNAME VORNAME ORT KREDITLIMIT ---------- ------------------------- ------------------------- --------------- ----------- 41460 Barr Annie Relecq-Kerhuon 7000 45015 Barr Annie Rhineland 9000 7792 Barr Annie Vidalia 9000 48794 Barr Annie North Utica 7000 2350 Barr Annie Berlin 9000 select id, nachname, vorname, ort, kreditlimit from kunden_tab where contains(nachname, 'NDATA(NAME, Ann Bar) and SDATA(KREDITLIMIT > 2000) ' )>0 and rownum<=5; ID NACHNAME VORNAME ORT KREDITLIMIT ---------- ------------------------- ------------------------- --------------- ----------- 37904 Atkins Anne Kent 10000 41459 Atkins Anne Malaga 3000 45014 Atkins Anne Paris 5000 7781 Atkins Anne Konstanz 5000
Die gesamte Suche wird nun in Oracle TEXT-Syntax in die CONTAINS-Funktion gepackt. Innerhalb von CONTAINS können einzelne Elemente wiederum mit AND, OR oder NOT kombiniert werden. Auch Klammerungen sind möglich, so dass der Inhalt von CONTAINS fast beliebig komplex werden kann. Sogar Wildcards (%) wie in einer LIKE-Abfrage sind machbar. Das sind übrigens Beispiele für die optimale Nutzung von Oracle TEXT: Die gesamte Suchanfrage ist in der CONTAINS-Klausel enthalten und wird über den Oracle TEXT-Index ausgeführt. Die Kombination von CONTAINS mit "relationalen Filtern" oder gar die Kombination mehrerer CONTAINS-Klauseln ist funktional möglich, führt aber zu schlechterer Abfrageperformance.
Gerade die Fuzzy-Suche (FUZZY(Bar)) fördert doch einiges an Namen zutage, auch wenn diese in der Datenbank anders geschrieben werden, als in der Such-Abfrage. Das Schlüsselwort WITHIN bestimmt die Section, also das "XML-Tag" innerhalb dessen gesucht werden soll. So können hier in der Tat mehrere Tabellenspalten mit ein und demselben Index abgefragt werden. Man kann völlig frei in der Tabelle suchen - das ist fast wie bei einer Suchmaschine. Gut wäre es nun allerdings, wenn das System schrittweise vorginge ...
- zuerst sollen alle Treffer gelistet werden, die so in der Datenbank stehen, wie sie gesucht werden
- danach nehmen wir den allgemeinen Fuzzy-Operator
- danach nehmen wir die spezielle Namenssuche
Diese Dinge sind möglich mit der sog. Query Relaxation. Mit dieser Technik wird eine Anzahl Treffer, die man gerne haben möchte vorgegeben, und außerdem eine Reihe von Abfragen. Die Abfragen werden solange ausgeführt, bis die gewünschte Anzahl Treffer erreicht wurde. Die Ausgabe des Score hilft bei der Einschätzung der Treffer.
select id, vorname, nachname, ort, score(1) score
from kunden_tab
where contains (
nachname,
'<query>
<textquery lang="GERMAN" grammar="CONTEXT">
<progression>
<seq>(Anne within (VORNAME)) and (Baer within (NACHNAME)) and (Berchtesgaden within (ORT))</seq>
<seq>(?Anne within (VORNAME)) and (?Baer within (NACHNAME)) and (Berchtesgaden within (ORT))</seq>
<seq>NDATA(NAME, Anne Baer) and (Berchtesgaden within (ORT))</seq>
</progression>
</textquery>
<score datatype="INTEGER" algorithm="DEFAULT"/>
</query>',
1
)>0
and rownum < 15
order by score(1) desc
/
ID VORNAME NACHNAME ORT SCORE
---------- ------------------------- ------------------------- --------------- ----------
9460 Annie Barr Berchtesgaden 37
Die Query sucht zuerst genau nach dem Namen "Anne Baer", danach sucht Sie mit allgemeiner Fuzzy-Suche, danach mit spezieller Namenssuche. Für letztere können bspw. auch Vor- und Nachnamen vertauscht sein. Das ganze wird solange gemacht, bis entweder alle Queries durchlaufen oder 15 Treffer erreicht sind. Gleichzeitig ermittelt Oracle TEXT einen "Score", der natürlich um so höher ist, je besser der Name die Suchanfrage trifft. Mit dieser Technik kann man sehr ausgefeilt in Tabellen suchen - und gerade für Callcenter-Anwendungen kann diese Technologie, die übrigens Teil der Standard-Edition ist, sehr hilfreich sein.
Eines ist für den Oracle TEXT Index aber noch zu beachten - und zwar unterscheidet er sich in einem Detail massiv von einem "normalen" Index: Bei Änderungen an den Daten ist er asynchron: Das bedeutet, dass er bei Änderungen an der Tabelle nicht automatisch mitgepflegt wird. Zwar "merkt" er sich zwar die Tabellenzeilen, die von DML-Kommandos betroffen waren, er aktualisiert sich aber nicht automatisch. Man muss ihn synchronisieren - hierzu gibt es eigene PL/SQL-Prozeduren im Paket CTX_DDL ( SYNC_INDEX). Dazu möchte ich allerdings wieder auf die entsprechenden Postings im Blog Oracle TEXT: Tipps, Tricks, Best Practice verweisen. Darüber hinaus finden sich darin zahlreiche andere Postings zu den Möglichkeiten von Oracle TEXT und auch zu Performance-Themen wie der Index-Optimierung. Der Volltextindex, das kann man sagen, ist "ein Universum für sich" - aber eines, dass ungeahnte Möglichkeiten eröffnet und das die Erkundung absolut wert ist.
Viel Spaß noch beim Ausprobieren.
Keine Kommentare:
Kommentar veröffentlichen