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.

Beliebte Postings