Montag, 8. Dezember 2008

Ergebnisse zählen: Nicht mit count(*), sondern mit COUNT_HITS!

Wenn man Oracle TEXT in einer Applikation nutzt und Abfragen mit der CONTAINS-Funktion durchführt, möchte man fast immer auch die Anzahl der Treffer auf der Ergebnisseite anzeigen. Und der erste, naheliegende Gedanke ist, hierfür ein SELECT COUNT(*) zu verwenden - die Abfrage findet ja auch mit einem SELECT statt.
select count(*) from dokument_tab where contains(dokument, 'Bundeskanzler') > 0
Gerade bei Oracle TEXT ist diese Variante jedoch sehr ineffektiv - denn die Datenbank muss hierfür sowohl auf den Volltextindex als auch auf die Tabelle zugreifen. Besser ist es, die dafür vorgesehene Funktion COUNT_HITS im PL/SQL-Paket CTX_QUERY zu nutzen.
set serveroutput on

declare
  v_number number;
begin
  v_number := ctx_query.count_hits(
    index_name => 'MY_FULLTEXT_IDX',
    text_query => 'oracle and text',
    exact =>      true
  );
  dbms_output.put_line('Anzahl Treffer: '||v_number);
end;
/
 
Anzahl Treffer: 2657
Man muss den Namen des Index kennen, um die Funktion nutzen zu können; mit der Dictionary View CTX_USER_INDEXES ist dieser aber leicht zu ermitteln. Interessant ist noch der Parameter exact - gibt man hier false an, so arbeitet die Funktion schneller, dafür ungenauer: Seit der letzten Indexoptimierung gelöschte und veränderte Dokumente werden dann nicht berücksichtigt; der Wert ist also zu hoch. Direkt nach einer Indexoptimierung liefern sowohl exact => true als auch exact => false gleiche Ergebnisse zurück. In allen Fällen ist CTX_QUERY.COUNT_HITS schneller als ein SELECT COUNT(*).
Mehr Information in der Oracle Dokumentation - TEXT Reference.

Montag, 24. November 2008

Suchverhalten der Anwender monitoren: Abfrage-Analyse mit CTX_REPORT

Was sind die häufigsten Begriffe, nach denen die Anwender suchen? Welche Abfragen laufen ständig ins Leere und liefern kein Resultat? Diese Fragen sind häufig wichtig, um "anwenderfreundliche" Applikationen gestalten zu können oder um die Suchanfragen optimal zur Verfügung zu stellen.
Ab Oracle 10g ist es möglich mit CTX_OUTPUT ein spezielles Logging für Suchanfragen anzustossen und mit CTX_REPORT eine Analyse dieser Loginformationen durchzuführen.
Im Folgenden demonstriert ein Beispiel die Anwendung.

Wie in einem unserer vorangegangenen
Blogs schon demonstriert, wird das Logging in den Sessions, in denen die Abfragen laufen, aktiviert. Dazu legen wir zuerst das Log Verzeichnis fest.

execute CTXSYS.CTX_ADM.SET_PARAMETER ('LOG_DIRECTORY','/tmp'); 

Danach starten wir das Query-Logging mit folgendem Aufruf:

exec CTX_OUTPUT.START_QUERY_LOG('querylog');

Die folgenden SELECT Statements werden nun in einer Logdatei aufgezeichnet. Dabei nehmen als Grundlage, die in Blog generierte Tabelle TEXTTABELLE und den dazugehörenden Textindex.

SELECT * FROM texttabelle WHERE contains(dokument, 'USA')>0;
SELECT * FROM texttabelle WHERE contains(dokument, 'USA')>0;
SELECT * FROM texttabelle WHERE contains(dokument, 'USA')>0;
SELECT * FROM texttabelle WHERE contains(dokument, 'USA')>0;
SELECT * FROM texttabelle WHERE contains(dokument, 'USA and Messe')>0; 
SELECT * FROM texttabelle WHERE contains(dokument, 'Software')>0;
SELECT * FROM texttabelle WHERE contains(dokument, 'Hamburg')>0;

Das Logging wird dann mit folgendem Aufruf beendet:

execute CTX_OUTPUT.END_QUERY_LOG;

Wie schon erwähnt, ist diese Art von Logging vergleichbar mit dem Logging mit CTX_OUTPUT.START_LOG (siehe Blog). Es wird eine Datei mit Namen QUERYLOG im Logverzeichnis /tmp erzeugt. Allerdings liegt diese Logdatei in einem speziellen XML Format vor, wie in unserem Beispiel zu sehen ist:


<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>USA</Query><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>USA</Query><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>USA</Query><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>USA</Query><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>USA and Messe</Query><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>Software</Query>><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>Oracle</Query>><ReturnHit>No</ReturnHit><QueryTag></QueryTag></QuerySet>

Wie lässt sich nun diese Datei, besonders wenn sie grösser als in unserem Minibeispiel ist, bequem auslesen?
Das Package CTX_REPORT mit der Prozedur QUERY_LOG_SUMMARY liefert eine mögliche Vorgehensweise. Diese Prozedur, als CTXSYS User ausgeführt, liest die TOP N Abfragen und liefert die Resultate in einer PL/SQL Table vom Typ CTX_REPORT.QUERY_TABLE. Die Zahl N ist dabei definierbar über den Parameter ROW_NUM. Folgender Beispiel-Code liest unsere Log-Datei QUERYLOG aus und liefert das Ergebnis der TOP 10 Abfragen:

SQL> connect ctxsys
Enter password:
Connected.
SQL> set serveroutput on

SQL> declare
  2  logentry ctx_report.query_table;
  3  begin
  4  ctx_report.query_log_summary('querylog','idx_text', logentry, row_num=>10);
  5  for i in 1..logentry.count 
  6  loop
  7  dbms_output.put_line(logentry(i).query || ' kommt '||logentry(i).times||' Mal vor');
  8  end loop;
  9  end;
  10  /
USA kommt 4 Mal vor
USA and Messe kommt 1 Mal vor
Software kommt 1 Mal vor

PL/SQL procedure successfully completed.

Standardmässig werden dabei nur die häufigsten Treffer der erfolgreichen Abfragen ausgewertet, da der Parameter MOST_FREQ auf TRUE steht. Manchmal ist es allerdings interessanter, die fehlgeschlagenen Abfragen zu dokumentieren. Der Parameter HAS_HIT (Defaultwert: TRUE) kontrolliert das Verhalten der Auswertung bzgl. der erfolgreichen Treffer. Setzen wir diesen Wert auf FALSE, werden nur die Resultate angezeigt, die von fehlgeschlagenen Suchabfragen stammen.
Folgendes Beispiel demonstriert das Verhalten:

SQL> set serveroutput on
SQL> declare
  2  logentry ctx_report.query_table;
  3  begin
  4  ctx_report.query_log_summary('querylog','idx_text', logentry, row_num=>10, has_hit=>false);
  5  for i in 1..logentry.count 
  6  loop
  7  dbms_output.put_line(logentry(i).query || ' kommt '||logentry(i).times||' Mal vor');
  8  end loop;
  9  end;
 10  /
Hamburg kommt 1 Mal vor

PL/SQL procedure successfully completed.

Um solches Fehlschlagen von Suchabfragen zu verhindern, könnte man z.B. in den Applikationen bessere Hilfestellung für den User anbieten oder über Synonyme (Thesaurus) alternative Schreibweisen ermöglichen.
Mehr dazu in einem unserer nächsten Blog-Veröffentlichungen. Viel Spass beim Ausporbieren ...

Dienstag, 11. November 2008

Mehrere Tabellenspalten indizieren: MULTICOLUMN_DATASTORE

Mitunter kommt es vor, dass die Dokumente in einer Tabelle in mehreren Spalten vorliegen. Eine Tabelle könnte wie folgt angelegt sein:
create table produktions_hinweise(
  fall_id               number(10),
  kommentar_werk        varchar2(4000),
  kommentar_controlling varchar2(4000),
  hinweise_produktion   clob
)
/
Wenn die Spalten KOMMENTAR_WERK, KOMMENTAR_CONTROLLING und HINWEISE_PRODUKTION nun volltextindiziert werden sollen, wird häufig (in Analogie zum normalen B-Baum-Index) der Weg gegangen, diese Spalten einzeln zu indizieren und später per AND bzw. OR kombinierte CONTAINS-Abfragen zu verwenden ...
/*
 * Abfragen auf mehrere Tabellenspalten NIEMALS so durchführen!
 */
select fall_id from produktions_hinweise
where
  contains(hinweise_produktion, 'Spezialmaschine') >0 and
  contains(kommentar_controlling, 'Abschreibungen') >0 and
  contains(kommentar_werk, 'Auslastung') >0
/
Dies führt allerdings immer zu sehr schlechter Performance. Denn es existieren nun drei Textindizes, die unabhängig voneinander ausgewertet werden. Die drei Zwischenergebnisse werden dann mit AND kombiniert. Insbesondere bei großen Datenmengen und wenn die Einzelabfragen nicht selektiv sind, führt dies zu sehr langen Antwortzeiten. Man kann als "Faustregel" festhalten, dass eine Volltextabfrage möglichst wenig CONTAINS()-Aufrufe (am besten nur einen) enthalten sollte. Das bedeutet aber auch, dass ein einziger Volltextindex auf alle drei Spalten erzeugt werden muss. Und das geht auch: Mit dem MULTI_COLUMN_DATASTORE.
Um den Multicolumn Datastore nutzen zu können, muss zunächst eine sog. Preference erzeugt werden; hier werden die Spalten, welche gemeinsam indiziert werden sollen, konfiguriert. Die folgenden SQL-Anweisungen erzeugen eine Preference vom Typ MULTICOLUMN_DATASTORE und legen die zu indizierenden Spalten der Tabelle als Inhalt fest.
begin
  ctx_ddl.create_preference(
    preference_name => 'kommentare_store'
   ,object_name     => 'MULTI_COLUMN_DATASTORE'
  );
  ctx_ddl.set_attribute(
    preference_name => 'kommentare_store'
   ,attribute_name  => 'columns'
   ,attribute_value => 'KOMMENTAR_WERK, KOMMENTAR_CONTROLLING, HINWEISE_PRODUKTION'
  );
end;
/
Um das Paket CTX_DDL nutzen zu können, benötigt man die Rolle CTXAPP (oder ein explizites EXECUTE-Privileg). Der Textindex wird im folgenden CREATE INDEX Kommando auf eine Tabellenspalte erzeugt (die tatsächlich angesprochenen Spalten sind in der Preference konfiguriert). Man kann nun eine der drei Dokumentspalten nehmen; aus Gründen der Übersichtlichkeit empfiehlt es sich jedoch, eine eigene "Dummy"-Spalte zu erzeugen.
alter table produktions_hinweise add (indexspalte char(1))
/
Nachtrag (danke für den Kommentar): Diese "Dummy-Spalte" muss bei einem Update der Tabellenzeile immer mit aktualisiert werden, damit der Index die Änderungen bemerkt. Das läßt sich z.B. durch einen Before-Update-Trigger realisieren.
create or replace trigger trg_produktions_hinweise
before update on produktions_hinweise
for each row
begin
  :new.indexspalte := :new.indexspalte;
end;
/
Nun kann der Index erzeugt werden.
create index idx_kommentare  on produktions_hinweise (indexspalte)
indextype is ctxsys.context
parameters ('
  datastore kommentare_store
  section group CTXSYS.AUTO_SECTION_GROUP'
)
/
Wie arbeitet der Multicolumn-Datastore nun? Obwohl es der Index formal für die Tabellenspalte INDEXSPALTE erzeugt wird, werden die tatsächlich indizierten Inhalte aus den drei in der Preference KOMMENTARE_STORE konfigurierten Spalten genommen. Die drei Dokumente pro Tabellenzeile werden zu einem einzigen zusammengefasst. Damit man jedoch noch in der Lage ist, auch gezielt in einer Spalte abzufragen, werden sie innerhalb des zusammengesetzten Dokuments durch XML Tags getrennt. Für das Beispiel wird (intern) also ein Dokument wie folgt generiert:
<KOMMENTAR_WERK>
  :
  Inhalt der Spalte KOMMENTAR_WERK hier  
  :
</KOMMENTAR_WERK>
<KOMMENTAR_CONTROLLING>
  :
  Inhalt der Spalte KOMMENTAR_CONTROLLING hier  
  :
</KOMMENTAR_CONTROLLING>
<HINWEISE_PRODUKTION>
  :
  Inhalt der Spalte HINWEISE_PRODUKTION hier  
  :
</HINWEISE_PRODUKTION>
Wie gesagt: Dieses Dokument wird nicht materialisiert - es wird nur während der Indexerstellung transient generiert. Nachdem es indiziert und alle Informationen in den Textindex aufgenommen wurden, wird es wieder zerstört. Für den Textindex gibt es pro Tabellenzeile jedoch nur noch ein Dokument, welches mehrere Abschnitte (Sections) hat. Damit diese Abschnitte separat abgefragt werden können, wurde dem CREATE INDEX Kommando der Parameter SECTION_GROUP CTXSYS.AUTO_SECTION_GROUP hinzugefügt (siehe oben). Eine kombinierte Abfrage (Beispiel oben) kann nun so aussehen:
select fall_id from produktions_hinweise
where
  contains(indexspalte, '(Spezialmaschine) and (Abschreibungen) and (Auslastung)') >0
/
Aus den drei CONTAINS-Aufrufen ist nun einer geworden; die AND bzw. OR-Verknüpfungen befinden sich nun innerhalb des CONTAINS(). Allerdings entspricht diese Abfrage der obigen nicht ganz genau. Denn das Token Spezialmaschine wurde oben gezielt in der Spalte HINWEISE_PRODUKTION gesucht; hier muss es nur in einer der Spalten vorkommen. Es wird also ein Zugriff auf die einzelnen Abschnitte des Dokuments benötigt ...
select fall_id from produktions_hinweise
where contains(
  indexspalte, 
  '(Spezialmaschine WITHIN (HINWEISE_PRODUKTION)) and 
   (Abschreibungen WITHIN (KOMMENTAR_CONTROLLING)) and 
   (Auslastung WITHIN (KOMMENTAR_WERK))'
) > 0
/
Mit der WITHIN-Klausel kann ein Token gezielt in einem der Abschnitt gesucht werden. Man kann die Begriffe nun also sehr flexibel innerhalb aller indizierten Spalten oder in bestimmten Spalten suchen. Alle anderen Operatoren der SQL-Funktion CONTAINS funktionieren wie gehabt.
Das entscheidende ist jedoch, dass diese Abfragen stets komplett in ein- und demselben Volltextindex ausgeführt wird. Es ergibt sich eine um Faktoren bessere Performance als mit einzelnen Textindizes ...
Übrigens: Man kann mit dem Multicolumn Datastore auch gewöhnliche relationale Tabellenspalten (bspw. Adressfelder) indizieren. Dann lassen sich die linguistischen Features (Fuzzy-Suche) auch für Abfragen in strukturierten Daten nutzen. Wie das geht und was man damit erreichen kann, ist in einem Tipp der deutschsprachigen Application Express-Community beschrieben.

Freitag, 24. Oktober 2008

Monitoren von Oracle Text Indizes: Grundsätzliche Funktionen

Wie kann man den Textindex monitoren? Welche Möglichkeiten gibt es? Dazu stehen spezielle Data Dictionary Views, Packages oder ab Oracle Database 11g die Enterprise Manager Funktionalität zur Verfügung. Im folgenden Beispiel nutzen wir eine Tabelle die ca. 600 000 Zeilen besitzt und die mit einer TEXT-Spalte mit Kurztexten ausgestattet ist.
Zuerst erzeugen wir den Textindex TEXT_COMP und monitoren gleichzeitig den Indexvorgang. Mit dem Aufruf CTX_ADM.SET_PARAMETER läßt sich dabei das Logverzeichnis festlegen - in unserem Fall /tmp. Mit CTX_OUTPUT.START_LOG starten wir den Logvorgang. Folgender Aussschnitt zeigt die Verwendung:

SQL> execute CTXSYS.CTX_ADM.SET_PARAMETER ('LOG_DIRECTORY','/tmp'); 
PL/SQL procedure successfully completed.

SQL> execute CTX_OUTPUT.START_LOG('textlog');
PL/SQL procedure successfully completed.

SQL> CREATE INDEX text_comp ON comp_test(text)
  2  INDEXTYPE IS ctxsys.context PARAMETERS('memory 100M');
Index created.

SQL> execute CTX_OUTPUT.END_LOG;
PL/SQL procedure successfully completed.

Bevor wir uns die Logtabelle ansehen, sollten wir zuerst überprüfen, ob der Index ohne Fehler erzeugt worden ist. Folgendes Kommando gibt Aufschluss darüber. Diese Überprüfung sollte nach jeder Indexerzeugung durchgeführt werden.

SQL> SELECT err_index_name, err_timestamp, err_textkey, err_text
   2 FROM ctx_user_index_errors ORDER BY err_index_name, err_timestamp;
no rows selected

In unserem Fall liegt kein Fehler vor. Falls diese Tabelle Zeilen enthält, gibt die Spalte ERR_TEXTKEY Aufschluss über die ROWID des entsprechenden Dokuments, das den Fehler hervorgerufen hat. Damit hat man einen Startpunkt, um den Fehler zu beheben.

Die Datei "textlog" im Verzeichnis /tmp gibt nun Auskunft über den Vorgang der Indizierung. Sehr wichtig dabei ist, dass die Angabe von "PARAMETERS ('MEMORY 100M')" zu einer Beschleunigung des Indizierungsvorgangs führt. Der Wert 100M bestimmt die Größe der Token und Mapping Tabelle im Memory, bevor die Informationen in die entsprechenden Tabellen geschrieben werden. Der Vorgang wird so lange wiederholt bis der Index erzeugt worden. Daher ist es sehr sinnvoll den Parameter Memory zu setzen und den Wert so gross wie möglich zu wählen. Folgender Ausschnitt zeigt ein Beispiel einer Logtabelle und gibt Aufschluss über die Nutzung des Memory Parameters.

Oracle Text, 11.1.0.6.0
14:00:28 10/24/08 begin logging
14:00:49 10/24/08 populate index: COMP.TEXT_COMP
14:00:49 10/24/08 Begin document indexing
14:00:49 10/24/08 100 documents indexed
14:00:49 10/24/08 200 documents indexed
14:00:49 10/24/08 300 documents indexed
14:00:49 10/24/08 400 documents indexed
14:00:49 10/24/08 500 documents indexed
...
14:01:58 10/24/08 118200 documents indexed
14:01:58 10/24/08 Errors reading documents: 0
14:01:58 10/24/08 Index data for 118236 documents to be written to database
14:01:58 10/24/08    memory use: 98846623
14:01:58 10/24/08 Begin sorting the inverted list.
14:01:58 10/24/08 End sorting the inverted list.
14:01:58 10/24/08 Writing index data ($I) to database.
14:02:06 10/24/08 Wrote 313223 rows (12067 big rows) to $I.
14:02:06 10/24/08 Writing index data ($R) to database.
14:02:06 10/24/08    index data written to database.
... 

Zusätzlich bietet die Verwendung des Package CTX_REPORT weitere Möglichkeiten, Informationen zum Textindex zu erhalten. Im folgenden sollen 2 Beipiele dies illustrieren.
Möchte man z.B. die Größe des Textindex herausfinden, kann der Aufruf von CTX_REPORT.INDEX_SIZE einen detaillierten Bericht dazu liefern. Abgesehen von der Gesamtgröße des Index erhält man eine detaillierte Auflistung über die Größe der zugehörigen $I, $K, $R Tabellen usw. und der Zuordnung zu den Tablespaces.
Folgender Abschnitt zeigt einen Auszug aus dem Report:

SQL> col ausgabe format a150
SQL> set long 2000000000 head off pagesize 10000
SQL> select ctx_report.index_size('TEXT_COMP') as ausgabe from dual;

===========================================================================
                       INDEX SIZE FOR COMP.TEXT_COMP
===========================================================================
TABLE:                               COMP.DR$TEXT_COMP$I
TABLESPACE NAME:                     USERS
BLOCKS ALLOCATED:                                                    13056
BLOCKS USED:                                                         12752
BYTES ALLOCATED:                                   106,954,752 (102.00 MB)
BYTES USED:                                         104,464,384 (99.63 MB)
LOB SEGMENT:                         COMP.SYS_LOB0000094903C00006$$
TABLE NAME:                          COMP.DR$TEXT_COMP$I
LOB COLUMN:                          TOKEN_INFO
TABLESPACE NAME:                     USERS
BLOCKS ALLOCATED:                                                        8

...

TOTALS FOR INDEX COMP.TEXT_COMP
---------------------------------------------------------------------------
TOTAL BLOCKS ALLOCATED:                                              16040
TOTAL BLOCKS USED:                                                   15487
TOTAL BYTES ALLOCATED:                             131,399,680 (125.31 MB)
TOTAL BYTES USED:                                  126,869,504 (120.99 MB)

Benötigt man ein zugehöriges CREATE INDEX Skript, ist es nicht notwendig, im Data Dictionary nach den Eigenschaften des Index zu suchen. Das einfache Ausführen der Funktion CREATE_INDEX_SCRIPT, liefert eine Liste von DDL Kommandos, die einen vergleichbaren Index erzeugen:

select ctx_report.create_index_script('TEXT_COMP') from dual
begin
  ctx_ddl.create_preference('"TEXT_COMP_DST"','DIRECT_DATASTORE');
end;
/

...
begin
  ctx_output.start_log('TEXT_COMP_LOG');
end;
/

create index "COMP"."TEXT_COMP"
  on "COMP"."COMP_TEST"
      ("TEXT")
  indextype is ctxsys.context
  parameters('
    datastore       "TEXT_COMP_DST"
    filter          "TEXT_COMP_FIL"
    section group   "TEXT_COMP_SGP"
    lexer           "TEXT_COMP_LEX"
    wordlist        "TEXT_COMP_WDL"
    stoplist        "TEXT_COMP_SPL"
    storage         "TEXT_COMP_STO"
  ')
/

begin
  ctx_output.end_log;
end;
/

Wer allerdings schon Oracle Database 11g einsetzt und die Möglichkeit besitzt auf Enterprise Manager Database Control zuzugreifen, kann sich das Skripting sparen. Alle hier aufgelisteten Funktionalitäten sind durch einfaches Navigieren im Text Manager unter "Schema => Text Indexes" zu finden.

Dienstag, 7. Oktober 2008

Oracle TEXT unterstützt Office2007 - mit Patchset 11.1.0.7

Die beste Nachricht zuerst: Oracle Text unterstützt nun Office2007.
Für TEXT-Anwender ist das kürzlich erschienene Patchset 11.1.0.7 sehr wichtig, denn der bislang enthaltene Verity-Filter wurde mit diesem Patchset durch Oracle Outside in HTML Export ersetzt. Dadurch ergeben sich Konsequenzen für die unterstützten Dokumentformate.
Einige Formate, die Verity bislang unterstütze werden nicht mehr unterstützt, bspw. "Applix Words (AW), versions 3.11, 4.0, 4.1, 4.2, 4.3, 4.4". Dafür ist (endlich) eine Unterstützung für Office 2007 vorhanden. Genaueres findet Ihr ...
  • ... in der README-Datei des Patchsets (da stehen die Dokumentformate drin, die nicht mehr unterstützt werden) ...
  • ... und in der Text Reference, Appendix B - da stehen die Formate drin, die nun unterstützt werden.
Wer die Filter von Oracle TEXT nutzt (und wer nutzt die nicht?) sollte hier unbedingt mal reinsehen, bevor er das Patchset einspielt.

Dienstag, 16. September 2008

Index-Synchonisierung und TRANSACTIONAL-Parameter

Eine sehr wichtige Eigenschaft jedes Textindex ist das TRANSACTIONAL Keyword, welches seit Oracle10g verwendet werden kann.
Normalerweise ist es ja so, dass Änderungen an der Dokumenttabelle im Index nicht sofort sichtbar werden, sondern erst nach dem Index Sync. Das kann man sehr schön in der View CTX_USER_PENDING nachvollziehen.
SQL> select PND_INDEX_NAME, PND_ROWID, PND_TIMESTAMP  from ctx_user_pending;

PND_INDEX_NAME                 PND_ROWID          PND_TIMESTAMP
------------------------------ ------------------ -------------------
IDX_DOKUMENT_VOLLTEXT          AAAiPsAAEAAAjUlAAD 16.09.2008 10:18:35
IDX_VOLLTEXT_2                 AAAiPsAAEAAAjUkAAA 29.07.2008 11:12:59
IDX_VOLLTEXT_2                 AAAiPsAAEAAAjUlAAD 16.09.2008 10:18:36
IDX_VOLLTEXT_2                 AAAiPsAAEAAAjUmAAD 29.07.2008 10:50:37
IDX_VOLLTEXT_2                 AAAiPsAAEAAAjUmAAE 29.07.2008 10:59:23
In einer Volltextrecherche sind diese Dokumente normalerweise nicht sichtbar; sie sind noch nicht in den Index synchronisiert. Doch was tut man, wenn die Anforderung besteht, dass alle Dokumente sofort durchsuchbar sein müssen ...?
Man könnte den Index nach jedem COMMIT synchronisieren; damit wäre die Anforderung erstmal erfüllt ... und es gibt sogar einen Parameter dafür: Beim CREATE INDEX kann als Parameter SYNC ON COMMIT mitgegeben werden. Das hat aber einen gewichtigen Nachteil:
Beim Synchronisieren (CTX_DDL.SYNC_INDEX) wird ebenfalls Wert auf möglichst kurze Laufzeit gelegt. Die neuen Informationen (die Tokens des neuen Dokumentes) werden also nicht an die Stellen in den Index eingepflegt, wo es für die Abfrageperformance optimal wäre, sondern dort, wo es am schnellsten geht: quasi "ans Ende" des Index. Die hinsichtlich Abfrageperformance "optimale" Struktur wird erst durch das Optimieren (CTX_DDL.OPTIMIZE_INDEX) erzeugt.
Eine Synchronisierung nach jedem Commit bedeutet also, dass der Index mit jedem Commit weiter "fragmentiert" - die Abfrageperformance also recht schnell immer schlechter wird. Als Faustregel kann man festhalten, dass eine Synchronisation mit so vielen Dokumenten wie möglich stattfinden sollte - auf jeden Fall aber mit mehr als einem.
Das sieht nach einem Dilemma aus: Wenn nun (siehe oben) die Anforderung besteht, dass ein neues Dokument sofort durchsuchbar sein soll, müssten wir ja nach jedem COMMIT synchronisieren - das wollen wir aber nicht, weil der Index dann zu schnell fragmentiert. Und genau hier greift der Parameter TRANSACTIONAL des Volltextindex. Ein transaktionaler Index wird wie folgt erzeugt:
create index idx_volltext on dokumente_tabelle (spalte)
indextype is CTXSYS.CONTEXT
parameters ('TRANSACTIONAL')
Bei Volltextabfragen mit der CONTAINS-Funktion auf diesen Index werden nun auch die noch gar nicht im Index befindlichen Dokumente gefunden. Wird der Parameter gesetzt, so durchsucht Oracle die noch nicht synchronisierten Dokumente (CTX_USER_PENDING) zur Abfragezeit on-the-fly.
Der Vorteil ist nun, dass man nicht mehr "den Druck hat", den Index sofort nach Einfügen eines Dokumentes zu synchronisieren - denn es ist ja auffindbar. Man kann sich nun ein geeignetes Synchronisierungsintervall überlegen, welches die Indexfragmentierung einerseits in Grenzen hält und andererseits häufig genug synchronisiert, so dass nicht zuviele Dokumente on-the-fly durchsucht werden müssen. Denn eins ist auch klar: Die Suche im Index ist auf jeden Fall günstiger als die on-the-fly Suche durch die noch nicht synchronisierten Dokumente.
Generell kann man sagen, dass der Parameter TRANSACTIONAL ab Oracle10g eine gute Sache ist - man kann ihn eigentlich generell setzen. Das Finden eines guten Intervalls zum Synchronisieren und zum Optimieren (also das Finden einer guten Strategie für die Indexwartung) bleibt jedoch weiterhin eine wichtige Aufgabe in einem Oracle TEXT Projekt.

Dienstag, 12. August 2008

Metadaten-Suche mit MDATA Section

Eine Mixed Query kombiniert eine relationale Abfrage mit einer Volltextrecherche und kann zusätzlich noch sortierte Ausgaben erfordern. Ein typisches Beispiel ist eine kombinierte Text- und eine Datumsabfrage im WHERE-Filter, oder einfach nur eine Textabfrage in Kombination mit einer weiteren relationalen Abfrage. Dies kann u.U. zu Performance-Einbussen führen, besonders wenn weder der Textanteil noch der strukturelle Anteil sehr selektiv sind.
Ab Oracle Database 10g gibt es ein neues „Section“ Feature - die neue MDATA Section, um Dokument-Metadaten separat zu handhaben. Die MDATA Section ist vergleichbar mit einer Zone- oder Field- Section, d.h. das Dokument muss eine interne Struktur („Section“) wie HTML oder XML besitzen. Bei MDATA wird der Metadaten-Anteil separat als strukturierter Anteil gespeichert und ist für den Textindex unsichtbar. Allerdings gibt es Unterschiede zur Field Section Suche wie z.B. MDATA kann transaktionell verändert werden, ohne den Rest des Index zu beeinträchtigen.
Folgendes Beispiel veranschaulicht die Nutzung. Nehmen wir als Ausgangstabelle die PRODUCTS Tabelle aus dem Schema SH und erzeugen folgende Tabellenstruktur mit XMLTYPE Spalte:

CREATE TABLE mdata_doc (prod_id NUMBER, xml_lob XMLTYPE);

INSERT INTO mdata_doc select prod_id,
xmlelement("Infos",
xmlelement( "Monat",extract(MONTH from prod_eff_from + seq.nextval)),
xmlelement("Prod_name", PROD_NAME),
xmlelement("Prod_kategorie", PROD_CATEGORY),
xmlelement("Status", PROD_STATUS),
xmlelement("Prod_list_preis", PROD_LIST_PRICE))
FROM products;

Das Ergebnis sieht dann folgendermassen aus:
SQL> SELECT * FROM mdata_doc WHERE rownum=1;
PROD_ID
----------
XML_LOB
-------------------------------------------------------------------------------- 
45
<infos>
<monat>1<monat>
<prod_name>O/S Documentation Set - Kanji</prod_name>
<prod_kategorie>Software/Other</prod_kategorie> <status>STATUS<status> <prod_list_preis>44.99<prod_list_preis> <infos>

Danach werden die Section, die Section Gruppe und die Fields erzeugt.

EXECUTE ctx_ddl.drop_section_group('my_seg');
EXECUTE ctx_ddl.create_section_group(group_name=>'my_seg',group_type=>'xml_section_group');

BEGIN
ctx_ddl.add_field_section(group_name=>'my_seg',section_name=>'Infos', tag=>'Infos');
ctx_ddl.add_field_section(group_name=>'my_seg', section_name=>'Prod_name', tag=>'Prod_name', visible=>TRUE);
ctx_ddl.add_field_section(group_name=>'my_seg', section_name=>'Prod_kategorie', tag=>'Prod_kategorie', visible=>TRUE);
ctx_ddl.add_field_section(group_name=>'my_seg', section_name=>'Prod_list_preis', tag=>'Prod_list_preis', visible=>TRUE);
END;
/

Nun fügen wir die MDATA ´Metadaten Sections "Status" und "Monat" mit ADD_MDATA_SECTION hinzu.

EXECUTE ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'Status', tag=>'Status');
EXECUTE ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'Monat', tag=>'Monat');

Nun legen wir den Text-Index an: dabei sind 4 Spalten "normal" Text indiziert und die Felder Status und Monat sind MDATA Section Group indiziert.

CREATE INDEX txt_index ON mdata_doc (xml_lob)
INDEXTYPE IS ctxsys.context
PARAMETERS ('SECTION GROUP my_seg');

SELECT err_text FROM ctx_user_index_errors WHERE err_index_name = 'TXT_INDEX';

Nach dem Indizieren suchen wir nach einem Produkt in der Kategorie "photo" im Monat "2". Dies kann nun vollständig über den Textindex gelöst werden. Die MDATA Suche ist dabei allerdings nur auf Gleichheit möglich.

SELECT count(*) FROM mdata_doc
WHERE contains (xml_lob, 'photo within Prod_kategorie and (mdata(monat,2) 
and mdata(status,STATUS)) ') > 0;

Nun können wir folgenden PL/SQL Block nutzen, um die Metadaten zu ändern. In unserem Fall wird der Wert "STATUS" der Section "Status" in "verfuegbar" geändert. Dabei verwenden wir die Prozeduren CTX_DDL.REMOVE_MDATA und CTX_DDL.ADD_MDATA.

BEGIN
FOR c1 IF (SELECT rowid FROM mdata_doc 
WHERE contains (xml_lob, 'mdata(Status,STATUS)') > 0)
LOOP
ctx_ddl.remove_mdata(idx_name=>'TXT_INDEX', section_name=>'Status', mdata_value=>'STATUS', mdata_rowid=>c1.rowid);
ctx_ddl.add_mdata(idx_name=>'TXT_INDEX', section_name=>'Status', mdata_value=>'verfuegbar', mdata_rowid=>c1.rowid);
END LOOP;
END;
/
COMMIT;

Da wir die Metatdaten geändert haben, muss die Abfrage nun folgendermassen lauten, um das gleiche Ergebnis wie vorhin zu liefern:

SELECT count(*)
FROM mdata_doc
WHERE contains (xml_lob, 'photo within Prod_kategorie and mdata(monat,2) 
and mdata(Status,verfuegbar)') > 0

Weitere Neuerungen und Features zum Thema Mixed Queries und Oracle Text gibt es in der aktuelle Oracle Datenbank Version 11g. Mehr dazu in einer der nächsten Postings...

Donnerstag, 10. Juli 2008

Keyword in Context: CTX_DOC.SNIPPET

Die Ergebnisse einer Volltextabfrage mit CONTAINS lassen sich ab Oracle 10.2 ganz einfach als "Keyword in Context" wie in gängigen Suchmaschinen darstellen. Dazu gibt es die Funktion CTX_DOC.SNIPPET.
Als Ausgangpunkt nehmen wir wieder das Beispiel aus dem ersten Posting. Allerdings sind diese Texte noch etwas kurz, so dass es keinen Sinn machen würde, etwas auszuschneiden. Wir fügen also noch zwei etwas längere Texte hinzu ...

insert into texttabelle values (seq_texttabelle.nextval, '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 ... ');
insert into texttabelle values (seq_texttabelle.nextval, '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.');

COMMIT nicht vergessen und dann noch den Index synchronisieren ...
SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> exec ctx_ddl.sync_index('IDX_TEXT');

PL/SQL-Prozedur erfolgreich abgeschlossen.
Und nun fragen wir mal ab ...
select 
  id, 
  ctx_doc.snippet(
    'IDX_TEXT',
    rowid,
    'Entwickler'
  ) snippet,
  score(0) score
from texttabelle
where contains (dokument, 'Entwickler',0) > 0
/

        ID SNIPPET                                                           SCORE
---------- ------------------------------------------------------------ ----------
        11 Und da die meisten Oracle-Anwender oder <b>Entwickler</b> Or          6
           acle TEXT noch nicht kennen, wollen wir
Man sieht also bei größeren Dokumenten sofort den Textbereich, in dem das Schlüsselwort sich befindet - der Endanwender kann wesentlich besser beurteilen, ob der Treffer passt ... eben wie bei gängigen Suchmaschinen.
Wenn man weiter keine Angabe macht, wird das Suchwort (Keyword) mit dem HTML-Tag <b> und </b> umschlossen; mit den Parametern STARTTAG und ENDTAG kann dies aber konfiguriert werden; man kann das Ergebnis der Funktion also direkt für die Web-Ausgabe vorbereiten. Natürlich sind auch einfache Begrenzungen wie | oder * möglich ...
Wenn ein Suchwort mehrfach im Dokument vorkommt, gibt es auch mehrere Snippets. Die Snippets werden dann standardmäßig durch drei Punkte (...) getrennt; auch dies kann mit dem Parameter SEPARATOR konfiguriert werden.

Dienstag, 24. Juni 2008

Scoring mit Oracle Text

Wie schon im ersten Blog angedeutet gibt es die unterschiedlichsten Arten, Oracle Text zu nutzen. Es können z.B. linguistische Suchen, Mustersuchen, Suchen mit Booleschen Operatoren, Abschnittssuchen bei XML und HTML-Formaten durchgeführt werden. Um dabei die Suchqualität zu erhöhen, wird zusätzlich die Technik des Relevanz Rankings genutzt. Im Folgenden wollen wir das Oracle Text Scoring (Ranking) Verhalten untersuchen und die neueste Entwicklung in 11g illustrieren. Folgende Beispiel-Tabelle wird als Grundlage genutzt:

SQL> SELECT * FROM texttabelle;

 ID DOKUMENT
--- ---------------------------------------------------------------------------
  1 Politik: A-Partei gewinnt Wahl in Hansestadt
  2 Politik: Terror in Nahost: Kriminalität steigt immer weiter an
  3 Wirtschaft: Erneuter Gewinnzuwachs in diesem Jahr
  4 Sport: Olympia rückt näher: Der Fackellauf ist in vollem Gange
  5 Politik: Wer wird US-Präsident? Obama und Clinton machen Wahlkampf
  6 Religion: Papst bestürzt über jüngsten Skandal!
  7 Politik: Wahlkampf in den USA geht weiter:  Clinton und Obama LIVE zu sehen
  8 Karriere: Software-Kenntnisse werden immer wichtiger
  9 Politik: Umfrage:  Alle wollen mehr Geld!
 10 Religion: Der Papst liest seine erste Messe in den USA!
 11 Wirtschaft: Muppet-Show der Steuerversprecher
 12 Politik: Bahn-Aufsichtsrat macht Weg frei für Börsengang
 13 Wissenschaft: Europäer im All
 14 Sport: EM-Gegner Türkei: Torwart Volkan bleibt gesperrt
 15 Sport: EM-Gastgeber Österreich: Trainer Hickersberger hört auf
 16 Sport: Deutschland-Gegner Türkei: Chaos als Prinzip
 17 Sport: Türkei-Spieler Altintop: "Ich habe Deutschland alles zu verdanken"
 18 Sport: Euro-Helden: Die größten Stars der EM-Geschichte
 19 Sport: WIMBLEDON Federer mühelos, Kohlschreiber enttäuscht
 20 Sport: Sport bleibt Sport!

20 rows selected.

Um ein Ranking (Scoring) für Dokumente in einer Abfrage zu erhalten, nutzt Oracle Text standardmäßig den inversen Häufigkeits-Algorithmus basierend auf Salton. Scoring nach der inversen Dokumenthäufigkeit bedeutet, dass häufiges Vorkommen eines Begriffes in Dokumenten zu einem niedrigen Scoringwert führt. Damit der Scoringfaktor hoch ist, muss ein Begriff zwar häufig im Dokument selbst, allerdings zusätzlich selten in allen Dokumenten insgesamt vorkommen. Im Handbuch Oracle® Text Reference 11g Release 1 (11.1) im Appendix F wird der Algorithmus am Beispiel erklärt. Um Relevanz-bezogene Abfragen auf Ergebnislisten durchzuführen, wird der Operator SCORE verwendet. Dabei kann auch eine Gewichtung durchgeführt werden, um das Ergebnis zu beeinflussen. Folgende Beispiele illustrieren die Gewichtung und den Einsatz des SCORE Operators: Es wird nach Dokumenten gesucht die den Suchbegriff 'Sport' oder 'Türkei' enthalten - sortiert nach ihrer Relevanz. Das Ergebnis sieht dann folgendermassen aus:

SQL> SELECT dokument, score(1) AS s FROM texttabelle
     WHERE contains(dokument, 'Sport or Türkei',1)>0
     ORDER BY score(1) DESC;

DOKUMENT                                                                      S
--------------------------------------------------------------------------- ---
Sport: Sport bleibt Sport!                                                   19
Sport: Olympia rückt näher: Der Fackellauf ist in vollem Gange                6
Sport: EM-Gegner Türkei: Torwart Volkan bleibt gesperrt                       6
Sport: EM-Gastgeber Österreich: Trainer Hickersberger hört auf                6
Sport: Deutschland-Gegner Türkei: Chaos als Prinzip                           6
Sport: Türkei-Spieler Altintop: "Ich habe Deutschland alles zu verdanken"     6
Sport: Euro-Helden: Die größten Stars der EM-Geschichte                       6
Sport: WIMBLEDON Federer mühelos, Kohlschreiber enttäuscht                    6

8 rows selected.

Gewichtet man den Suchbegriff 'Türkei', ändert sich der entsprechende Scoringwert und die Reihenfolge.

SQL> SELECT dokument, score(1) as s FROM texttabelle
     WHERE contains(dokument, 'Sport or Türkei*5',1)>0
     ORDER BY score(1) DESC
DOKUMENT                                                                      S
--------------------------------------------------------------------------- ---
Sport: EM-Gegner Türkei: Torwart Volkan bleibt gesperrt                      32
Sport: Deutschland-Gegner Türkei: Chaos als Prinzip                          32
Sport: Türkei-Spieler Altintop: "Ich habe Deutschland alles zu verdanken"    32
Sport: Sport bleibt Sport!                                                   19
Sport: Olympia rückt näher: Der Fackellauf ist in vollem Gange                6
Sport: EM-Gastgeber Österreich: Trainer Hickersberger hört auf                6
Sport: Euro-Helden: Die größten Stars der EM-Geschichte                       6
Sport: WIMBLEDON Federer mühelos, Kohlschreiber enttäuscht                    6

Wem diese Möglichkeiten des Rankings nicht ausreichen, kann in Oracle Database 11g die Möglichkeit nutzen, das Scoring weiter zu beeinflussen bzw. im Rahmen bestimmter Funktionen selbst zu definieren. Dazu stehen die neuen Operatoren DEFINESCORE und DEFINEMERGE zur Verfügung. Dem Suchbegriff können folgende Scoring-Ausdrücke und Funktionen zugeordnet werden:
  • DISCRETE: Term vorhanden 100, falls nicht 0
  • OCCURRENCE: Anzahl der Vorkommen
  • RELEVANCE: Standard-Relevanz-Ranking
  • COMPLETION: Ratio für Section Suche: Anzahl der der Treffer zu Anzahl aller Ausdrücke
  • IGNORE: Scoring des Terms ignorieren
  • LOG,ABS und Rechenoperationen
  • () für Gruppierung
  • Folgende Beispiele demonstrieren die Verwendung:
    
    SQL> SELECT dokument, score(1) as s
         FROM texttabelle
         WHERE contains(dokument,'DEFINESCORE(Sport, occurrence)', 1) >0
         ORDER BY score(1);
    
    DOKUMENT                                                                       S
    --------------------------------------------------------------------------- ----
    Sport: Olympia rückt näher: Der Fackellauf ist in vollem Gange                 1
    Sport: EM-Gegner Türkei: Torwart Volkan bleibt gesperrt                        1
    Sport: EM-Gastgeber Österreich: Trainer Hickersberger hört auf                 1
    Sport: Deutschland-Gegner Türkei: Chaos als Prinzip                            1
    Sport: Türkei-Spieler Altintop: "Ich habe Deutschland alles zu verdanken"      1
    Sport: Euro-Helden: Die größten Stars der EM-Geschichte                        1
    Sport: WIMBLEDON Federer mühelos, Kohlschreiber enttäuscht                     1
    Sport: Sport bleibt Sport!                                                     3
    
    8 rows selected.
    
    
    
    Die Scoring-Ausdrücke lassen sich auch hier gewichten, wie das nächste Beispiel zeigt:
    
    SQL> SELECT dokument, score(1) as s
         FROM  texttabelle
         WHERE contains(dokument,'DEFINESCORE(Sport, relevance*5)',1) >0
         ORDER BY score(1);
    
    DOKUMENT                                                                       S
    --------------------------------------------------------------------------- ----
    Sport: Olympia rückt näher: Der Fackellauf ist in vollem Gange                24
    Sport: EM-Gegner Türkei: Torwart Volkan bleibt gesperrt                       24
    Sport: EM-Gastgeber Österreich: Trainer Hickersberger hört auf                24
    Sport: Deutschland-Gegner Türkei: Chaos als Prinzip                           24
    Sport: Türkei-Spieler Altintop: "Ich habe Deutschland alles zu verdanken"     24
    Sport: Euro-Helden: Die größten Stars der EM-Geschichte                       24
    Sport: WIMBLEDON Federer mühelos, Kohlschreiber enttäuscht                    24
    Sport: Sport bleibt Sport!                                                    72
    
    8 rows selected.
    
    
    Möchte man die Scoring Algorithmen miteinander verbinden, ist dies mit dem AND und OR Operator in Kombination mit dem neuen DEFINEMERGE-Operator möglich. Das Scoring Endresultat wird dann über eine MERGE Methode wie AVG, ADD, MIN oder MAX bestimmt. Folgendes Beispiel zeigt die Verwendung:
    
    SQL> SELECT dokument, score(1) as s
         FROM texttabelle
         WHERE contains(dokument,'DEFINEMERGE(
    ((DEFINESCORE(Sport,occurrence)),(DEFINESCORE(Sport, relevance*5))),AND,MAX)',1) >0;
    
    DOKUMENT                                                                       S
    --------------------------------------------------------------------------- ----
    Sport: Olympia rückt näher: Der Fackellauf ist in vollem Gange                24
    Sport: EM-Gegner Türkei: Torwart Volkan bleibt gesperrt                       24
    Sport: EM-Gastgeber Österreich: Trainer Hickersberger hört auf                24
    Sport: Deutschland-Gegner Türkei: Chaos als Prinzip                           24
    Sport: Türkei-Spieler Altintop: "Ich habe Deutschland alles zu verdanken"     24
    Sport: Euro-Helden: Die größten Stars der EM-Geschichte                       24
    Sport: WIMBLEDON Federer mühelos, Kohlschreiber enttäuscht                    24
    Sport: Sport bleibt Sport!                                                    72
    
    
    Am besten probiert man das Ganze einfach einmal aus ...

    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