Mittwoch, 25. März 2015

JSON (ab 12.1.0.2) mit Oracle TEXT indizieren

Mit dem Patchset 12.1.0.2 wurde die JSON-Unterstützung in der Oracle-Datenbank eingeführt; auf dem Blog SQL und PL/SQL in Oracle ist dazu auch ein Posting erschienen. Gemeinsam mit dem SQL/JSON-Funktionen wurde auch eine JSON-Unterstützung in Oracle TEXT eingeführt. Um die soll es jetzt gehen: Angenommen, wir haben eine Tabelle JSON_TAB, welche die JSON-Dokumente als CLOB in der Spalte JSON enthält ...
SQL> desc json_tab
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 FILENAME                                           VARCHAR2(200)
 JSON                                               CLOB


SQL> select json from json_tab where rownum = 1;

JSON
--------------------------------------------------------------------------------
{"PurchaseOrder":{"$":{"xmlns:xsi":"http://www.w3.org/2001/XMLSchema-instance","
xsi:noNamespaceSchemaLocation":"http:/localhost:9021/public/XMLDEMO/purchaseOrde
r.xsd"},"Reference":["ADAMS-2001112712104128PST"],"Actions":[{"Action":[{"User":
["SCOTT"]}]}],"Reject":[""],"Requestor":["Julie P. Adams"],"User":["ADAMS"],"Cos
tCenter":["R20"],"ShippingInstructions":[{"name":["Julie P. Adams"],"address":["
300 Oracle Parkway\r\nRedwood Shores\r\nCA\r\n94065\r\nUSA"],"telephone":["650 5
06 7300"]}],"SpecialInstructions":["Hand Carry"],"LineItems":[{"LineItem":[{"$":
{"ItemNumber":"1"},"Description":["The Life of Brian"],"Part":[{"$":{"Id":"71551
5010320","UnitPrice":"39.95","Quantity":"2"}}]},{"$":{"ItemNumber":"2"},"Descrip
tion":["Hamlet"],"Part":[{"$":{"Id":"037429128428","UnitPrice":"29.95","Quantity
Wie man mit den SQL-Funktionen JSON_VALUE, JSON_QUERY oder JSON_TABLE in diese JSON-Dokumente "hineingreifen" kann, könnt Ihr in oben erwähntem Blog-Posting nachlesen - jetzt wollen wir aber die Volltextsuche ermöglichen - dazu erzeugen wir, wie immer, einen Oracle TEXT-Index und verwenden dazu die neue Section Group CTXSYS.JSON_SECTION_GROUP - JSON-Dokumente werden also ganz ähnlich wie XML-Dokumente indiziert.
create index ft_jsontable on json_tab (json) 
indextype is ctxsys.context 
parameters ('section group ctxsys.json_section_group');
Nach dem Indizieren werfen wir einen Blick in die Token-Tabelle ($I). JSON-Attributnamen werden (wie auch XML-Tags) als Token-Type 7, Daten als Token-Type 0 indiziert.
SQL> select token_text from DR$FT_JSONTABLE$I where token_type = 7 and rownum <= 100;

TOKEN_TEXT
----------------------------------------------------------------
Action
Actions
CostCenter
Description
Id
ItemNumber
:

SQL> select token_text from DR$FT_JSONTABLE$I where token_type = 0 and rownum <= 100;

TOKEN_TEXT
----------------------------------------------------------------
WALKABOUT
WARD
WASHINGTON
WATCHED
WAVE
WESTMINSTER
WHISPERS
WILD
:
Demzufolge kann man die JSON-Dokumente nun, wie auch XML-Dokumente, mit WITHIN abfragen ... und natürlich gehen auch Oracle Text-Funktionen wie die Fuzzy-Suche. Achtet bei euren WITHIN-Abfragen aber darauf, dass die Namen der JSON-Attribute auch für Oracle TEXT Case-Sensitiv sind. Die Daten, also der Token-Type 0 werden dagegen (normalerweise) Case-Insensitiv indiziert.
SQL> select json_value(json, '$.PurchaseOrder.Reference[0]') 
  2  from json_tab 
  3  where contains(json, '?Washingtn within (Description)') > 0;

JSON_VALUE(JSON,'$.PURCHASEORDER.REFERENCE[0]')
--------------------------------------------------------------------------------
BLAKE-20021009123336231PDT
ALLEN-2002100912333742PDT
ALLEN-20021009123337553PDT
FORD-20021009123337463PDT
:

18 Zeilen ausgewählt.

SQL> select json_value(json, '$.PurchaseOrder.Reference[0]') 
  2  from json_tab 
  3  where contains(json, '?Washingtn within (UnitPrice)') > 0;

Keine Zeilen ausgewählt.

SQL> select json_value(json, '$.PurchaseOrder.Reference[0]') 
  2  from json_tab 
  3  where contains(json, '?Washingtn within (DESCRIPTION)') > 0;

Keine Zeilen ausgewählt.
Mit JSON_TEXTCONTAINS wurde noch ein zusätzlicher Query-Operator geschaffen; anstelle von CONTAINS mit WITHIN kann also auch eine Abfrage mit JSON_TEXTCONTAINS ausgeführt werden - letzterer erlaubt die Verwendung der gleichen JSON-Path-Expressions, wie sie auch in den SQL/JSON-Funktionen JSON_VALUE, JSON_QUERY und JSON_TABLE verwendet werden.
SQL> select json_value(json, '$.PurchaseOrder.Reference[0]') 
  2  from json_tab 
  3  where json_textcontains(json, $.PurchaseOrder.LineItems.LineItem.Description', 'Washington');

JSON_VALUE(JSON,'$.PURCHASEORDER.REFERENCE[0]')
--------------------------------------------------------------------------------
BLAKE-20021009123336231PDT
ALLEN-2002100912333742PDT
ALLEN-20021009123337553PDT
FORD-20021009123337463PDT
FORD-20021009123337653PDT
:

18 Zeilen ausgewählt.
Allerdings unterstützt JSON_TEXTCONTAINS nicht die Oracle TEXT Abfragesyntax; das bedeutet, dass man zwar standardisierte JSON-Pfadausdrücke verwenden kann, nicht jedoch Features wie Stemming, Fuzzy-Suche, UND/ODER-Kombinationen, Progressive Relaxation und vieles mehr. Insofern bleibt der "klassische" CONTAINS-Operator auch für JSON-Dokumente nach wie vor interessant. Viel Spaß beim Ausprobieren.

Donnerstag, 5. Februar 2015

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.

Donnerstag, 6. November 2014

Performance beim Erzeugen eines Oracle Text Index

Häufig erhalten wir die Frage, ob und wie sich die Performance beim Erzeugen eines Oracle Text Index beeinflussen lässt. Die Frage taucht in der Regel dann auf, wenn dem Anwender das Indexanlegen zu lange dauert. Eine Überprüfung der Oracle Text Memory Einstellung ist eine erste einfache Massnahme bei dieser Fragestellung. Setzt man nämlich einen Oracle Text Index ohne zusätzliche Memory Parameter Einstellung ab, wird die Default Einstellung, die häufig zu klein ist, genommen.

Wie kann man nun die Default Einstellungen einsehen? Eine einfache Abfrage auf die Tabelle CTX_PARAMETERS zeigt die Einstellungen für den Oracle Text Index.
col PAR_NAME format a40
col PAR_VALUE format a30
select * from ctx_parameters where PAR_NAME like '%MEMORY%';

PAR_NAME                                 PAR_VALUE
---------------------------------------- ------------------------------
DEFAULT_INDEX_MEMORY                     67108864
MAX_INDEX_MEMORY                         274877906944
Die Defaulteinstellung DEFAULT_INDEX_MEMORY liegt also bei 64 MB. Das bedeutet, beim CREATE INDEX werden normalerweise 64 MB verwendet. Der zweite Parameter gibt die maximale Einstellung an, die beim Indexanlegen überhaupt genutzt werden können. Hier - in einer 12.1.0.2 Umgebung - ist der Wert des Parameters 256 GB.

Es gibt nun zwei Möglichkeiten, die Memoryeinstellung zu ändern. Entweder während der Laufzeit über das CREATE INDEX Statement oder über die Änderung der Gesamteinstellung mit dem Package CTX_ADM. Folgendes Beispiel zeigt die Nutzung beim Indexanlegen.
create index my_idx on customers(CUST_STREET_ADDRESS) 
indextype is ctxsys.context parameters ('memory 100M');
Folgendes Beispiel zeigt die Verwendung von CTX_ADM.
begin
ctxsys.ctx_adm.set_parameter('MAX_INDEX_MEMORY','100G');
end;
/

begin
ctxsys.ctx_adm.set_parameter('DEFAULT_INDEX_MEMORY','512M');
end;
/
Ziel sollte sein, das Index Memory so hoch wie möglich zu setzen - allerdings unter Vermeidung von Paging. Einerseits kann mit dieser Massnahme das Indexanlegen beschleunigt werden, andererseits wird dabei auch die Fragmentierung des Index geringer.

Wichtiger Hinweis: Die maximale Einstellung für MAX_INDEX_MEMORY hat sich übrigens von 11g nach 12c verändert. In 11g lag der Wert noch bei 2 GB, in 12c ist dieser Wert auf 256 GB erhöht worden. Dies wird übrigens auch in den Defaulteinstellung sichtbar (siehe obige Abfrage auf CTX_PARAMETERS). Beachten Sie auch: 256 GB ist eine theoretische Grenze, die tatsächliche Grenze von MAX_INDEX_MEMORY könnte je nach Umgebung niedriger ausfallen.

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.

Beliebte Postings