Donnerstag, 15. Mai 2008

DML Operationen und der Oracle Text Index

Im Folgenden wollen wir die Möglichkeiten der Oracle Text Index-Maintenance in Verbindung mit DML-Operationen demonstrieren. Nehmen wir die Beispieltabelle und den Text Index aus dem ersten Blog als Grundlage. Der Inhalt der Dokumenttabelle sieht folgendermassen aus:


  ID DOKUMENT
---- ---------------------------------------------------------------------
1 A-Partei gewinnt Wahl in Hansestadt
2 Terror in Nahost: Kriminalität steigt immer weiter an
3 Wirtschaft: Erneuter Gewinnzuwachs in diesem Jahr
4 Olympia rückt näher: Der Fackellauf ist in vollem Gange
5 Wer wird US-Präsident? Obama und Clinton machen Wahlkampf
6 Papst bestürzt über jüngsten Skandal!
7 Wahlkampf in den USA geht weiter: Clinton und Obama LIVE zu sehen
8 Software-Kenntnisse werden immer wichtiger
9 Umfrage: Alle wollen mehr Geld!
10 Der Papst liest seine erste Messe in den USA

Werden zusätzliche Dokumente in unsere Dokumenttabelle eingefügt, wird der Index nicht automatisch aktualisiert. Um den Index up-to-date zu haben, muss der Index synchronisiert werden. Dies kann man manuell mit der Prozedur CTX_DDL.SYNC_INDEX erreichen oder aber automatisch in periodischen Abständen in Verbindung mit dem DBMS_JOB oder ab 10g mit dem DBMS_SCHEDULER Paket. In 10g ist es nun zusätzlich möglich diese Operation ganz bequem beim CREATE INDEX oder dem ALTER INDEX REBUILD PARAMETERS mitanzugeben.
In folgendem Beispiel wird der Sync-Scheduler-Job alle 5 Minuten durchgeführt; dabei wird 15MB Memory zur Verfügung gestellt und mit der Parallelisierung von 2 gearbeitet.
SQL> alter index idx_text rebuild parameters ('replace metadata sync (every "SYSDATE+5/1440" parallel 2 memory 15M)');
Nun fügen wir eine weitere Zeile ein und versuchen das Resultat zu selektieren:
SQL>insert into texttabelle values (seq_texttabelle.nextval, 'Muppet-Show der Steuerversprecher');
SQL>commit;
SQL>select * from texttabelle where contains(dokument, 'Muppet')>0;
no rows selected
Monitoring ist möglich über CTX_USER_PENDING View (oder ctxsys.dr$pending View):
SQL> select pnd_index_name,pnd_rowid,pnd_timestamp from ctx_user_pending;
PND_INDEX_NAME  PND_ROWID                  PND_TIMESTAMP
--------------- -------------------------- --------------
IDX_TEXT        AAAUYsAAEAABfg0AAK         16-05-08 09:21
Und nun das Ergebnis nach kurzer Wartezeit:
SQL>select * from texttabelle where contains(dokument, 'Muppet')>0;
  ID DOKUMENT
---- ------------------------------------------------------------
  11 Muppet-Show der Steuerversprecher

Falls die Wartezeit bis zur Synchronisierung zu lange ist, hat man in 10g die Möglichkeit Intervalle zum COMMIT Zeitpunkt oder aber bzgl. der Transaktion zu wählen. Folgendes Beispiel zeigt den Einsatz der COMMIT Option:
SQL>alter index idx_text rebuild parameters ('replace metadata sync (on commit)');

SQL>insert into texttabelle values (seq_texttabelle.nextval, 'Bahn-Aufsichtsrat macht Weg frei für Börsengang');
SQL>select * from texttabelle where contains(dokument, 'Weg')>0;
no rows selected
SQL>commit;
SQL>select * from texttabelle where contains(dokument, 'Weg')>0;
 ID DOKUMENT
--- ------------------------------------------------------------
 12 Bahn- Aufsichtsrat macht Weg frei für Börsengang

Die COMMIT Option erlaubt allerdings keine zusätzlichen Memory- oder PARALLEL- Optionsangaben im Statement. Ausserdem könnte der Index durch häufiges COMMIT auch fragmentiert werden. Die zweite Option ist ein transaktionsbezogenes Intervall (auch transactional query genannt) auszuwählen. Der Index wird auch hier entweder mit CREATE INDEX oder folgendem ALTER INDEX REBUILD Statement erzeugt.
SQL>alter index idx_text rebuild parameters ('replace metadata transactional');

Das folgende Beispiel zeigt das Verhalten:
SQL>select count(*) from texttabelle where contains(dokument, 'im all')>0; -- kein Treffer
SQL>insert into texttabelle values (seq_texttabelle.nextval, 'Europäer im All');
SQL>select * from ctxsys.dr$unindexed;
UNX_IDX_ID UNX_IXP_ID UNX_ROWID
---------- ---------- ------------------
1457       0          AAAUVdAAEAABfdUAAO
SQL>select count(*) from texttabelle where contains(dokument, 'im all')>0; -- 1 Treffer
SQL>rollback;
SQL>select count(*) from texttabelle where contains(dokument, 'im all')>0; -- kein Treffer
Und wie funktioniert das Ganze?
UPDATE und INSERT Statements eines transaktionalen Index werden wie beim normalen Index in der dr$pending View mitgeloggt. Zusätzlich dazu werden die ROWIDs in dr$unindexed mitgeschrieben. Während einer Abfrage wird jede ROWID in dr$unindexed mit den Ergebnissen aus der $I Tabelle evaluiert. Die Menge der ROWIDS von dr$unindexed wird mit dem Resultat der $I Tabelle kombiniert und liefert die Endresultate.
Transactional Queries ersetzen allerdings keinen SYNC-Prozess. Um zu verhindern, dass die Queries immer langsamer werden mit wachsender dr$unindexed Tabelle, ist es notwendig ein sinnvolles Intervall für den SYNC-Prozess einzustellen.
Und am Schluss noch ein kleiner Tipp: Benötigen einige Abfragen sofortige Ergebnisse bei der Suche und andere nicht, kann das Feature einfach ein- und ausgeschaltet werden mit:
SQL>exec ctx_query.disable_transactional_query := TRUE;

Viel Spass beim Ausprobieren ...



Donnerstag, 17. April 2008

Ein Einstieg in Oracle TEXT ...

Oracle TEXT ist eine in die Datenbank integrierte Volltextrecherche, die in allen Datenbankeditionen enthalten ist (kostet also nichts extra) und normalerweise ohne weitere Installation direkt zur Verfügung steht. Man kann in einem "normalen" Datenbankschema also sofort starten. Und da die meisten Oracle-Anwender oder Entwickler Oracle TEXT noch nicht kennen, wollen wir nun genau dies tun:
Das folgende SQL-Skript erzeugt eine Tabelle, fügt ein paar (kleine) "Dokumente" ein, erzeugt den Volltextindex und zeigt, wie man darin (sogar linguistisch) suchen kann ...
Zunächst: Tabelle anlegen und füllen - als "Dokumente" haben wir ein paar "Schlagzeilen" generiert ...
drop table texttabelle
/
drop sequence seq_texttabelle
/

create table texttabelle(
  id          number(10),
  dokument    clob
)
/

create sequence seq_texttabelle
/

insert into texttabelle values (seq_texttabelle.nextval, 'A-Partei gewinnt Wahl in Hansestadt');
insert into texttabelle values (seq_texttabelle.nextval, 'Terror in Nahost: Kriminalität steigt immer weiter an');
insert into texttabelle values (seq_texttabelle.nextval, 'Wirtschaft: Erneuter Gewinnzuwachs in diesem Jahr');
insert into texttabelle values (seq_texttabelle.nextval, 'Olympia rückt näher: Der Fackellauf ist in vollem Gange');
insert into texttabelle values (seq_texttabelle.nextval, 'Wer wird US-Präsident? Obama und Clinton machen Wahlkampf');
insert into texttabelle values (seq_texttabelle.nextval, 'Papst bestürzt über jüngsten Skandal!');
insert into texttabelle values (seq_texttabelle.nextval, 'Wahlkampf in den USA geht weiter:  Clinton und Obama LIVE zu sehen');
insert into texttabelle values (seq_texttabelle.nextval, 'Software-Kenntnisse werden immer wichtiger');
insert into texttabelle values (seq_texttabelle.nextval, 'Umfrage:  Alle wollen mehr Geld!');
insert into texttabelle values (seq_texttabelle.nextval, 'Der Papst liest seine erste Messe in den USA!');

commit
/
Nun wird der Index erzeugt ...
create index idx_text on texttabelle (dokument)
indextype is ctxsys.context
/
Fertig. Nun kann man suchen ... und das geht wie folgt:
  1. Die einfachste Variante: Suche nach einem Wort:
    SQL> select * from texttabelle where contains(dokument, 'Papst')>0;
    
            ID DOKUMENT
    ---------- ---------------------------------------------------------------
             6 Papst bestürzt über jüngsten Skandal!
            10 Der Papst liest seine erste Messe in den USA!
    
    2 Zeilen ausgewählt.
    
  2. Boole'sche Operatoren wie AND, OR, NOT gehen natürlich auch ...
    SQL> select * from texttabelle where contains(dokument, 'Papst and Skandal')>0;
    
            ID DOKUMENT
    ---------- ---------------------------------------------------------------------------
             6 Papst bestürzt über jüngsten Skandal!
    
    1 Zeile wurde ausgewählt.
    
  3. Nun wird's interessant: Wir suchen Dokumente, in denen jemand etwas "liest" ... und das kann sprachlich ja unterschiedlich aussehen ... (lesen, las, liest, gelesen, ...). Dazu gibt es in Oracle TEXT eine Wortstammsuche ...
    SQL> select * from texttabelle where contains(dokument, '$lesen')>0;
    
            ID DOKUMENT
    ---------- ------------------------------------------------------------------------
            10  Der Papst liest seine erste Messe in den USA!
    
    1 Zeile wurde ausgewählt.
    
  4. Oracle TEXT ist übrigens auch noch fehlertolerant ... wenn man den Fuzzy-Operator verwendet ... Suchen wir mal nach dem USA-"Wahlkrampf":
    SQL> select * from texttabelle where contains(dokument, '?Wahlkrampf')>0;
    
            ID DOKUMENT
    ---------- ------------------------------------------------------------------------
             5 Wer wird US-Präsident? Obama und Clinton machen Wahlkampf 
             7 Wahlkampf in den USA geht weiter:  Clinton und Obama ...
    
    2 Zeilen ausgewählt.
    
  5. Ein (vorerst) letztes Beispiel: Zwei Wörter sollen nah beeinander stehen ("nah" meint hier: es darf nur ein Wort dazwischen stehen):
    SQL> select * from texttabelle 
      2   where contains(dokument, 'NEAR((Clinton, Wahlkampf),2)')>0;
    
            ID DOKUMENT
    ---------- --------------------------------------------------------------------------------
             5 Wer wird US-Präsident? Obama und Clinton machen Wahlkampf 
    
    1 Zeile wurde ausgewählt.
    
Am besten probiert man es einfach mal aus ... mehr zum Thema kommt in Kürze ...

Impressum

Dieser Blog dient dem Austausch von Informationen und der Meinungsbildung über Volltextrecherche in der Oracle-Datenbank. Er wird herausgegeben von:

Carsten Czarski
Mühlhölzlweg 7
80999 München

Mail: carsten[dot]czarski[at]gmx[dot]de
Telefon: +49 89 1430 2116

Ulrike Schwinn
Hiltenspergerstr 32
80796 München

Mail: USchwinn[at]gmx[dot]net
Telefon: +49 89 1430 1865

Redaktionell Verantwortliche: Carsten Czarski und Ulrike Schwinn

Montag, 31. März 2008

Start ...

Da es recht bislang recht wenig deutschsprachige Informationen zum Thema Oracle TEXT gibt, haben wir uns entschlossen, über das Medium Blog etwas daran zu ändern. Künftig werden hier (mehr oder weniger regelmäßig) Tipps, Tricks und Neuigkeiten zur Volltextrecherche in der Oracle-Datenbank erscheinen.

Beliebte Postings