Da wir immer wieder gefragt werden, wie man einen
User Datastore mit Oracle TEXT erstellt und
auch sehr oft bemerken, dass ein User Datastore DIE Lösung für alle möglichen Suchszenarien ist,
möchten wir dieses Blog-Posting nochmals dem User Datastore widmen. Das Beispiel heute ist
etwas anspruchsvoller: Wir möchten eine Mitarbeitersuche im Oracle-Beispielschema
HR
umsetzen. Die zu indizierenden, suchrelevanten Daten sind über mehrere
Tabellen verteilt (genau 7) - dennoch soll genau
ein Index entstehen, über
den ein Mitarbeiter anhand aller möglicher Suchkriterien gefunden werden kann.
- Anhand des Namens, auch Ähnlichkeitssuche soll möglich sein
- Anhand der Abteilung
- Anhand des Ortes, des Landes, der Region
- Anhand des Jobs
- Anhand des Managers
Man kann sich schon vorstellen, dass die Lösung dieser Aufgabe mit "klassischem" SQL
nicht besonders einfach ist. Aber Oracle TEXT passt hier genau. Zur Verdeutlichung
nochmals eine Übersicht über die vorhandenen, zu indizierenden Tabellen.
Damit man die Physik der eigentlichen Datentabellen beliebig ändern kann, werden wir den Volltextindex komplett
davon lösen. Es wird also eine eigene "Suchtabelle" angelegt, auf die
später der Index erzeugt wird. Damit wir beim Anzeigen der Trefferliste nicht zu den
eigentlichen Datentabellen joinen müssen, nehmen wir alle Informationen, die in der Trefferliste
angezeigt werden sollen, hier mit auf. Wobei das "nur" die Daten sind, die potenziell in der
Trefferliste dargestellt werden - in den Index werden noch mehr Daten aufgenommen.
create table employees_search(
employee_id number(6) primary key,
FIRST_NAME varchar2(20),
LAST_NAME varchar2(25),
EMAIL varchar2(25),
PHONE_NUMBER varchar2(20),
JOB_TITLE varchar2(35),
DEPARTMENT_NAME varchar2(30),
POSTAL_CODE varchar2(12),
CITY varchar2(30),
COUNTRY_NAME varchar2(40)
)
/
insert into employees_search(
select
e.EMPLOYEE_ID,
e.FIRST_NAME,
e.LAST_NAME,
e.EMAIL,
e.PHONE_NUMBER,
j.JOB_TITLE,
d.department_name,
l.postal_code,
l.city,
c.country_name
from
employees e
join jobs j on (j.job_id = e.job_id)
join departments d on (d.department_id = e.department_id)
join locations l on (d.location_id = l.location_id)
join countries c on (c.country_id = l.country_id)
)
/
Als nächstes wird die Prozedur erstellt, welche die zu indizierenden Daten aufbereitet. Oracle TEXT wird diese
Prozedur später für jede Zeile der zu indizierenden Tabelle (EMPLOYEES_SEARCH) aufrufen und das, was die Prozedur zurückgibt,
indizieren. Der PL/SQL-Code sollte
dann die zu indizierenden Daten aus allen Tabellen "zusammensammeln" und dabei möglichst effizient arbeiten. Wir
bedienen uns eines expliziten Cursors in einem Helper-Package. Da wir den Index auf die Tabelle EMPLOYEES_SEARCH
erzeugen wollen, müssen wir mit dem Cursor, anhand einer ROWID aus dieser Tabelle, alle Daten zusammenstellen.
create or replace package emp_suche_uds_helper is
cursor emp_suche_cur (emp_rid rowid) is
select
e.EMPLOYEE_ID,
e.FIRST_NAME || ' ' || e.LAST_NAME as full_name,
e.EMAIL,
e.PHONE_NUMBER,
to_char(e.hire_date, 'YYYY-MM-DD') hire_date,
m.first_name || ' ' ||m.last_name as mgr_full_name,
j.JOB_TITLE,
d.department_name,
l.street_address,
l.postal_code,
l.city,
l.state_province,
c.country_name,
r.region_name
from
employees_search es
join employees e on (e.employee_id = es.employee_id)
join jobs j on (j.job_id = e.job_id)
join departments d on (d.department_id = e.department_id)
join locations l on (d.location_id = l.location_id)
join countries c on (c.country_id = l.country_id)
join regions r on (r.region_id = c.region_id)
left outer join employees m on (m.employee_id = e.manager_id)
where es.rowid = emp_rid;
end emp_suche_uds_helper;
/
sho err
Danach kommt die eigentliche Prozedur für den User Datastore. Beachtet bitte immer deren Signatur - die
ist von Oracle TEXT vorgegeben:
- Als erstes wird eine ROWID als IN-Parameter erwartet.
- Der zweite Parameter muss IN OUT und vom Datentyp VARCHAR2, CLOB oder BLOB sein.
Der Code sieht dann wie folgt aus.
create or replace procedure emp_suche_uds_proc(
rid in rowid,
tlob in out nocopy varchar2
) is
l_row emp_suche_uds_helper.emp_suche_cur%ROWTYPE;
begin
if emp_suche_uds_helper.emp_suche_cur%ISOPEN then
close emp_suche_uds_helper.emp_suche_cur;
end if;
open emp_suche_uds_helper.emp_suche_cur(rid);
fetch emp_suche_uds_helper.emp_suche_cur into l_row;
tlob :=
'<EMPLOYEE_ID>' || l_row.employee_id || '</EMPLOYEE_ID>' ||
'<FULL_NAME>' || l_row.full_name || '</FULL_NAME>' ||
'<ND_FULL_NAME>' || l_row.full_name || '</ND_FULL_NAME>' ||
'<EMAIL>' || l_row.email || '</EMAIL>' ||
'<PHONE_NUMBER>' || l_row.phone_number || '</PHONE_NUMBER>' ||
'<HIRE_DATE>' || l_row.hire_date || '</HIRE_DATE>' ||
'<MGR_FULL_NAME>' || l_row.mgr_full_name || '</MGR_FULL_NAME>' ||
'<ND_MGR_FULL_NAME>' || l_row.mgr_full_name || '</ND_MGR_FULL_NAME>' ||
'<JOB_TITLE>' || l_row.job_title || '</JOB_TITLE>' ||
'<DEPARTMENT_NAME>' || l_row.department_name || '</DEPARTMENT_NAME>' ||
'<STREET_ADDRESS>' || l_row.street_address || '</STREET_ADDRESS>' ||
'<POSTAL_CODE>' || l_row.postal_code || '</POSTAL_CODE>' ||
'<CITY>' || l_row.city || '</CITY>' ||
'<STATE_PROVINCE>' || l_row.state_province || '</STATE_PROVINCE>' ||
'<COUNTRY_NAME>' || l_row.country_name || '</COUNTRY_NAME>' ||
'<REGION_NAME>' || l_row.region_name || '</REGION_NAME>';
close emp_suche_uds_helper.emp_suche_cur;
end emp_suche_uds_proc;
/
sho err
Als nächstes sollte man die Prozedur mal testen - eine ROWID der Tabelle EMPLOYEES_SEARCH reingeben und es sollte
ein XML-Dokument zurückkommen. Mit SQL*Plus sieht der Test dann so aus ...
SQL> var XML varchar2(4000);
SQL> exec emp_suche_uds_proc('AAArJdAAEAAAJx7AAA', :XML);
SQL> print
XML
--------------------------------------------------------------------------------
<EMPLOYEE_ID>198</EMPLOYEE_ID><FULL_NAME>Donald OConnell</FULL_NAME><ND_FULL_NAM
E>Donald OConnell</ND_FULL_NAME><EMAIL>DOCONNEL</EMAIL><PHONE_NUMBER>650.507.983
3</PHONE_NUMBER><HIRE_DATE>2007-06-21</HIRE_DATE><MGR_FULL_NAME>Kevin Mourgos</M
GR_FULL_NAME><ND_MGR_FULL_NAME>Kevin Mourgos</ND_MGR_FULL_NAME><JOB_TITLE>Shippi
ng Clerk</JOB_TITLE><DEPARTMENT_NAME>Shipping</DEPARTMENT_NAME><STREET_ADDRESS>2
011 Interiors Blvd</STREET_ADDRESS><POSTAL_CODE>99236</POSTAL_CODE><CITY>South S
an Francisco</CITY><STATE_PROVINCE>California</STATE_PROVINCE><COUNTRY_NAME>Unit
ed States of America</COUNTRY_NAME><REGION_NAME>Americas</REGION_NAME>
Puristen mögen einwenden, dass dies gar kein richtiges XML-Dokument ist - denn es fehlt das Root-Tag. Aber
das ist Oracle TEXT egal: Ein Root-Tag braucht es nicht unbedingt, also verzichten wir darauf. Wenn die PL/SQL-Prozedur soweit funktioniert, können wir damit beginnen, sie im Oracle TEXT Dictionary
zu registrieren. Dazu werden Preference-Objekte erzeugt. Wir beginnen mit der Datastore-Preference:
begin
ctx_ddl.drop_preference(
preference_name => 'employee_ds'
);
end;
/
sho err
begin
ctx_ddl.create_preference(
preference_name => 'employee_ds',
object_name => 'user_datastore'
);
ctx_ddl.set_attribute(
preference_name => 'employee_ds',
attribute_name => 'procedure',
attribute_value => 'emp_suche_uds_proc'
);
end;
/
sho err
Jetzt könnte man den Index schon erzeugen und über alle Attribute suchen. Allerdings wollen
wir noch zwei Dinge zusätzlich:
- Wir wollen auch gezielt nach bestimmten Attributen suchen, also nach Manager, Abteilung oder Adresse
- Für den Employee- und den Managernamen gezielt das
Feature "Name Search"
einsetzen. Aus diesem Grund sind beide Namen im generieren XML auch zweimal enthalten (FULL_NAME und ND_FULL_NAME, MGR_FULL_NAME und ND_MGR_FULL_NAME).
- Wir wollen für das HIREDATE die in Version 11 neu eingeführten SDATA-Sections nutzen. Damit wird
es möglich, eine Datumssuche (<, >) über den Volltextindex zu machen.
begin
ctx_ddl.drop_section_group(
group_name => 'employee_sg'
);
end;
/
begin
ctx_ddl.create_section_group(
group_name => 'employee_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('employee_sg', 'EMPLOYEE_ID', 'EMPLOYEE_ID', false);
ctx_ddl.add_field_section('employee_sg', 'FULL_NAME', 'FULL_NAME', false);
ctx_ddl.add_field_section('employee_sg', 'EMAIL', 'EMAIL', false);
ctx_ddl.add_field_section('employee_sg', 'PHONE_NUMBER', 'PHONE_NUMBER', false);
ctx_ddl.add_field_section('employee_sg', 'MGR_FULL_NAME', 'MGR_FULL_NAME', false);
ctx_ddl.add_field_section('employee_sg', 'JOB_TITLE', 'JOB_TITLE', false);
ctx_ddl.add_field_section('employee_sg', 'DEPARTMENT_NAME', 'DEPARTMENT_NAME', false);
ctx_ddl.add_field_section('employee_sg', 'STREET_ADDRESS', 'STREET_ADDRESS', false);
ctx_ddl.add_field_section('employee_sg', 'POSTAL_CODE', 'POSTAL_CODE', false);
ctx_ddl.add_field_section('employee_sg', 'CITY', 'CITY', false);
ctx_ddl.add_field_section('employee_sg', 'STATE_PROVINCE', 'STATE_PROVINCE', false);
ctx_ddl.add_field_section('employee_sg', 'COUNTRY_NAME', 'COUNTRY_NAME', false);
ctx_ddl.add_field_section('employee_sg', 'REGION_NAME', 'REGION_NAME', false);
/*
* Auf das Hiredate soll die Suche auch mit ">" und "<" möglich sein, daher SDATA-Section
*/
ctx_ddl.add_sdata_section('employee_sg', 'HIRE_DATE', 'HIRE_DATE', 'DATE');
/*
* Zusätzliche NDATA-Sections für Namenssuche
*/
ctx_ddl.add_ndata_section('employee_sg', 'ND_MGR_FULL_NAME', 'ND_MGR_FULL_NAME');
ctx_ddl.add_ndata_section('employee_sg', 'ND_FULL_NAME', 'ND_FULL_NAME' );
end;
/
sho err
Es werden drei unterschiedliche Section-Typen innerhalb der Section Group
employee_sg erzeugt. Eine Field Section ist der
einfachste Typ: In einer solchen Section kann einfacher Text stehen, der normal indiziert wird. Untertags sind jedoch nicht erlaubt;
geschachtelte Strukturen müssen als Zone-Sections deklariert werden - die brauchen aber etwas mehr Platz im Index. Wichtig beim
Aufruf von
ADD_FIELD_SECTION ist der letzte Parameter
VISIBLE. Wird er auf "false" gesetzt, so muss die Section bei der Suche
stets angebenen werden - es muss also immer "MILLER within (FULL_NAME)" gesucht werden. Steht er auf "true", kann man auch einfach
nur nach "MILLER" suchen - letzteres macht den Index aber auch größer - man muss einfach anhand der Anforderungen entscheiden. Mehr
zu Zone- und Field-Sections findet Ihr
hier.
Für die Namenssuche haben wir, wie schon gesagt, zusätzliche XML-Tags erzeugt - für diese Tags werden eigene
NDATA-Sections erzeugt.
Diese machen die in 11.2 neu eingeführte
Name Search möglich. Man
kann also für eine Namenssuche entweder mit der
"normalen" Fuzzy-Suche
arbeiten, oder, wenn diese "nicht genug" findet, die spezielle Namenssuche anwerfen.
Als nächstes legen wir eine Wordlist-Preference an. Damit werden wir einige Einstellungen für Name Search vornehmen und
allgemein Dinge wie die Reduktion von diakritischen Zeichen auf ihre Grundformen aktivieren. Ein Herr "Müller" wird also als
"MULLER" in den Index geschrieben - man kann ihn dann entweder als Müller oder als Muller finden - letzteres ist wichtig, wenn
international gearbeitet werden soll - schließlich ist nicht jede Tastatur mit deutschen Umlauten gesegnet. Im folgenden werden
die dazu nötigen Wordlist und Lexer Preferences eingestellt.
begin
ctx_ddl.drop_preference('employee_wl');
end;
/
sho err
begin
ctx_ddl.create_preference('employee_wl', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('employee_wl', 'NDATA_ALTERNATE_SPELLING', 'TRUE');
ctx_ddl.set_attribute('employee_wl', 'NDATA_BASE_LETTER', 'TRUE');
end;
/
sho err
begin
ctx_ddl.drop_preference('employee_lx');
end;
/
sho err
begin
ctx_ddl.create_preference('employee_lx', 'BASIC_LEXER');
ctx_ddl.set_attribute('employee_lx', 'MIXED_CASE', 'NO');
ctx_ddl.set_attribute('employee_lx', 'BASE_LETTER', 'YES');
ctx_ddl.set_attribute('employee_lx', 'BASE_LETTER_TYPE', 'GENERIC');
end;
/
sho err
Mehr Informationen zu den verfügbaren Einstellungen findet sich in der Dokumentation:
Nun ist es geschafft. Wir können den Index (endlich) anlegen.
create index ft_employee_suche on employees_search(last_name)
indextype is ctxsys.context
parameters('
datastore employee_ds
section group employee_sg
wordlist employee_wl
lexer employee_lx
stoplist ctxsys.empty_stoplist
memory 500M
')
/
Wenn der Index fertig ist, kann man suchen ... die SQL-Abfrage sieht immer etwa so aus ..
select employee_id, first_name, last_name from employees_search
where contains(last_name, '{contains-query}') > 0
- Suche nach einem "Accountant" namens "Higins" oder so ähnlich:
?Higins within (FULL_NAME) and ?Accountant within (JOB_TITLE)
- Suche nach dem Team von einem Manager, dessen Name irgendwie auf "assuriz" endet (klassisch mit "Fuzzy" findet nichts):
?assuriz within (MGR_FULL_NAME)
- Suche nach dem Team von einem Manager, dessen Name irgendwie auf "assuriz" endet (Name Search ist erfolgreich):
NDATA(ND_MGR_FULL_NAME, assuriz)
- Mit Name Search kann man Vor- und Nachnamen auch verdrehen:
NDATA(FULL_NAME, Baer Hermann)
- Alle Angestellten in Oxford, die nach dem 01.01.2008 eingestellt wurden:
Oxford within (CITY) and SDATA(HIRE_DATE >= '2008-01-01')
Es sind beliebige Abfragen denkbar. Innerhalb von CONTAINS kann man ja mit AND, OR, NOT arbeiten und sich damit
beliebig komplexe Abfrageausdrücke überlegen. Und das alles wird aus ein- und demselben Index bedient und zusätzlich
hat man noch linguistische Features wie die Base-Letter Konvertierung (Ä -> A), Ähnlichkeits- und Namenssuche. Für
Suchapplikationen (bspw. im Callcenter) kann Oracle TEXT so eine sehr mächtige Angelegenheit sein und durchaus
auch mit spezieller Software mithalten. Und bei allem immer im Auge behalten: Oracle TEXT ist in der Datenbank "drin" und kostet
nix extra!.
Es bleibt nun die Frage, was bei Datenänderungen (DML) an den zugrundeliegenden Tabellen passiert. Soviel vorab: Der bis hierher
angelegte Index bekommt von etwaigen Änderungen überhaupt nichts mit. Um ihn zu aktualisieren, müsste man ihn neu bauen -
und für manche Anwendungen würde das vielleicht auch in einem nächtlichen Wartungsfenster reichen. Andere Anwendungen brauchen
eher einen ständig aktuellen Index - und wie man das macht, erfahrt Ihr im nächsten Blog-Posting.
