Auswahl  

 

Oracle
PL/SQL
8.x
29.06.18 (MP)
30.07.18 (MP)
SQL, PL/SQL, Standard Packages

Body

Seit der Version 7 bietet Oracle die Möglichkeit der Volltextsuche. Die frühen Optionen mußten noch separat installiert werden und in der Oracle -Version 8i war die Textsuche in das kostenpflichtige Zusatzmodul interMedia integriert. 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.06


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 downloadhier 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 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):

conn scott/tiger
ALTER INDEX sprueche_idx REBUILD PARAMETERS ('ADD STOPWORD wird');

Anlegen einer eigenen Stopwortliste:


conn scott/tiger
BEGIN
   ctx_ddl.create_stoplist('scott_stop');
   ctx_ddl.add_stopword('scott_stop', 'A');
   ctx_ddl.add_stopword('scott_stop', 'B');
   ...
END;
/

Leider gibt es nicht die Möglichkeit, die eigene Stopwortliste auf der Grundlage der default-Liste aufzubauen. Man muss also alle Stopwörter eingeben (am besten per copy and paste aus dem Skript drdefd.sql) und danach den Index mit der neuen Stopwortliste aktualisieren:

ALTER INDEX sprueche_idx REBUILD PARAMETERS ('REPLACE STOPLIST scott_stop');

SYNCHRONISATION UND OPTIMIERUNG DES INDEXES
In früheren Versionen von Oracle Text musste der Index nach DML-Operationen manuell oder über DBMS_JOB bzw. DBMS_SCHEDULER neu aufgebaut werden.
Ab Version 10g hat man auch hier mehrere Möglichkeiten.


1. MANUELL:

INSERT INTO Sprichwoerter
VALUES(seq1.nextval, 'Wissen ist Macht, aber nichts wissen macht auch nichts');
SELECT * FROM sprichwoerter WHERE CONTAINS(text, 'wissen') > 0; => kein Treffer
exec ctx_ddl.sync_index('sprueche_idx');
SELECT * FROM sprichwoerter WHERE CONTAINS(text, 'wissen') > 0; => Treffer


2. ÜBER EINEN JOB IN REGELMÄSSIGEN INTERVALLEN. DAZU GAB ES IN DER ORACLE DB VERSION 9 EIN KLEINES SKRIPT NAMENS DRJOBDML.SQL, DASS DEN NAMEN DES INDEXES UND DAS INTERVALL IN MINUTEN PER AUSTAUSCHVARIABLE ÜBERNIMMT:


set define on
DECLARE
  job NUMBER;
BEGIN
  dbms_job.submit(job, 'ctx_ddl.sync_index(''&indexname'');',
                  interval => 'SYSDATE+&interval/1440');
  commit;
END;
/
=>

Geben Sie einen Wert für indexname ein: sprueche_idx
Geben Sie einen Wert für interval ein: 60

Der Index wird nun alle 60 Minuten synchronisiert


3. AUTOMATISCH IN REGELMÄSSIGEN INTERVALLEN (DAS MACHT DAS 9I-SKRIPT ÜBERFLÜSSIG).
Diese Option kann man beim Anlegen des Indexes oder nachträglich einrichten. Man braucht dazu das Create Job-Recht:
Synchronisation jeden Tag um Mitternacht

CREATE INDEX sprueche_idx ON sprichwoerter(text) INDEXTYPE IS CTXSYS.CONTEXT   PARAMETERS ('SYNC (EVERY "TRUNC(sysdate)+1/24")');

oder nachträglich (hierbei werden nur die Metadaten des Index verändert, nicht die Struktur):

ALTER INDEX sprueche_idx
REBUILD PARAMETERS (REPLACE METADATA 'SYNC (EVERY "TRUNC(sysdate)+1/24")';


4. AUTOMATISCH NACH JEDEM COMMIT (AB ORACLE 10G). DIES IST NUR DANN SINNVOLL, WENN SELTEN DML-AKTIONEN STATTFINDEN, WEIL DER INDEX SONST UNNÖTIG FRAGMENTIERT:

CREATE INDEX sprueche_idx ON sprichwoerter(text)
INDEXTYPE IS ctxsys.context PARAMETERS ('SYNC (ON COMMIT)');

bzw.:

ALTER INDEX sprueche_idx REBUILD PARAMETERS
('REPLACE METADATA SYNC (ON COMMIT)');


5. AUTOMATISCH NACH JEDER TRANSAKTION (AB ORACLE 10G).
Damit werden Änderungen in den Texten sofort registriert (in der Tabelle dr$unindexed im Schema ctxsys), die Synchronisation muss aber zusätzlich erfolgen, damit die Abfrageperformance mit wachsender Größe dieser Tabelle nicht in den Keller geht:

CREATE INDEX sprueche_idx ON sprichwoerter(text) INDEXTYPE IS ctxsys.context PARAMETERS ('TRANSACTIONAL');

oder

ALTER INDEX sprueche_idx REBUILD PARAMETERS ('REPLACE METADATA TRANSACTIONAL');

Beispiel:

INSERT INTO Sprichwoerter VALUES(seq1.nextval, 'Besser ein Ende mit Schrecken, als ein Schrecken ohne Ende.');
SELECT * FROM sprichwoerter WHERE CONTAINS(text, 'Schrecken') > 0; => Treffer
ROLLBACK;
SELECT * FRO

Besuchen Sie uns doch bei einer unsere über 40 Oracle Schulungen in München - Unterhaching.