Mixed Queries in 11g

MDATA Sections sind in 10g eingeführt worden, um gemischte Abfragen (auch mixed queries genannt) - also Abfragen mit Text- und relationalen Anteilen, besser handhaben zu können. Generell können damit kurze Textfelder(sogenannte Metadaten), die als Ganzes im Textindex indiziert wurden, einfach abgefragt werden. Mehr Informationen zur MDATA-Nutzung finden Sie Metadatensuche mit MDATA Blog und im MData Section und MULTI_COLUMN_DATASTORE Blog. Abfragen auf die Metadaten wie prod_list_price und flag sehen dann beispielsweise folgendermassen aus:

SELECT prod_id, prod_list_price, prod_desc
FROM products
WHERE contains (prod_desc, 'Card AND MDATA(prod_list_price, 69.99)
AND MDATA(flag,N)') > 0;

Ein wichtiger Unterschied zu gewohntem Sectionverhalten ist, dass MDATA Bereiche transaktionell verändert werden können, ohne den Rest des Index zu beeinträchtigen bzw. zu re-indizieren. Nachteile dieser Technologie ist die Tatsache, dass nur auf Gleichheit abgfragt werden kann und zusätzlich die MDATA Werte als einziges Token behandelt und minimal normalisiert werden können (Whitespace-Entfernung etc). Daher ist in 11g eine weitere Form der Section Suche eingeführt worden - die SDATA Section (SDATA steht dabei für Structured Data). Die Indizierung der SDATA Section erlaubt Operationen wie Range Scans, Nutzung von Funktionen, Projektionen usw. So können neue Kombinationen aus Text und strukturierte Anteilen abgefragt werden. Um die Unterschiede aufzuzeigen, nehmen wir das Beispiel aus MData Section und MULTI_COLUMN_DATASTORE und verwenden dabei die neue SDATA Section. Wir belassen den MULTI_COLUMN_DATASTORE my_multi_pref, und erzeugen eine SDATA Section mit Namen prod_list_price.

connect sh/sh
execute ctx_ddl.drop_section_group('my_seg');
begin
ctx_ddl.create_section_group(group_name=>'my_seg',group_type=>'basic_section_group');
ctx_ddl.add_sdata_section('my_seg','PROD_LIST_PRICE','prod_list_price', 'NUMBER');
end;
/
DROP INDEX mdata_index;
DROP INDEX sdata_index;
CREATE INDEX sdata_index ON products(prod_desc)
INDEXTYPE IS ctxsys.context
PARAMETERS ('DATASTORE my_multi_pref SECTION GROUP my_seg sync (on commit)');

SELECT err_text FROM ctx_user_index_errors WHERE err_index_name = 'SDATA_INDEX';
no rows selected

Folgende Abfrageart mit dem SDATA-Operator ist nun möglich.

SELECT prod_id, prod_list_price, prod_desc FROM products
WHERE contains (prod_desc, 'Card AND SDATA(prod_list_price >= 69.99)') > 0;

   PROD_ID PROD_LIST_PRICE
---------- ---------------
PROD_DESC
--------------------------------------------------------------------------------
        25          112.99
SIMM- 8MB PCMCIAII card

        26          149.99
SIMM- 16MB PCMCIAII card

       138           69.99
256MB Memory Card

Untersucht man genauer die neuangelegten Objekte, wird man feststellen, dass ein zusätzliches Indexsegment, eine IOT-Tabelle mit Namen DR$SDATA_INDEX$S, erzeugt wurde.

Nun stellt sich die Frage, ob das Ganze nicht einfacher zu bewerkstelligen ist, ohne zusätzlich Sections zu verwenden. Die Antwort dazu gibt die neue Composite Domain Index Technologie. Mit nur einem CREATE INDEX-Kommando ohne zusätzliche SDATA Sections kann dies erreicht werden. Folgendes Kommando zeigt die Implementierung in unserem Fall. Die FILTER BY Klausel ermöglicht dabei die Teilabfrage auf die Spalte PROD_LIST_PRICE vollständig im Text-Index durchzuführen.

DROP INDEX sdata_index;

CREATE INDEX comp_index ON products(prod_desc)
INDEXTYPE IS ctxsys.context
FILTER BY prod_list_price;

Ein kurzer Blick auf die erzeugten Objekte, gibt den Hinweis darauf, dass die SDATA Technologie offensichtlich als Grundlage dient, da wir nun eine zusätzliche IOT-Tabelle DR$COMP_INDEX$S besitzen. Die Abfragen können nun im gewohnten Stil ohne Verwendung von speziellen Operatoren verwendet werden.

SELECT prod_id, prod_list_price, prod_desc 
FROM products
WHERE contains (prod_desc, 'Card')>0  AND prod_list_price <= 69.99;

   PROD_ID PROD_LIST_PRICE
---------- ---------------
PROD_DESC
--------------------------------------------------------------------------------
       136           32.99
64MB Memory Card

       137           52.99
128MB Memory Card

       138           69.99
256MB Memory Card

 

Mehr zur Composite Domain Index Technik in einem der nächsten Blogs.....

Wie ähnlich soll es sein ...? Ein paar Worte zum FUZZY-Operator

Es ist weitgehend bekannt, dass Oracle TEXT eine Ähnlichkeitssuche mit dem Fuzzy-Operator unterstützt. Die einfachste Variante ist die Verwendung des Fragezeichens ?. Der Fuzzy-Operator ist gut geeignet, um mit etwaigen Rechtschreibfehlern in den Dokumenten umzugehen. Sucht man bspw. nach ?Spatial, so findet der Index auch Dokumente, in denen fälschlicherweise "Sptial" geschrieben wurde.

select dateiname, score(0) from dokumente where contains (content, '?Spatial', 0) > 0

Diese Ähnlichkeitssuche kann übrigens auch parametrisiert werden: Neben dem Fragezeichen steht auch das Schlüsselwort FUZZY zur Verfügung. Oder anders ausgedrückt: Obige SQL-Abfrage ließe sich auch so schreiben:

select dateiname, score(0) from dokumente where contains (content, 'FUZZY(Spatial, 60, 100, N)'), 0) > 0

Und mit diesen Parametern kann man das Verhalten des FUZZY-Operators nun steuern. Der erste Parameter legt fest, wie ähnlich die Tokens im Dokument dem Suchbegriff sein müssen. Erlaubt sind Werte von 1 bis 80. Je niedriger Ihr den Wert ansetzt, desto mehr Begriffe kommen in Frage; desto mehr Dokumente werden also gefunden. Allerdings stellt sich die Frage, wie relevant die Dokumente bei sehr niedrigen Grenzen noch sind.

Der zweite Parameter legt fest, wieviele Werte überhaupt in die Termexpansion einbezogen werden. Dazu kurz einige Worte: Oracle TEXT führt die Fuzzy-Suche über eine Termexpansion durch; es werden also zunächst aus der Token-Tabelle ($I) die ähnlichen Tokens herausgesucht (der erste Parameter legt, wie gesagt, fest, wie ähnlich die Tokens sein müssen). Mit den so gefundenen Tokens wird dann eine ODER Suche durchgeführt.

Mit dem zweiten Parameter legt man also fest, wieviele ähnliche Wörter maximal einbezogen werden sollen. Erlaubt sind Werte zwischen 1 und 5000. Ein Wert von 20 würde bewirken, dass maximal 20 ähnliche Wörter in der Suche berücksichtigt werden, auch wenn anhand des ersten Parameters mehr in Frage kämen. Hier gilt also: Je höher der Wert gesetzt wird, desto mehr Dokumente werden tendenziell gefunden ...

Der letzte Parameter wirkt sich nur auf den Score aus, den ein Dokument im Relevanz-Ranking bekommt. Mit einem W werden die Scores anhand der Ähnlichkeit der Suchwörter gewichtet; mit einem N geschieht das nicht. Ein W führt zu tendenziell höheren Scores.

Mit diesen Parametern könnt Ihr also spielen, um mit der Fuzzy-Suche mehr oder weniger Ergebnisse zu bekommen. Was konkret gebraucht wird, hängt von den Anforderungen des Projekts ab ... zur Verdeutlichung hier nochmals zwei Extrembeispiele. Das erste ist zwar "formal" eine Ähnlichkeitssuche, aber die Parameter "würgen" die Fuzzy-Logik weitgehend ab.

select dateiname, score(0) from dokumente 
where contains(content, 'fuzzy(sptial, 80, 1, W)',0) > 0;

Das zweite bohrt die Grenzen so weit auf, dass sehr viele Dokumente in Frage kommen ...

select dateiname, score(0) from dokumente 
where contains(content, 'fuzzy(sptial, 1, 5000, W)',0) > 0;

Dies lässt sich auch sehr gut mit Query Relaxation verbinden. In einer ersten Stufe würde ohne den Fuzzy-Operator suchen, in einer nächsten Stufe dann mit dem Fuzzy-Operator, aber eher restriktiven Kriterien und danach mit sehr weit gehenden Parametern. Query Relaxation arbeitet die Stufen dann bekanntlich so lange ab, bis genügend Treffer gefunden sind - mehr dazu im Blog Posting.

USER_DATASTORE ... indiziert wirklich alles

Eine der interessanten Eigenschaften des Oracle TEXT-Index ist, dass die zu indizierenden Dokumente nicht nur einfach aus einer Tabellenspalte, sondern aus unterschiedlichen Data Stores kommen können.

• DIRECT_DATASTORE: der "Normalfall"
• MULTICOLUMN_DATASTORE: Mehrere Tabellenspalten (dazu hatten wir schon Blog-Postings
• FILE_DATASTORE: Die Dokumente liegen im Dateisysten; die Tabelle enthält für jedes Dokument einen vollständigen, absoluten Pfad
• URL_DATASTORE: Die Dokumente liegen im "Netzwerk"; die Tabelle enthält für jedes Dokument einen vollständigen, absoluten URL (FTP oder HTTP)
• DETAIL_DATASTORE: Die Dokumente liegen in einer anderen Tabelle, welche mit der zu indizierenden in einer Master-Detail-Beziehung steht
• NESTED_DATASTORE: Die Dokumente liegen in nicht direkt in der zu indizierenden Tabelle, sondern in einer Nested Table (dürfte selten vorkommen).

Und schließlich gibt es den USER_DATA_STORE, der quasi "alles kann". Und um den soll es in diesem Posting auch gehen. Als Ausgangspunkt haben wir eine Tabelle mit Dokumenten und diese sollen nicht direkt indiziert werden, sondern über einen USER_DATA_STORE. Warum? Weil wir über eine zusätzliche Spalte (INDEX_DOCUMENT) kontrollieren möchten, ob die Dokumente indiziert werden sollen oder nicht. Beginnen wir mit dem Tabellenaufbau ...

SQL> desc dokumente
 Name                                      Null?    Typ
 ----------------------------------------- -------- ---------------------

 ID                                                 NUMBER
 FILENAME                                           VARCHAR2(2000)
 DOCUMENT                                           BLOB
 INDEX_DOCUMENT                                     CHAR(1)

Nun geht es ans Erstellen des Textindex mit dem User Datastore. Ein User Datastore bedeutet nichts weiter als dass der Index seine Dokumente von einer PL/SQL-Prozedur zugewiesen bekommt und diese eben nicht direkt aus der Tabelle holt. Da man in der PL/SQL-Prozedur programmieren kann, was man möchte, ist das Verhalten des User Datastore auch sehr individuell. Dieses Beispiel soll (wie gesagt), das Dokument normal indizieren, wenn die Spalte INDEX_DOCUMENT auf "Y" steht und gar nicht indizieren, wenn die Spalte auf "N" steht. Beginnen wir mit der PL/SQL-Prozedur:

create or replace procedure dokument_uds_proc (
  rid  in              rowid,
  tlob in out NOCOPY   blob    
) is
begin
  begin
    select document into tlob
    from dokumente where rowid = rid and index_document = 'Y';
  exception
    when NO_DATA_FOUND then tlob := null;
  end;
end;
/

Wichtig an dieser Prozedur ist die Signatur, diese muss genau so aussehen, wie in diesem Beispiel vorgegeben. Die zu indizierende ROWID wird in die Prozedur hineingegeben, das zu indizierende Dokument kommt als OUT-Parameter wieder zurück. Man sieht, dass man das Dokument selbst nun beliebig zusammensetzen kann. Die Inhalte können sehr wohl aus verschiedensten Tabellen oder anderen Datenquellen kommen.

Aber bis jetzt ist die Prozedur nur eine normale PL/SQL-Prozedur. Damit sie für etwas ORACLE Text etwas bewirken kann, muss sie zunächst in Oracle TEXT bekannt gemacht werden; die folgenden PL/SQL-Blöcke richten den User Datastore im Dictionary von Oracle TEXT ein.

begin
  ctx_ddl.drop_preference('DOKUMENT_UDS');
end;
/
sho err

begin
  ctx_ddl.create_preference('DOKUMENT_UDS','user_datastore');
  ctx_ddl.set_attribute('DOKUMENT_UDS','procedure','dokument_uds_proc');
  ctx_ddl.set_attribute('DOKUMENT_UDS','output_type','blob_loc');
end;
/
sho err

Bevor der Index nun erzeugt wird, fehlt noch eine Kleinigkeit: Der Index muss in zwei Fällen aktualisiert werden: Erstens wenn sich das Dokument ändert und zweitens wenn die Spale INDEX_DOCUMENT verändert wird. Wenn der Index also auf die Spalte INDEX_DOCUMENT gelegt wird, benötigen wir noch einen Trigger, der UPDATE-Operationen in DOCUMENT auf die Spalte INDEX_DOCUMENT überträgt:

create or replace trigger trg_uds_document
before update on dokumente
for each row
begin
  :new.index_document := :new.index_document;
end;
/

Nun kann der Index erstellt werden. Wie schon beschrieben, wird der Index auf die Spalte INDEX_DOCUMENT gelegt:

create index idx_text_dokumente
on dokumente (index_document)
indextype is ctxsys.context
parameters ('filter ctxsys.auto_filter
             datastore dokument_uds
             memory 200M
             transactional')
/

Wenn alle Einträge ein "N" in der Spalte INDEX_DOCUMENT haben, ist der Index nach Erstellung immer noch leer. Setzt man eine oder mehrere Zeilen auf "Y", so können diese anschließend dank des TRANSACTIONAL-Parameters (Posting!) gefunden werden, das dauert aber sehr lange. Kein Wunder, denn der Index ist noch nicht synchronisiert; die Dokumente in der Pending-Tabelle werden also on-the-fly durchsucht und dabei muss natürlich auch die PL/SQL-Prozedur des User Datastore durchlaufen werden. Nach einem CTX_DDL.SYNC_INDEX('IDX_TEXT_DOKUMENTE') befinden sich die Einträge denn auch im Textindex.

Dieses einfache Beispiel zeigt, wie man einen User Datastore nutzen kann. Wichtig ist, dass hier keine Grenzen existieren; die PL/SQL-Prozedur des UDS ist für Oracle TEXT eine "Black Box"; man kann dort hineinprogrammieren, was man möchte ...

MDATA Section und MULTI_COLUMN_DATASTORE

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. Ein Beispiel dazu findet sich in folgendem Blog. Der MULTI_COLUMN_DATASTORE (siehe dazu auch Blog) führt die Spalten einer Tabellen zu einem Dokument zusammen und trennt die Informationen durch XML Tags.
Bringt man nun diese beiden Techniken zusammen, so lassen sich Spalten im MULTI_COLUMN_DATASTORE zusammenführen und danach als MDATA Metadaten Sections kennzeichen und nutzen.

Für das folgenden Beispiel verwenden wir die Tabelle PRODUCTS im Schema SH. Zu beachten ist, um das Paket CTX_DDL nutzen zu können, benötigt man die Rolle CTXAPP (oder ein explizites EXECUTE-Privileg).
Zuerst legen wir den MULTI_COLUMN_DATASTORE mit folgenden Statements an. Die Spalten "PROD_NAME", "PROD_STATUS", "PROD_LIST_PRICE" und "PROD_DESC" werden für den MULTI_COLUMN_DATASTORE konfiguriert:

connect sh/sh
execute ctx_ddl.drop_preference (preference_name =>'MY_MULTI_PREF');
begin 
  ctx_ddl.create_preference(preference_name =>'MY_MULTI_PREF',
                            object_name => 'multi_column_datastore' );
  ctx_ddl.set_attribute(preference_name => 'MY_MULTI_PREF',
           attribute_name  => 'COLUMNS',
           attribute_value => 'PROD_NAME, PROD_STATUS, PROD_LIST_PRICE, PROD_DESC');
end;
/
sho err

Im nächsten Schritte werden die MDATA Sections "PROD_NAME", "PROD_STATUS", "PROD_LIST_PRICE" mit ADD_MDATA_SECTION konfiguriert. Die Section "PROD_DESC" wird als eine FIELD-Section angelegt. Zusätzlich zu den existierenden Sections erzeugen wir eine neue Section "flag", die neue Informationen, die nicht in der Tabelle enthalten sind, aufnehmen kann. Eine typische Verwendung wäre zum Beispiel eine zusätzliche Kennzeichnung der Daten durch spezielle Zugriffsrechte.

execute ctx_ddl.drop_section_group('my_seg');

execute ctx_ddl.create_section_group(group_name=>'my_seg',   
                                     group_type=>'basic_section_group');
execute ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'PROD_NAME', 
                                  tag=>'prod_name');
execute ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'PROD_STATUS', 
                                  tag=>'prod_status');
execute ctx_ddl.add_mdata_section(group_name=>'my_seg',  
                                  section_name=>'PROD_LIST_PRICE', 
                                  tag=>'prod_list_price');
execute ctx_ddl.add_field_section(group_name=>'my_seg', section_name=>'PROD_DESC', 
                                  tag=>'prod_desc', visible=>true);
execute ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'flag', 
                                  tag=>'flag');

sho err

Nun kann der Index mit dem DATASTORE my_multi_pref und der SECTION GROUP my_seg erzeugt werden. Die Synchronisierung soll dabei automatisch nach jedem COMMIT erfolgen.

DROP INDEX mdata_index;

CREATE INDEX mdata_index ON products(prod_desc)
indextype IS ctxsys.context 
parameters ('DATASTORE my_multi_pref SECTION GROUP my_seg sync (on commit)');

SELECT err_text FROM ctx_user_index_errors WHERE err_index_name = 'MDATA_INDEX';
no rows selected

Folgende Abfrageart mit dem MDATA-Operator ist nun möglich.

SELECT prod_id, prod_list_price, prod_desc FROM products 
WHERE contains (prod_desc, 'Card AND MDATA(prod_list_price, 69.99)') > 0;

   PROD_ID PROD_LIST_PRICE
---------- ---------------
PROD_DESC
--------------------------------------------------------------------------------
       138           69.99
256MB Memory Card

Die Tokentypes größer gleich 400 in der $I-Tabelle zeigen an, dass MDATA-Sectionen vorhanden sind, da MDATA Tokens mit Tokentype zwischen 400 und 499 gekennzeichnet sind. Gibt es zusätzlich negative Werte (kleiner gleich 400), die sogenannten "Delta-Zeilen", ist der Index nicht optimiert. Dies wird nun mit folgender Abfrage verifiziert:

SELECT token_type, count(*) 
FROM dr$mdata_index$i 
WHERE token_type<= -400 OR token_type>=400 GROUP BY token_type;
TOKEN_TYPE   COUNT(*)
---------- ----------
       400         71
       402         42
       401          1

Die 3 Zeilen zeigen, dass 3 verschiedene MDATA Tokentypes mit 400, 401 und 402 existieren. Diese stehen für die Sections "PROD_NAME", "PROD_STATUS" und "PROD_LIST_PRICE".
Nun werden manuell Werte mit der Prozedur ADD_MDATA in die "flag" Metadaten-Section eingefügt. Dazu erzeugen wir mit folgendem Skript die entsprechenden Aufrufe, um "flag"-Einträge mit Wert "J" bzw. "N" je nach Größe des PROD_LIST_PRICE zu generieren.

spool liste.lst
SELECT 
'execute ctx_ddl.add_mdata'||'(''MDATA_INDEX'''||','||'''flag'''||','||'''N'''||','||''''||rowid||''''||')'||';' 
FROM products WHERE prod_list_price<=70;
SELECT 'execute ctx_ddl.add_mdata'||'(''MDATA_INDEX'''||','||'''flag'''||','||'''J'''||','||''''||rowid||''''||')'||';'
FROM products WHERE prod_list_price>70;
COMMIT;
spool off

-- start des ablaufbaren Skripts mit
start liste.lst
execute ctx_ddl.add_mdata('MDATA_INDEX','flag','N','AAAew2AAEAADLwUAAG');
execute ctx_ddl.add_mdata('MDATA_INDEX','flag','N','AAAew2AAEAADLwUAAJ');
execute ctx_ddl.add_mdata('MDATA_INDEX','flag','N','AAAew2AAEAADLwUAAK');
...

ADD_MDATA ist transaktional und muss mit COMMIT oder ROLLBACK abgeschlossen werden. Danach ist die "flag" Section in Abfragen wie folgt nutzbar:

SELECT prod_id, prod_list_price, prod_desc 
FROM products WHERE contains (prod_desc, 
'Card AND MDATA(prod_list_price, 69.99) AND MDATA(flag,N)') > 0;
   PROD_ID PROD_LIST_PRICE
---------- ---------------
PROD_DESC
--------------------------------------------------------------------------------
       138           69.99
256MB Memory Card


SELECT prod_id, prod_list_price, prod_desc 
FROM products WHERE contains (prod_desc, 
'Card AND MDATA(prod_list_price, 69.99) AND MDATA(flag,J)') > 0;

no rows selected

Der Index ist allerdings noch nicht optimiert, wie die Überprüfung der $I Tabelle zeigt:

SELECT token_type, count(*)
FROM dr$mdata_index$i 
WHERE token_type<= -400 OR token_type>=400 GROUP BY token_type;

TOKEN_TYPE   COUNT(*)
---------- ----------
       400         71
       403          2
       402         42
      -403         70
       401          1

Tokenweise Optimierung ist mit folgender speziellen OPTIMIZE_INDEX Prozedur möglich:

execute ctx_ddl.optimize_index(idx_name=> 'MDATA_INDEX',
              optlevel=> ctx_ddl.optlevel_token_type, 
              token_type=> ctx_report.token_type('MDATA_INDEX', 'mdata flag')); 

Der Index liegt nun optimiert vor. Das Ergebnis in der Token-Tabelle sieht nun folgendermassen aus:

SELECT token_type, count(*)
FROM dr$mdata_index$i 
WHERE token_type<= -400 OR token_type>=400 GROUP BY token_type;

TOKEN_TYPE   COUNT(*)
---------- ----------
       400         71
       403          2
       402         42
       401          1

Zusätzlich zu den MDATA Sections gibt es in 11g SDATA Sections, die einige der Einschränkungen von MDATA aufheben. Darüberhinaus eröffnet der Composite Domain Index in 11g ganz neue Möglichkeiten, um die Problematik der Mixed Queries zu lösen. Mehr dazu in einer der nächsten Postings...

Index-Optimierung: Einige Grundlagen

Häufige Indexsynchronisierung nach DML Operationen kann zu Indexfragmentierungen führen. Indexfragmentierungen wiederum können dabei die Antwortzeiten der Textabfragen negativ beeinflussen. Weniger häufige Synchronisierungen - also Batchoperation und längere Listen beim Synchronisieren - führen zu weniger Zeilen im Index und daher geringerer Fragmentierung. Häufig hängt allerdings das entsprechende Intervall der Synchronisierung von Anwenderanforderungen ab. Falls Sie mehr Informationen zur Synchronisierung bzw. zur Indexfragmentierung benötigen, schauen Sie einfach in den folgenden Blog. Regelmässige Optimierung ist also wichtige Operation, da die Indexfragmentierung und Indexgröße dabei reduziert werden und somit die Performance der Abfragen erhöht werden kann. Optimiert wird der Index mit der Prozedur CTX_DDL.OPTIMIZE_INDEX, die in unterschiedlichen Modi durchgeführt werden können:

• FAST: Aufhebung der fragmentierten Zeilen, allerdings keine Optimierung der gelöschten Daten
• FULL: Aufhebung der fragmentierten Zeilen und Optimierung der gelöschten Daten (Garbage)
• REBUILD: wie FULL nur dass die $I Tabelle neuaufgebaut wird-u.U. schneller als FULL
• TOKEN: volle Optimierung einzelner Tokens
• TOKEN_TYPE: volle Optimierung bezogen auf einen bestimmten Tokentyp

Der Anwender kann auch während einer OPTIMIZE-Operation weitersuchen und einfügen. Je nach Modus kann die OPTIMIZE-Operation allerdings sehr lange dauern, CPU und I/O intensiv sein und somit das ganze System beeinflussen. Daher ist es im Modus FULL möglich die OPTIMIZE-Operation mithilfe des Arguments MAXTIME auf eine gewisse Zeit zu beschränken. Dabei wird sich der letzte optimierte Zeitpunkt gemerkt und beim nächsten Anlauf dort weiter fortgeführt. Um eine genauere Vorstellung von der Funktionsweise zu erhalten, werden wir im Folgenden einige Optimierungen durchführen. Folgende Indexfragmentierung sei beim Index vorhanden, ausgelöst durch INSERT und DELETE Operationen. Die Ausgabe erfolgt analog zum Blog.

---------------------------------------------------------------------------
FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                                270,000 (263.67 KB)

$I rows:                                                           90,000
estimated $I rows if optimal:                                          72
estimated row fragmentation:                                        100 %

garbage docids:                                                     7,001
estimated garbage size:                                 52,297 (51.07 KB)

most fragmented tokens:
  SAT (0:TEXT)                                                      100 %
  MAT (0:TEXT)                                                      100 %
  MARY (0:TEXT)                                                     100 %
  LAMB (0:TEXT)                                                     100 %
  CAT (0:TEXT)                                                      100 %
  LITTLE (0:TEXT)                                                   100 %

Offensichtlich ist der Index fragmentiert und besitzt Garbage (gelöschte Daten). Wir werden nun den Index mit folgendem Kommando optimieren und dabei den Modus FAST verwenden:

execute ctx_ddl.optimize_index (idx_name => 'TESTX', optlevel => 'FAST', parallel_degree => 1);

Nach Abschluss der OPTIMIZE-Operation monitoren wir wieder die Fragmentieren. Die Fragmentierung ist verschwunden, allerdings ist noch Garbage vorhanden.

---------------------------------------------------------------------------
FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                                270,003 (263.67 KB)

$I rows:                                                               72
estimated $I rows if optimal:                                          72
estimated row fragmentation:                                          0 %

garbage docids:                                                     7,001
estimated garbage size:                                 52,298 (51.07 KB)

most fragmented tokens:
  SAT (0:TEXT)                                                        0 %
  MAT (0:TEXT)                                                        0 %
  MARY (0:TEXT)                                                       0 %
  LAMB (0:TEXT)                                                       0 %
  CAT (0:TEXT)                                                        0 %
  LITTLE (0:TEXT)                                                     0 %

Nun führen wir das Ganze statt im FAST Modus im FULL Modus aus:

execute ctx_ddl.optimize_index (idx_name => 'TESTX', optlevel => 'FULL', parallel_degree => 1);

Parallel können wir in einer zusätzlichen Session, die Optimierung beobachten:

SQL>set pagesize 100
SQL>col indx_name format a30
SQL>col idx_opt_type format a20

SQL> SELECT idx_name,idx_opt_token,idx_opt_type,idx_opt_count FROM ctxsys.dr$index; 
IDX_NAME                              IDX_OPT_TOKEN IDX_OPT_TYPE IDX_OPT_COUNT
-------------------- ------------------------------ ------------ -------------
D01_F_ALL
SUP_TEXT_IDX
AUTH_IDX
IDX_TEXT
TESTX MAT                                                      0          7001
TEXT_IND

Mit unserem Monitoring Skript erhalten wir nun folgende Information über die Fragmentierung:

---------------------------------------------------------------------------
FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                                206,997 (202.15 KB)
$I rows:                                                               57
estimated $I rows if optimal:                                          57
estimated row fragmentation:                                          0 %

garbage docids:                                                         0
estimated garbage size:                                                 0

most fragmented tokens:
  SAT (0:TEXT)                                                        0 %
  MAT (0:TEXT)                                                        0 %
  MARY (0:TEXT)                                                       0 %
  LAMB (0:TEXT)                                                       0 %
  CAT (0:TEXT)                                                        0 %
  LITTLE (0:TEXT)                                                     0 %

Stellt man fest, dass häufig nach einzelnen Tokens gesucht wird, könnte man die Optimierung auch auf einzelne Tokens oder Token-Types (im Fall der MDATA Nutzung) beschränken. Im folgenden Beispiel werden wir dies für das Token LITTLE durchführen: Das Ergebnis sieht dann folgendermassen aus:

---------------------------------------------------------------------------
                         FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                                263,998 (257.81 KB)

$I rows:                                                           80,007
estimated $I rows if optimal:                                          71
estimated row fragmentation:                                        100 %

garbage docids:                                                     7,001
estimated garbage size:                                 51,134 (49.94 KB)

most fragmented tokens:
  SAT (0:TEXT)                                                      100 %
  MAT (0:TEXT)                                                      100 %
  MARY (0:TEXT)                                                     100 %
  LAMB (0:TEXT)                                                     100 %
  CAT (0:TEXT)                                                      100 %
  LITTLE (0:TEXT)                                                     0 %

Mehr zur Optimierung in einem der nächsten Blogs....

Einstellungen für einen Textindex: Komposita, Printjoins, Skipjoins, Mixed Case und mehr ...

Beim Erstellen eines Oracle TEXT-Index kann man eine ganze Menge Einstellungen vornehmen. Im letzten Posting ging es um das Thema Stopwörter, heute schauen wir uns ein paar andere Einstellungen an.

Wir beginnen ganz einfach und erzeugen zunächst eine Tabelle mit ein paar Textzeilen.

drop table texte
/

create table texte (
  id number,
  text varchar2(4000)
)
/

insert into texte values (1, 'Das Treffen am Bahnhofsplatz heute abend war schön');
insert into texte values (2, 'Dem Chat trat der Nutzer "user_7642" bei');

commit
/

Erstellen wir nun einen Index - zunächst mal ohne jede Parametrisierung ...

drop index idx_texte
/

create index idx_texte on texte (text)
indextype is ctxsys.context
/

Anschließend kann man sich "den Index" mit einem Blick auf die Token-Tabelle ansehen ...

SQL> select token_text from dr$idx_texte$i;

TOKEN_TEXT
--------------------------------------------------
7642
Bahnhof
Bahnhofsplatz
Chat
Das
Dem
Nutzer
Platz
Treffen
abend
heute
schön
trat
user

Die erste Auffälligkeit ist die Tatsache, dass die Wörter (Tokens) im Mixed Case in der Token-Tabelle stehen. Das ist für die meisten Fälle ungeeignet, da eine Suche nach "chat" (alles kleingeschrieben) zu keinem Ergebnis führen würde. Dies gälte es also durch Einstellung von Parametern zu ändern. An anderer Stelle es gut erkennbar, dass der Index die deutsche Sprache erkannt hat; das Token "Bahnhofsplatz" wurde korrekt in die zusätzlichen Tokens "Bahnhof" und "Platz" zerlegt. Experimentieren wir nun ein wenig mit den Parametern: Als erstes soll der Index nicht mehr Case-Sensitiv sein ...

drop index idx_texte
/

begin
  ctx_ddl.drop_preference( 
    preference_name => 'MY_LEXER'
  );
end;
/

begin
  ctx_ddl.create_preference(
    preference_name => 'MY_LEXER',
    object_name     => 'BASIC_LEXER'
  );
  -- Mixed Case abschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO'
  );
end;
/

create index idx_texte on texte (text)
indextype is ctxsys.context
parameters ('LEXER MY_LEXER')
/

Die Parameter werden in die sog. Preference MY_LEXER eingestellt. Anschließend wird der Index neu erstellt - die Token-Tabelle sieht dann so aus:

SQL> select token_text from dr$idx_texte$i;

TOKEN_TEXT
----------------------------------------------------------------
7642
ABEND
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
SCHÖN
TRAT
TREFFEN
USER

OK ... damit ist das Mixed-Case-Problem behoben. Allerdings wurde der Bahnhofsplatz nun nicht mehr zerlegt - und das war ja eigentlich ganz gut so ... Das Erstellen der Preference MY_LEXER ändern wir also nochmals und schalten die Kompositazerlegung wieder ein (von nun an stelle ich nur noch die create_preference Aufrufe hier vor.

begin
  ctx_ddl.create_preference(
    preference_name => 'MY_LEXER',
    object_name     => 'BASIC_LEXER'
  );
  -- Mixed Case abschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO'
  );
  -- Kompositazerlegung einschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'COMPOSITE',
    attribute_value => 'GERMAN'
  );
end;
/

Ergebnis ..

SQL>  select token_text from dr$idx_texte$i;

TOKEN_TEXT
---------------------------------------------------------
7642
ABEND
BAHNHOF
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
PLATZ
SCHÖN
TRAT
TREFFEN
USER

Das Token user_7642 wurde offensichtlich ebenfalls zerlegt: Oracle TEXT behandelt den Unterstrich (_) als Trenner von Tokens. Auch dies kann man mit dem Parameter PRINTJOINS abschalten ...

begin
  ctx_ddl.create_preference(
    preference_name => 'MY_LEXER',
    object_name     => 'BASIC_LEXER'
  );
  -- Mixed Case abschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO'
  );
  -- Kompositazerlegung einschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'COMPOSITE',
    attribute_value => 'GERMAN'
  );
  -- Den Unterstrich (_) als "Printjoin" deklarieren
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'PRINTJOINS',
    attribute_value => '_'
  );
end;
/

Das Ergebnis ...

SQL>  select token_text from dr$idx_texte$i;

TOKEN_TEXT
---------------------------------------------------
ABEND
BAHNHOF
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
PLATZ
SCHÖN
TRAT
TREFFEN
USER_7642

Doch hierbei Vorsicht: Der Unterstrich wirkt nun überhaupt nicht mehr als Trennzeichen für Tokens - die Aufnahme eines Zeichens zu den Printjoins sollte also nur dann erfolgen, wenn man sich sicher ist, dass dies auch für den gesamten Dokumentbestand in Ordnung geht. Weiterhin könnt Ihr nur einzelne Zeichen als Printjoins deklarieren, keine Zeichenketten. Hierbei muss man also ein wenig aufpassen ...

Eine andere Variante wäre, den Unterstrich als Skipjoin zu deklarieren ...

begin
  ctx_ddl.create_preference(
    preference_name => 'MY_LEXER',
    object_name     => 'BASIC_LEXER'
  );
  -- Mixed Case abschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO'
  );
  -- Kompositazerlegung einschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'COMPOSITE',
    attribute_value => 'GERMAN'
  );
  -- Den Unterstrich (_) als "Skipjoin" deklarieren
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'SKIPJOINS',
    attribute_value => '_'
  );
end;
/

... was dann so aussieht; der Unterstrich wäre dann verschwunden und würde bei Abfragen ignoriert.

SQL>  select token_text from dr$idx_texte$i;

TOKEN_TEXT
---------------------------------------------
ABEND
BAHNHOF
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
PLATZ
SCHÖN
TRAT
TREFFEN
USER7642

Eine vollständige Übersicht über alle Parameter (es gibt noch ein paar mehr) findet Ihr im Handbuch Text Reference. So ... das war's für heute - mehr zu Textindex-Parametern und Einstellungsmöglichkeiten in den nächsten Postings ...

Stopwörter und Stoplisten: Unwichtige Wörter ... (wirklich unwichtig? ...)

Bekanntlich unterstützt Oracle TEXT Stopwortlisten. Wird ein Wort als Stopwort deklariert, wird es von Oracle TEXT völlig ignoriert - Stopwörter werden nicht in den Index aufgenommen und bei Abfragen insofern nicht berücksichtigt. Bei Abfragen auf einzelne Wörter (Tokens) ist das relativ klar; eine Suche nach "und" wäre auch sinnlos. Stopwörter haben allerdings einen Einfluß auf die Phrasensuche - sie passen auf jedes Wort. Sucht man beispielsweise nach der Phrase "Radio und Fernseher", so sucht Oracle TEXT (wenn die Stopwortliste aktiv ist und bspw. die Wörter "und", "oder" und "unter" enthält) im Index nach "Radio {?} Fernseher". Gefunden würden auch Dokumente, in denen die Phrase Radio oder Fernseher oder Radio im Fernseher vorkommt. Wenn solcherlei Effekte nicht gewünscht sind, muss man die Stopwortliste abschalten.

Aber welche Wörter sind überhaupt Stopwörter ...? Dazu schaut man erstmal nach, welche Stopwortlisten es überhaupt gibt - die Stopwörter sind in Listen organisiert ...

SQL> select * from ctx_stoplists

SPL_OWN SPL_NAME                   SPL_COUNT SPL_TYPE
------- ------------------------- ---------- ----------------
CTXSYS  DEFAULT_STOPLIST                 235 BASIC_STOPLIST
WKSYS   WK_STOPLIST                       76 BASIC_STOPLIST
CTXSYS  EMPTY_STOPLIST                     0 BASIC_STOPLIST
CTXSYS  EXTENDED_STOPLIST                  0 BASIC_STOPLIST
TM      SWL_BRANCHEN                      10 BASIC_STOPLIST

5 Zeilen ausgewählt.

Die CTXSYS.DEFAULT_STOPLIST ist eine Standardliste für die Sprache, mit der die Datenbank aufgesetzt wurde - Hierzulande ist das im Normalfall Deutsch. Und auch da kann man reingucken ...

SQL> select spw_type, SPW_WORD from ctx_stopwords where SPW_STOPLIST='DEFAULT_STOPLIST'

SPW_TYPE   SPW_WORD
---------- ------------------------------
STOP_WORD  Ihnen
STOP_WORD  Ihre
STOP_WORD  Ihrem
STOP_WORD  Ihren
STOP_WORD  Ihrer
STOP_WORD  Ihres
:          :

Enthalten sind also Bindewörter, Präpositionen, Artikel und ähnliche Wörter; also alles, was typischerweise in jedem Dokument vorkommt und daher ignoriert werden kann.

Die Skripte, mit denen diese Defaults erzeugt werden befinden sich in $ORACLE_HOME/ctx/admin/defaults. So spielt das darin vorhandene Skript drdefd.sql die deutsche Standard-Stopliste ein; drdefus.sql entsprechend die US-amerikanische. Wenn in der Datenbank also eine englische Stopliste enthalten ist (weil beim Aufsetzen alles mit englischer Sprache gelaufen ist), so lässt sich das (als User CTXSYS) einfach ändern ...

SQL> start ?/ctx/admin/defaults/dr0defdp.sql
SQL> start ?/ctx/admin/defaults/drdefd.sql

Stoplisten sind wie alle anderen Datenbankobjekte einem Schema zugeordnet; das Schema CTXSYS enthält die Standardlisten. Eigene Stoplisten sollten stets auch ins eigene Datenbankschema (und nicht in CTXSYS) abgelegt werden. Die Skripte können auch als Basis für Erweiterungen verwendet werden. Möchte man mit Stopwortlisten arbeiten, kann es durchaus sinnvoll sein, diese durch branchentypische Fachbegriffe zu erweitern. Die Standardlisten enthalten nur solche Wörter, von denen Oracle sicher ist, dass sie in nahezu jedem Dokument vorkommen. Es mag nun branchenspezifische Wörter geben, die man hinzufügen möchte: In einer Bank könnten dies Begriffe wie "Bank", "Betrag", "Konto" oder ähnliche sein. Beim Festlegen solcher Begriffe sollte man aber stets darauf achten, dass die Stopwörter (wie eingangs erwähnt) auch bei der Phrasensuche ignoriert werden.

begin
  ctx_ddl.add_stopword(
    'MY_STOPLIST',
    'bank'
  );
end;

Ein Index mit dieser Stopliste wird dann so gebaut:

create index idx_volltext on dokument_tab (spalte)
indextype is ctxsys.context
parameters ('stoplist my_stoplist')
/

Diese Syntax hat keinen Einfluß auf schon bestehende Volltextindizes. Möchte man auf einem schon erzeugten Index ein (zusätzliches) Stopwort einrichten, gibt es eine spezielle Syntax:

  alter index idx_volltext add stopword bank;

Der Index wird dadurch nicht neu erzeugt, auch werden diese Wörter (so sie vorhanden sind) nicht aus dem Index gelöscht. Das neue Stopwort wird einfach nur in die Metadaten eingetragen, so dass es bei künftigen Abfragen und neuen Dokumenten wirken kann. Diese Operation kann es logischerweise nicht für das Entfernen von Stopwörtern geben; denn dazu müssen diese ja neu in den Index aufgenommen werden. Möchte man Stopwörter aus einem Index entfernen, so muss dieser neu gebaut werden.

begin
  ctx_ddl.remove_stopword( 'MY_STOPLIST', 'bank');
  ctx_ddl.remove_stopword( 'MY_STOPLIST', '{...}');
end;
/

ALTER INDEX idx_volltext REBUILD PARAMETERS('replace stoplist MY_STOPLIST')
/

Mehr zum Thema findet sich in der Oracle-Dokumentation: TEXT Application Developers' Guide und TEXT Reference.