Seit der Version 7 bietet Oracle die Möglichkeit der Volltextsuche. Seit Version 9i ist Oracle Text jedoch fester Bestandteil der Datenbank, auch in der Express Edition.
Das kann man leicht nachprüfen, indem man nach dem User ctxsys sucht:
conn scott/tiger
SELECT * FROM all_users WHERE username = 'CTXSYS';
=>
USERNAME USER_ID CREATED
------------------------------ ---------- --------
CTXSYS 25 07.02.20
Die Basisfunktionalitäten kann man ohne zusätzliche Rechte nutzen, für die Anpassung der Sucheinstellungen braucht man jedoch mindestens das Execute-Recht an dem wichtigsten Package des Schemas ctxsys: ctx_ddl oder die Rolle ctxapp.
conn / AS sysdba
GRANT EXECUTE ON ctx_ddl TO scott;
oder
GRANT ctxapp TO scott;
WIE FUNKTIONIERT ORACLE TEXT ?
Die Dokumente werden in einen so genannten Datastore eingelesen. Die zu indizierenden Texte können dabei entweder in CLOB-, VARCHAR2- oder XMLTYPE-Spalten einer Tabelle in der Datenbank liegen (direct_datastore), im Filesystem des Datenbankservers (file_datastore) oder im Inter- bzw. Intranet (url_datastore). Es besteht sogar die Möglichkeit, die Texte über eine selbst definierte Prozedur direkt vor der Indizierung zusammenzustellen (user_datastore).
Im 2. Schritt werden die Objekte im Bedarfsfall gefiltert. Das ist nur dann nötig, wenn es sich um binäre Files, wie Word-Dokumente oder PDF-Dateien handelt. Text-, HTML- und XML-Dateien müssen nicht gefiltert werden. Oracle erkennt über 150 Formate automatisch.
Der Sectioner kann HTML- oder XML-Dokumente anhand von Tags (z.B. <H1> ...</H1> in HTML oder <Produktbeschreibung> .... </Produktbeschreibung> in XML) in einzelne Abschnitte aufteilen.
Der Lexer extrahiert alle relevanten Wörter aus dem Text. Interpunktions- und Sonderzeichen werden entfernt. Bei diesem Schritt kann man u.a. einstellen,
was als Trennzeichen gewertet bzw. ignoriert werden soll (Leerzeichen, Unterstriche etc.)
ob Groß- und Kleinschreibung beibehalten werden soll,
ob zusammengesetzte Worte in ihre Einzelteile zerlegt werden sollen, etc.
Beim Indizierungsprozeß wird aus den gesammelten Wörtern ein invertierter Index erzeugt. Jedem Wort wird dabei eine Liste seiner Fundstellen zugeordnet.
Artikel, Konjunktionen, Präpositionen und Hilfsverben etc., bei Oracle Text Stopwörter genannt, werden nicht indiziert.
Die Einstellungen der sog. wordlist legen fest, welche grammatikalischen Regeln verwendet werden sollen, damit bei der Textsuche auch Beugungsformen des gesuchten Verbs oder Wörter mit ähnlichem Stamm erkannt werden.
In diesem Monatstipp sollen zunächst die grundlegenden Funktionen der Volltextsuche am Beispiel eines Context-Indexes auf einer Text-Spalte erklärt werden.
Die Beispiel-Tabelle, eine Sammlung von Sprichwörtern (mit diversen Schreibfehlern und kleinen Abwandlungen) können Sie (Initiates file download) hier als SQL-File herunterladen.
VOLLTEXTSUCHE IN TEXTSPALTEN
Wir erstellen die Tabelle und erzeugen erstmal einen Index ohne zusätzliche Parameter:
@ d:\sprichwoerter
CREATE INDEX sprueche_idx ON sprichwoerter(text)
INDEXTYPE IS ctxsys.context;
Die Suche in einem Context-Index wird über das Schlüsselwort CONTAINS durchgeführt:
SELECT spaltenliste FROM tabelle
WHERE CONTAINS(index_spalte, '<suchbegriff>')>0;
Die wichtigsten Suchmöglichkeiten:
1. EINFACHE SUCHE NACH WÖRTERN Z.B:
SELECT * FROM sprichwoerter WHERE CONTAINS(text, 'Abend') > 0;
=>
NUMMER TEXT
------ -------------------------------------------
50 Es ist noch nicht aller Tage Abend
89 Man soll den Tag nicht vor dem Abend loben
2. SUCHE NACH WORT-KOMBINATIONEN ODER -ALTERNATIVEN MIT DEN BOOLESCHEN OPERATOREN "AND" UND "OR":
SELECT * FROM sprichwoerter
WHERE CONTAINS(text, 'Gold AND Silber') > 0;
=>
NUMMER TEXT
------ ----------------------------------------
103 Reden ist Silber, Schweigen ist Gold
SELECT * FROM sprichwoerter
WHERE CONTAINS(text, 'Abend OR Morgen') > 0;
=>
NUMMER TEXT
------ ---------------------------------------------------------
50 Es ist noch nicht aller Tage Abend
89 Man soll den Tag nicht vor dem Abend loben
92 Morgen, morgen, nur nicht heute, sagen alle faulen Leute
3. SUCHE NACH ÄHNLICH GESCHRIEBENEN WÖRTERN:
Wenn man den Operator "?" vor das gesuchte Wort stellt, kann man auch Wörter mit Rechtschreibfehlern oder Buchstabendrehern finden:
SELECT * FROM sprichwoerter WHERE CONTAINS(text, '?Länder') > 0;
=>
NUMMER TEXT
------ ------------------------------
8 Andere Lender, andere Sitten.
Dann bekommt man aber leicht unerwünschte Ergebnisse - wie hier Messer statt Meister.
SELECT * FROM sprichwoerter WHERE CONTAINS(text, '?Meister') > 0;
=>
NUMMER TEXT
------ ------------------------------------------------------------
45 Einmal findet jeder seinen Meister.
49 Es ist noch kein Meitser vom Himmel gefallen, aber vom Gerüst.
90 Messer, Gabel, Scher' und Licht sind für kleine Kinder nicht.
113 Übung macht den Meister
Um die Möglichkeiten einzugrenzen, kann man das Schlüsselwort Fuzzy einsetzen, z.B.:
SELECT * FROM sprichwoerter
WHERE CONTAINS(text, 'FUZZY(Meister, 70, 5, N)') > 0;
=>
NUMMER TEXT
------ ---------------------------------------------------------
45 Einmal findet jeder seinen Meister.
49 Es ist noch kein Meitser vom Himmel gefallen, aber vom Gerüst.
113 Übung macht den Meister
Der 1. Parameter bestimmt die Ähnlichkeit (minimal = 1, identisch = 80, default = 60). Daraus resultiert eine Liste ähnlicher Worte (hier Meister, Meitser und Messer). Mit diesen Wörtern wird dann eine oder-Suche durchgeführt.
Der 2. Parameter bestimmt die maximale Anzahl der Wörter dieser Liste (1- 5000, default = 100).
Der 3. Parameter wirkt sich nur auf die Relevanzgewichtung (s.u. Score) aus.
4. SUCHE MIT WILDCARDS: "%" FÜR KEIN ODER BELIEBIG VIELE ZEICHEN UND "_" FÜR GENAU 1 ZEICHEN:
SELECT * FROM sprichwoerter WHERE CONTAINS(text, '_elbst%') > 0;
=>
NUMMER TEXT
------ -----------------------------------------------------
109 Selbst ist der Mann!
110 Selbsterkenntnis ist der erste Weg zur Besserung.
Eigentlich erwartet man hier auch Ergebnisse, die das Wort "selbst" enthalten. Das wird aber als Stopwort behandelt und nicht indiziert.
5. SUCHE NACH ÄHNLICH KLINGENDEN WÖRTERN (SOUNDEX-FUNKTION) MIT DEM OPERATOR "!":
Da die Soundex-Funktion auf englische Phonetik ausgerichtet ist, kann man dabei aber durchaus Überraschungen erleben (wie die zum Hasen mutierte Katze im Beispiel).
SELECT * FROM sprichwoerter
WHERE CONTAINS(text, '!Kaze') > 0;
=>
NUMMER TEXT
------ ---------------------------------------------------------------
19 Da liegt der Hase im Pfeffer.
28 Die Katze lässt das Mausen nicht.
87 Lügen haben kurze Beine
129 Wenn die Katze aus dem Haus ist, tanzen die Mäuse auf dem Tisch.
6. SUCHE NACH AUSDRÜCKEN, DIE DENSELBEN WORTSTAMM HABEN WIE DAS SUCHWORT ODER MIT DEM SUCHWORT ZUSAMMENGESETZTE WORTE BILDEN, MIT DEM OPERATOR "$":
SELECT * FROM sprichwoerter
WHERE CONTAINS(text, '$helfen') > 0;
=>
NUMMER TEXT
------ -------------------------------------------------
31 Dumm bleibt dumm, da helfen keine Pillen!
66 Hilf dir selbst, so hilft dir Gott!
oder
SELECT * FROM sprichwoerter
WHERE CONTAINS(text, '$Porzellan') > 0;
=>
NUMMER TEXT
------ ---------------------------------------------
120 Vorsicht ist die Mutter der Porzellankiste.
Enterprise-Edition:
PRV_OWNER PRV_PREFERENCE PRV_ATTRIBUTE PRV_VALUE
---------- ------------------- ------------------------- ----------------------------
CTXSYS DEFAULT_WORDLIST STEMMER GERMAN
CTXSYS DEFAULT_WORDLIST FUZZY_MATCH GERMAN
CTXSYS URL_DATASTORE TIMEOUT 30
CTXSYS URL_DATASTORE MAXTHREADS 8
CTXSYS URL_DATASTORE URLSIZE 256
CTXSYS URL_DATASTORE MAXURLS 256
CTXSYS URL_DATASTORE MAXDOCSIZE 2097152
CTXSYS DEFAULT_LEXER COMPOSITE GERMAN
CTXSYS DEFAULT_LEXER MIXED_CASE YES
CTXSYS DEFAULT_LEXER ALTERNATE_SPELLING GERMAN
CTXSYS DEFAULT_STORAGE R_TABLE_CLAUSE lob (data) store AS (cache)
CTXSYS DEFAULT_STORAGE I_INDEX_CLAUSE compress 2
Erklärungen:
STEMMER: legt die Grammatik für Stamm- und Beugungsformen fest. Die Voreinstellung GERMAN führt dazu, dass z.B. die Suche nach $laufen auch Formen wie lief, gelaufen, läuft, etc. liefert.
FUZZY_MATCH: bestimmt, nach welcher Routine ähnlich geschriebene Wörter gesucht werden (hier sind allerdings nicht allzu viele Unterschiede zwischen GERMAN und GENERIC festzustellen)
Die Einstellung COMPOSITE = GERMAN im deutschen Lexer ermöglicht die Zerlegung eines Wortes im Index in seine Bestandteile (Neben Porzellankiste wird im Index auch Porzellan und Kiste gespeichert).
MIXED_CASE = YES bedeutet, dass Groß- und Kleinschreibung unterschieden wird.
ALTERNATE_SPELLING = GERMAN bewirkt, dass Wörter in alternativen Schreibweisen im Index gespeichert werden, also z.B. Töchter als Toechter und bisschen als bißchen.
Ein weiterer Unterschied zeigt sich bei den oben erwähnten Stopwörtern, die von Oracle Text nicht indiziert werden, weil sie in jedem Text vorkommen, wie z.B. Artikel, Konjunktionen und Präpositionen. Diese Listen kann man einsehen:
SELECT * FROM ctx_stoplists WHERE spl_owner = 'CTXSYS';
=>
-- 10g und 11g-Enterprise-Edition
SPL_OWNER SPL_NAME SPL_COUNT SPL_TYPE
---------- -------------------- ---------- ---------------
CTXSYS EMPTY_STOPLIST 0 BASIC_STOPLIST
CTXSYS EXTENDED_STOPLIST 0 BASIC_STOPLIST
CTXSYS DEFAULT_STOPLIST 235 BASIC_STOPLIST -- deutsche Stopwörter
-- Express-Edition
SPL_OWNER SPL_NAME SPL_COUNT SPL_TYPE
---------- -------------------- ---------- ---------------------
CTXSYS EMPTY_STOPLIST 0 BASIC_STOPLIST
CTXSYS DEFAULT_STOPLIST 114 BASIC_STOPLIST -- englische Stopwörter
CTXSYS EXTENDED_STOPLIST 0 BASIC_STOPLIST
Die darin enthaltenen Wörter erhält man über:
SELECT spw_word FROM ctx_stopwords WHERE spw_stoplist = 'DEFAULT_STOPLIST';
NACHRÜSTEN DER SPRACHSPEZIFISCHEN EINSTELLUNGEN IN DER EXPRESS-EDITION
Bei der Installation der Express-Edition wird Oracle Text mit den amerikanischen Einstellungen vorkonfiguriert. Die Konfiguration kann man jedoch leicht über das Skript drdefd.sql im Ordner <Oracle-Home>\ctx\admin\defaults ändern (ein Blick in dieses Skript lohnt sich).
Wenn die Einstellungen für alle User gelten sollen, nimmt man diese Änderungen am besten direkt im ctxsys-Schema vor. Dies muss man zuerst freischalten:
CONN / AS sysdba
ALTER USER ctxsys IDENTIFIED BY text ACCOUNT UNLOCK;
CONN ctxsys/text
Die alten Einstellungen werden über Bord geworfen:
BEGIN
ctx_ddl.drop_preference('DEFAULT_LEXER');
ctx_ddl.drop_preference('DEFAULT_WORDLIST');
ctx_ddl.drop_stoplist('DEFAULT_STOPLIST');
ctx_ddl.drop_policy('DEFAULT_POLICY_ORACONTAINS');
END;
/
und die neuen eingespielt:
@ =>\ctx\admin\defaults\drdefd.sql
Jetzt muss man noch den Index an die neuen Einstellungen anpassen. Das geht entweder über einen Alter Index Befehl:
conn scott/tiger
ALTER INDEX sprueche_idx REBUILD PARAMETERS
('REPLACE wordlist ctxsys.default_wordlist stoplist ctxsys.default_stoplist lexer ctxsys.default_lexer');
oder über Löschen und Neuerstellen des Indexes:
DROP INDEX sprueche_idx;
CREATE INDEX sprueche_idx ON sprichwoerter (text) INDEXTYPE IS ctxsys.context
PARAMETERS('wordlist ctxsys.default_wordlist stoplist ctxsys.default_stoplist
lexer ctxsys.default_lexer');
Die neuen Einstellungen kann man über die Views CTX_STOPLISTS und CTX_PREFERENCE_VALUES überprüfen.
ANPASSUNGEN DER SPRACH-EINSTELLUNGEN
Die Einstellungen von wordlist, stoplist und lexer kann man nachträglich noch anpassen. Wenn man z.B. die Case-Sensitivity abschalten will, kann man sich über die Prozedur CTX_DDL.CREATE_PREFERENCE auf der Basis des default-Lexers einen eigenen Lexer erzeugen, dessen Attribute man selber setzen kann. Leider genügt es nicht, hier nur diesen einen Parameter anzupassen, dann gehen nämlich die deutschen Einstellungen für ALTERNATE_SPELLING und COMPOSITE verloren.
Wenn man einen Text indizieren will, der Wörter mit Sonderzeichen enthält, möchte man nicht, dass diese Sonderzeichen als Trennzeichen gewertet werden. Dies kann man mit dem Attribut PRINTJOINS erreichen. Im Beispiel unten werden Unterstrich, Dollarzeichen, Raute und das Apostroph (das wie üblich mit einem zusätzlichen Apostroph maskiert werden muss) als Bestandteile von Wörtern festgelegt.
Zudem kann man über das Attribut NEW_GERMAN_SPELLING bewirken, dass sowohl die alten und die neuen Formen (z.B. rauh / rau, Stengel / Stängel) im Index abgespeichert und bei der Suche gefunden werden.
BEGIN
-- Preference löschen, falls schon vorhanden
-- ctx_ddl.drop_preference('scott_insensitive');
ctx_ddl.create_preference(
preference_name => 'scott_insensitive', -- Name der neuen Einstellung
object_name => 'basic_lexer'); -- basiert auf dem basic_lexer
ctx_ddl.set_attribute(
preference_name => 'scott_insensitive',
attribute_name => 'mixed_case',
attribute_value => 'no'); -- default ist yes
ctx_ddl.set_attribute('scott_insensitive','composite', 'german');
ctx_ddl.set_attribute('scott_insensitive','alternate_spelling', 'german');
ctx_ddl.set_attribute('scott_insensitive','printjoins', '_$#-''');
ctx_ddl.set_attribute('scott_insensitive','new_german_spelling','yes');
END;
/
Anpassen des Indexes:
ALTER INDEX sprueche_idx REBUILD PARAMETERS ('REPLACE LEXER scott_insensitive');
VORSICHT, FALLE!
Da im neuen Lexer Bindestriche als Bestandteile von Wörtern gewertet werden, sollte die folgende Abfrage eigentlich ein Ergebnis zurückliefern:
SELECT * FROM sprichwoerter WHERE CONTAINS(text, 'Oracle-Experte') > 0;
=>
Es wurden keine Zeilen ausgewählt
Der Bindestrich wird jedoch in der Abfrage-Syntax als Minus-Zeichen ausgewertet. Ähnliche Probleme gibt es z. B. mit dem $-Zeichen. Wenn man nach Wörtern mit diesen Zeichen sucht, muss man sie maskieren, indem man entweder einen Schrägstrich vor das Zeichen stellt oder das ganze Suchwort in geschweifte Klammern stellt:
SELECT * FROM sprichwoerter WHERE CONTAINS(text, 'Oracle\-Experte') > 0;
=>
NUMMER TEXT
------ -------------------------------------------------------
54 Früh übt sich, was ein Oracle-Experte werden will.
oder
SELECT * FROM sprichwoerter WHERE CONTAINS(text, '{18-Karat-Gold}') > 0;
=>
NUMMER TEXT
------ ---------------------------------------------
48 Es ist nicht alles 18-Karat-Gold, was glänzt
ANPASSEN DER STOPWORT-LISTE
Hier gibt es mehrere Möglichkeiten.
Änderungen der default-Liste mit den Prozeduren ADD_STOPWORD und REMOVE_STOPWORD:
conn ctxsys/text
exec ctx_ddl.add_stopword('default_stoplist', 'wird');
exec ctx_ddl.remove_stopword('default_stoplist', 'selbst');
Danach muss man den Index im Schema Scott anpassen:
conn scott/tiger
ALTER INDEX sprueche_idx
REBUILD PARAMETERS ('REPLACE STOPLIST ctxsys.default_stoplist');
direkte Anpassung des Index (nur Hinzufügen möglich):