Ähnlichkeitssuche in einem APEX-Bericht: Mit Oracle Text

Ein APEX-Bericht mit einem Suchfeld ist nichts Besonderes; interaktive Berichte enthalten diese aus dem Stand; klassische Berichte sind schnell damit ausgestattet. Endanwender können dann sehr bequem in den Daten suchen. Das funktioniert in den meisten Fällen ganz wunderbar; hat aber Grenzen.

• Sind die Datenbestände sehr groß, wird eine freie Suche mit Wildcards schnell inperformant
• Man braucht Indizes auf jeder durchsuchbaren Tabellenspalte.
• Die Suche ist nicht fehlertolerant - so können fehlerhaft gespeicherte Daten oft nur schwer gefunden werden. Besonders Namen machen den Anwendern oft das Leben schwer: fremdsprachige Sonderzeichen und Schreibweisen sorgen oft dafür, dass diese Namen nur schwer auffindbar sind.

Die gute Nachricht ist, dass die Datenbank mit der Volltext-Engine Oracle Text Antworten auf diese Fragen hat. Oracle Text kann nicht nur zur Suche in Dokumentbeständen, sondern auch zur Suche in relationalen Tabellendaten genutzt werden. Wie man einen solchen Index aufsetzt und in einer Application Express-Anwendung nutzen kann, zeigt dieser Community-Tipp.

Zunächst braucht es einen Datenbestand, auf den man den Oracle Text Index erzeugen und dann damit suchen kann. Sie können hier natürlich Ihren eigenen Datenbestand nehmen oder Sie nehmen das Oracle-Beispielschema SH, welches die Tabelle CUSTOMERS enthält. Darin sind genug Namen und Adressen enthalten, so dass man es wagen kann, einen Oracle Text Index zu erstellen. Das Schema 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 es nachträglich installieren können.

Bevor Sie beginnen, stellen Sie sicher, dass die Tabelle SH.CUSTOMERS in Ihrem APEX-Workspace zur Verfügung steht und von Ihrer Anwendung selektiert werden kann. Sie sollte 55.500 Zeilen beinhalten.

grant select on sh.customers to {Workspace-User}

Am besten kopieren Sie nun die Tabelle SH.CUSTOMERS in Ihr Workspace-Schema ...

create table customers as select * from sh.customers

und erzeugen Sie dann eine Anwendung mit einer Seite und einem klassischen Bericht auf Ihre neue Tabelle CUSTOMERS. Nehmen Sie die Spalten CUST_FIRST_NAME, CUST_LAST_NAME, CUST_STREET_ADDRESS, CUST_CREDIT_LIMIT und CUST_CITY in Ihren Bericht auf. Erstellen Sie dann ein Texteingabefeld P1_SUCHE in einer eigenen Region oberhalb des Berichts. Stellen Sie Submit when Enter pressed vorerst auf Yes. Das Ergebnis sollte wie in Abbildung 1 dargestellt aussehen.

Abbildung 1: Ausgangssituation: Ein Bericht auf die Tabelle CUSTOMERS

Wenn Sie nun etwas ins Suchfeld eingeben, passiert noch gar nichts. Normalerweise würde man jetzt die SQL-Abfrage des Berichts um eine WHERE-Klausel erweitern, so dass die Zeilen anhand des Eingabefeldes P1_SUCHE gefiltert werden. Doch heute gehen wir anders vor: Da die Volltextengine Oracle Text verwendet werden soll, braucht es zunächst einen Volltextindex. Normalerweise denkt man bei einem Volltextindex an Office-Dokumente oder andere Fließtexte - aber Oracle Text kann auch mit gewöhnlichen, relationalen Spalten umgehen.

Damit Sie Oracle Text nutzen können, muss es zunächst in der Datenbank verfügbar sein - das ist fast immer der Fall. Darüber hinaus brauchen Sie die Rolle CTXAPP, da Sie den Volltextindex parametrisieren wollen. Diese Rolle müssen Sie sich gegebenenfalls von Ihrem Datenbankadministrator freischalten lassen. Wenn Sie Zugriff auf das PL/SQL Paket CTX_DDL haben, ist alles in Ordnung und Sie können loslegen.

In unserem Beispiel enthält der Bericht fünf Spalten - und alle sollen durchsuchbar sein. Mit Oracle Text wird dennoch nur ein Index erzeugt - dieser wird die Daten aller fünf Tabellenspalten zusammenfassen und indizieren. Die folgenden PL/SQL-Blöcke erzeugen die dazu nötige Konfigurationsobjekte. Zuerst wird eine Oracle Text Datastore Preference als MULTI_COLUMN_DATASTORE erstellt. So wird festgelegt, welche Tabellenspalten Oracle Text indizieren soll.

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

Als nächstes wird eine Oracle Text Section Group erzeugt. Hier lassen sich für die verschiedenen Spalten verschiedene Features konfigurieren. Von besonderer Bedeutung ist die numerische Spalte CUST_CREDIT_LIMIT - diese wird als SDATA-Section eingerichtet, so dass numerische Filter wie größer oder kleiner möglich werden.

begin
  ctx_ddl.create_section_group(
    group_name => 'customers_sg', 
    group_type => 'XML_SECTION_GROUP'
  );

  ctx_ddl.add_field_section(
    group_name   => 'customers_sg',
    section_name => 'FIRSTNAME',
    tag          => 'CUST_FIRST_NAME',
    visible      => true
  );

  ctx_ddl.add_field_section(
    group_name   => 'customers_sg',
    section_name => 'LASTNAME',
    tag          => 'CUST_LAST_NAME',
    visible      => true
  );

  ctx_ddl.add_field_section(
    group_name   => 'customers_sg',
    section_name => 'STREET_ADDRESS',
    tag          => 'CUST_STREET_ADDRESS',
    visible      => true
  );

  ctx_ddl.add_field_section(
    group_name   => 'customers_sg',
    section_name => 'CITY',
    tag          => 'CUST_CITY',
    visible      => true
  );

  ctx_ddl.add_sdata_section(
    group_name   => 'customers_sg',
    section_name => 'CREDIT_LIMIT',
    tag          => 'CUST_CREDIT_LIMIT',
    datatype     => 'NUMBER'
  );
end;
/

Mit der Datastore Preference wurde bestimmt, wo die zu indzierenden Daten herkommen, mit der Section Group wurde festgelegt, welche Abschnitte welche Daten beinhalten. Als nächstes wird die Lexer Preference eingerichtet - diese legt fest, wie die konkreten Daten indiziert werden sollen, insbesondere wie mit Sonderzeichen umgegangen werden soll.

begin
  ctx_ddl.create_preference(
    preference_name => 'customers_lx', 
    object_name     => 'BASIC_LEXER'
  );

  ctx_ddl.set_attribute(
    preference_name => 'customers_lx', 
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO'
  );

  ctx_ddl.set_attribute(
    preference_name => 'customers_lx', 
    attribute_name  => 'BASE_LETTER',
    attribute_value => 'YES'
  );

  ctx_ddl.set_attribute(
    preference_name => 'customers_lx', 
    attribute_name  => 'BASE_LETTER_TYPE',
    attribute_value => 'GENERIC'
  );
end;
/

Damit sind die nötigen Konfigurationsobjekte gespeichert - übrigens können diese Preferences für mehrere Indizes verwendet werden; Sie können Konfigurationen also einmal anlegen und dann wiederverwenden.

Nun wird der Oracle Text-Index mit einem CREATE INDEX-Kommando erzeugt, wobei die Preferences in der PARAMETERS-Klausel übergeben werden. Für die Performance wichtig ist der Parameter MEMORY; dieser legt fest, wieviel Hauptspeicher dem Indizierungsprozess zur Verfügung gestellt werden soll. Dieser Hauptspeicher wird nur zur Indizierung benötigt und allokiert, nicht jedoch später, wenn der Index genutzt werden soll. Wenn die Datenmengen größer werden, sollte man hier nicht zu sparsam sein; dann sind 1G oder 2G nicht zu wenig. Natürlich muss der Hauptspeicher auch wirklich im System vorhanden sein.

create index customers_ftx on customers (cust_last_name)
indextype is ctxsys.context
parameters (
 'datastore     customers_ds
  section group customers_sg
  lexer         customers_lx
  stoplist      ctxsys.empty_stoplist 
  sync          (on commit)
  memory        100M'
)
/

Je nach Größe Ihrer Tabelle braucht es Zeit, um den Index zu erstellen. Wenn er fertig ist, kann er für Volltextabfragen genutzt werden. Eine SQL-Abfrage könnte so aussehen.

select 
  cust_first_name,
  cust_last_name,
  cust_street_address,
  cust_city,
  cust_credit_limit
from customers
where contains(cust_last_name, 'Venkayala and Arbuckle') > 0;


CUST_FIRST_NAME      CUST_LAST_NAME       CUST_STREET_ADDRESS                      CUST_CITY
-------------------- -------------------- ---------------------------------------- -------------------
Samuel               Venkayala            87 West De Soto Avenue                   Arbuckle
Ian                  Venkayala            37 Washington Street                     Arbuckle
Abbie                Venkayala            27 North Summit Boulevard                Arbuckle

Nun geht es daran, dies in den Application Express-Bericht zu integrieren. Navigieren Sie zur Berichtsabfrage und ändern Sie diese wie folgt um.

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

Wenn Sie Ihre Seite nun mit der vorigen Abfrage testen, sieht das Ergebnis in etwa wie in Abbildung 2 aus.

Abbildung 2: Oracle Text und Application Express: in Aktion

Sie können nun den gesamten Funktionsumfang von Oracle Text nutzen.

• Baer
  Sucht nach dem Wort "Baer" in allen in Index vorhandenen Spalten
• ?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 LASTNAME
  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 FIRSTNAME
  Findet alle Adressen, deren Vorname mit Her anfängt.
• ?Herman WITHIN FIRSTNAME and SDATA(CREDITLIMIT >= 3000)
  Findet alle Adressen, deren Vorname mit Her anfängt und deren CREDIT_LIMIT größer als 3000 ist. Dieses Beispiel ist besonders interessant, da es eine numerische mit einer Ähnlichkeitsuche kombiniert.

Die Oracle Text Abfragesyntax ist sehr mächtig; es können verschiedene Features wie Ähnlichkeitssuche, Stemming, Namenssuche, Section-Suche oder andere mit bool'schen Operatoren beliebig kombiniert werden. Ausführliche Informationen enthält die Dokumentation. Für den Endanwender dürfte das meist zu komplex sein. Es empfiehlt sich daher, eine PL/SQL-Funktion zu erstellen, welche die Nutzereingabe entgegennimmt und in eine Oracle Text-Abfrage umwandelt. Eine sehr einfache Funktion könnte so aussehen.

create or replace function convert_end_user_search (
    p_search in varchar2 
) return varchar2 is
    l_search varchar2(32767) := p_search;
begin
    -- remove special characters; irrelevant for full text search
    if substr( l_search, 1, 8 ) = 'ORATEXT:' then
        return substr( l_search, 9 );
    else 
        l_search := regexp_replace( l_search, '[<>{}/()*%&!$?.:,;\+#]', '' );
        return 'FUZZY({' || l_search || '}, 70, 300)';
    end if;
end;

Die Funktion entfernt alle Sonderzeichen aus dem Suchbegriff des Endanwenders und führt damit eine Ähnlichkeitssuche (FUZZY) durch. Wer sich mit Oracle Text gut auskennt, kann seiner Abfrage ORATEXT: voranstellen und sie so 1:1 ausführen lassen. Diese Funktion ist natürlich nur ein einfaches Beispiel - in der Praxis sollte sie sich anhand der konkreten Suchbedürfnisse orientieren.

Abbildung 3: Die Fuzzy-Suche wird nun automatisch angewendet

Abbildung 4: Wird der Abfrage ein ORATEXT: vorangestellt, wo wird sie 1:1 durchgereicht ...

Abbildung 5: ... und der Fuzzy-Parameter muss explizit gesetzt werden

Zum Abschluß noch einige Worte zur Wartung des Index: Ein Oracle Text Volltextindex ist anders aufgebaut als ein gewöhnlicher Datenbankindex. So ist die Synchronisierung des Index bei Änderungen an den Daten aufwändiger als bei einem normalen Index. Auch kann ein Volltextindex bei sehr häufigen Änderungen an den Daten "fragmentieren" und eine Optimierung nötig werden.

In diesem Beispiel wurde im CREATE INDEX-Kommando der Parameter SYNC (ON COMMIT) gesetzt, so dass sich der Oracle Text-Index fast wie ein normaler Index verhält. Wenn die Daten allerdings eher einen statischen Character haben und sich nicht oder nur zu definierten Zeitpunkten ändern, so sollte dieser Parameter weggelassen und eine manuelle Synchronisierung angestrebt werden. Ausführliche Informationen zu Synchronisierung und Wartung eines Oracle TEXT Index finden Sie in der Online-Dokumentation zu Oracle TEXT.

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.

Weitere Informationen zu Oracle Text finden sich ...

• Oracle Dokumentation: Oracle Text Application Developers' Guide
  http://docs.oracle.com/database/121/CCAPP/toc.htm
• Oracle Dokumentation: Oracle Text Reference
  http://docs.oracle.com/database/121/CCREF/toc.htm
• Deutschsprachiges Blog: Oracle Text
  http://oracle-text-de.blogspot.com

Zurück zur Community-Seite