Nochmal USER_DATASTORE: Ein umfassendes Beispiel - Teil I

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:

• Dokumentation zu BASIC_LEXER
• Dokumentation zu BASIC_WORDLIST

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.