12. Juni 2012

Ähnlichkeitssuche in SQL-Abfragen: Oracle TEXT für alle!

Similarity search in SQL Queries - creative usage of Oracle TEXT

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.

For german readers, this text is, strictly speaking, posted in the wrong blog, since it is about Oracle Text. And for this topic, a colleague and me are maintaining an own blog (in german language only): oracle-text-de.blogspot.com. But the similarity search, which I will talk about, is interesting for virtually everyone developing applications on top of Oracle and not only for the "classic" Oracle TEXT users. So I will show, how one can implement search functionality on a normal, relational table with features like searching over multiple columns, similarity search and a special "name matching". All these features are not possible using "plain" SQL. And all this is part of Oracle TEXT, which is available even in standard edition.

For those, who are new to Oracle TEXT, this posting will introduce a lot of new terms like preference objects, section groups, datastore and others, without explaining them in the very detail. The reason is that Oracle Text is "a universe of its own"; I'd like to introduce it here and to show what is possible. For further reading, I'd recommend the documentation or our (german language) Oracle TEXT blog

When querying a table with a "plain" SQL SELECT, this is always an exact search; even if the LIKE keyword is being used, it searches exactly the specified string or substring. But reality often poses higher requirements - a callcenter agent, for instance, often does not know how to spell the name of the customer "at the other" end. So they are typing a guess - and it would be nice, when the system tries also to find entries similar to it. Oracle TEXT provides special functions for similarity search like FUZZY or (beginning with 11.2.0.2) the new Name Searching feature.

But how to use these functions? We need an Oracle TEXT Index created beforehand - but Oracle TEXT Indexes are being created on "documents" in CLOB, BLOB or VARCHAR2 columns, whereas "our" data resides in plain relational columns. But, as we'll see, there is of course a way to get an Oracle TEXT index on that kind of data. The example, I'll use in this blog posting, is based on the table CUSTOMERS in the SH sample schema. If you have an own table with names and addresses in it, use your table, since the data in SH.CUSTOMERS was generated from a very small set of distinct rows.

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

As said: We want to search in that data and we want to have similarity search as well. Oracle TEXT provides that feature, so we need an Oracle TEXT index on that data. And here is the first of many "special" characteristics of Oracle TEXT: Unlike the "relational" world, where we would have one index on each table column, we create only one Oracle TEXT index on one table column indexing all the data. This is a very important thing to know about Oracle TEXT with respect to query performance: Only one Text Index is the optimum - multiple TEXT indexes will degrade query performance significantly.

One Index on multiple columns will be achieved with a special Oracle TEXT feature: User Datastore. Oracle Text allows us to put a PL/SQL procedure "between" the table and the index. This PL/SQL procedure is being called by Oracle TEXT during the indexing process, it receives a ROWID as input and it returns VARCHAR2, CLOB or BLOB data as output. So this procedure allows us to prepare the data to be indexed ourselves: In the procedure code we can query any data we want and pass it to the Oracle TEXT index. So this functionality is key to create one Oracle TEXT index on a table containing any data we want. More details on User Datastores feature can be found in the Oracle documentation.

So we'll start creating our own User Datastore function. We'll prepare the data on our table KUNDEN_TAB in "XML" format. Note that we generate the customer name twice: We will have a XML tag for the first name, one for the last name and one for the complete name. We'll need this for the different search variants we want to provide.

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

Test the procedure (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> select rowid from kunden_tab where rownum = 1;

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>

XML experts might argue that this is "not really" XML - since there is no root tag. But Oracle TEXT does not require well-formed XML, just "tagged" text is sufficient for our needs. And since this procedure will be called for each row in the table to be indexed, we'll just generate what is absolutely necessary. The next step is to "register" this procedure in the Oracle TEXT dictionary. Note that you need execute provileges on CTX_DDL or (better) the CTXAPP role in order to execute the following commands.

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

Next, we'll need to tell Oracle TEXT how to handle the data inside the different "XML" tags. The VORNAME needs to be handled differently from the KREDITLIMIT. While we need similarity search on the former one, we need between searches on the latter one. So we'll now create a section group with sections as follows ...

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'
  );
  /*
   * First, simple "field sections" are being created. This is for plain text
   * data. The last parameter "visible" determines whether the section name has to
   * be given in queries using WITHIN (true) or not (false).
   */
  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);
  /*
   * For the "name matching" feature, we create an own "NDATA" section
   */
  ctx_ddl.add_ndata_section('kunden_sg', 'NAME',             'NAME');
  /* 
   * KREDITLIMIT should support "between" searches (<,>) - so we need
   * a "SDATA" section here.
   */
  ctx_ddl.add_sdata_section('kunden_sg', 'KREDITLIMIT',      'KREDITLIMIT',      'NUMBER');
end;
/
sho err

In the next step we'll adjust a few indexing details. Note that there are several paramaters to adjust each individual detail of the indexing process. For this example we'll just reduce diacritic characters to their normal form, which is essential for german (ä -> a, ü -> u), french (é -> e, ê -> e) or other languages. Beyond that we make sure that the index is case-insensitive.

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

Now all preparations have been made - we can create the index. We have to choose one of the table columns to create the index on - but note that the data will be providey by our PL/SQL procedure. As an alternative we also could add an additional "search" column on the table.

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
')
/

Now, since the Oracle TEXT index is present, we could start executing queries. But, before that, another little thing: We now have created the Index on the table column NACHNAME. What happens, when a SQL UPDATE statement just updates the VORNAME column ...? Nothing. The index would not take any notice - since that column is not indexed. But it should take notice, since the PL/SQL procedure grabs data also from VORNAME. So we need to create a trigger on the table making sure that any change on the table also "touches" the NACHNAME column.

create or replace trigger trg_upd_kundentab
before update on kunden_tab
for each row
begin
  :new.nachname := :new.nachname;
end;
/

But that's not the only special characteristic of our Oracle TEXT index with regard to DML on the table: Oracle TEXT indexes are asynchronous - after changes on the table data, they need to be synchronized. But more on this later on - now we want to see what we get for all our efforts. Here are some queries using Oracle TEXT and its features.

select id, nachname, vorname, ort from 
kunden_tab where contains(nachname, 'Bar within (NACHNAME)')>0 and rownum<=5;

No rows selected.


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

The complete query is part of the Oracle TEXT contains function. Even the "relational" filter on KREDITLIMIT has been added to CONTAINS and is not part of the "plain" SQL query. This is an example for the most efficient usage of Oracle TEXT. All search logic is part of CONTAINS; all relevant data is part of the Oracle TEXT index. Queries will always perform very well in such a scenario: Mixed Queries (combining CONTAINS and "relational" filters) or queries with multiple CONTAINS clauses will always perform worse.

CONTAINS allows to use boolean operators like AND, OR or NOT, we can use brackets and make the query as complex as we want. Wildcards (%) are allowed similar to SQL LIKE. And Oracle TEXT provides special functions like FUZZY for similarity, NDATA for name matching or SDATA for numeric or DATE filters. As the examples show, the FUZZY function is very useful, since it returns much more matches than the exact search term. The keyword WITHIN specifies the section to be search in - and here a section matches to an "XML" tag in our PL/SQL procedure and this matches to a table column.

So, we now can query multiple columns with just using CONTAINS in the WHERE clause - it's like using a search engine. But in reality ... we can imagine, that we not always need all this power. Some queries would return enough matches even with the simple, exact search - other queries will need all similarity and name matching power we have. So ...

  • first, we want to see all exact matches
  • then we'd like to see similar matches
  • finally we'll start the special name matching

And all this until we got (say: 20) returned rows. So if we get 20 rows with exact matches, the subsequent queries should not be executed any more. For that purpose, Oracle TEXT provides the query relaxation feature. Using the technique, we'll give all queries in sequential order and we provide the number of rows we'd like to have returned. Oracle TEXT will then execute all queries, until the amount of rows has been reached. The "Score" which Oracle TEXT calculates for each returned row, gives in indication about its quality.

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

This query will first search for an exact match of "Anne Baer", after that it uses the FUZZY operator (generic similarity), and then it employs the "name matching" facility. The latter one, for instance, also matches rows, in which the first and last name are inverted. And this is being executed until either 15 rows are returned or all the queries have been executed. In our example, we only get one row, which is due to the data in KUNDEN_TAB - as stated in the beginning - this data has been generated, based on about only 30 to 40 distinct names.

This shows, how powerful the Oracle database can be, when it is about finding data. And keep in mind, that all this is part of the standard edition - no separate licenses are needed.

I've already mentioned: There is one very important additional characteristic about our Oracle TEXT index: It is asynchonous - so when the table data changes, this is not immediately reflected by the Oracle TEXT index. Though Oracle TEXT keeps track about the changed rows, we need to synchronize it after changes and, to maintain query performance over time, we also need to optimize the index in regular intervals. But this is far too much for just one blog posting - for more information about this I'd like to reference the documentation chapters about synchronizing and optimizing the index.

I'd encourage everyone to play a bit with those features; you will see that Oracle TEXT is "a universe of its own" with a lot of things one needs to know. But looking at the query power, Oracle TEXT provides, it's abolutely worth discovering it.

Have fun!

Beliebte Postings