Ähnlichkeitssuche in einem APEX-Bericht: Mit Oracle Text
Erscheinungsmonat |
APEX-Version |
Datenbankversion |
August 2016 |
alle |
ab 11.2 |
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.
Am besten kopieren Sie nun die Tabelle SH.CUSTOMERS in Ihr Workspace-Schema ...
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.
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.
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.
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.
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.
Nun geht es daran, dies in den Application Express-Bericht zu integrieren. Navigieren Sie zur
Berichtsabfrage und ändern Sie diese wie folgt um.
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.
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
|