Tablespace und mehr: Storage-Klausel für den Oracle-TEXT Index

Nachdem uns jüngst die Frage erreichte, wie man den Tablespace für einen Oracle TEXT-Index festlegen
kann ... machen wir dazu gleich ein Posting: Im Gegensatz zu einem normalen Textindex kann man nicht
einfach eine TABLESPACE-Klausel an das CREATE INDEX-Kommando anhängen. Das folgende funktioniert also
nicht.

create index ft_dokument on dokumente_tab(dokument)
indextype is mdsys.context
tablespace TS_IDXDATA
/

Bei Oracle TEXT ist hier etwas mehr zu tun. Oracle TEXT erlaubt unglaublich viel Flexibilität, was
auf der anderen Seite natürlich immer Konfigurationsaufwand bedeutet. Aber zum Thema: Ein Oracle TEXT Index
besteht intern stets aus mindestens vier Tabellen: $I, $R, $K und $N. Je nachdem, welche zusätzlichen
Features aktiviert wurden, kommen noch Tabellen dazu. Und jede dieser Tabellen kann ihre eigene
Storage-Klausel bekommen. Wie immer bei Oracle TEXT wird auch hier mit den Preference-Objekten
gearbeitet - diese physikalischen Details werden in eine Storage Preference gespeichert (nicht zu
verwechseln mit der Datastore Preference, die beschreibt, wo die zu indizierenden Daten herkommen). Wir
wollen also unseren TEXT Index in den Tablespace TS_IDXDATA legen. Also brauchen alle Tabellen, die
Oracle TEXT unter der Haube erzeugt, eine entsprechende Storage-Klausel.

begin
  ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');

  ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE', 'tablespace ts_idxdata LOB(DATA) STORE AS (CACHE)');
  ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE', 'tablespace ts_idxdata compress 2');
  ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE', 'tablespace ts_idxdata ');
end;
/

Wie man am Skript sehr schön sehen kann, werden hier die einzelnen Storage Klauseln für alle
an einem Textindex beteiligten Tabellen hinterlegt. Hier kann alles eingetragen werden, was auch
in die Storage-Klausel einer "normalen" Tabelle hineinkann. Wichtig sind allerdings die Einstellungen
LOB (DATA) STORE AS (CACHE) bei der R_TABLE_CLAUSE und COMPRESS 2 bei der I_INDEX_CLAUSE. Diese
Defaulteinstellungen sind sehr wichtig und es ist für die Performance ganz entscheidend, diese
bei eigenen Storage-Klauseln ebenfalls zu übernehmen. Hieran sollte man immer denken!

Die Nutzung der Storage Preference ist nun wieder einfach:

create index ft_dokument on dokumente_tab(dokument)
indextype is mdsys.context
parameters ('storage mystore')
/

Ansonsten gilt, wie gesagt: Es werden Standard-Storage-Klauseln für Tabellen hinterlegt. Neben dem
Tablespace selbst können also alle möglichen Details eingetragen werden. Am wichtigsten sind jedoch
wahrscheinlich in allen Fällen die Einstellungen zum Tablespace, zur Extent-Verwaltung oder zum Cache.
Weitere Details lassen sich in der Dokumentation nachlesen.

Einzelne Zeilen vom Index ausnehmen - Parameter "FORMAT COLUMN"

Heute geht es um einen eher unscheinbaren Parameter beim Erstellen eines Oracle TEXT Index:
Mit FORMAT COLUMN kann zum einen der Filter für Binärdokumente gesteuert werden - darüber hinaus
kann man damit aber auch festlegen, ob das Dokument überhaupt indiziert werden soll. Und das geht so - wir fangen mit einer einfachen Tabelle an:

create table test(
  id     number(10) not null,
  text   varchar2(4000) not null,
  format varchar2(10) not null,
  constraint pk_test primary key (id),
  constraint ck_format check (format in ('TEXT','BINARY','IGNORE'))
)
/

insert into test values (1, 'Dieses Dokument wird indiziert.', 'TEXT')
/
insert into test values (2, 'Hierfür stehen keine Einträge im Textindex', 'IGNORE')
/

Die Indexerstellung funktioniert wie immer - nur, dass der Parameter FORMAT_COLUMN mitgegeben wird.

create index ft_test on test (text)
indextype is ctxsys.context
parameters ('format column format')
/

Danach schauen wir in die $I-Tabelle - dort stehen in der Tat nur Tokens für das erste
Dokument - die Spalte FORMAT enthält hier "TEXT".

SQL> select token_text from dr$ft_test$i;

TOKEN_TEXT
------------------------------------------
DIESES
DOKUMENT
INDIZIERT
WIRD

4 Zeilen ausgewählt.

Aber was passiert, wenn man die Spalte FORMAT ändert - angenommen, wir ändern den Inhalt
für das zweite Dokument von IGNORE in TEXT ...

SQL> update test set format = 'TEXT' where id = 2
/

1 Zeile aktualisiert.

Wenn man nun in der Dictionary View CTX_USER_PENDING nachsieht, ändert sich diese
(obwohl mit der Spalte FORMAT eine "index-relevante" Spalte geändert wurde, nicht. Ein CTX_DDL.SYNC_INDEX
bewirkt also ebenfalls nichts. Man muss
also entweder im Update-Kommando selbst oder mit einem Trigger sicherstellen, dass bei einer
Änderung der Spalte FORMAT auch die Indexspalte selbst "angefasst" wird - das könnte so
aussehen ...

create or replace trigger tr_upd_format
before update of format on test
for each row
begin
  if :old.format != :new.format then 
    :new.text := :old.text;
  end if;
end;
/

Von nun an wirkt eine Änderung an der Spalte FORMAT wie eine Änderung am Dokument - nach der
Index-Synchronisierung ist das Dokument indiziert bzw. aus dem Index entfernt (je nachdem, wie
die Spalte FORMAT gesetzt wurde).

SQL> update test set format = 'IGNORE' where id = 1; 

1 Zeile wurde aktualisiert.

SQL> update test set format = 'TEXT' where id = 2; 

1 Zeile wurde aktualisiert.

SQL> select pnd_rowid from ctx_user_pending; 

PND_ROWID
------------------------------ 
AAAmVGAAFAAAMWTAAA
AAAmVGAAFAAAMWTAAB

2 Zeilen ausgewählt.

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

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> exec ctx_ddl.optimize_index('FT_TEST', ctx_ddl.optlevel_full); 

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select token_text from dr$ft_test$i; 

TOKEN_TEXT
----------------------------------------------------------------
EINTRÄGE
HIERFÜR
IM
KEINE
STEHEN
TEXTINDEX

6 Zeilen ausgewählt.

Mehrere Sprachen in einer Tabelle unterstützen: MULTI_LEXER

Die Lexer Einstellungen beeinflussen die Art der Speicherung von Texten und den Zugriff auf den Textindex. Stand Datenbank Version 11g gibt es unterschiedliche Lexer-Typen wie zum Beispiel BASIC_LEXER, MULTI_LEXER und WORLD_LEXER. Die Definitionen zu den unterschiedlichen Lexer-Typen sind in Kurzfassung in der Tabelle zu finden.
Wir wollen uns im folgenden Blogeintrag mit dem MULTI_LEXER beschäftigen. Der MULTI_LEXER ist eine Art "Container" für verschiedene Sub-Lexer. Die Nutzung kann in multi-lingualen Umgebungen sinnvoll sein. Im Gegensatz zum WORLD_LEXER erfordert der MULTI_LEXER allerdings eine zusätzliche Language Spalte. Dies bedeutet die Tabellen müssen als zusätzliche Metadateninformation eine Spalte mit der Sprachzugehörigkeit beinhalten.

Nehmen wir als Beispiel eine Tabelle, die nicht nur deutsche sondern auch englische Dokumente abspeichert. Das unterschiedliche Vorkommen wird mit einem Eintrag in einer Language Spalte SPRACHE dokumentiert. Es sollen dabei unterschiedliche Spracheigenschaften wie zum Beispiel Groß-Kleinschreibung und unterschiedliche Stopplisten berücksichtigt werden.
Dazu definieren wir folgende Präferenzen für einen deutschen und einen englischen Lexer.

execute ctx_ddl.drop_preference('global_lexer'); 
execute ctx_ddl.drop_preference('english_lexer');
execute ctx_ddl.drop_preference('german_lexer');

begin
ctx_ddl.create_preference('english_lexer', 'basic_lexer'); 
ctx_ddl.set_attribute('english_lexer','SKIPJOINS','_');
ctx_ddl.set_attribute('english_lexer','mixed_case','no'); 

ctx_ddl.create_preference('german_lexer', 'basic_lexer');
ctx_ddl.set_attribute('german_lexer','mixed_case','yes'); 
ctx_ddl.set_attribute('german_lexer','composite','german'); 
ctx_ddl.set_attribute('german_lexer','alternate_spelling','german');
end;
/

Zusätzlich werden nun auch sprach-spezifische Stopplisten erzeugt. Beim Anlegen des Index, wird je nach Dokumentsprache, das entsprechende Stoppwort ausgesondert.

execute ctx_ddl.drop_stoplist('mymulti_stoplist');

begin
ctx_ddl.create_stoplist('mymulti_stoplist', 'MULTI_STOPLIST');
ctx_ddl.add_stopword('mymulti_stoplist', 'den','german');
ctx_ddl.add_stopword('mymulti_stoplist', 'this','english');
end;
/

Nun definieren wir die MULTI_LEXER Präferenz.

execute ctx_ddl.create_preference('global_lexer','multi_lexer');

Im nächsten Schritt müssen die sprach-spezifischen Lexer mit einem ADD_SUB_LEXER Aufruf hinzugefügt werden. Wichtig ist eine Default Sprache zu definieren - in unserem Fall Deutsch.

begin
ctx_ddl.add_sub_lexer('global_lexer', 'default', 'german_lexer');  
ctx_ddl.add_sub_lexer('global_lexer', 'english','english_lexer', 'eng'); 
end;
/

Nun erzeugen wir die Tabelle mit der zusätzlichen Language Spalte SPRACHE und fügen einige Einträge hinzu.

DROP TABLE globaldoc PURGE;

CREATE TABLE globaldoc ( 
   doc_id       NUMBER, 
   sprache      VARCHAR2(30), 
   text         CLOB); 

INSERT INTO globaldoc values (10,'english','this is not America');
INSERT INTO globaldoc values (12,'english','this is user_110');
INSERT INTO globaldoc values (13,'english','this or that');
INSERT INTO globaldoc values (1,'deutsch','Wer den Pfennig nicht ehrt');
INSERT INTO globaldoc values (2,'deutsch','das ist user_111');
INSERT INTO globaldoc values (5,'deutsch','München');
INSERT INTO globaldoc values (6,'deutsch','Muenchen');
COMMIT;

Nun wird der Index erzeugt.

DROP INDEX globalx FORCE;

CREATE INDEX globalx ON globaldoc (text) 
  indextype is ctxsys.context 
  parameters ('lexer global_lexer 
               language column sprache 
               stoplist mymulti_stoplist'); 

Um mehr über den Index zu erfahren, listen wir die Tokenliste auf. Der Eintrag USER110 ist zu finden, allerdings nicht USER111. Das ist ein Beispiel für die sprach-spezifische Präferenz SKIPJOINS.

SQL> SELECT token_text FROM dr$globalx$i;
TOKEN_TEXT
----------------------------------------------------------------
111
AMERICA
IS
Muenchen
München
NOT
OR
Pfennig
THAT
USER110
Wer
das
den
ehrt
ist
nicht
user

Zum Abfragezeitpunkt untersucht der MULTI_LEXER die Sessionsprache und nutzt die entsprechende Sub-Lexer Präferenz und die aktive stoplist zum Parsen der Abfrage. Falls die Sprache nicht gesetzt ist, wird der Default Lexer verwendet. Um die Abfrage auf eine bestimmte Sprache einzuschränken, kann eine zusätzliche Abfrage-Erweiterung auf die Language Spalte SPRACHE sinnvoll sein.
Folgende Abfragen dokumentieren das Verhalten.

ALTER SESSION SET NLS_LANGUAGE='GERMAN';

-- wegen MIXED_CASE
-- SElECT * FROM globaldoc WHERE contains(text, 'Pfennig')>0;
-- oder
SELECT * FROM globaldoc WHERE contains(text, 'Pfennig')>0 AND sprache='deutsch';
    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
         1 deutsch         Wer den Pfennig nicht ehrt

SELECT * FROM globaldoc WHERE contains(text, 'PFENNIG')>0;
no rows selected

SELECT * FROM globaldoc WHERE contains(text, 'pfennig')>0;
no rows selected

-- wegen alternate_spelling
SELECT * FROM globaldoc WHERE contains(text, 'Muenchen')>0;

    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
         5 deutsch         München
         6 deutsch         Muenchen

-- nun englische Dokumente
ALTER SESSION SET NLS_LANGUAGE=ENGLISH;

-- wegen MIXED_CASE
SELECT * FROM globaldoc WHERE contains(text, 'AMERICA')>0;
    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
        10 english         this is not America

SELECT * FROM globaldoc WHERE contains(text, 'america')>0;
    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
        10 english         this is not America

SELECT * FROM globaldoc WHERE contains(text, 'America')>0;
    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
        10 english         this is not America

-- wegen SKIPJOINS und stoplist mit 'this'
SELECT * FROM globaldoc WHERE contains(text, 'user110 or this')>0 AND sprache='English';

    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
        12 english         this is user_110


SELECT * FROM globaldoc WHERE contains(text, 'user110 or that')>0 AND sprache='English';

    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
        12 english         this is user_110
        13 english         this or that

Webseminar: Oracle TEXT und APEX

Das Thema Suchen ist in Geschäftsanwendungen allgegenwärtig. Seien es Kunden, Produkte, Lieferanten oder Geschäftsstellen - immer wieder muss man "etwas finden". Jeder Leser dieses Blogs weiss, dass Oracle TEXT hierfür genau richtig ist.

Doch wie bringt man Oracle TEXT möglichst schnell und einfach zum Anwender? Schließlich arbeitet kein Endanwender direkt mit SQL*Plus. Hier bietet sich Application Express an - damit lassen sich schnell und einfach Webanwendungen bauen.

Wie man Oracle TEXT und APEX gemeinsam nutzt, dazu gibt es ein Webseminar am 11. August 2011 um 11:00 Uhr an. Alles, was man braucht, ist ein Browser, ein Telefon für die Audiokonferenz (Gebührenfrei) und eine Stunde Zeit. Die Teilnahme ist kostenlos - am besten gleich anmelden.

Unscharfe Namenssuche (Name Search) mit NDATA

Vor einiger Zeit als das Feature Name Search mit 11.2.0.2 zur Verfügung gestellt wurde, haben wir schon eine Einführung in das Thema gegeben. Der Blogeintrag dazu ist hier zu finden.
Da mittlerweile einige Migrationen nach 11.2 durchgeführt wurden, wollen wir das Thema noch einmal aufgreifen und einige zusätzliche Informationen dazu geben. Generell ist das Name Searching Feature mit dem neuen Operator NDATA zu verwenden und hilft eine unscharfe Suche durchzuführen. Ich verwende dazu in folgendem Beispiel eine Tabelle mit ca 200 000 Sätzen und ca 8200 verschiedenen Einträgen in einer Spalte ORT. Um Name Searching zu verwenden, benötigen wir einen XML Tag, den ich mit einem MULTICOLUMN DATASTORE wie folgt zur Verfügung stelle.

exec ctx_ddl.drop_preference('name_ds')
begin
  ctx_ddl.create_preference('name_ds', 'MULTI_COLUMN_DATASTORE');
  ctx_ddl.set_attribute('name_ds', 'COLUMNS', 'ort');
end;
/
begin
  ctx_ddl.drop_section_group('name_sg');
end;
/
begin
  ctx_ddl.create_section_group('name_sg', 'BASIC_SECTION_GROUP');
  ctx_ddl.add_ndata_section('name_sg', 'ort', 'ort');
end;
/

Zusätzlich kann auch ALTERNATE SPELLING und BASE LETTER mit NDATA verwendet werden. Hierzu erzeugen wir folgende BASIC_WORDLIST.

exec ctx_ddl.drop_preference('name_wl');
begin
ctx_ddl.create_preference('name_wl', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('name_wl', 'NDATA_ALTERNATE_SPELLING', 'TRUE');
ctx_ddl.set_attribute('name_wl', 'NDATA_BASE_LETTER', 'TRUE');
end;
/

Nun kann der Index erzeugt werden.

CREATE INDEX ns_ort ON basic_lob (ort)
INDEXTYPE IS ctxsys.context
PARAMETERS ('sync (on commit) datastore name_ds 
section group name_sg wordlist name_wl');
SELECT * FROM ctx_index_errors;

Im ersten Versuch suchen wir den Ort "Vilshofen" in Niederbayern - die Schreibweise ist dabei etwas ungewöhnlich. Der Ort wird allerdings schon bei der ersten Abfrage gefunden.

 
SQL> SELECT distinct ort, score(1) FROM basic_lob
     WHERE contains(ort, 'NDATA(ort,Filschthofen)',1) > 0 
     AND score(1)>40;
ORT                                        SCORE(1)
---------------------------------------- ----------
Vilshofen                                        68
Osthofen                                         45

Nun suchen wir einen Ort in Kalifornien, der mit "Santa" beginnt - auch hier mit etwas unüblicher Schreibweise. Zusätzlich können weitere Argumente mitgegeben werden. Das Argument "Proximity" zum Beispiel gibt an, ob die Ähnlichkeit des Suchbegriffs zum tatsächlichen Namen den Score beeinflussen soll.

  
SQL> SELECT distinct ort, score(1) FROM basic_lob
     WHERE contains(ort, 'NDATA(ort,santa kallifornia,, proximity)',1) > 0 
     AND score(1)>40 ORDER BY 2;
ORT                                        SCORE(1)
---------------------------------------- ----------
Santa Clara/Kalifornien                          52
Staat Kalifornien                                52
Santa Maria                                      57
Santa Monica                                     57
santa clara1 california                          61
Santa California                                 83
Santa Kalifornia                                 96

SQL> SELECT distinct ort, score(1) FROM basic_lob 
     WHERE contains(ort, 'NDATA(ort,santa kallifornia)',1) > 0 
     AND score(1)>40 ORDER BY 2;
ORT                                        SCORE(1)
---------------------------------------- ----------
Santa Clara/Kalifornien                          52
Staat Kalifornien                                52
Santa Maria                                      57
Santa Monica                                     57
Santa California                                 83
santa clara1 california                          83
Santa Kalifornia                                 96

Was bedeutet das nun für unseren Index? Schauen wir uns noch kurz die Statistiken an. Klar wird dabei, dass diese umfangreiche Suche auch mit entsprechenden Ressourcen verbunden ist. Die Anzahl der Tokens ist beispielsweise höher als beim Index ohne NDATA. Der nächste Ausschnitt gibt Aufschluss über die Statistik der Tokens. Informationen über die Vorgehensweise findet sich auch in folgendem Blogeintrag.

DROP TABLE ausgabe;
CREATE TABLE ausgabe (resultat CLOB);
declare
    ergebnis clob := null;
  begin
    ctx_report.index_stats(index_name=>'NS_ORT',report=>ergebnis,stat_type=>null);
    insert into ausgabe values (ergebnis);
    commit;
    dbms_lob.freetemporary(ergebnis);
end;
/
set long 32000
set head off
set pagesize 10000
SELECT * FROM ausgabe;

Folgende Ausgabe liefert diese Abfrage:

                       STATISTICS FOR "US"."NS_ORT"
===========================================================================
indexed documents:                                                200,003
allocated docids:                                                 200,003
$I rows:                                                          170,909
---------------------------------------------------------------------------
                             TOKEN STATISTICS
---------------------------------------------------------------------------
unique tokens:                                                     66,955
average $I rows per token:                                           2.55
...
token statistics by type:
  token type:                                             200:NDATA "ORT"
    unique tokens:                                                 66,955
    total rows:                                                   170,909
    average rows:                                                    2.55
    total size:                                     26,653,305 (25.42 MB)
    average size:                                                     398
    average frequency:                                              61.15

Vergleicht man dies mit einer Context Index ohne die Verwendung von NDATA kommt man in unserem Beispiel nur auf ca 6100 eindeutige Tokens und eine Größe von 1,4 MB. Bevor man Name Search verwendet, kann man auch alternativ den FUZZY Operator ausprobieren. Die vollständige Syntax in folgendem Blogeintrag zeigt, wie umfangreich auch mit FUZZY gesucht werden kann.

Mehr Performance durch Index-Preloading

Wie kann man die Performance von Zugriffen auf den Textindex erhöhen? Eine Möglichkeit besteht darin, Tabellen und Indizes im Cache zu halten, damit möglichst wenig I/O durchgeführt wird. Roger Ford, Development Manager für Oracle Text, hat zu diesem Thema schon vor längerer Zeit einen Artikel auf OTN verfasst. Da das Thema nicht an Relevanz verloren hat, wollen wir auch hier in unserem Blog das Thema besprechen.
Die Technik, die man zum optimierten Speichern von Segmenten im Cache verwendet, ist die Nutzung von unterschiedlichen Pools im Datenbank Cache. Es ist zum Beispiel sinnvoll, Objekte, auf die sehr häufig zugegriffen wird (z.B. Lookup-Tabellen), dem sogenannten KEEP Pool Cache zuzuordnen. Dabei ist der KEEP Cache ein eigener Bereich im Datenbank Cache, der mit dem Parameter DB_KEEP_CACHE_SIZE konfiguriert wird. Standardmässig ist der Wert auf 0 gesetzt.
Drei Schritte sind zur Nutzung des KEEP Pools nötig:

• die Bestimmung der Größe des KEEP Pools und Setzen von DB_KEEP_CACHE_SIZE
• das Setzen der KEEP Storage Option im Index- und Tabellen-Segment
• das Laden der Objekte in den Cache mit den entsprechenden SQL-Kommandos

Um ein Gefühl für die Größeneinstellung des Pools zu erhalten, kann man sich zuerst die Größe des Index ansehen. Dazu eignet sich die Funktion CTX_REPORT, wie folgt:

SET long 10000
SELECT ctx_report.index_size('TXT_IDX') FROM dual;

TOTALS FOR INDEX US.TXT_IDX
--------------------------------------------------------------------------
CTX_REPORT.INDEX_SIZE('TXT_IDX')
--------------------------------------------------------------------------
TOTAL BLOCKS ALLOCATED:                                              51496
TOTAL BLOCKS USED:                                                   51167
TOTAL BYTES ALLOCATED:                             421,855,232 (402.31 MB)
TOTAL BYTES USED:                                  419,160,064 (399.74 MB)

In unserem Beispiel ist der Index insgesamt 400 MB groß. Nach einigen typischen Abfragen können wir überprüfen, welche Objekte mit wievielen Blöcken sich im Cache befinden. Eine Abfrage auf die V$BH und DBA_OBJECTS Tabelle listet die Objekte auf, die sich momentan im Cache befinden. Die Abfrage sieht dann folgendermassen aus:

COLUMN OWNER FORMAT A10
COLUMN OBJECT_NAME FORMAT A25
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999

SELECT o.object_name, o.owner, o.object_type, COUNT(*) NUMBER_OF_BLOCKS
FROM dba_objects o, v$bh bh
WHERE o.data_object_id = bh.objd AND o.owner ='US'
GROUP BY o.object_name, o.owner, o.object_type
ORDER BY COUNT(*);

OBJECT_NAME               OWNER      OBJECT_TYPE         NUMBER_OF_BLOCKS
------------------------- ---------- ------------------- ----------------
DR$TXT_IDX$R              US         TABLE                              7
SYS_LOB0000143350C00002$$ US         LOB                              345
DR$TXT_IDX$X              US         INDEX                            440
DR$TXT_IDX$I              US         TABLE                            495
BASIC_LOB                 US         TABLE                          5,634

Die Basistabelle BASIC_LOB oder Teile davon befinden sich offensichtlich im Cache. Der Textindex besteht aus mehreren Komponenten, die ebenfalls in der Abfrage aufgelistet werden: die Token Tabelle $I, der Index $X der $I Tabelle, die ROWID Tabelle $R und ein zusätzliches Lobsegment SYS_LOB0000143350C00002$$ der $R-Tabelle. Folgende Abfrage zeigt die Tabellen und die zugehörigen LOB-Segmente:

SELECT table_name, segment_name, in_row, cache FROM user_lobs;

TABLE_NAME                     SEGMENT_NAME                   IN_ CACHE
------------------------------ ------------------------------ --- ----------
BASIC_LOB                      SYS_LOB0000094513C00010$$      YES NO
DR$TXT_IDX$I                   SYS_LOB0000143761C00006$$      YES NO
DR$TXT_IDX$R                   SYS_LOB0000143766C00002$$      YES YES
...

Die Texte (hier: Spalte TEXT) der Basistabelle liegen in einem LOB-Segment. Ob LOB-Segmente generell den Buffer Cache verwenden oder nicht, hängt von dem Storage-Parameter CACHE ab. Standardmässig werden LOB-Segmente nicht in den Buffer geladen. Um dies einzustellen, ist folgendes Kommando notwendig.

ALTER TABLE BASIC_LOB MODIFY LOB(text) CACHE;

Nun stellt sich die Frage, wie man die Textkomponenten wie $I usw. und die Tabelle in den Cache laden kann. Folgende Abfragen können dabei hilfreich sein:

SELECT /*+ FULL(ITAB) */ SUM(token_count), SUM(LENGTH(token_info))
                                             FROM dr$txt_idx$i ITAB;

SELECT /*+ INDEX(ITAB) */ SUM(LENGTH(token_text))
                                             FROM dr$txt_idx$i ITAB;
SELECT SUM(row_no) FROM dr$txt_idx$r;
SELECT /*+ FULL(BTAB) */ SUM(dok_id) FROM basic_lob BTAB;

Bei den Full Table Scans (FTS) muss allerdings berücksichtigt werden, dass Oracle ein optimiertes Verfahren verwendet, um Objekte in den Cache zu laden. Falls die Größe der Tabelle 2% des Buffer Cache übersteigt - was häufig der Fall sein kann - wird über direct load gelesen und nicht über den Buffer Cache. Möchte man das Laden über den Buffer Cache erzwingen, ist der Einsatz folgendes Parameters notwendig:

ALTER SESSION SET "_small_table_threshold"=zahl;
-- wobei Zahl die Blockgröße angibt, die grösser als das Segment ist.
-- danach kann der FTS ausgeführt werden
SELECT /*+ FULL(BTAB) */ SUM(dok_id) FROM basic_lob BTAB;

Nachlesen kann man diese Information auch in der Oracle Support Note Doc ID 787373.1.
Damit die Segmente auch im KEEP Pool Cache gespeichert werden, müssen nun noch die Buffer Pool Einstellungen der Objekte verändert werden. Folgende Kommandos passen die Storage-Klausel an:

ALTER TABLE dr$txt_idx$i STORAGE (buffer_pool keep);

ALTER INDEX dr$txt_idx$x STORAGE (buffer_pool keep);

ALTER TABLE dr$txt_idx$r STORAGE (buffer_pool keep);

ALTER TABLE basic_lob STORAGE (buffer_pool keep);

Allerdings ist ein weiterer Schritt notwendig, um die Lob-Segmente der $R Tabelle im KEEP Pool zu speichern. Die LOB-Segmente der $I und $R Tabellen werden standardmässig "in-row" gespeichert. Falls die Werte kleiner als 4K sind, wird der Inhalt in der Tabelle selbst gespeichert, wächst er darüber hinaus, wird ein separates Segment angelegt. Da die LOB-Länge der Token Tabellen $I auf 4K limitiert ist, werden die TOKEN-Informationen immer im Segment selbst abgespeichert.
Ganz im Gegensatz dazu kann die Liste der ROWIDs, die in der $R Tabelle gespeichert sind, größer als 4K sein. Dann kommt es zu einer Speicherung in einem separaten Segment. Um sicherzustellen, dass die Informationen der LOBs auch im KEEP Pool gespeichert werden, sollte folgende Einstellung durchgeführt werden.

ALTER TABLE dr$txt_idx$r MODIFY LOB (data) (STORAGE (buffer_pool keep));

Mit den oben aufgeführten Abfragen lassen sich allerdings die LOB-Segmente im separat gespeicherten Segment NICHT in den Cache laden. Folgende Prozedur erledigt diese Aufgabe. Die einzelnen ROWID-Segmente, die grösser als 4K sind, werden dabei ausgelesen.

create or replace procedure loadAllDollarR (idx_name varchar2) is
  v_idx_name varchar2(30) := upper(idx_name);
  type c_type is ref cursor;
  c2 c_type;
  s varchar2(2000);
  b blob;
  buff varchar2(100);
  siz number;
  off number;
  cntr number;
begin
-- wenn Index partitioniert, dann mehr als eine Tabelle
  for c1 in (select table_name t from user_tables
             where table_name like 'DR_'v_idx_name'%$R') loop
    dbms_output.put_line('loading from table 'c1.t);
    s := 'select data from 'c1.t;
    open c2 for s;
    loop
       fetch c2 into b;
       exit when c2%notfound;
       siz := 10;
       off := 1;
       cntr := 0;
-- falls ROWIDs gespeichert sind, dann ROWID (Laenge 10) lesen und OFFSET plus4096
   if dbms_lob.getlength(b) > 4096 then
         begin
           loop
             dbms_lob.read(b, siz, off, buff);
             cntr := cntr + 1;
             off := off + 4096;
           end loop;
         exception when no_data_found then
           if cntr > 0 then
             dbms_output.put_line('4K chunks fetched: 'cntr);
           end if;
         end;
       end if;
    end loop;
  end loop;
end;
/
--Ausführung
exec LoadAllDollarR('TXT_IDX')

Zum Laden der Daten sind insgesamt folgende Abfragen notwendig:

ALTER SESSION SET "_small_table_threshold"= wert;

SELECT /*+ FULL(ITAB) */ SUM(token_count), SUM(LENGTH(token_info))
                                           FROM dr$index_name$i ITAB
SELECT /*+ INDEX(ITAB) */ SUM(LENGTH(token_text))
                                           FROM dr$index_name$i ITAB
SELECT SUM(row_no) FROM dr$index_name$r;
SELECT /*+ FULL(BTAB) */ SUM(spalte_name) FROM table_name BTAB;
exec LoadAllDollarR('index_name')

Summiert man die Anzahl der Blöcke der V$BH Tabelle auf, erhält man den Wert für die Größe von DB_KEEP_CACHE_SIZE.
Folgende Kommandos zeigt die Einstellung dazu:

-- DB_CACHE_SIZE anpassen
ALTER SYSTEM SET DB_CACHE_SIZE=wert;
-- DB_KEEP_CACHE_SIZE setzen
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=wert;

Viel Spass beim Ausprobieren...

"Section"-Suche in Oracle TEXT

Wie die meisten wissen, ist Oracle TEXT in der Lage, XML-Dokumente (oder besser: "getaggte Dokumente") zu durchsuchen. Und wie Oracle TEXT mit solchen Dokumenten umgeht, kann sehr stark beeinflusst werden. Heute geht es also um das Section Searching und einige Grundlagen, die man dazu wissen sollte ... Angenommen, wir haben eine Tabelle mit folgenden Inhalten.

create table xml_text(
  id  number,
  doc xmltype
)
/

insert into xml_text values (1, '<KUNDE><NAME>Czarski</NAME><VORNAME>Carsten</VORNAME></KUNDE>')
/
insert into xml_text values (2, '<KUNDE><NAME>Schwinn</NAME><VORNAME>Ulrike</VORNAME></KUNDE>')
/
insert into xml_text values (3, '<KUNDE><NAME>Mustermann</NAME><VORNAME>Max</VORNAME></KUNDE>')
/

... dann legt man den Textindex darauf bspw. so an (das ist am einfachsten).

create index ft_xmltext on xml_text (doc)
indextype is ctxsys.context
parameters ('section group ctxsys.auto_section_group');

Danach kann man suchen ...

SQL> select id from xml_text where contains(doc, 'Carsten within (VORNAME)') > 0;

        ID
----------
         1

1 Zeile wurde ausgewählt.

SQL> select id from xml_text where contains(doc, 'Carsten within (NAME)') > 0;

Es wurden keine Zeilen ausgewählt

So weit so gut - schauen wir mal in den Index hinein (genauer gesagt: in die Token-Tabelle):

SQL> select token_text, token_type from dr$ft_xmltext$i

TOKEN_TEXT           TOKEN_TYPE
-------------------- ----------
CARSTEN                       0
CZARSKI                       0
KUNDE                         2
MAX                           0
MUSTERMANN                    0
NAME                          2
SCHWINN                       0
ULRIKE                        0
VORNAME                       2

Man sieht, dass die XML-Tags mit im Index stehen - sie haben den Token Type 2. Das bedeutet aber, dass der Index bei großen Tabellen eine ganze Menge Tags mitindiziert. Und vor allem indiziert er alle Tags - manche braucht man eigentlich gar nicht: in unserem Fall hier ist das Tag KUNDE völlig überflüssig.

Grundsätzlich bietet Oracle Text verschiedene Varianten (Section Group Types) für die Abschnittssuche an. Die Dokumentation enthält eine Übersicht. Für XML-Dokumente kommt es nun darauf an, wie man suchen möchte.

• Möchte man eine Pfadsuche machen, also in XML-Manier (/KUNDE/VORNAME) in den Dokumenten suchen, so muss man die PATH_SECTION_GROUP verwenden. In CONTAINS kann dann mit den Abfrageoperatoren INPATH und HASPATH gearbeitet werden. Dies ist von der Indizierung her die aufwändigste Variante - der Index wird am größten. Auf der anderen Seite hat man mit INPATH und HASPATH die mächtigsten Abfragemöglichkeiten. Man sollte diese Variante aber auch nur dann wählen, wenn diese Möglichkeiten tatsächlich gebraucht werden.
• XML_SECTION_GROUP und AUTO_SECTION_GROUP erlauben die einfache Section-Suche mit WITHIN. Während die AUTO_SECTION_GROUP bis auf explizit ausgeschlossene XML-Tags alle indiziert, müssen zu indizierende Tags bei der XML_SECTION_GROUP manuell angegeben werden. Das bedeutet aber auch mehr Kontrolle.

Also könnte man mit der XML_SECTION_GROUP arbeiten und nur die Tags NAME und VORNAME indizieren - damit würde das Tag KUNDE aus dem Index rausfallen - brauchen wir ohnehin nicht.

begin
  ctx_ddl.create_section_group('kunde_section_group', 'XML_SECTION_GROUP');
  ctx_ddl.add_zone_section('kunde_section_group', 'NAME', 'NAME');
  ctx_ddl.add_zone_section('kunde_section_group', 'VORNAME', 'VORNAME');
end;
/

create index ft_xmltext on xml_text (doc)
indextype is ctxsys.context
parameters ('section group kunde_section_group');

Die Suche funktioniert genauso wie vorhin - die Token-Tabelle ist leicht verändert.

SQL> select token_text, token_type from dr$ft_xmltext$i;

TOKEN_TEXT           TOKEN_TYPE
-------------------- ----------
CARSTEN                       0
CZARSKI                       0
MAX                           0
MUSTERMANN                    0
NAME                          2
SCHWINN                       0
ULRIKE                        0
VORNAME                       2

Das Token KUNDE ist weg. Jetzt haben wir allerdings die Tags NAME und VORNAME als Zone Sections indiziert. Zone Sections sind hier erklärt. Wie XML-Tags können Sie mehrfach im Dokument vorkommen und verschachtelt sein. Bei XML-Tags wird das ja durchaus gebraucht.

In unserem Falle aber nicht! Beide kommen nur einmal vor und enthalten nur noch Text.

Wenn man weiss, dass die Tags nur noch Text enthalten (also nicht mehr verschachtelt sind) und nur einmal im Dokument vorkommen, so kann man auch Field Sections (Dokumentation) verwenden.

begin
  ctx_ddl.create_section_group('kunde_section_group', 'XML_SECTION_GROUP');
  ctx_ddl.add_field_section('kunde_section_group', 'NAME', 'NAME', false);
  ctx_ddl.add_field_section('kunde_section_group', 'VORNAME', 'VORNAME', false);
end;
/

Der Index ist nun noch kompakter ...

SQL> select token_text, token_type from dr$ft_xmltext$i;

TOKEN_TEXT           TOKEN_TYPE
-------------------- ----------
CARSTEN                      17
CZARSKI                      16
MAX                          17
MUSTERMANN                   16
SCHWINN                      16
ULRIKE                       17

Die Suche funktioniert wieder wie vorhin ... mit einer Ausnahme ...

SQL> select id from xml_text where contains(doc, 'Carsten') > 0;

Es wurden keine Zeilen ausgewählt

Eine Field Section ist im Rest des Dokumentes nicht sichtbar. Für die Vornamen und Namen kann man in diesem Falle nur noch Section-Suche machen. Wenn man möchte, dass die globale Dokumentsuche trotzdem funktioniert, muss man den letzten Parameter beim Aufruf von ADD_FIELD_SECTION auf true setzen.

begin
  ctx_ddl.create_section_group('kunde_section_group', 'XML_SECTION_GROUP');
  ctx_ddl.add_field_section('kunde_section_group', 'NAME', 'NAME', true);
  ctx_ddl.add_field_section('kunde_section_group', 'VORNAME', 'VORNAME', true);
end;
/

Aber Achtung: Die Tokens werden dann doppelt indiziert ...

SQL> select token_text, token_type from dr$ft_xmltext$i;

TOKEN_TEXT           TOKEN_TYPE
-------------------- ----------
CARSTEN                       0
CARSTEN                      17
CZARSKI                       0
CZARSKI                      16
MAX                           0
MAX                          17
MUSTERMANN                    0
MUSTERMANN                   16
SCHWINN                       0
:                             :

Man sieht, dass man bei der Section-Suche eine ganze Menge Möglichkeiten hat, das Verhalten des Index zu beeinflussen. Und gerade bei großen Dokumentbeständen und Indizes kommt es auf die Indexgröße an - ein kleinerer Textindex kann noch in den Hauptspeicher passen und für performantere Abfragen sorgen ... Bei Bedarf empfiehlt es sich, auf diese Aspekte besonders Acht zu geben ...

Übrigens ist das Geschriebene auch bei Verwendung eines USER_DATASTORE oder eines MULTICOLUMN_DATASTORE relevant - in beiden Fällen werden die Informationen als XML-Dokumente aufbereitet und an den Index übergeben.