Dienstag, 23. September 2014

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.

Freitag, 18. Juli 2014

FILE_DATASTORE und Security oder: Was ist die FILE_ACCESS_ROLE?

Dass Oracle TEXT einen FILE_DATASTORE anbietet, mit dem man Dateien im lokalen Dateisystem des Datenbankservers indizieren kann, ist den meisten bekannt. Dazu braucht es (wie immer) eine Tabelle, in der sich nun aber keine Dokumente, sondern Dokumentpfade befinden, wie folgendes Beispiel zeigt.
create table index_files (
  id number(10), 
  path varchar2(500)
)
/

insert into index_files (id, path) values (1, '/home/oracle/twp_apex_best_practices.pdf')
/

commit
/
Wenn nun ein Textindex auf die Spalte PATH erzeugt wird, ist es klar, dass nicht die Pfade selbst, sondern die Dateien, die sich hinter dem Pfad befinden, indiziert werden sollen. Das wird mit der Datastore-Preference FILE_DATASTORE bestimmt. Ein erster Versuch wird allerdings mit einer Fehlermeldung quittiert.
create index ft_files on index_files (path)
indextype is ctxsys.context
parameters (
'filter ctxsys.auto_filter 
 datastore ctxsys.file_datastore
')
/

create index ft_files on index_files (path)
*
FEHLER in Zeile 1:
ORA-29855: Fehler bei Ausführung der Routine ODCIINDEXCREATE
ORA-20000: Oracle Text error:
DRG-10758: index owner does not have the privilege to use file or URL datastore
ORA-06512: in "CTXSYS.DRUE", Zeile 160
ORA-06512: in "CTXSYS.TEXTINDEXMETHODS", Zeile 366
Diejenigen, die Oracle TEXT schon länger kennen, dürften sich nun wundern; denn "früher" wäre dieses CREATE INDEX-Kommando fehlerfrei durchgelaufen. Nun fehlt ein Privileg - und das ist auch gut so - denn über den Umweg des Textindex ließen sich ja ansonsten große Teile des Filesystems auf dem Datenbankserver auslesen. Also wurde hier ein zusätzlicher Schutz eingeführt. Der Eigentümer des Oracle-Text-Index braucht eine zusätzliche Rolle. Der Name dieser Rolle ist vom Oracle TEXT Development allerdings flexibel gehalten worden (und genau das führt oft zu Konfusion). Im Oracle TEXT Dictionary wird eine Datenbankrolle als sog. FILE_ACCESS_ROLE registriert. Diese Rolle muss der Eigentümer des Textindex dann zugewiesen bekommen.
  • Also legen wir zunächst eine Rolle an, welche die FILE_ACCESS_ROLE sein soll. Das geschieht als DBA.
    SYS@[sccloud033:1521/orcl]
    SQL> create role text_dateizugriff not identified;
    
    Rolle wurde erstellt.
    
  • Dann legen wir diese Rolle (hier also TEXT_DATEIZUGRIFF) als die FILE_ACCESS_ROLE für Oracle TEXT fest.
    SYS@[sccloud033:1521/orcl]
    SQL> begin
      2    ctxsys.ctx_adm.0set_parameter ('FILE_ACCESS_ROLE', 'TEXT_DATEIZUGRIFF');
      3  end;
      4  /
    
    PL/SQL-Prozedur erfolgreich abgeschlossen.
    
  • Schließlich wird die Rolle TEXT_DATEIZUGRIFF dem Datenbankschema zugewiesen, welches den Textindex erzeugen möchte, also SCOTT oder wie hier: TESTIT.
    SYS@[sccloud033:1521/orcl]
    SQL> grant text_dateizugriff to testit;
    
    Benutzerzugriff (Grant) wurde erteilt.
    
Versucht der User TESTIT nun nochmals, den Index mit CREATE INDEX zu bauen, so wird er nun erfolgreich sein.
SQL> create index ft_files on index_files (path)
  2  indextype is ctxsys.context
  3  parameters (
  4  'filter ctxsys.auto_filter
  5   datastore ctxsys.file_datastore
  6  ')
  7  /

Index wurde erstellt.

SQL> select * from index_files where contains(path, 'application express') > 0
/

        ID PATH
---------- --------------------------------------------------
         1 /home/oracle/twp_apex_best_practices.pdf
Also alles ganz einfach. Zusammengefasst kann man sagen:
  • Möchte man mit Oracle TEXT den FILE_DATASTORE verwenden, um Inhalte des lokalen Dateisystems zu indizieren, so braucht der Datenbankuser eine zusätzliche Rolle.
  • Diese Rolle heißt nicht FILE_ACCESS_ROLE - vielmehr muss man (als DBA) eine eigene Rolle (mit eigenem Namen) erzeugen, diese als FILE_ACCESS_ROLE im Oracle TEXT Dictionary registrieren und dem Datenbankschema zuweisen.
Das ist auch in der Oracle TEXT Dokumentation - im Abschnitt FILE_DATASTORE and Security beschrieben.

Montag, 12. Mai 2014

11.2.0.4 und Oracle Text - das sollte man wissen!

Nach einem Upgrade auf 11.2.0.4 können nach einem DELETE auf Tabellen mit Oracle Text Index Fehler wie folgt eintreten:
ERROR at line 1: 
ORA-00604: error occurred at recursive SQL level 1 
ORA-06550: line 1, column 7: 
PLS-00306: wrong number or types of arguments in call to 'SYNCRN' 
ORA-06550: line 1, column 7: 

PL/SQL: Statement ignored

Die genutzte Tabelle und die zugehörigen Text Indizes sind dabei vor dem Upgrade angelegt worden.

Was ist passiert?
Die Analyse des zugehörigen Bug 17501296 gibt als Ursache das Fehlen des Primary Keys der $R Tabelle an.

Was kann man tun?
Eine ausführliche Antwort hierzu liefert My Oracle Support Note 1608029.1 (Oracle Text Release 11.2.0.4.0 mandatory Patches). Hier wird das notwendige Vorgehen beschrieben. Kurz zusammengefasst handelt es sich dabei im Wesentlichen um 2 Dinge: 
  1. Die Ausführung von Patch 17501296 korrigiert das fehlerhafte Upgrade und Downgrade Script, so dass nachfolgende Upgrades erfolgreich sind. 
  2. Ein zusätzliches SQL Skript korrgiert die schon existierenden und migrierten Tabellen mit Text Indizes, falls die Constraints für die $R Tabellen nicht existieren.
Und fertig!

Dienstag, 8. April 2014

Oracle12c: Verändertes Verhalten bei Datastore Triggern

Falls Sie einen Multicolumn Datastore oder einen User Datastore nutzen, kann folgender Blogeintrag ganz nützlich sein. Es geht um das veränderte Verhalten bei Verwendung von Datenbank Trigger und was man tun kann, um das alte Verhalten wiederherzustellen. Als Erinnerung bzw. als Grundlage nehmen wir den Blog Mehrere Tabellenspalten indizieren: MULTICOLUMN_DATASTORE.
Hier kurz noch einmal der Code zur Erinnerung:

-- Anlegen der Tabelle
drop table  produktions_hinweise purge;
create table produktions_hinweise(
  fall_id               number(10),
  kommentar_werk        varchar2(4000),
  kommentar_controlling varchar2(4000),
  hinweise_produktion   clob);

-- Einfügen eines Satzes
insert into produktions_hinweise 
(fall_id, kommentar_werk, kommentar_controlling, hinweise_produktion) 
values 
(1,'Auslastung','Abschreibungen', 'Spezialmaschine');
Um einen Multicolumn Datastore nutzen zu können, muss zunächst eine sogenannte Preference erzeugt werden; hier werden die Spalten, welche gemeinsam indiziert werden sollen, konfiguriert.

begin
  ctx_ddl.create_preference(
    preference_name => 'kommentare_store'
   ,object_name     => 'MULTI_COLUMN_DATASTORE'
  );
  ctx_ddl.set_attribute(
    preference_name => 'kommentare_store'
   ,attribute_name  => 'columns'
   ,attribute_value => 'KOMMENTAR_WERK, KOMMENTAR_CONTROLLING, HINWEISE_PRODUKTION'
  );
end;
/

Man kann nun eine der drei Dokumentspalten zur Indizierung nehmen; aus Gründen der Übersichtlichkeit empfiehlt es sich jedoch, eine eigene "Dummy"-Spalte zu erzeugen.

alter table produktions_hinweise add (indexspalte char(1));
Nun Erzeugen wir einen Index - die Preferences geben den Datastore, die Section Group und das Synchronisieren nach dem COMMIT an.

create index idx_kommentare on produktions_hinweise (indexspalte)
indextype is ctxsys.context parameters 
('datastore kommentare_store 
  section group CTXSYS.AUTO_SECTION_GROUP
  SYNC (ON COMMIT)');
Wir haben nun einen Index auf einer spezifischen Spalte; der Index wird allerdings nur verändert, wenn diese Spalte sich ändert. Damit der Index aber immer beim UPDATE auf einer anderen Spalte (hier beispielsweise KOMMENTAR_WERK) verändert wird, konnte man vor 12c einen BEFORE UPDATE Trigger verwenden wie folgendes Beispiel zeigt:

create or replace trigger trg_produktions_hinweise
before update on produktions_hinweise
for each row
begin
  :new.indexspalte := :new.indexspalte;
end;
/
Ändert man nun irgendeine Spalte mit anschliessendem COMMIT (in unserem Beispiel KOMMENTAR_WERK), wird der Index synchronisiert. Dieses Verhalten hat sich in 12c geändert, nachdem Bug 14155722 gefixed wurde! Oracle Text hat nämlich implizit folgende Funktion verwendet - Zitat aus dem Bug:
"If an enabled "BEFORE UPDATE" row trigger references a column A but that column A is not updated by the trigger, only referenced, logminer reports SQL_REDO as updating the column A to itself when some other column B is updated. This issue has been reported in the Bug 7720026: SQL_REDO SHOWS UPDATES OF COLUMNS NOT IN UPDATE STATEMENT"
Trigger prüfen nun als Fix zu Bug 14155722 den "before" und "after" Zustand aller referenzierten Spaltenwerte. Falls die Spalte sich NICHT verändert hat, dann findet auch KEIN Index Update statt. Dies bedeutet, dass unser Trigger kein Index Update initiiert, wenn eine andere Spalte verändert wird.

Was kann man tun? Laut Roger Fords (Productmanager von Oracle Text) Blogeintrag kann man in der Session oder im ganzen System ein Fix Control verwenden.
alter session set "_fix_control"='14155722:OFF';
-- oder
alter system set "_fix_control"='14155722:OFF';
Was ist noch einmal ein Fix Control (Bezeichnung:_FIX_CONTROL)? "_FIX_CONTROL" ist ein spezieller "hidden" Parameter - eingeführt mit 10.2.0.2 - mit dem man Bug Fixes ein- und ausschalten kann. Der Default (ON oder OFF) für einen Fix wird meist über den Wert von OPTIMIZER_FEATURES_ENABLE bestimmt. Ansehen kann man sich die Einstellungen übrigens mit V$SYSTEM_FIX_CONTROL oder über V$SESSION_FIX_CONTROL. Mehr dazu findet sich auch in My Oracle Support Note 567171.1.

Zum Schluss treten wir natürlich noch den Beweis in 12c an :) ...
-- Fix Control setzen 
alter session set "_fix_control"='14155722:OFF';
--Überprüfung, VALUE 0 bedeutet OFF
SQL> select * from V$SESSION_FIX_CONTROL where bugno=14155722;

SESSION_ID      BUGNO      VALUE
---------- ---------- ----------
SQL_FEATURE
----------------------------------------------------------------
DESCRIPTION
----------------------------------------------------------------
OPTIMIZER_FEATURE_ENABLE       EVENT IS_DEFAULT     CON_ID
------------------------- ---------- ---------- ----------
        67   14155722          0
QKSFM_CBO_14098180
check whether outbinds were modified in before row UPDATE trigge
8.0.0                              0          0          0
-- ein Update
update produktions_hinweise set kommentar_werk='TTT';
commit; 
-- die Abfrage
select fall_id from produktions_hinweise
where contains(
  indexspalte, 
  '(Spezialmaschine WITHIN (HINWEISE_PRODUKTION)) and 
   (Abschreibungen WITHIN (KOMMENTAR_CONTROLLING)) and 
   (TTT WITHIN (KOMMENTAR_WERK))'
)>0
/
   FALL_ID
----------
         1

Und das alte Verhalten ist wiederhergestellt.

Montag, 17. Februar 2014

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 ...

Montag, 2. Dezember 2013

Oracle12c: Unterstützung für XML-Namespaces in Oracle TEXT

Heute geht es nochmals um Oracle12c: Oracle TEXT hat ein hochinteressantes neues Feature dazubekommen: Und zwar die Unterstützung für XQuery Full Text. Damit bietet Oracle TEXT nun wirklich eine vollständige Unterstützung für XML. Aber Moment mal: Konnte Oracle TEXT nicht schon immer mit XML umgehen? Im Prinzip ja - aber einige XML-Besonderheiten wurden von Oracle TEXT nicht unterstützt - zum Beispiel XML Namespaces. Dazu ein kleines Beispiel: Zunächst füllen wir eine Tabelle mit ein paar XML-Dokumenten:
create table tab_xml (id number, docs xmltype)
/

/*
 * Das Dokument enthält ein Tag "tag" aus dem Namespace "http://mynamespaces.com/ns1"
 */
insert into tab_xml values (
  1, 
  '<ns1:dokument xmlns:ns1="http://mynamespaces.com/ns1" 
                 xmlns:ns2="http://mynamespaces.com/ns2">
    <ns1:tag att="attr1">Das ist ein Text</ns1:tag>
   </ns1:dokument>'
)
/

/*
 * Das Dokument enthält ein Tag "tag" aus dem Namespace "http://mynamespaces.com/ns2"
 * Also ein anderer Namespace als Dokument 1
 */
insert into tab_xml values (
  2, 
  '<ns1:dokument xmlns:ns1="http://mynamespaces.com/ns1" 
                 xmlns:ns2="http://mynamespaces.com/ns2">
    <ns2:tag att="attr1">Das ist ein Text</ns2:tag>
   </ns1:dokument>'
)
/

/*
 * Das Dokument enthält ein Tag "tag" aus dem Namespace "http://mynamespaces.com/ns2"
 * Also gleicher Namespace wie Dokument 2 - ABER: anderer Präfix!
 */
insert into tab_xml values (
  3, 
  '<ns_1:dokument xmlns:ns_1="http://mynamespaces.com/ns1" 
                  xmlns:ns_2="http://mynamespaces.com/ns2">
    <ns_2:tag att="attr1">Das ist ein Text</ns_2:tag>
   </ns_1:dokument>'
)
/

commit
/
Das Besondere an diesen Dokumenten sind die XML Namespaces. Die XML-Tags enthalten ein sogenanntes Namespace-Präfix. Das ist aber noch nicht der Namespace selbst - denn oben im ersten Tag wird dieser Präfix mit dem Attribut xmlns auf den tatsächlichen Namespace abgebildet. Das erste Dokument definiert also zwei Namespaces: http://mynamespaces.com/ns1 und http://mynamespaces.com/ns2. Das XML-Tag names tag trägt den Präfix ns1, gehört also zum Namespace http://mynamespaces.com/ns1. Im zweiten Dokument werden die gleichen Namespaxes und auch die gleichen Namespace-Präfixe verwendet - nur gehört das Tag tag hier zum anderen Namespace http://mynamespaces.com/ns2.
Im dritten Dokument kommt es nun: Auch hier werden die gleichen Namespaces verwendet, aber mit anderen Präfixen. Das XML Tag tag trägt nun den Präfix ns_2 - dieser ist aber auf den Namespace http://mynamespaces.com/ns2 gemappt - es ist also tatsächlich das gleiche Tag wie in Dokument 2.
In XML kann der gleiche Namespace tatsächlich mit unterschiedlichen Präfixen angesprochen werden; wenn man wissen möchte, ob zwei XML Tags identisch sind, reicht es also nicht aus, nur auf die Präfixe zu schauen; man muss nachsehen, auf welche Namespaces diese Präfixe gemappt wurden. Und genau das tut Oracle TEXT bis einschließlich Oracle11g nicht. Legen wir zunächst einen "klassischen" Oracle TEXT-Index mit "XML-Unterstützung" an.
create index ft_tabxml on tab_xml (docs)
indextype is ctxsys.context
parameters ('section group ctxsys.path_section_group')
/
Ein mit der PATH_SECTION_GROUP erzeugter Volltextindex eröffnet die Möglichkeit, mit den CONTAINS-Operatoren INPATH und HASPATH zu arbeiten. Diese unterstützen XML auch ganz wunderbar; man kann ganz ähnlich zu XPath arbeiten und auch Attribute werden unterstützt. Nur ... mit den Eigenheiten der XML-Namespaces kann die PATH_SECTION_GROUP in Oracle11g nicht umgehen ...
select id from tab_xml
where contains(docs, 'text INPATH(/ns1:dokument/ns2:tag)') > 0
/

        ID
----------
         2

1 Zeile wurde ausgewählt.
Es gibt von der Syntax her keine Möglichkeit, einen Namespace "richtig" anzugeben. Man kann nur den Namespace-Präfix angeben (und der wird so behandelt, als wäre er Teil des Tag-Namens). Aber Oracle TEXT kann in 11g nicht nachsehen, auf welchen Namespace er gemappt wurde und ob der gleiche Namespace noch von anderen Präfixen verwendet wird. Dokument 3 verwendet einen anderen Präfix, daher wird es nicht gefunden - es müsste aber gefunden werden, denn es ist das gleiche Tag. Doch Oracle12c schafft Abhilfe - zunächst muss der Index etwas anders erzeugt werden ...
begin
  ctx_ddl.create_section_group('my_sg_xquery', 'PATH_SECTION_GROUP');
  ctx_ddl.set_sec_grp_attr('my_sg_xquery', 'xml_enable', 'true');
end;
/
sho err

create index ft_tabxml on tab_xml (docs)
indextype is ctxsys.context
parameters ('section group my_sg_xquery')
/
Vor dem CREATE INDEX muss eine eigene Section Group vom Typ PATH_SECTION_GROUP erstellt werden. Das muss sein, denn bei dieser muss das Attribut XML_AWARE auf TRUE gestellt sein. Danach kann der Index normal erzeugt werden.
Nun liegt ein Textindex vor, der auch XML-Namespaces vollständig unterstützt. Nicht erweitert wurde dagegen die Syntax des CONTAINS-Operators - und das hat einen guten Grund: Denn für die Volltextsuche in XML-Dokumenten gibt es inzwischen einen Standard - XQuery Full Text - und diesen sollte man auch verwenden. Es machte also keinen Sinn, mit einer Erweiterung für CONTAINS neue, proprietäre Syntax zu schaffen, man entschied sich, auf den Standard zu setzen. Und der sieht so aus:
SELECT id
FROM tab_xml
WHERE XMLExists('declare namespace ns="http://mynamespaces.com/ns2";
                 //ns:tag[. contains text "text"]'
                  PASSING docs)
/
Achtung: Wenn Ihr mit SQL*Plus arbeitet, kann euch das Semikolon in der dritten Zeile in die Query kommen - SQL*Plus betrachtet die Abfrage als dort zu Ende. Das kann man mit einem set sqlterminator # umstellen. Diese Abfrage findet nun alles, was gefunden werden soll ...
        ID
----------
         2
         3

2 Zeilen ausgewählt.
Schauen wir nun mal in die Indexstrukturen hinein. Wie immer bei Oracle TEXT, ist der Index tatsächlich in den DR$-Tabellen abgelegt.
SQL> select tname from tab where tname like 'DR$%'
  2  /

TNAME
-------------------------------------------------------
DR$FT_TABXML$R
DR$FT_TABXML$N
DR$FT_TABXML$K
DR$FT_TABXML$I
DR$FT_TABXML$E
DR$FT_TABXML$D

6 Zeilen ausgewählt.
Die XML-Unterstützung ist nun in den $D- und $E-Tabellen enthalten. Die $E-Tabelle kann man lesen: Wie die folgende Abfrage zeigt, speichert Oracle TEXT dort alle XML-Tags mitsamt Ihren Namespaces ab.
SQL> select * from DR$FT_TABXML$E
/

ID           NAMESPACE                    LNAME        NS
------------ ---------------------------- ------------ ----------------------------
_-_1         http://mynamespaces.com/ns1  dokument     http://mynamespaces.com/ns1
_-_2         http://mynamespaces.com/ns1  tag          http://mynamespaces.com/ns1
_-_3         http://mynamespaces.com/ns2  tag          http://mynamespaces.com/ns2

3 Zeilen ausgewählt.
Die $D-Tabelle gehört eigentlich zum Save Copy Feature von Oracle TEXT, welches hier gar nicht explizit angesprochen wurde. In dieser Tabelle speichert Oracle TEXT, aktiviert man das Feature, gefilterte Versionen von Binärdokumenten ab, um Snippet- oder Markup-Operationen schneller durchführen zu können (auch dazu gibt es noch ein Blog Posting). Ein "XML-Aware" Textindex nutzt diese Tabellen aber ebenfalls für seine interne Verarbeitung. Die Spalte DOC der $D-Tabelle enthält eine nochmals aufbereitete Variante des XML-Dokumentes.
Zusammenfassend kann man also sagen, dass Oracle TEXT in der Version 12c eine umfangreiche und wirklich komplette Volltextsuche für XML anbietet - mit der Unterstützung für XQuery Full Text. Weitere Informationen zum Thema findet Ihr im XML DB Developers' Guide: Indexing XML Data for Full-Text Queries. Viel Spaß beim Ausprobieren.

Dienstag, 8. Oktober 2013

Oracle Text in Oracle 12c: Automatic Near Real-Time Index

Oracle Database 12c steht schon seit einiger Zeit zur Verfügung und wie schon angesprochen, wollen wir im Rahmen unserer Blogpostings die neuen Features nach und nach beschreiben. Dieses Mal geht es um das sogenannte Feature "Automatic Near Real-Time Indexing". Dabei geht es um die typische Anforderung den Index möglich aktuell zu halten - sogar bei hoher Änderungsrate. Hoher Aktualitätsanspruch bedeutet allerdings häufiges Synchronisieren und führt natürlich zu hoher Fragmentierung bzw. zu vermehrtem und länger andauernden OPTIMIZE Operationen.

12c löst dieses Dilemma mit dem neuen Konzept des "two-level" Index bzw. auch unter dem Feature Name "near real time index" bekannt. Dies erlaubt einen kleinen, fragmentierten Stage Index, der alle aktuellen Veränderungen enthält, vorzuhalten, ohne Änderungen an dem großen Index vornehmen zu müssen. Die Idee dabei ist, dass der Stage Index klein genug ist, um in die SGA zu passen. Die Daten können dann nach und nach vom Stage Index zum Hauptindex verlagert werden. Dies geschieht mit einem neuen MERGE Modus für Indizes.

Folgendes Beispiel zeigt wie das Feature genutzt werden kann. Zuerst stellen wir eine leere Tabelle zur Verfügung.
drop table texttabelle
/
drop sequence s
/
create sequence s
/
create table texttabelle(
  id          number(10) default s.nextval,
  dokument varchar2(1000))
/
Realisiert wird das Feature dann mit der zusätzlichen Storage Option STAGE_ITAB.
exec ctx_ddl.drop_preference ('my_storage');
exec ctx_ddl.create_preference('my_storage', 'BASIC_STORAGE');
exec ctx_ddl.set_attribute ('my_storage', 'STAGE_ITAB', 'true');
Nun erzeugen wir den Index.
create index my_index on texttabelle (dokument) 
indextype is ctxsys.context
parameters( 'storage my_storage sync (on commit)');
Überprüfen wir die erzeugten Tabellenobjekte, stellen wir fest, dass zusätzlich eine neue Tabelle mit Namen DR$MY_INDEX$G generiert wurde, die die gleiche Struktur wir die $I Tabelle besitzt.
SQL> select * from tab
  2  /

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DR$MY_INDEX$G                  TABLE
DR$MY_INDEX$N                  TABLE
DR$MY_INDEX$R                  TABLE
DR$MY_INDEX$K                  TABLE
TEXTTABELLE                    TABLE
DR$MY_INDEX$I                  TABLE

6 rows selected.
Darüberhinaus werden weitere Objekte wie zum Beispiel ein $H B-tree Index auf der $G Tabelle erzeugt - dieser erfüllt die gleiche Aufgabe wie der $X Index auf der $I Tabelle.

Nun fügen wir einfach zwei Zeilen ein und schließen jeweils mit COMMIT ab.
insert into texttabelle (dokument) values 
         ('A-Partei gewinnt Wahl in Hansestadt');
commit;
insert into texttabelle (dokument) values 
         ('Terror in Nahost: Kriminalität steigt immer weiter an'); 
commit;
Überprüfen wir die Inhalte der beiden Tabellen, stellen wir fest, dass die $I Tabelle leer ist, wohingegen die $G Tabelle befüllt wurde. Neue Inhalte werden also nicht mehr direkt in der $I Tabelle geschrieben, sondern in der $G Tabelle mitgeführt.
SQL> select token_text from DR$MY_INDEX$I;
no rows selected

SQL> select token_text from DR$MY_INDEX$G;

TOKEN_TEXT
----------------------------------------------------------------
GEWINNT
HANSESTADT
IMMER
KRIMINALIT?T
NAHOST
PARTEI
STEIGT
TERROR
WAHL
WEITER

10 rows selected.
Um diese Tabelle in der SGA zu halten, kann man sich entweder auf das normale Caching Verhalten verlassen oder aber - falls konfiguriert - den KEEP Pool der Datenbank nutzen. Dazu kann man die folgenden Storage Attribute verwenden.
exec ctx_ddl.set_attribute ('my_storage', 'G_TABLE_CLAUSE', 
                                                  'storage (buffer_pool keep)');
exec ctx_ddl.set_attribute ('my_storage', 'G_INDEX_CLAUSE', 
                                                 'storage (buffer_pool keep)');
Möchte man nun die Daten in den Hauptindex manuell integrieren, kann man dazu das folgende OPTIMIZE Kommando verwenden.
execute ctx_ddl.optimize_index(idx_name=>'MY_INDEX', optlevel=>'MERGE');
Damit werden die Einträge aus $G in optimierter Form in die $I Tabelle überführt und gleichzeitig aus der $G Tabelle gelöscht.

Optimal wäre es nun, wenn dieser Aufgabe von Oracle automatisch durchgeführt werden könnte. Die neue Prozedur ADD_AUTO_OPTIMIZE erfüllt diese Aufgabe.
exec ctx_ddl.add_auto_optimize( 'my_index' )
PL/SQL procedure successfully completed.
Nach der Ausführung wird der Index zur automatischen Optimierung registriert, wie wir in der folgenden View sehen können.
SQL> select * from ctx_user_auto_optimize_indexes;

AOI_INDEX_NAME                 AOI_PARTITION_NAME
------------------------------ ------------------------------
MY_INDEX
Ausgeführt wird diese Aktion von einem DBMS_SCHEDULER Job, der automatisch im Hintergrund angelegt und ausgeführt wird.
SQL> select job_name, program_name, schedule_type, last_start_date 
     from dba_scheduler_jobs 
     where owner='CTXSYS';

JOB_NAME             PROGRAM_NAME         SCHEDULE_TYP
-------------------- -------------------- ------------
LAST_START_DATE
---------------------------------------------------------------------------
DR$BGOPTJOB          DR$BGOPTPRG          IMMEDIATE
08-OCT-13 04.42.13.977418 PM EUROPE/VIENNA
Übrigens lässt sich dieses Konzept des "two-level" Index - auch nachträglich mit einem ALTER INDEX REBUILD Kommando zu einem bestehenden Index hinzufügen.
alter index my_index rebuild parameters('replace storage my_storage');

Beliebte Postings