"Unscharfe Suche" in Datenbeständen

Wenn Sie mit den Application Express-Assistenten einen "normalen" Bericht auf eine Datenbanktabelle erzeugen, wird ihnen passend dazu gleich ein Suchfeld generiert, mit dem Sie einfach in den Daten suchen können (Abbildung 1). Vielfach (z.B. wenn die Daten Rechtschreibfehler enthalten) entsteht jedoch der Bedarf nach unscharfer Suche - Es sollen auch Datensätze gefunden werden, die der Suche nicht exakt, sondern nur ungefähr entsprechen.

Das hier vorgestellte Beispiel basiert auf der Tabelle CUSTOMERS im Datenbankschema SH. SH ist vorhanden, wenn die Oracle Sample Schemas in Ihrer Datenbank installiert sind. Falls der Datenbankuser SH bei Ihnen nicht vorhanden ist, erfahren Sie in der Online-Dokumentation, wie Sie die Sample Schemas nachträglich installieren können. Natürlich können Sie das Beispiel auch auf Ihre eigenen Tabellen anwenden.

Bevor Sie beginnen, stellen Sie sicher, dass die Tabelle SH.CUSTOMERS von Ihrer Anwendung selektiert werden kann. Setzen Sie ggfs. als Datenbankuser SH oder als DBA folgendes SQL ab.

grant select on sh.customers to public

Kopieren Sie nun die Tabelle SH.CUSTOMERS in Ihr Workspace-Schema ...

create table customers as select * from sh.customers

und erzeugen Sie sich dann eine Anwendung mit einer Seite und einem Bericht auf Ihre neue Tabelle #OWNER#.CUSTOMERS. Nehmen Sie die Spalten CUST_FIRST_NAME, CUST_LAST_NAME, CUST_STREET_ADDRESS und CUST_CITY in Ihren Bericht auf. Das Ergebnis sollte wie in Abbildung 1 dargestellt aussehen.

Abbildung 1: Ausgangssituation: Ein Bericht auf die Tabelle SH.CUSTOMERS

Nun können Sie schon ganz bequem suchen - ein gutes Beispiel ist der Name "Baer" - Abbildung 1 zeigt, wie nach diesem Namen gesucht wurde. Nun könnte es jedoch (gerade im deutschsprachigen Raum) sein, dass nach "Bär" gesucht wird. Ebenfalls wäre möglich, dass der Nutzer am Telefon sitzt - er sucht nach "Beer" , da er den Namen nur hört, aber nicht sieht ...

Wie lässt sich eine solche unscharfe Suche realisieren ...?

Ein erster Ansatz könnte die SQL SOUNDEX-Funktion sein. Diese liefert per Definition zwar ähnlichklingende Sätze zurück - der Algorithmus orientiert sich jedoch an der englischen Sprache. Im deutschsprachigen Raum sind die Ergebnisse meist nicht zufriedenstellend. Ein SOUNDEX-Operator wäre wahrscheinlich auch zu kurz gegriffen, denn es könnte durchaus gewünscht sein, auch Worte zu finden, die anders klingen, aber ähnlich geschrieben werden. Gefragt ist also eine Suche nach ähnlichen Begriffen.

Zunächst indizieren Sie also die Tabelle SH.CUSTOMERS mit Oracle TEXT. Da in diesem Fall jedoch keine Textdokumente, sondern Adressen vorliegen, muss der Index vorher richtig parametrisiert werden.

begin
  ctx_ddl.create_preference(
    preference_name => 'adressen_store'
   ,object_name     => 'MULTI_COLUMN_DATASTORE'
  );
  ctx_ddl.set_attribute(
    preference_name => 'adressen_store'
   ,attribute_name  => 'columns'
   ,attribute_value => 'CUST_FIRST_NAME, CUST_LAST_NAME, CUST_STREET_ADDRESS, CUST_CITY'
  );
end;
/

Mit diesen SQL.Anweisungen wird ein sog. MULTICOLUMN-Datastore eingerichtet; dem Index wird also mitgeteilt, dass die zu indizierenden Daten sich in mehreren Tabellenspalten befinden - eben den in attribute_value genannten. Damit Sie die Anweisungen im SQL Workshop ausführen können (Abbildung 2), müssen Ihrem Workspace-Datenbankschema EXECUTE-Privilegien auf dem PL/SQL-Paket CTX_DDL eingeräumt sein. Ggfs. müssen Sie diese Privilegien als DBA einräumen.

grant execute on CTX_DDL to [Workspace-Schema]

Abbildung 2: Einrichten des MULTI_COLUMN_DATASTORE

Im nächsten Schritt wird der Index angelegt. Setzen Sie (wiederum mit dem SQL Workshop) folgende SQL-Anweisungen ab.

create index idx_context_adressen on customers (cust_street_address)
indextype is ctxsys.context
parameters ('datastore adressen_store section group CTXSYS.AUTO_SECTION_GROUP stoplist ctxsys.empty_stoplist sync (on commit)')
/

• Der Index wird auf die Spalte CUST_STREET_ADDRESS erstellt, beinhaltet jedoch, da die Index-Präferenz adressen_store eingebunden wurde, alle 4 Spalten des Berichts.
• Für Volltextsuche ist es meist sollvoll, bestimmte Wörter (Bindewörter, Präpositionen und andere) auszuschließen, da sie ohnehin in jedem Dokument vorkommen. Da hier jedoch Adressen indiziert werden, werden keine Wörter ignoriert (EMPTY_STOPLIST).
• Der Volltextindex verhält sich standardmäßog nicht wie ein normaler Index - So werden neue Dokumente erst sichtbar, wenn der Index synchronisiert wurde. Bei einer Tabelle mit Textdokumenten und hoher Änderungsrate macht es durchaus Sinn, den Index nur in bestimmten Intervallen zu synchronisieren. Für dieses Beispiel gehen wir jedoch davon aus, dass die Änderungsrate der Tabelle CUSTOMERS gering ist. Es kann also im vorliegenden Fall ON COMMIT verwendet werden - der Index verhält sich also wie ein normaler Index. Allerdings hat dies Auswirkungen auf die Administration und Wartung des Index - weiter unten erfahren Sie mehr dazu.

Ändern Sie im nächsten Schritt die SQL-Abfrage des Berichts wie folgt:

select 
  cust_first_name
 ,cust_last_name
 ,cust_street_address
 ,cust_city
from customers
where contains(cust_street_address, :P1_REPORT_SEARCH) > 0

Starten Sie die Seite nun neu und geben Sie einige Abfragen ein:

• Baer
  Sucht nach dem Wort "Baer" in allen in Index vorhandenen Spalten
• Bär
  Sucht nach dem Wort "Bär" in allen in Index vorhandenen Spalten. Beachten Sie, dass "Baer" nun auch gefunden wird - das liegt daran, dass der Volltextindex linguistisch arbeitet.
• ?Beer
  Führt eine Ähnlichkeitssuche durch. Es werden alle Zeilen gefunden, in welchen - in einer der vier Spalten - ein Wort ähnlich Beer vorkommt.
• ?Beer WITHIN CUST_LAST_NAME
  Führt die Ähnlichkeitssuche nur mit dem Nachnamen (CUST_LAST_NAME) durch.
• ?Beer and Hermann
  Findet alle Adressen, die das Wort "Hermann" und ein Wort ähnlich "Beer" enthalten.
• Her% WITHIN CUST_FIRST_NAME
  Findet alle Adressen, deren Vorname mit Her anfängt.
• ?Beer WITHIN CUST_LAST_NAME and Hermann WITHIN CUST_FIRST_NAME
  Findet alle Adressen, deren Vorname Hermann und deren Nachname ähnlich Beer ist.

Abbildung 3: Ähnlichkeitssuche in Aktion

Sie sehen, dass Sie nun schon sehr mächtige Suchabfragen durchführen können. Allerdings dürfte so mancher Endanwender mit dieser Abfragesyntax überfordert sein. Die Aufgabe für den Anwendungsentwickler ist nun, eine anwenderfreundliche Suchmaske zu gestalten. So könnte der Seite eine Checkbox Fuzzy hinzugefügt werden; wird sie angeklickt, so wird der Fuzzy-Operator "?" dem Suchbegriff vorangestellt. Sie erreichen dies durch folgende Wertelistendefinition:

STATIC2:Fuzzy;?

select 
  cust_first_name
 ,cust_last_name
 ,cust_street_address
 ,cust_city
from customers
where contains(cust_street_address, :P1_FUZZY || :P1_REPORT_SEARCH) > 0

Zum Abschluß noch einige Worte zur Wartung des Index: Wie bereits oben erwähnt, ist ein Volltextindex anders aufgebaut als ein gewöhnlicher Datenbankindex. Normalerweise werden Volltextindizes nicht (wie in diesem Beispiel) ON COMMIT, sondern in gewissen Intervallen synchronisiert. Ist die Änderungsrate auf der Tabelle hoch, würde es bei einer Synchronisierung ON COMMIT dazu kommen, dass der Index mehr und mehr fragmentiert und die Abfragen somit langsamer werden. Ausführliche Informationen zu Synchronisierung und Wartung eines Oracle TEXT Index finden Sie in der Online-Dokumentation zu Oracle TEXT.

Die nachfolgenden Maßnahmen zur Indexwartung dienen also der Abfrageperformance. Wie häufig die Maßnahmen nötig sind, hängt allein von der Änderungsrate auf der Tabelle CUSTOMERS ab. Finden nur wenige Änderungen am Tag statt, so reicht es u.U. aus, den Index nur einmal im Jahr neu zu erstellen. Behalten Sie die Performance der Abfragen im Auge - wird sie schlechter, so ist es an der Zeit, etwas zu tun:

Der Index sollte dann optimiert werden - dazu steht im PL/SQL Paket CTX_DDL die Prozedur OPTIMIZE_INDEX zur Verfügung. Es empfiehlt sich eine Art Doppelstrategie:

1. Regelmäßiges Optimieren:
   Das "richtige" Intervall richtet sich nach der Häufigkeit von Änderungen an der Adresstabelle. Mit Hilfe des PL/SQL-Paketes DBMS_SCHEDULER kann sichergestellt werden, dass der Job regelmäßig (bspw. einmal in der Nacht oder alle 8 Stunden) ausgeführt wird.

   begin
     ctx_ddl.optimize_index(
       index_name => 'idx_context_adressen'
      ,optlevel   => 'FULL'
     );
   end;
   /

2. Index-Rebuild:
   Auch die Notwendigkeit eines Index-Rebuild richtet sich nach der Häufigkeit von Änderungen an der Adresstabelle - es kann seltener erfolgen als das "normale" Optimieren (ein Beispiel wäre einmal pro Monat).

   begin
     ctx_ddl.optimize_index(
       index_name => 'idx_context_adressen'
      ,optlevel   => 'REBUILD'
     );
   end;
   /

Oracle TEXT ist zwar ursprünglich zur Volltextsuche in Dokumenten vorgesehen, lässt sich jedoch, wie hier erkennbar ist, auch sehr gut für andere Einsatzbereiche nutzen. Als weiterer Vorteil ergibt sich, dass auch bei sehr vielen einbezogenen Tabellenspalten nur ein Index entsteht, welcher administriert und gepflegt werden muss.

Zurück zur Community-Seite