Schnellere TEXT-Abfragen mit SNIPPET in Oracle 12c: Forward Index / Save Copy

Das Indizieren binärer Dokumente, wie PDF- oder Office-Formate, ist mit Oracle TEXT bekanntlich überhaupt kein Problem. Einfach in den Index-Parametern den "FILTER" aktivieren und los geht's.

create index idx_ppt_folien on ppt_folien(FOLIENSATZ)
indextype is ctxsys.context
parameters('filter ctxsys.auto_filter');

Und danach kann man auch schon abfragen ...

select dateiname from ppt_folien where contains (FOLIENSATZ, '"Oracle TEXT"') > 0;

DATEINAME
--------------------------------------------------------------------------------
XMLDB_US.ppt
11gDWHFeatures_us.ppt
11g_dwh_us.ppt
TextMining_11g_200912_ccz.ppt
SecureFiles_odd_201002_us_cc.ppt
OracleText-ccz-201410.pptx
01_BigDataWorkshop_BigData_Overview_032012_ccz.pptx
:

38 Zeilen ausgewählt.

Abgelaufen: 00:00:00.99

Achtet auf die Ausführungszeit - die ist hier völlig OK. Nun reicht der Dateiname zur Anzeige natürlich nicht aus; man hätte schon gerne den Textausschnitt, in dem das Suchwort gefunden wurde - auch das ist für Oracle TEXT kein Problem - dazu gibt es die Funktion CTX_DOC.SNIPPET; die neue Query sieht also so aus.

select 
  dateiname, 
  ctx_doc.snippet('IDX_PPT_FOLIEN',rowid,'"Oracle TEXT"','**','**') as snippet 
from ppt_folien where contains (FOLIENSATZ, '"Oracle TEXT"') > 0;

DATEINAME                      SNIPPET
------------------------------ --------------------------------------------------------------------
XMLDB_US.ppt                   **Oracle TEXT**-Index (Alle Speicherungsformen) ¦ Volltextrecherche
11gDWHFeatures_us.ppt          Funktionstests mit neuen **Oracle Text** Indizes (CDI)
TextMining_11g_200912_ccz.ppt  in einer Tabelle abgelegt ¦ **Oracle TEXT**-Abfragesyntax
:

38 Zeilen ausgewählt.

Abgelaufen: 00:00:16.10

Das funktioniert sehr gut, aber die Performance lässt doch sehr zu wünschen übrig; es sind nun 16 Sekunden (!) - vorher war es eine. Der Grund ist schnell gefunden: um das Snippet generieren zu können, muss Oracle TEXT das Binärdokument nochmals abrufen, nochmals filtern und dann die Snippets berechnen. Dabei geht viel Zeit verloren. Bislang wurde spätestens an dieser Stelle empfohlen, die Dokumente einfach in gefilterter Form in eine eigene Tabelle abzulegen und diese dann zu indizieren.

Ab Oracle 12.1 bietet Oracle TEXT ein Feature an, welches uns die Arbeit dafür abnimmt: Der Forward Index und das Save Copy-Feature. Das Speichern des gefilterten Textes übernimmt dann Oracle TEXT für uns; die Verwaltung der eigenen Tabelle müssen wir nicht mehr selbst übernehmen. Zusätzlich speichert Oracle TEXT im Forward Index weitere Informationen über die konkreten Wortpositionen, was das Berechnen der Snippets nochmal beschleunigt. Und das ganze geht so.

• 1. Neue Storage Preference erzeugen und die Attribute SAVE_COPY und FORWARD_INDEX setzen:
  

  begin
    ctx_ddl.create_preference('MY_STORAGE','BASIC_STORAGE');
    ctx_ddl.set_attribute('MY_STORAGE','forward_index', 'TRUE');
    ctx_ddl.set_attribute('MY_STORAGE','save_copy', 'PLAINTEXT');
    ctx_ddl.set_attribute('MY_STORAGE','save_copy_max_size', '0');
  end;
  

  Wenn bereits eine Storage Preference existiert, nimmt man natürlich diese. Das Attribut SAVE_COPY wird entweder mit dem Wert NONE, PLAINTEXT oder HTML versehen. PLAINTEXT reicht für das Erzeugen der Snippets aus; möchte man auch HTML-Vorschauversionen bereitstellen (Preview-Funktion), so sollte man HTML wählen. Das Attribut SAVE_COPY_MAX_SIZE legt eine Obergrenze für die Größe des gefilterten Dokumentes fest.
• 2. Index neu erstellen - mit der neuen Storage Preference
  

  drop index idx_ppt_folien;
  
  Index wurde gelöscht.
  
  create index idx_ppt_folien on ppt_folien(FOLIENSATZ)
  indextype is ctxsys.context
  parameters('filter ctxsys.auto_filter storage MY_STORAGE');
  
  Index wurde erstellt.
  

Und fertig. Setzt man die Abfrage mit dem Snippet nochmal ab, so merkt man, dass diese nun wesentlich schneller ist ...

select 
  dateiname, 
  ctx_doc.snippet('IDX_PPT_FOLIEN',rowid,'"Oracle TEXT"','**','**') as snippet 
from ppt_folien where contains (FOLIENSATZ, '"Oracle TEXT"') > 0;

DATEINAME                      SNIPPET
------------------------------ --------------------------------------------------------------------
XMLDB_US.ppt                   **Oracle TEXT**-Index (Alle Speicherungsformen) ¦ Volltextrecherche
11gDWHFeatures_us.ppt          Funktionstests mit neuen **Oracle Text** Indizes (CDI)
TextMining_11g_200912_ccz.ppt  in einer Tabelle abgelegt ¦ **Oracle TEXT**-Abfragesyntax
:

38 Zeilen ausgewählt.

Abgelaufen: 00:00:00.95

Auf der anderen Seite verbraucht das Speichern des Snippets natürlich Platz - allerdings hält dieser sich, wie man hier sehen kann, in Grenzen. Die Tabelle, welche die Plaintext-Versionen enthält, ist die $D-Tabelle (hier rot markiert); der Forward Index ist in der $O-Tabelle abgelegt (blau markiert). Die verschiedenen Tabellentypen sind hier beschrieben. Es werden (in diesem Fall) etwa 8MB zusätzlich verbraucht - die indizierten PPT-Dokumente sind etwa 750MB.

Natürlich hängt dieses Verhältnis stark von den indizierten Dokumenten ab - Folien enthalten viele Bilder und wenig Text; bei anderen Dokumenttypen ist das anders. Generell ist das neue "Forward Index / Save Copy" Feature jedoch eine gute Variante, um die Performance von "Snippet-Queries" zu verbessern.

select t.table_name, s.segment_name, sum(s.bytes) 
from user_tables t, user_lobs l, user_segments s
where  l.table_name = t.table_name and ( l.segment_name = s.segment_name or t.table_name = s.segment_name)
  and (t.table_name like 'DR$IDX_PPT_FOLIEN$%' or t.table_name = 'PPT_FOLIEN')
group by t.table_name, s.segment_name
order by 1

TABLE_NAME                SEGMENT_NAME                SUM(S.BYTES)
------------------------- ------------------------- --------------
DR$IDX_PPT_FOLIEN$D       SYS_LOB0000135517C00003$$      6.488.064
DR$IDX_PPT_FOLIEN$D       SYS_LOB0000135517C00002$$        131.072
DR$IDX_PPT_FOLIEN$D       DR$IDX_PPT_FOLIEN$D              131.072
DR$IDX_PPT_FOLIEN$I       SYS_LOB0000135503C00006$$      1.245.184
DR$IDX_PPT_FOLIEN$I       DR$IDX_PPT_FOLIEN$I            3.145.728
DR$IDX_PPT_FOLIEN$O       SYS_LOB0000135514C00007$$      1.245.184
DR$IDX_PPT_FOLIEN$O       DR$IDX_PPT_FOLIEN$O              524.288
DR$IDX_PPT_FOLIEN$R       DR$IDX_PPT_FOLIEN$R               65.536
DR$IDX_PPT_FOLIEN$R       SYS_LOB0000135508C00002$$        131.072
PPT_FOLIEN                PPT_FOLIEN                       131.072
PPT_FOLIEN                SYS_LOB0000135467C00003$$    785.580.032

9 Zeilen ausgewählt.

Viel Spaß damit.

Ergebniscache für TEXT-Abfragen: Oracle12c TEXT Query Filter Cache

Heute geht es um den mit Oracle12c neu eingeführten Query Filter Cache. Das ist ein Ergebnis-Cache, speziell für Oracle TEXT Abfragen. Ergebnisse von Oracle TEXT-Abfragen werden - ganz ähnlich zum "normalen" SQL Result Cache - in einem separaten Hauptspeicherbereich abgelegt bei Bedarf wiederverwendet. Kann ein Ergebnis aus dem Cache geholt werden, muss die eigentliche Oracle TEXT Abfrage nicht mehr ausgeführt werden; die Antwortzeit sollte dann wesentlich besser sein.

Gut geeignet ist der Query Filter Cache zum nachträglichen Filtern von Abfrageergebnissen. Dabei kann es um das Anwenden von Zugriffsregeln gehen (Sicherheit); aber auch Anwendungen, die die nachträgliche An- und Abwahl von Dokumentkategorien erlauben, können so durch einen Ergebniscache unterstützt werden. Denn jede An- oder Abwahl einer Kategorie (bspw. durch Setzen einer Checkbox in der Anwendung) führt ja zu einer erneuten Ausführung der Query. Nutzt man den Query Filter Cache, so müssen die Ergebnisse nur noch aus dem Cache geholt und nachgefiltert werden. Das folgende Beispiel stellt den Query Filter Cache vor - los geht's wie immer mit dem Erstellen einer Tabelle.

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

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

commit
/

In der Spalte DOKUMENT soll gesucht werden, anhand der Spalte RESSORT soll dann nachträglich gefiltert werden. Um den Query Filter Cache nutzen zu können, muss eine explizite Storage Preference erstellt und im Index genutzt werden. Dabei wird die Größe des Cache mit dem Attribut QUERY_FILTER_CACHE_SIZE in Bytes angegegen. Dieser Speicherbereich wird dann in der SGA allokiert - die Größe sollte also mit Bedacht - und nach Absprache mit dem DBA bestimmt werden.

begin
 ctx_ddl.create_preference('my_idx_storage', 'basic_storage');
 ctx_ddl.set_attribute('my_idx_storage', 'query_filter_cache_size', '10M');
end;
/  
 

create index idx_text on texttabelle (dokument)
indextype is ctxsys.context
filter by ressort
parameters ('storage my_idx_storage sync (on commit)')
/

Die Data Dictionary View CTX_FILTER_CACHE_STATISTICS gibt Informationen über den Cache - Zu Beginn stehen alle Einträge natürlich auf Null.

SQL> select * from ctx_filter_cache_statistics

FCS_INDEX_OWNER FCS_INDEX_NAME FCS_PARTITION_NAME   FCS_SIZE FCS_ENTRIES FCS_REQUESTS   FCS_HITS
--------------- -------------- ------------------ ---------- ----------- ------------ ----------
TEXT            IDX_TEXT                                   0           0            0          0

Nun geht es daran, den neuen Cache zu nutzen. Das geschieht zunächst, ganz normal, mit Hilfe des Oracle TEXT SQL-Operators CONTAINS. In diesem wird nun aber die spezielle Abfragefunktion CTXFILTERCACHE verwendet - und zwar wie folgt.

select * from texttabelle 
where contains(dokument, 'ctxfiltercache((Wirtschaft), true, true)') > 0;

        ID RESSORT              DOKUMENT
---------- -------------------- ---------------------------------------------------------------------------
         3 Wirtschaft           Wirtschaft: Erneuter Gewinnzuwachs in diesem Jahr

Der neue Cache wird also mit dem Schlüsselwort CTXFILTERCACHE explizit angesprochen. Der erste Parameter (Wirtschaft) ist die Text-Query, die anderen beiden bestimmen das Verhalten des Cache näher. Der zweite Parameter (true) legt fest, ob der SCORE-Wert eines Ergebnisses ebenfalls im Cache abgelegt werden soll. Standardmäßig steht dieser Parameter auf FALSE: Ein aus dem Cache geholtes Ergebnis hat dann einen Score von 100. Wird der Parameter, wie oben, auf TRUE gesetzt, werden die SCORE-Werte ebenfalls in den Cache gelegt und wiederverwendet. Der dritte Parameter bestimmt, ob nur die "TOP-N"-Ergebnisse in den Cache gelegt werden sollen - wieviele das sind, bestimmt die Datenbank automatisch; eine manuelle Festlegung ist nicht möglich. Um TOP-N auf TRUE zu setzen, muss auch der zweite SCORE-Parameter auf TRUE gesetzt sein. Die Dokumentation enthält nähere Details.

Nach der ersten Ausführung der Query kann man anhand der Statistik-View schon erkennen, dass mit dem Cache gearbeitet wurde.

SQL> select * from ctx_filter_cache_statistics

FCS_INDEX_OWNER FCS_INDEX_NAME FCS_PARTITION_NAME   FCS_SIZE FCS_ENTRIES FCS_REQUESTS   FCS_HITS
--------------- -------------- ------------------ ---------- ----------- ------------ ----------
TEXT            IDX_TEXT                               13096           1            1          0

Anhand von FCS_ENTRIES sieht man, dass nun eine Abfrage mit dem Cache gearbeitet hat. Es erfolgte eine Anfrage an den Cache (FCS_REQUESTS), aber da die Abfrage zum ersten Mal abgesetzt wurde, ergab sich noch kein Hit (FCS_HITS). Anders sieht es aus, wenn man die Abfrage nochmals absetzt: Nun ergeben sich zwei Anfragen und ein Cache-Hit. Die Abfrage wurde also aus dem Cache bedient.

SQL> select * from ctx_filter_cache_statistics

FCS_INDEX_OWNER FCS_INDEX_NAME FCS_PARTITION_NAME   FCS_SIZE FCS_ENTRIES FCS_REQUESTS   FCS_HITS
--------------- -------------- ------------------ ---------- ----------- ------------ ----------
TEXT            IDX_TEXT                               13096           1            2          1

Nun zu einem der eigentlichen Anwendungsfälle. Wir suchen in der oberen Tabelle nach Obama.

SQL> select * from texttabelle where contains(dokument,  'ctxfiltercache((Obama), true, true)') > 0 ;

        ID RESSORT              DOKUMENT
---------- -------------------- ----------------------------------------------------------------------------
         5 Politik              Wer wird US-PrSsident? Obama und Clinton machen Wahlkampf
         7 USA                  Wahlkampf in den USA: Clinton und Obama LIVE zu sehen

2 Zeilen ausgewählt.

SQL> select * from ctx_filter_cache_statistics;

FCS_INDEX_OWNER FCS_INDEX_NAME FCS_PARTITION_NAME   FCS_SIZE FCS_ENTRIES FCS_REQUESTS   FCS_HITS
--------------- -------------- ------------------ ---------- ----------- ------------ ----------
TEXT            IDX_TEXT                               13096           1            1          0

1 Zeile wurde ausgewählt.

Diese Abfrage liefert Ergebnisse aus zwei Ressorts. Wieder haben wir einen Request an den Cache und (noch) keinen Hit. Nun nehmen wir aber an, dass der Anwender in der Benutzeroberfläche in der Checkbox für die Ressorts klickt - und zwar wählt er das Ressort "USA" ab. Das führt zu einer neuen Query, bei der ein SDATA-Ausdruck anstelle einer AND-Verknüpfung auf SQL-Ebene verwendet wird. Dies dient der Vermeidung von Mixed Queries - was bei der Arbeit mit Oracle TEXT generell zu empfehlen ist (siehe Blog Posting Abfrage-Optimierung mit Composite Domain Index).

select * from texttabelle where contains(
  dokument,  
  'ctxfiltercache((Obama), true, true) and sdata(ressort = ''Politik'')'
) > 0;

        ID RESSORT              DOKUMENT
---------- -------------------- --------------------------------------------------------------------------------
         5 Politik              Wer wird US-PrSsident? Obama und Clinton machen Wahlkampf

1 Zeile wurde ausgewählt.

SQL> select * from ctx_filter_cache_statistics;

FCS_INDEX_OWNER FCS_INDEX_NAME FCS_PARTITION_NAME   FCS_SIZE FCS_ENTRIES FCS_REQUESTS   FCS_HITS
--------------- -------------- ------------------ ---------- ----------- ------------ ----------
TEXT            IDX_TEXT                               13096           1            2          1

1 Zeile wurde ausgewählt.

Obgleich die Gesamt-Abfrage eine andere war, wurde der Cache angefragt und genutzt. Der Query Filter Cache ist also eine gute Unterstützung für das nachträgliche Filtern von Oracle TEXT Abfrageergebnissen, wie es in vielen Anwendungen mittlerweile Standard ist.

Der Filter Cache muss in der Anwendung jedoch explizit angesprochen werden; eine automatische Nutzung für existierende Anwendungen ist nicht möglich - dazu sind die möglichen Seiteneffekte, speziell beim Score, einfach zu wesentlich. Solange also CTXFILTERCACHE nicht innerhalb CONTAINS verwendet wird, wird auch kein Cache genutzt. Als Entwickler muss man eine explizite Entscheidung für die Nutzung des Cache treffen und dies die CONTAINS-Abfragen entsprechend kodieren.

Welche DR$-Tabellen gibt es und wozu sind sie gut?

Den meisten Anwendern fällt recht schnell auf, dass sich nach dem Erstellen eines Oracle TEXT-Index einige zusätzliche Tabellen im Datenbankschema befinden.

SQL> create index FT_PO on PURCHASEORDER_TAB (XML_DOCUMENT)
  2  indextype is ctxsys.context
  3  /

Index wurde erstellt.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PURCHASEORDER_TAB              TABLE
DR$FT_PO$N                     TABLE
DR$FT_PO$R                     TABLE
DR$FT_PO$K                     TABLE
DR$FT_PO$I                     TABLE
:                              :

Diese Tabellen enthalten den eigentlichen Textindex - meist sind es vier Tabellen, wie wir noch sehen werden, können es aber durchaus mehr werden. Das Namensschema ist immer gleich.

• Es beginnt mit dem Präfix DR
• Bei nichtpartitionierten Indizes folgt ein "$", bei partitionierten Indizes ein "#"
• Dann folgt der Name des Volltextindex
• Bei einem partitionierten Index schließt sich eine "Partitions-ID" direkt an den Indexnamen an
• Es folgt wieder ein "$"
• Abschließend folgt ein Suffix, welches die genaue Aufgabe der Tabelle bezeichnet - im folgenden werden die einzelnen Tabellentypen näher erläutert

Ein Oracle TEXT Index besteht aber immer aus wenigstens vier Tabellen - in Oracle12c können es aber bis zu neun werden.

$I: Token-Tabelle

 ----------------------------------------- -------- ------------------
 TOKEN_TEXT                                NOT NULL VARCHAR2(64)
 TOKEN_TYPE                                NOT NULL NUMBER(10)
 TOKEN_FIRST                               NOT NULL NUMBER(10)
 TOKEN_LAST                                NOT NULL NUMBER(10)
 TOKEN_COUNT                               NOT NULL NUMBER(10)
 TOKEN_INFO                                         BLOB
 ----------------------------------------- -------- ------------------

Die Token-Tabelle ist die eigentliche Indextabelle. Sie speichert die Tokens, also die einzelnen Wörter, die sich durch die Zerlegung des Fließtexts ergeben haben, in der Spalte TOKEN_TEXT ab. Die Spalte TOKEN_INFO enthält (in binärer Form) die Informationen, in welchen Dokumenten, an welchen Stellen das jeweilige Token vorkommt. Die übrigen Spalten sind Hilfsspalten, damit das Interpretieren des BLOB effizienter wird.

Wird eine Volltextabfrage ausgeführt, so schaut Oracle TEXT zuerst in diese Tabelle - anhand der Suchbegriffe werden die BLOBs aus TOKEN_INFO ausgelesen und weiter verarbeitet.

$R: Mapping von DOCID auf ROWID

 ----------------------------------------- -------- ------------
 ROW_NO                                    NOT NULL NUMBER(3)
 DATA                                               BLOB
 ----------------------------------------- -------- ------------

Oracle Text arbeitet intern nicht mit ROWIDs als Zeiger auf die Tabellenzeilen, sondern mit DOCIDs. Bei der Indizierung erhält die erste Zeile die DOCID 1 und dann wird weitergezählt. Der Grund dafür ist, dass ein Volltextindex eine invertierte Liste ist - im Gegensatz zu einem "normalen" Index zeigen mehrere Einträge auf eine Tabellenzeile. Die Verwendung von ROWIDs würde zu extrem großen Indizes führen, so dass man die sparsameren DOCIDs verwendet. Die bereits erwähnte Spalte TOKEN_INFO in der $I-Tabelle liefert also DOCIDs zurück. Damit Oracle TEXT aber Tabellenzeilen zurückliefern kann, braucht es eine Mapping-Tabelle - anhand der $R-Tabelle kann nun zu jeder gefundenen DOCID die ROWID herausgesucht werden. Die ROWID zeigt dann schließlich auf die gewünschte Tabellenzeile. Normalerweise ist ein Oracle Text Index so aufgesetzt, dass diese Tabelle immer im Hauptspeicher residiert, also zur Abfragezeit keine I/O-Last generiert.

$K: Mapping von ROWID auf DOCID

 ----------------------------------------- -------- ----------------------------
 DOCID                                              NUMBER(38)
 TEXTKEY                                   NOT NULL ROWID
 ----------------------------------------- -------- ----------------------------

Die $K-Tabelle ist das "Gegenstück" zur $R-Tabelle - anhand einer gegebenen ROWID findet sie die für den Textindex relevante DOCID heraus. Für Abfragen ist diese Tabelle weniger wichtig; benötigt wird sie aber bei DML-Operationen auf bereits indizierte Zeilen - wird beispielsweise eine Zeile mit SQL DELETE gelöscht, dann braucht Oracle TEXT die DOCID, um das Löschen auch im Textindex zu vermerken.

$N: Negativliste - enthält gelöschte Dokumente

 ----------------------------------------- -------- ----------------------------
 NLT_DOCID                                 NOT NULL NUMBER(38)
 NLT_MARK                                  NOT NULL CHAR(1)
 ----------------------------------------- -------- ----------------------------

Bekanntlich arbeitet Oracle TEXT asynchron (der Parameter TRANSACTIONAL bleibt hier außer Acht). Eine SQL INSERT-Anweisung bewirkt, dass die ROWID der Tabellenzeile in die PENDING-Tabelle eingetragen und damit "zur Indizierung vorgesehen" wird (die tatsächliche Indizierung erfolgt durch eine SYNC-Operation). Eine SQL DELETE Anweisung bewirkt, dass die Datenbank die DOCID ermittelt ($K-Tabelle) und diese dann in die Negativliste einträgt. Bei Abfragen ermittelt Oracle TEXT die Treffermenge ganz normal und filtert dann die Einträge der Negativliste heraus. Ein SQL UPDATE wird wie ein DELETE, gefolgt von einem INSERT, behandelt. Das hat den interessanten Effekt, dass neu eingefügte Dokumente erst nach dem SYNC sichtbar werden, gelöschte dagegen sofort verschwinden und mit SQL UPDATE veränderte Einträge ebenfalls bis zum nächsten SYNC "unsichtbar" werden.

$P: Hilfstabelle für den Substring-Index

 ----------------------------------------- -------- ----------------------------
 PAT_PART1                                 NOT NULL VARCHAR2(61)
 PAT_PART2                                 NOT NULL VARCHAR2(64)
 ----------------------------------------- -------- ----------------------------

Der Substring-Index muss mit Hife der Wordlist-Preference explizit eingeschaltet werden und unterstützt Abfragen mit einem Wildcard auf der linken Seite. Sucht man bspw. nach dem Text %DATENBANK, so ist klar, dass die $I-Tabelle (Spalte TOKEN_TEXT) prinzipiell komplett geparst werden muss - anstelle des Wildcard können ja beliebige Zeichen stehen. Bei sehr großen Indizes kann das ein Problem sein - schließlich wird auch die $I-Tabelle nun sehr groß. Der Substring-Index legt die $P-Tabelle als Hilfstabelle an - das Token ORACLEDATENBANK würde in dieser Tabelle wie folgt abgelegt:

PAT_PART1            PAT_PART2
-------------------- --------------------
ORACLEDATENB         ANK
ORACLEDATEN          BANK
ORACLEDATE           NBANK
ORACLEDAT            ENBANK
:                    :
ORACLE               DATENBANK
:

Ist diese Tabelle vorhanden, so werden die Suchbegriffe mit einer Wildcard links zuerst anhand dieser Tabelle über die Spalte PAT_PART2 aufgelöst - mit der sich ergebenden Tokenliste wird danach normal verfahren. Die Größe der $P-Tabelle liegt typischerweise zwischen 10 und 20 Prozent der Größe des Textindex.

$S: Hilfstabelle für strukturierte Elemente (SDATA-Sections)

 ----------------------------------------- -------- ----------------------------
 SDATA_ID                                  NOT NULL NUMBER
 SDATA_LAST                                NOT NULL NUMBER
 SDATA_DATA                                         RAW(2000)
 ----------------------------------------- -------- ----------------------------

In der $S-Tabelle werden strukturierte Bestandteile des Index gespeichert - dies ist seit Oracle11g mit den Composite Domain Indexes möglich. Oracle TEXT erlaubt auf diesen SDATA-Section zusätzlich auch Suchen wie <, >, BETWEEN und andere. SDATA-Sections haben einen Datentypen - unterstützt ist neben VARCHAR2 auch NUMBER oder DATE, was für Abfragen wichtig ist (intern speichert die Tabelle die Daten im RAW-Format ab). SDATA-Sections können entweder durch die Klauseln FILTER BY und ORDER BY im CREATE INDEX-Kommando oder durch die Erstellung einer Section Group mit expliziten SDATA Sections deklariert werden.

$G: Automatic Near Realtime Index (Oracle12c)

 ----------------------------------------- -------- ----------------------------
 TOKEN_TEXT                                NOT NULL VARCHAR2(64)
 TOKEN_TYPE                                NOT NULL NUMBER(10)
 TOKEN_FIRST                               NOT NULL NUMBER(10)
 TOKEN_LAST                                NOT NULL NUMBER(10)
 TOKEN_COUNT                               NOT NULL NUMBER(10)
 TOKEN_INFO                                         BLOB
 ----------------------------------------- -------- ----------------------------

Die $G-Tabelle wurde mit dem neuen Feature Automatic Near Realtime Indexing in Oracle12c eingeführt; sie sieht genauso aus wie die Token-Tabelle ($I-Tabelle). Das Feature wurde bereits in einem Blog Posting näher beschrieben.

$E: Hilfstabelle für XML-Namespaces (Oracle12c)

 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(12)
 NAMESPACE                                          VARCHAR2(4000)
 LNAME                                     NOT NULL VARCHAR2(256)
 NS                                                 VARCHAR2(100)
 ----------------------------------------- -------- ----------------------------

Ein weiteres neues Feature in Oracle12c ist die Unterstützung von XQuery Fulltext, also der Volltextsuche in XML-Dokumenten mit vollständiger Unterstützung des XML-Datenmodells. Die $E-Tabelle speichert dabei XML-Zusatzinformationen ab, die im "normalen" Volltextindex keinen Platz finden - hier sind vor allem die XML-Namespaces zu nennen, die denn auch vor Oracle12c nicht von Oracle TEXT unterstützt wurden. Das Feature wurde ebenfalls bereits in einem Blog-Posting näher beschrieben.

$D: Hilfstabelle für das Save-Copy Feature (Oracle12c)

 ----------------------------------------- -------- ----------------------------
 DOCID                                     NOT NULL NUMBER(10)
 ATTRIBUTES                                         BLOB
 DOC                                                BLOB
 CONFIG                                             VARCHAR2(2000)
 FLAG                                      NOT NULL NUMBER(5)

Auch das Save Copy Feature wurde in Oracle12c eingeführt. Ist es aktiviert, so speichert Oracle TEXT die Plaintext- oder gefilterte Version eines Binärformates wie PDF, DOC, PPT oder anderen in der $D-Tabelle ab. Zum Generieren von SNIPPETs, MARKUPs oder HIGHLIGHT-Dokumenten können dann die in dieser Tabelle abgelegten Kopien verwendet werden - früher musste das Originaldokument hierfür nochmals gefiltert werden, was sich natürlich negativ auf die Performance auswirkte ...

Oracle TEXT in Oracle12c: Neues Feature 'Pattern Stopclass'

Heute festgestellt, dass das letzte Blog Posting schon fast 3 Monate zurückliegt. Nun wird es aber Zeit: Das neue Datenbankrelease Oracle12c bringt auch im Bereich Oracle TEXT einige neue Features mit sich - diese werden wir nach und nach in diesem Blog besprechen. Heute geht es um die neue Möglichkeit, eine Stopwortliste nicht nur mit einzelnen Wörtern, sondern mit Stop-Patterns zu versehen. Hierauf haben viele sicherlich schon lange gewartet. Ein Beispiel: Wir erzeugen eine Tabelle und füllen diese mit ein paar "Wörtern" ...

create table tab_stopclasstest (
  textcol  varchar2(200)
)
/

insert into tab_stopclasstest values ('200');
insert into tab_stopclasstest values ('100');
insert into tab_stopclasstest values ('99');
insert into tab_stopclasstest values ('Oracle TEXT');
insert into tab_stopclasstest values ('A100');
insert into tab_stopclasstest values ('01.09.2012');

Wenn es nun an die Definition der Stopwörter geht, so konnte man bislang mit CTX_DDL.CREATE_STOPLIST eine Stopliste erzeugen und mit CTX_DDL.ADD_STOPWORD die Wörter ("Oracle") hinzufügen.

begin
  ctx_ddl.create_stoplist(
    stoplist_name => 'NEUE_STOPLISTE'
  );
  ctx_ddl.add_stopword ('NEUE_STOPLISTE', 'Oracle');
  ctx_ddl.add_stopclass('NEUE_STOPLISTE', 'NUMBERS');
end;
/

Aber genau dabei beginnt in vielen Fällen das Problem: Denn wenn Zahlen nicht indiziert werden sollen, ist es nahezu unmöglich, im Vorfeld alle möglichen Varianten als Stopwörter zu bestimmen. Die Stopclasses machen nun genau das möglich (übrigens kann man die Stopklasse NUMBERS schon in Oracle 11.2 verwenden) - aber dort eben nicht mehr. Anders in Oracle12c ...

begin
  ctx_ddl.create_stoplist(
    stoplist_name => 'NEUE_STOPLISTE'
  );
  ctx_ddl.add_stopword ('NEUE_STOPLISTE', 'Oracle');
  ctx_ddl.add_stopclass('NEUE_STOPLISTE', 'NUMBERS');
  ctx_ddl.add_stopclass('NEUE_STOPLISTE', 'KLASSE_1','[A-Z]\d+');
end;
/

CTX_DDL.ADD_STOPCLASS nimmt drei Parameter entgegen. Der erste ist, wie schon bei ADD_STOPWORD, der Name der Stopliste. Danach kommt die "Stopklasse" - Oracle bringt eine vordefinierte Klasse mit: NUMBERS, die, wie gesagt, schon in 11.2 vorhanden ist. Ab Oracle 12.1 kann man aber auch einen anderen Namen eintragen - und dann braucht es noch den dritten Parameter: Dort wird ein regulärer Ausdruck hinterlegt, der die zu ignorierenden Wörter erfasst. In obigem Beispiel wäre das genau ein Buchstabe, gefolgt von mindestens einer Zahl. Legt man, basierend auf dieser Stopliste einen Index auf die obige Tabelle an, so wird dieser nur ein einziges Token enthalten: TEXT.

create index ft_stopclasstest on tab_stopclasstest (textcol)
indextype is ctxsys.context 
parameters ('stoplist neue_stopliste')
/

select token_text from dr$ft_stopclasstest$i
/

TOKEN_TEXT
-----------------------------------------
TEXT

Viel Spaß beim Ausprobieren.