Die Firma ORDIX bietet im Jahr 2016 an mehreren Terminen kostenpflichtige Oracle TEXT Seminare in Wiesbaden an. Für diejenigen, die für 3 Tage lang nochmals richtig tief einsteigen, ist dies
sicherlich eine interessante Gelegenheit.
Freitag, 29. Januar 2016
Dienstag, 8. September 2015
Transaktionen und CTX_DDL: COMMIT oder nicht COMMIT?
Heute geht es um ein kleines, aber dennoch nützliches Feature in Oracle TEXT, wenn
es um das Erstellen von Preferences geht. Die verschiedenen Prozeduren in CTX_DDL
setzen alle ein implizites COMMIT ab, und verhalten sich damit wie normale SQL DDL Kommandos.
Das folgende Codebeispiel zeigt das - obwohl ein ROLLBACK erfolgte, ist die neue Preference
noch sichtbar - sie wurde vorher schon festgeschrieben.
SQL> select pre_name, pre_class from ctx_user_preferences;
Es wurden keine Zeilen ausgewählt
SQL> exec ctx_ddl.create_preference('MYLEXER', 'BASIC_LEXER');
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> rollback;
Transaktion mit ROLLBACK rückgängig gemacht.
SQL> select pre_name, pre_class from ctx_user_preferences;
PRE_NAME PRE_CLASS
------------------------------ ------------------------------
MYLEXER LEXER
1 Zeile wurde ausgewählt.
In den meisten Fällen ist dies sicherlich auch das gewünschte Verhalten; aber es geht
auch anders. Letztlich werden durch die verschiedenen CTX_DDL-Aufrufe doch nur
Zeilen in interne Tabellen eingefügt - es wäre also schon interessant, wenn man
kein implizites COMMIT machen würde - dann könnte man auch mehrere CTX_DDL-Aufrufe
per Rollback rückgängig machen. Und das geht so.
begin CTX_DDL.PREFERENCE_IMPLICIT_COMMIT := FALSE; end; / PL/SQL-Prozedur erfolgreich abgeschlossen.
Wenn man die Kommandos von oben nun nochmals laufen lässt, sieht das Bild so aus.
SQL> select pre_name, pre_class from ctx_user_preferences;
Es wurden keine Zeilen ausgewählt
SQL> exec ctx_ddl.create_preference('MYLEXER', 'BASIC_LEXER');
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> rollback;
Transaktion mit ROLLBACK rückgängig gemacht.
SQL> select pre_name, pre_class from ctx_user_preferences;
Es wurden keine Zeilen ausgewählt
Kombiniert man dies in einem SQL-Skript mit einem WHENEVER SQLERROR EXIT oder WHENEVER SQLERROR ROLLBACK,
so muss man sich nicht mehr mit dem Problem "halb" angelegter Index-Preferences herumschlagen. Mehr
dazu findet Ihr in der Oracle TEXT Dokumentation.
Montag, 1. Juni 2015
12c Feature: $I Tabelle mit neuer Storage Preference BIG_IO
Wir haben in den letzten Blogeinträgen schon Einiges zum Thema Oracle Database 12c veröffentlicht. Auch in diesem Blog wollen wir uns wieder einem neuen Oracle Text 12c Feature widmen. Es geht dabei um neue Möglichkeiten innerhalb der Text Indexstruktur, Änderungen an dem Default Speicherverhalten vorzunehmen, um unter Umständen bei Indexzugriffen weniger I/Os durchführen zu müssen. Gemeint ist damit die $I Tabelle und die Spalte TOKEN_INFO.
Welche Informationen speichert die Spalte TOKEN_INFO? Zur Erinnerung: Jedes Wort (besser Token) wird über die DOCID (Dokumenten ID des Dokuments, das das Token enthält) und die entsprechenden Wortpositionen in diesem Dokument gefunden. Beide Informationen werden in der Spalte TOKEN_INFO in binärer Form gespeichert. Standardmässig ist diese Spalte vom Datentyp BLOB und kann Informationen bis zu 4000 Bytes (dies ist eine interne Begrenzung) speichern. Müssen viele Informationen (mehr als 4000 Bytes) in der TOKEN_INFO Spalte gespeichert werden, werden neue Zeilen hinzugefügt. Die Idee in 12c ist nun, weniger Zeilen für große TOKEN_INFO Einträge speichern zu müssen. Aber schauen wir uns zuerst das Standrrdverhalten an einem einfachen Beispiel an.
Überprüft man die genaue Definition der Tabelle DR$MY_INDEX$I, kann man feststellen, dass sich die Defaultspeicherung von LOBs in 12c geändert hat und nun statt der Basicfile eine Securefile Speicherung vorliegt. Für diejenigen, die es genau wissen wollen: Dies liegt an dem geänderten Parameterwert PREFERRED für den Initialisierungsparameter DB_SECUREFILE.
Nun wenden wir das neue Feature BIG_IO an, dass diese Grenze aufheben soll. Dazu ist die neue Option BIG_IO als Attribute in der Preference BASIC_STORAGE auf TRUE zu setzen.
BIG_IO sorgt also dafür, dass wir unter Umständen weniger Einträge pro Token in der $I Tabelle speichern. Dies kann dann dabei helfen, die Zugriffe auf große Indexfragmente zu reduzieren.
Welche Informationen speichert die Spalte TOKEN_INFO? Zur Erinnerung: Jedes Wort (besser Token) wird über die DOCID (Dokumenten ID des Dokuments, das das Token enthält) und die entsprechenden Wortpositionen in diesem Dokument gefunden. Beide Informationen werden in der Spalte TOKEN_INFO in binärer Form gespeichert. Standardmässig ist diese Spalte vom Datentyp BLOB und kann Informationen bis zu 4000 Bytes (dies ist eine interne Begrenzung) speichern. Müssen viele Informationen (mehr als 4000 Bytes) in der TOKEN_INFO Spalte gespeichert werden, werden neue Zeilen hinzugefügt. Die Idee in 12c ist nun, weniger Zeilen für große TOKEN_INFO Einträge speichern zu müssen. Aber schauen wir uns zuerst das Standrrdverhalten an einem einfachen Beispiel an.
SQL> create table my_table( id number primary key, text varchar2(2000) ); Table created. SQL> create index my_index on my_table( text ) indextype is ctxsys.context; Index created. SQL> desc DR$MY_INDEX$I Name Null? Type ----------------------------------------- -------- ---------------------------- 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
Überprüft man die genaue Definition der Tabelle DR$MY_INDEX$I, kann man feststellen, dass sich die Defaultspeicherung von LOBs in 12c geändert hat und nun statt der Basicfile eine Securefile Speicherung vorliegt. Für diejenigen, die es genau wissen wollen: Dies liegt an dem geänderten Parameterwert PREFERRED für den Initialisierungsparameter DB_SECUREFILE.
SQL> set long 10000 pagesize 100
SQL> execute DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE=>-
DBMS_METADATA.SESSION_TRANSFORM, name=>'STORAGE', value=>false);SQL> SELECT DBMS_METADATA.GET_DDL(object_type=>'TABLE', name=>'DR$MY_INDEX$I') AS ausgabe
FROM dual;
AUSGABE
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."DR$MY_INDEX$I"
( "TOKEN_TEXT" VARCHAR2(64) NOT NULL ENABLE,
"TOKEN_TYPE" NUMBER(10,0) NOT NULL ENABLE,
"TOKEN_FIRST" NUMBER(10,0) NOT NULL ENABLE,
"TOKEN_LAST" NUMBER(10,0) NOT NULL ENABLE,
"TOKEN_COUNT" NUMBER(10,0) NOT NULL ENABLE,
"TOKEN_INFO" BLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
LOB ("TOKEN_INFO") STORE AS SECUREFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES )
MONITORING
Dies ändert allerdings noch nichts an der internen Begrenzung auf 4000 Bytes. Ein einfaches Beispiel demonstriert das Standardverhalten - auch in 12c. Dazu legen wir eine einfache Tabelle mit einer Spalte an und speichern 5000 Mal den Eintrag HELLO ab.SQL> drop table my_table purge; Table dropped
SQL> create table my_table (text varchar2(80)); Table created
SQL> begin
for i in 1 .. 5000 loop
insert into my_table values ('hello');
commit;
end loop;
end;
/
SQL> create index my_index on my_table (text) indextype is ctxsys.context;
Index createdSelektieren wir nun die $I Tabelle, stellen wir fest, dass 5 Zeilen für das Token HELLO verwendet wurden - mit einer Länge von 2499 bzw. 3501 Bytes.
SQL> column token_text format a15 SQL> select token_text, length(token_info) from dr$my_index$i; TOKEN_TEXT LENGTH(TOKEN_INFO) --------------- ------------------ HELLO 2499 HELLO 3501
HELLO 3501 HELLO 3501 HELLO 3501
Nun wenden wir das neue Feature BIG_IO an, dass diese Grenze aufheben soll. Dazu ist die neue Option BIG_IO als Attribute in der Preference BASIC_STORAGE auf TRUE zu setzen.
SQL> begin
ctx_ddl.create_preference( 'my_storage', 'BASIC_STORAGE' );
ctx_ddl.set_attribute ( 'my_storage', 'BIG_IO', 'true' );
end;
/ -- entweder Neuanlegen des Index oder mit ALTER INDEX
SQL> alter index my_index rebuild parameters ('replace storage my_storage');
Index altered.
Wenn wir nun die Token Information überprüfen, finden wir nur noch einen einzigen (!) Eintrag vor - allerdings der Länge 15023.SQL> select token_text, length(token_info) from dr$my_index$i; TOKEN_TEXT LENGTH(TOKEN_INFO) --------------- ------------------ HELLO 15023
BIG_IO sorgt also dafür, dass wir unter Umständen weniger Einträge pro Token in der $I Tabelle speichern. Dies kann dann dabei helfen, die Zugriffe auf große Indexfragmente zu reduzieren.
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.
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.
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.
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 274877906944Die 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.
Abonnieren
Posts (Atom)