Mit der neuen Oracle Database 12c Version sind einige interessante neue Features im Oracle Text Umfeld implementiert worden. Nach und nach wollen wir diese in unseren deutschsprachigen Postings thematisieren.
Wer jetzt allerdings schon einen Überblick über alle neuen Text Features erhalten möchte, kann entweder im
Oracle Text Application Developer's Guide 12c Release 1 (12.1) im Abschnitt New Features recherchieren oder sich über das neue Whitepaper New Features in Oracle Text with Oracle Database12c informieren.
Viel Spaß dabei!
Montag, 26. August 2013
Montag, 15. Juli 2013
Oracle TEXT in Oracle12c: Neues Feature 'Pattern Stopclass'
Heute festgestellt, dass das letzte Blog Posting schon fast 3 Monate zurückliegt. Nun wird es aber Zeit:
Das neue Datenbankrelease Oracle12c bringt auch im Bereich Oracle TEXT einige neue Features
mit sich - diese werden wir nach und nach in diesem Blog besprechen. Heute geht es um
die neue Möglichkeit, eine Stopwortliste nicht nur mit einzelnen Wörtern, sondern
mit Stop-Patterns zu versehen. Hierauf haben viele sicherlich schon lange gewartet. Ein
Beispiel: Wir erzeugen eine Tabelle und füllen diese mit ein paar "Wörtern" ...
create table tab_stopclasstest ( textcol varchar2(200) ) / insert into tab_stopclasstest values ('200'); insert into tab_stopclasstest values ('100'); insert into tab_stopclasstest values ('99'); insert into tab_stopclasstest values ('Oracle TEXT'); insert into tab_stopclasstest values ('A100'); insert into tab_stopclasstest values ('01.09.2012');
Wenn es nun an die Definition der Stopwörter geht, so konnte man bislang mit
CTX_DDL.CREATE_STOPLIST eine Stopliste erzeugen und mit
CTX_DDL.ADD_STOPWORD die Wörter ("Oracle") hinzufügen.
begin ctx_ddl.create_stoplist( stoplist_name => 'NEUE_STOPLISTE' ); ctx_ddl.add_stopword ('NEUE_STOPLISTE', 'Oracle'); ctx_ddl.add_stopclass('NEUE_STOPLISTE', 'NUMBERS'); end; /
Aber genau
dabei beginnt in vielen Fällen das Problem: Denn wenn Zahlen nicht indiziert werden sollen,
ist es nahezu unmöglich, im Vorfeld alle möglichen Varianten als Stopwörter zu bestimmen. Die
Stopclasses machen nun genau das möglich (übrigens kann man die Stopklasse NUMBERS
schon in Oracle 11.2 verwenden) - aber dort eben nicht mehr. Anders in Oracle12c ...
begin ctx_ddl.create_stoplist( stoplist_name => 'NEUE_STOPLISTE' ); ctx_ddl.add_stopword ('NEUE_STOPLISTE', 'Oracle'); ctx_ddl.add_stopclass('NEUE_STOPLISTE', 'NUMBERS'); ctx_ddl.add_stopclass('NEUE_STOPLISTE', 'KLASSE_1','[A-Z]\d+'); end; /
CTX_DDL.ADD_STOPCLASS nimmt drei Parameter entgegen. Der erste ist, wie schon
bei ADD_STOPWORD, der Name der Stopliste. Danach kommt die "Stopklasse" - Oracle
bringt eine vordefinierte Klasse mit: NUMBERS, die, wie gesagt, schon in 11.2 vorhanden ist.
Ab Oracle 12.1 kann man aber auch einen anderen
Namen eintragen - und dann braucht es noch den dritten Parameter: Dort wird ein regulärer Ausdruck
hinterlegt, der die zu ignorierenden Wörter erfasst. In obigem Beispiel wäre das genau ein
Buchstabe, gefolgt von mindestens einer Zahl. Legt man, basierend auf dieser Stopliste einen Index
auf die obige Tabelle an, so wird dieser nur ein einziges Token enthalten: TEXT.
create index ft_stopclasstest on tab_stopclasstest (textcol) indextype is ctxsys.context parameters ('stoplist neue_stopliste') / select token_text from dr$ft_stopclasstest$i / TOKEN_TEXT ----------------------------------------- TEXT
Viel Spaß beim Ausprobieren.
Donnerstag, 18. April 2013
Indexstatistiken (INDEX_STATS) im XML-Format: Oracle TEXT Management einfach automatisieren!
Heute geht es um Statistiken für einen Oracle TEXT Index - vor einiger Zeit gab es
dazu schon mal ein Posting - heute soll es darum gehen, wie man
diese Statistiken so zur Verfügung stellt, dass man mit Reporting- oder Management-Tools
einfach darauf zugreifen oder diese zur Automatisierung von Aufgaben verwenden kann.
Indexstatistiken kann man mit
CTX_REPORT.INDEX_STATS abrufen. In nahezu allen Beispielen
(so auch im erwähnten Blog-Posting) werden die Statistiken als Textausgabe erzeugt - in etwa wie folgt ...
=========================================================================== STATISTICS FOR "TESTIT"."IDX_TEXT" =========================================================================== indexed documents: 10 allocated docids: 10 $I rows: 56 --------------------------------------------------------------------------- TOKEN STATISTICS --------------------------------------------------------------------------- :
Dieses Format ist zwar gut lesbar, zur Automatisierung jedoch völlig ungeeignet. Allerdings
bietet Oracle TEXT noch eine andere Variante zur Ausgabe der Statistiken an: XML - und
das geht wie folgt.
DROP TABLE ausgabe / CREATE TABLE ausgabe ( index_name varchar2(200), zeitstempel date, resultat xmltype ) xmltype column resultat store as binary xml / declare ergebnis clob := null; begin ctx_report.index_stats( index_name => 'IDX_TEXT', report => ergebnis, report_format => ctx_report.fmt_xml, stat_type => null ); insert into ausgabe values ('IDX_TEXT', sysdate, xmltype(ergebnis)); dbms_lob.freetemporary(ergebnis); end; / set long 32000 set head off set pagesize 10000 SELECT * FROM ausgabe /
Das generierte Format sieht nun wie folgt aus ...
<CTXREPORT> <INDEX_STATS> <STAT_INDEX_NAME>"TESTIT"."IDX_TEXT"</STAT_INDEX_NAME> <STAT_INDEX_STATS> <STAT_STATISTIC NAME="indexed documents">10</STAT_STATISTIC> <STAT_STATISTIC NAME="allocated docids">10</STAT_STATISTIC> <STAT_STATISTIC NAME="$I rows">56</STAT_STATISTIC> :
Bei großen Indizes braucht der Aufruf von INDEX_STATS sehr lange - hier ist es sicher sinnvoll,
mit Hilfe von
DBMS_SCHEDULER einen Job zu erzeugen, welcher die Statistiken
regelmäßig (bspw. über Nacht) aktualisiert. Das Interessante am XML-Format ist nun,
dass es sich maschinell auswerten lässt. Dazu nutzen wir die XML-Funktionen in der
Oracle-Datenbank und erstellen eine (relationale) View auf die XML-Ausgabe von
CTX_REPORT.INDEX_STATS.
create or replace view view_index_stats as select a.index_name, a.zeitstempel, x.indexed_documents, x.allocated_docids, x.dollar_i_rows, x.dollar_i_dsize, x.index_frag, x.garbage_docids, x.garbage_size from ausgabe a, xmltable( '/CTXREPORT/INDEX_STATS' passing a.resultat columns indexed_documents number path '/INDEX_STATS/STAT_INDEX_STATS/STAT_STATISTIC[@NAME="indexed documents"]', allocated_docids number path '/INDEX_STATS/STAT_INDEX_STATS/STAT_STATISTIC[@NAME="allocated docids"]', dollar_i_rows number path '/INDEX_STATS/STAT_INDEX_STATS/STAT_STATISTIC[@NAME="$I rows"]', dollar_i_dsize number path '/INDEX_STATS/STAT_FRAG_STATS/STAT_STATISTIC[@NAME="total size of $I data"]', index_frag varchar2(10) path '/INDEX_STATS/STAT_FRAG_STATS/STAT_STATISTIC[@NAME="estimated row fragmentation"]', garbage_docids number path '/INDEX_STATS/STAT_FRAG_STATS/STAT_STATISTIC[@NAME="garbage docids"]', garbage_size number path '/INDEX_STATS/STAT_FRAG_STATS/STAT_STATISTIC[@NAME="estimated garbage size"]' ) x /
Diese View lässt sich nun wie eine Tabelle verwenden ...
SQL> select * from view_index_stats INDEX_NAME ZEITSTEMPEL INDEXED_DOCUMENTS ALLOCATED_DOCIDS ---------- -------------------- ----------------- ---------------- DOLLAR_I_ROWS DOLLAR_I_DSIZE INDEX_FRAG GARBAGE_DOCIDS GARBAGE_SIZE ------------- -------------- ---------- -------------- ------------ IDX_TEXT 18.04.2013 14:43:49 10 10 56 198 0 % 0 0 1 Zeile wurde ausgewählt.
Nicht so schön ist die Spalte INDEX_FRAG - denn das XML, welches von INDEX_STATS generiert
wurde, enthält hier tatsächlich das Prozentzeichen - weshalb es nicht als NUMBER aufgefasst
werden kann - hierfür müssen wir in der View-Definition noch ein wenig was tun ...
create or replace view view_index_stats as select a.index_name, a.zeitstempel, x.indexed_documents, x.allocated_docids, x.dollar_i_rows, x.dollar_i_dsize, to_number(replace(x.index_frag, ' %', '')) as index_frag, x.garbage_docids, :
Danach ist auch die Spalte INDEX_FRAG vom Typ NUMBER und man kann nun numerische
Vergleiche durchführen. Analog dazu lassen sich auch die Token-Statistiken
entsprechend aufbereiten ... Dazu bauen wir eine zweite View ...
create or replace view index_stats_frag_tokens as select a.index_name, a.zeitstempel, x.token_text, x.token_type, to_number(replace(x.token_frag, ' %', '')) as token_frag from ausgabe a, xmltable( '/CTXREPORT/INDEX_STATS/STAT_FRAG_STATS/STAT_TOKEN_LIST/STAT_TOKEN' passing a.resultat columns token_text varchar2(64) path '/STAT_TOKEN/STAT_TOKEN_TEXT', token_type varchar2(50) path '/STAT_TOKEN/STAT_TOKEN_TYPE', token_frag varchar2(20) path '/STAT_TOKEN/STAT_TOKEN_STATISTIC' ) x / select * from index_stats_frag_tokens /
... deren Inhalt nun wie folgt aussieht ...
SQL> select * from index_stats_frag_tokens INDEX_NAME ZEITSTEMPEL TOKEN_TEXT TOKEN_TYPE TOKEN_FRAG ---------- -------------------- -------------------- -------------------- ---------- IDX_TEXT 18.04.2013 14:55:28 ZU 0:TEXT 0 IDX_TEXT 18.04.2013 14:55:28 WOLLEN 0:TEXT 0 IDX_TEXT 18.04.2013 14:55:28 WIRTSCHAFT 0:TEXT 0 IDX_TEXT 18.04.2013 14:55:28 WIRD 0:TEXT 0 IDX_TEXT 18.04.2013 14:55:28 WICHTIGER 0:TEXT 0 IDX_TEXT 18.04.2013 14:55:28 WERDEN 0:TEXT 0 IDX_TEXT 18.04.2013 14:55:28 WER 0:TEXT 0 IDX_TEXT 18.04.2013 14:55:28 WEITER 0:TEXT 0 IDX_TEXT 18.04.2013 14:55:28 WAHLKAMPF 0:TEXT 0 :
Lässt man das XML nun, wie schon erwähnt, per DBMS_SCHEDULER regelmäßig aktualisieren, so
hat man (wie im Data Dictionary) bequem nutzbare Views mit Statistiken zum Index. Diese
können nun natürlich auch für eine automatisierte Verarbeitung genutzt werden. So könnte
ein Job regelmäßig alle Tokens optimieren (CTX_DDL.OPTIMIZE mit OPTLEVEL_TOKEN),
die eine bestimmte Fragmentierung überschreiten. Gleiches gilt natürlich auf Indexebene.
Auch
die Integration mit Management-Werkzeugen wie dem Oracle Enterprise Manager ist kein Problem. In
der deutschsprachigen DBA Community
ist beschrieben, wie man eine eigene "Metrik", basierend auf einer SQL-Abfrage einrichtet. Nimmt man hierfür die erstellten Views, so kann der Enterprise Manager einen Alert senden, sobald
die Indexfragmentierung einen bestimmten Schwellenwert übersteigt. Die professionelle Wartung einer
Oracle TEXT-Installation ist damit kein Problem mehr ...
Dienstag, 5. März 2013
Alle Posts auf einen Blick
Um einen besseren Überblick über die schon veröffentlichten Posts in diesem Blog zu bekommen, an dieser Stelle eine aktuelle Linksammlung ...
Grundsätzliche Themen: Administration, Index anlegen ...
Grundsätzliche Themen: Administration, Index anlegen ...
- Performance beim CREATE INDEX
- FILE_DATASTORE und Security oder: Was ist die FILE_ACCESS_ROLE?
- 11.2.0.4 und Oracle Text Patch
- Ein Einstieg in Oracle Text
- Oracle Text Komponente überprüfen und installieren
- Welche DR$-Tabellen gibt es und wozu sind sie gut?
- Der CTXCAT-Index
- DML Operationen und der Oracle Text Index
- Index Synchronisierung und Transactional Parameter
- Index-Optimierung: Einige Grundlagen
- Tablespace und mehr: Storage-Klausel für den Oracle-TEXT Index
- Stopwörter und Stoplisten: Unwichtige Wörter ...
- Einstellungen für einen Textindex: Komposita, Printjoins, Skipjoins, Mixed Case und mehr ...
- Einzelne Zeilen vom Index ausnehmen - Parameter "FORMAT COLUMN"
- Einige Gedanken zu Oracle TEXT und Tabellen-Partitionierung
- Der Textindex und der Datentyp SECUREFILE
- Oracle TEXT und Database Links ...
- Hochverfügbarer Textindex mit Schattenindex-Technologie
- Arbeiten mit einem Thesaurus in Oracle TEXT
- Mehrere Sprachen in einer Tabelle unterstützen: MULTI_LEXER
- Oracle Text Features im Überblick - 11g Release 1, 11g Release 2
- DBMS_PCLXUTIL zur Erzeugung von lokalen Indizes
- Oracle 12c Alle 12c Text Features auf einen Blick!
- Oracle 12c: Automatic Near Real-Time Index
- Transaktionen und CTX_DDL: COMMIT oder nicht COMMIT?
- 12c Feature: $I Tabelle mit neuer Storage Preference BIG_IO
- Statistiken im Oracle Text Umfeld
- Mehr Performance durch Index-Preloading
- Fragmentierung feststellen mit INDEX_STATS
- Monitoren von Oracle Text Indizes: Grundsätzliches
- Query Result Set Feature
- CTX_QUERY.EXPLAIN: Erklärungen für eine TEXT-Abfrage
- Suchverhalten der Anwender monitoren: Abfrage-Analyse mit CTX_REPORT
- Indexstatistiken (INDEX_STATS) im XML-Format: Oracle TEXT Management einfach automatisieren!
- Suche in JSON Dokumenten in 12.2: wie geht das?
- Ergebniscache für TEXT-Abfragen: Oracle12c TEXT Query Filter Cache
- Oracle12c: Verändertes Verhalten bei Datastore Triggern
- Oracle12c: Neues Feature 'Pattern Stopclass'
- Oracle12c: Unterstützung für XML-Namespaces in Oracle TEXT
- Keyword in Context: CTX_DOC.SNIPPET
- Ähnliche "Tokens" mit der BROWSE_WORDS-Funktion finden
- Abfragen vordefinieren: Stored Query Expressions (SQE)
- Ergebnisse zählen: Nicht mit count(*), sondern mit COUNT_HITS!
- Viele Abfragen auf einmal: Query Relaxation
- Wie ähnlich soll es sein ...? Ein paar Worte zum FUZZY-Operator
- Neues Oracle TEXT-Feature NAME SEARCH
- Unscharfe Namenssuche (Name Search) mit NDATA
- Name Matching (NDATA) mit einem Thesaurus "erweitern"
- Mächtige Suchabfragen: PL/SQL-Funktionen innerhalb CONTAINS()
- Suche nach "Aktenzeichen" oder "Autonummer" mit Oracle TEXT: Sind Printjoins die Lösung?
- Section"-Suche in Oracle TEXT
- Die Wordlist und das wildcard_maxterms Attribut
- "Sicheres" Suchen: Oracle TEXT und die "Virtual Private Database"
- Scoring mit Oracle Text
- Filter Preferences - Grundsätzliches
- USER_DATASTORE ... indiziert wirklich alles
- Nochmal USER_DATASTORE: Ein umfassendes Beispiel - Teil I
- Mehrere Tabellenspalten indizieren: MULTICOLUMN_DATASTORE
- MDATA Section und MULTI_COLUMN_DATASTORE
- Anwendungsbeispiel für MULTI_COLUMN_DATASTORE, MDATA und Operatoren ACCUM und WEIGHT
- Metadaten-Suche mit MDATA Section
- Mixed Queries in 11g
- Abfrage-Optimierung mit Composite Domain Index
- Treffer zählen:COUNT_HITS und Mixed Queries zusammen!
Mittwoch, 12. Dezember 2012
Suche nach "Aktenzeichen" oder "Autonummer" mit Oracle TEXT: Sind Printjoins die Lösung?
Heute möchte ich mich dem Thema "Suche nach Sonderzeichen" und dem damit verbundenen
Thema Printjoins in Oracle TEXT widmen. Vorab schon soviel: Dieses Posting wird
eine Warnung vor Printjoins - mit diesem Feature sollte sehr vorsichtig umgegangen werden.
Printjoins werden mitunter verwendet, wenn
man "Strukturen" wie Aktenzeichen oder "Autonummern" in den Dokumenten hat.
Das könnte in etwa so aussehen.
create table dokumente( id number(10), doc varchar2(4000) ) / insert into dokumente values ( 1, 'Aktenzeichen 67.MEIER.1455-2012: Steuersache Meier.Erklärung abgegeben' ); insert into dokumente values ( 2, 'Aktenzeichen 12.MUSTER.1455-2012: Steuersache Muster.Erklärung abgegeben.' ); commit /
Immer wieder kommt die Anforderung, exakt nach dem
Aktenzeichen suchen zu können. Oracle Text erkennt diese Struktur jedoch nicht und indiziert
wie folgt:
create index ft_dokumente on dokumente(doc) indextype is ctxsys.context / select token_text from dr$ft_dokumente$i / TOKEN_TEXT ---------------------- 12 1455 2012 67 ABGEGEBEN AKTENZEICHEN ERKLÄRUNG MEIER MUSTER STEUERSACHE
Wenn man nun nach dem Term 1455 sucht, werden beide Dokumente zurückgeliefert. Fachlich
ist das eigentlich falsch, denn die 1455 kommt alleinstehend nirgends vor - sie ist überall
Teil des Aktenzeichens.
SQL> select * from dokumente where contains(doc, '1455') > 0 / ID DOC ---------- -------------------------------------------------- 1 Aktenzeichen 67.MEIER.1455-2012: Steuersache Meier .Erklärung abgegeben 2 Aktenzeichen 12.MUSTER.1455-2012: Steuersache Must er.Erklärung abgegeben.
Um diesen Effekt zu verhindern, werden dann gerne Printjoins eingesetzt. Zeichen, die als
Printjoins deklariert werden, trennen Wörter nicht mehr voneinander - sie werden dann
(nicht ganz) wie Buchstaben behandelt. Ist also das Zeichen "-" als Printjoin deklariert,
dann wird der Willy-Brandt-Platz als ein Token "Willy-Brandt-Platz" indiziert und nicht
als drei Tokens "Willy", "Brandt" und "Platz".
begin ctx_ddl.drop_preference('MY_PJ_PREF'); end; / sho err begin ctx_ddl.create_preference('MY_PJ_PREF', 'BASIC_LEXER'); ctx_ddl.set_attribute('MY_PJ_PREF', 'PRINTJOINS', '.-'); end; / sho err create index ft_dokumente on dokumente(doc) indextype is ctxsys.context parameters ('lexer MY_PJ_PREF') / select token_text from dr$ft_dokumente$i / TOKEN_TEXT ----------------------------- 12.MUSTER.1455-2012 67.MEIER.1455-2012 ABGEGEBEN AKTENZEICHEN MEIER.ERKLÄRUNG MUSTER.ERKLÄRUNG STEUERSACHE
Die Anforderung, dass Teile des Aktenzeichens nicht mehr das Aktenzeichen finden, ist erfüllt.
Auf den ersten Blick ist das doch eine gute Lösung, oder ...?
select * from dokumente where contains(doc, '1455') > 0; Es wurden keine Zeilen ausgewählt select * from dokumente where contains(doc, '{67.MEIER.1455-2012}') > 0; ID DOC ---------- ---------------------------------------------------------------------- 1 Aktenzeichen 67.MEIER.1455-2012: Steuersache Meier.Erklärung abgegeben
Wie man aber schon am Inhalt der Token-Tabelle erkennen kann, hat das ganze einige
"Nebenwirkungen" ... die Suche nach dem Meier schlägt nun fehl.
SQL> select * from dokumente where contains(doc, 'Meier') > 0 / Es wurden keine Zeilen ausgewählt
Das ist logisch, weil das Token Meier gar nicht indiziert wurde. In den Dokumenten
fehlt dummerweise das Leerzeichen nach dem Punkt zwischen Meier und Erklärung. Da
der Punkt selbst ein Printjoin ist, wurde Meier.Erklärung indiziert. Und eine Suche
nach dem Meier schlägt nun fehl.
Printjoins werden stets global für den ganzen Index definiert. Wenn also
der Bindestrich eines Aktenzeichens als Printjoin deklariert wird, gilt das nicht
nur für die Aktenzeichen, sondern für alle Bindestriche im gesamten Dokumentbestand:
Das Aufnehmen zusätzlicher Zeichen zu den Printjoins sollte also stets mit Vorsicht
gemacht werden, es führt fast immer zu unerwünschten Nebenwirkungen, für die dann aufwändige
Workarounds mit Wildcards ("Meier%") nötig werden.
Doch wie geht man mit dem Thema Aktenzeichen um?
Eine denkbare Lösung könnte ein
PROCEDURE_FILTER sein. Dieser sucht mit einem regulären Ausdruck
nach dem Aktenzeichen und wandelt die Bindestriche und Punkte in ein Zeichen, welches
definitiv keine Probleme macht, um - das könnte bspw. der Underscore ("_") sein.
Zunächst erstellen wir also die Prozedur für
den PROCEDURE_FILTER.
create or replace function escape_aktenzeichen(p_az in varchar2) return varchar2 deterministic is begin return regexp_replace(p_az, '(\d\d)(.)([A-Z]*)(.)(\d*)(-)(\d*)', '\1_\3_\5_\7'); end escape_aktenzeichen; / sho err create or replace procedure aktenzeichen_filter( p_src IN VARCHAR2, p_dst IN OUT NOCOPY VARCHAR2 ) is begin p_dst := escape_aktenzeichen(p_src); end aktenzeichen_filter; / sho err
Dass die eigentliche Funktionalität in eine separate Funktion gepackt wurde, hat einen Sinn - dazu weiter unten mehr. Dann erstellen wir die Filter Preference ...
begin ctx_ddl.create_preference('MY_AZ_FILTER', 'procedure_filter'); ctx_ddl.set_attribute('MY_AZ_FILTER', 'procedure', 'aktenzeichen_filter'); ctx_ddl.set_attribute('MY_AZ_FILTER', 'input_type', 'varchar2'); ctx_ddl.set_attribute('MY_AZ_FILTER', 'output_type', 'varchar2'); ctx_ddl.set_attribute('MY_AZ_FILTER', 'rowid_parameter', 'false'); ctx_ddl.set_attribute('MY_AZ_FILTER', 'charset_parameter', 'false'); end; / sho err
... und nicht zu vergessen: Wir definieren die Lexer Preference neu, damit der
Underscore (und nur der Underscore) das neue Printjoin wird.
begin ctx_ddl.drop_preference('MY_PJ_PREF'); end; / sho err begin ctx_ddl.create_preference('MY_PJ_PREF', 'BASIC_LEXER'); ctx_ddl.set_attribute('MY_PJ_PREF', 'PRINTJOINS', '_'); end; / sho err
Nun noch indizieren ...
create index ft_dokumente on dokumente(doc) indextype is ctxsys.context parameters ('lexer MY_PJ_PREF filter MY_AZ_FILTER') /
Und jetzt sieht die Token-Tabelle so aus:
TOKEN_TEXT ------------------------- 12_MUSTER_1455_2012 67_MEIER_1455_2012 ABGEGEBEN AKTENZEICHEN ERKLÄRUNG MEIER MUSTER STEUERSACHE
Eine Suche nach 1455 schlägt nun fehl, so wie es sein soll.
select * from dokumente where contains(doc, '1455') > 0; Es wurden keine Zeilen ausgewählt
Wenn nun nach einem Aktenzeichen gesucht werden soll, muss man das Aktenzeichen
in der Suchanfrage natürlich auch umwandeln - es darf also nicht mehr nach 12.MUSTER.1455-2012,
vielmehr muss nach 12_MUSTER_1455_2012 gesucht werden. Und jetzt ist es sehr nützlich, dass
wir vorhin die Funktion ESCAPE_AKTENZEICHEN gebaut haben ...
select * from dokumente where contains(doc, escape_aktenzeichen('12.MUSTER.1455-2012')) > 0; ID DOC ---------- -------------------------------------------------- 2 Aktenzeichen 12.MUSTER.1455-2012: Steuersache Must er, Erklärung abgegeben.
Voilá. Und das ganze lässt sich natürlich auch mit binären (PDF, Office)-Dokumenten
kombinieren - in diesem Fall muss der PROCEDURE_FILTER vor dem Anwenden des regulären
Ausdrucks mit
CTX_DOC.POLICY_FILTER das eigentliche Umwandeln des Binärformats
in ASCII-Text machen.
create or replace procedure aktenzeichen_filter( p_src IN VARCHAR2, p_dst IN OUT NOCOPY VARCHAR2 ) is begin CTX_DOC.POLICY_FILTER( ... ); p_dst := escape_aktenzeichen(p_src); end aktenzeichen_filter; / sho err
Donnerstag, 18. Oktober 2012
Treffer zählen:COUNT_HITS und Mixed Queries zusammen!
Vor nun schon fast vier Jahren hatten wir bereits ein Blog-Posting zum Thema
"Treffer zählen mit Oracle TEXT" veröffentlicht. Zusammengefasst kann man
sagen, dass man nach Möglichkeit immer mit CTX_QUERY.COUNT_HITS arbeiten
sollte. Diese Prozedur stellt sicher, dass die Zählung ausschließlich im Textindex stattfindet und
dass (teure) Zugriff auf die Basistabelle unterbleiben.
set serveroutput on declare v_number number; begin v_number := ctx_query.count_hits( index_name => 'MY_FULLTEXT_IDX', text_query => 'oracle and text', exact => true ); dbms_output.put_line('Anzahl Treffer: '||v_number); end; / Anzahl Treffer: 2657
Doch was ist, wenn der Oracle Textindex mit dem in Oracle11g neuen Feature
Composite Domain Index erstellt wird ...?
CREATE INDEX comp_ind ON customers(cust_first_name) INDEXTYPE IS ctxsys.context FILTER BY cust_id, cust_year_of_birth ORDER BY cust_year_of_birth
Der Composite Domain Index nimmt die in der FILTER BY-Klausel angegebenen Spalten mit
in den Volltextindex auf und führt mixed Queries wie die folgende dann allein
mit Hilfe des Oracle TEXT Index aus.
SELECT cust_id FROM customers WHERE contains (cust_first_name, 'A% or D% or N% or B%') > 0 AND cust_year_of_birth > 1970 /
Wenn nun die Treffer gezählt werden sollen, tut man sich bei der Nutzung von
CTX_QUERY.COUNT_HITS etwas schwer ... denn wo soll man das Filterkriterium
cust_year_of_birth > 1970 einsetzen ...?
FUNCTION COUNT_HITS RETURNS NUMBER Argument Name Typ In/Out Defaultwert? ------------------------------ ----------------------- ------ -------- INDEX_NAME VARCHAR2 IN TEXT_QUERY VARCHAR2 IN EXACT BOOLEAN IN DEFAULT PART_NAME VARCHAR2 IN DEFAULT
Die Lösung ist einfacher, als man denkt: Denn für jede der in der FILTER BY-Klausel
angegebenen Spalten bildet Oracle TEXT eine
SDATA-Section gleichen Namens. In diesem Fall haben wir
also die SDATA Sections CUST_YEAR_OF_BIRTH und CUST_ID. Und diese
lässt sich nun auch als Teil der CONTAINS-Abfrage explizit ansprechen. Die
CONTAINS-Abfrage mit SDATA-Section sieht dann so aus ...
'A% or D% or N% or B% and SDATA(cust_year_of_birth > 1970)
Eingesetzt in CTX_QUERY.COUNT_HITS ...
set serveroutput on declare v_number number; begin v_number := ctx_query.count_hits( index_name => 'COMP_IND', text_query => 'A% or D% or N% or B% and SDATA(cust_year_of_birth > 1970)', exact => true ); dbms_output.put_line('Anzahl Treffer: '||v_number); end; / Anzahl Treffer: 1623
Mehr Information in der Oracle Dokumentation - TEXT Reference.
Montag, 10. September 2012
Einige Gedanken zu Oracle TEXT und Tabellen-Partitionierung
Dieses Blog Posting widmet sich dem Thema Partitionierung und Oracle TEXT. Die Grundzüge der
Partitionierung werden hier jedoch nicht mehr erläutert; es wird davon ausgegangen, dass der
Leser weiss, wie Partitionierung funktioniert. Informationen zum Thema finden sich in
der der
Dokumentation im "VLDB and Partitioning Guide" und im Data Sheet "Partitioning".
Es wird also darüber nachgedacht, eine Tabelle mit Dokumenten, auf die ein Oracle TEXT Index
angelegt werden soll, zu partitionieren. Partitionierung kann folgende Vorteile bieten:
- Wenn der Partitionierungsschlüssel gleichzeitig ein Abfragekriterium ist, kann der Optimizer die jeweilige Abfrage auf die relevanten Partitionen beschränken (Partition Pruning). Damit Oracle TEXT davon profitiert, braucht es einen lokal partitionierten Textindex.
- Partitionierung erlaubt administrative Arbeiten an ganzen Partitionen - so können Partitionen als Ganzes gelöscht werden. Als eigene Tabelle vorhandene Daten können per Partition Exchange als neue Partition an die Tabelle gehängt werden. Auch hier sollte der Oracle TEXT Index lokal partitioniert sein; ein globaler Textindex müsste ansonsten komplett neu gebaut werden.
Ein Oracle TEXT Index sollte also fast immer lokal partitioniert sein, also die gleiche
Partitionierung aufweisen, wie die zugrundeliegende Tabelle. Ist der Index nicht partitioniert,
gehen nicht nur einige Vorteile verloren; es können sogar Mehraufwände entstehen; bspw. wenn
eine Tabellenpartition (alte Daten) gelöscht wird. Ein lokal partitionierter Oracle TEXT Index
wird wie folgt erstellt - zunächst erzeugen wir eine Beispieltabelle.
create table doktest_part ( id number(10), text varchar2(200), datum date ) partition by range(datum) ( partition p_alt values less than (to_date('2010-12-31','YYYY-MM-DD')), partition p_2011 values less than (to_date('2011-12-31','YYYY-MM-DD')), partition p_2012 values less than (to_date('2012-12-31', 'YYYY-MM-DD')) ) / insert into doktest_part values (1, 'Dies ist ein Text aus 2011', DATE'2011-08-01'); insert into doktest_part values (2, 'Oracle TEXT indiziert Texte auch in 2012', DATE'2012-09-01'); commit /
Danach kommt der Volltextindex:
create index ft_doktest on doktest_part (text)
indextype is ctxsys.context
local
/
Das Schlüsselwort local erzeugt einen zur Tabelle "lokal" partitionierten Textindex. Eine
wichtige Einschränkung sei an dieser Stelle genannt. Alle Domain-Indizes, also auch Oracle TEXT,
unterstützen nur RANGE-Partitioning. Die Tabelle muss also RANGE-Partitioniert sein, damit ein
lokal partitionierter Textindex gebaut werden kann. Ist die Tabelle anders partitioniert, stößt das
CREATE INDEX auf einen Fehler.
Wenn nun aber eher eine List oder Hash-Partitionierung gebraucht wird, muss man diese mit
einer RANGE-Partitionierung emulieren. Wird beispielsweise ein HASH-Partitioning mit
4 Partitionen benötigt, so kann man die Partitionsnummer mit der Funktion ORA_HASH selbst
generieren. Dann lässt sich die Tabelle mit einer virtuellen Spalte ausstatten - und danach
kann man wieder eine normale RANGE-Partitionierung einsetzen. Diese wirkt nun aber wie
eine Hash-Partitionierung; anhand des Primärschlüssels werden die Zeilen nun möglichst gleichmäßig
über die vier Partitionen verteilt.
create table doktest_hashpart ( id number(10), text varchar2(200), part# as (ora_hash(id, 4, 81978923)) ) partition by range(part#) ( partition p_h1 values less than (2), partition p_h2 values less than (3), partition p_h3 values less than (4), partition p_h4 values less than (5) ) /
Auch LIST-Partitioning lässt sich mit RANGE-Partitioning emulieren. Geht es um einfache
Buchstabenkürzel, so kann man diese alphabetisch sortieren und direkt mit LESS THAN arbeiten;
Oracle ordnet die Zeilen dann anhand binärer Sortierung zu. Manchmal ist die Praxis aber
nicht so einfach. Angenommen, es soll nach einem Abteilungskürzel wie folgt partitioniert werden:
- Partition 1: Abteilungen A, B, C
- Partition 2: Abteilungen A1, A2, A3
- Partition 3: Abteilungen D-F
Also - "A" ist eine andere Abteilung als "A1" - und die werden in unterschiedliche Partitionen
einsortiert. Mit einem einfachen LESS THAN kann das nicht mehr ausgedrückt werden; da die Partition
1 als LESS THAN ('B') definiert werden müsste, würden A1 bis A3 ebenfalls dort einsortiert. Allerdings
kann uns ein SQL CASE Konstrukt weiterhelfen ...
case when abteilung in ('A', 'B', 'C') then 1 when abteilung in ('A1', 'A2', 'A3') then 2 when abteilung in ('D', 'E', 'F') then 3 end
Damit können wir die Tabelle wieder als RANGE-Partionierte Tabelle erzeugen, wobei wir
aber de-facto eine LIST-Partionierte Tabelle haben.
create table doktest_listpart ( id number(10), text varchar2(200), abteilung varchar2(3), part# as (case when abteilung in ('A', 'B', 'C') then 1 when abteilung in ('A1', 'A2', 'A3') then 2 when abteilung in ('D', 'E', 'F') then 3 end ) ) partition by range(part#) ( partition p_abc values less than (2), partition p_a1a2a3 values less than (3), partition p_def values less than (4) ) /
An dieser Stelle aber eine Warnung zu den virtuellen Spalten: Sobald eine Tabelle anhand einer virtuelle Spalte partitioniert wurde, kann diese
virtuelle Spalte nicht mehr geändert werden! Wenn also in diesem Beispiel neue Abteilungen
eingeführt werden, wäre es nicht mehr möglich, den CASE-Ausdruck zu erweitern, um die neue Abteilung
auf eine Partition abzubilden. Man sollte also sicher sein, dass man mit der virtuellen Spalte
die ganze Fachlichkeit korrekt, umfassend und nachhaltig abbildet. Wenn Ihr euch da nicht
sicher seid, ist eine "normale" Spalte mit einem Trigger (wie früher) vielleicht die sicherere Alternative ...
create table doktest_listpart_tr ( id number(10), text varchar2(200), abteilung varchar2(3), part# number(4) ) partition by range(part#) ( partition p_abc values less than (2), partition p_a1a2a3 values less than (3), partition p_def values less than (4) ) / create or replace trigger tr_setpart# before insert or update on doktest_listpart_tr for each row begin :new.part# := ( case when :new.abteilung in ('A', 'B', 'C') then 1 when :new.abteilung in ('A1', 'A2', 'A3') then 2 when :new.abteilung in ('D', 'E', 'F') then 3 end ); end; /
... denn den Trigger kann man auch nachträglich noch beliebig ändern.
Nun kann auf jede der Tabelle mit obigem CREATE INDEX-Kommando und dem Schlüsselwort
LOCAL ein lokal partitinierter Textindex erzeugt werden. For Composite Partitioning
kann das Verfahren ähnlich funktionieren; allerdings dürfte dieses Partitionsverfahren für
Oracle TEXT Anwendungen meist nicht das richtige sein.
In Oracle10g gab es mit 9999 noch
ein eigenes Limit für die Anzahl der Partitionen. Ab Oracle11g unterstützt Oracle TEXT
ebensoviele Partitionen wie die Datenbank selbst, nämlich 1048575
Die Tabellenstruktur sieht nach Erstellung des Index wie folgt aus.
TNAME TABTYPE CLUSTERI ------------------------------ ------- --------- DR#FT_PART10K0001$I TABLE DR#FT_PART10K0001$K TABLE DR#FT_PART10K0001$N TABLE DR#FT_PART10K0001$R TABLE DR#FT_PART10K0002$I TABLE DR#FT_PART10K0002$K TABLE DR#FT_PART10K0002$N TABLE DR#FT_PART10K0002$R TABLE :
- DR#
- Name des Volltextindex
- Laufende Nummer der Partition. Ab Partition 10000 arbeitet Oracle TEXT mit Buchstaben: 10000 ist also AAAA, 10001 ist AAAB und so fort.
- Kürzel für den Tabellentyp ($I, $N, $K, $R, $P, $S)
Ist der Partitionsschlüssel nun Teil der Abfrage, so findet (wie immer bei Partitioning) ein
Partition Pruning statt; der Optimizer beschränkt die Abfrage also auf die relevante
Partition. Im Ausführungsplan sieht das dann wie folgt aus.
--------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart | Pstop | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 4 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 1 | 14 | 4 (0)| 00:00:01 | 2 | 2 | |* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| DOKTEST_LISTPART | 1 | 14 | 4 (0)| 00:00:01 | 2 | 2 | |* 3 | DOMAIN INDEX | FT_DOKTEST_LISTPART | | | 4 (0)| 00:00:01 | | | ---------------------------------------------------------------------------------------------------------------------------
Man erkennt, dass die Abfrage auf die Partition 2 beschränkt wurde. Wurde allerdings, wie oben
beschrieben, eine Hash- oder List-Partitionierung "emuliert", so muss man aufpassen. Ein Beispiel anhand
der oben vorgestellten Emulation eines List-Partitioning:
create table doktest_listpart ( id number(10), text varchar2(200), abteilung varchar2(3), part# as (case when abteilung in ('A', 'B', 'C') then 1 when abteilung in ('A1', 'A2', 'A3') then 2 when abteilung in ('D', 'E', 'F') then 3 end ) ) partition by range(part#) ( partition p_abc values less than (2), partition p_a1a2a3 values less than (3), partition p_def values less than (4) ) /
Ein paar Zeilen einfügen ...
SQL> insert into doktest_listpart values (1, 'Die Oracle-Datenbank enthält Oracle TEXT','A1', DEFAULT); 1 Zeile wurde erstellt. SQL> insert into doktest_listpart values (2, 'Mit Oracle Spatial werden Geodaten verwaltet.','D', DEFAULT); 1 Zeile wurde erstellt.
Nun wird eine Abfrage ausgeführt - der "Partitionsschlüssel" liegt in Form der Abteilung vor ...
SQL> select * from doktest_listpart where contains(text, 'Spatial') > 0 and abteilung='D'; --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart | Pstop | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 4 (0)| 00:00:01 | | | | 1 | PARTITION RANGE ALL | | 1 | 14 | 4 (0)| 00:00:01 | 1 | 3 | |* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| DOKTEST_LISTPART | 1 | 14 | 4 (0)| 00:00:01 | 1 | 3 | |* 3 | DOMAIN INDEX | FT_DOKTEST_LISTPART | | | 4 (0)| 00:00:01 | | | ---------------------------------------------------------------------------------------------------------------------------
Am PARTITION RANGE ALL erkennt man, dass der Optimizer kein Partition Pruning
durchgeführt, sondern die Abfrage über alle Partitionen ausgeführt hat. Das ist -strenggenommen-
auch logisch, denn aus Sicht von Oracle TEXT ist die Tabelle gar nicht anhand der Spalte
ABTEILUNG partitioniert - sondern anhand der Spalte PART#. Das muss sich auch
in der Abfrage widerspiegeln. Zunächst macht es an dieser Stelle absolut Sinn, das "Mapping"
der Abteilung zu einer Partitionsnummer in einer PL/SQL-Funktion zu kapseln.
create or replace function get_partid_for_range ( p_abteilung in varchar2 ) return number deterministic is l_partnum number; begin l_partnum := case when p_abteilung in ('A', 'B', 'C') then 1 when p_abteilung in ('A1', 'A2', 'A3') then 2 when p_abteilung in ('D', 'E', 'F') then 3 end; return l_partnum; end; /
Mit Hilfe dieser Funktion lässt sich die Abfrage recht einfach formulieren ...
select * from doktest_listpart where contains(text, 'Spatial') > 0 and part# = get_partid_for_range('D') /
Der Ausführungsplan sieht dann so aus ...
-------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 0 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 1 | 42 | 0 (0)| 00:00:01 | KEY | KEY | |* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| DOKTEST_LISTPART | 1 | 42 | 0 (0)| 00:00:01 | KEY | KEY | |* 3 | DOMAIN INDEX | FT_DOKTEST_LISTPART | | | 0 (0)| 00:00:01 | | | --------------------------------------------------------------------------------------------------------------------------
KEY in den Spalten PSTART und PSTOP meint lediglich, dass der Wert zur Compile-Zeit des
SQL noch nicht bekannt ist und erst zur Ausführungszeit Run-Time ermittelt wird. Die Abfrage
wird aber, wie man erkennen kann, auf eine Partition begrenzt. Für eine "emulierte" Hash-Partition
arbeitet man genauso.
Die Tatsache, ob ein Textindex partitioniert ist oder nicht, hat großen Einfluß auf die Wartung
desselben. Die Aufrufe zur Synchronisierung oder zur Optimierung erwarten einen Parameter
part_name. Für nicht-partitionierte Indizes kann dieser SQL NULL sein. Bei einem lokal
partitinierten Index wird dagagen der Name einer Partition erwartet. Das bedeutet, dass das komplette Synchronisieren
einex lokal partitionierten Oracle TEXT Index nicht mehr mit einem einzigen Aufruf von CTX_DDL.SYNC_INDEX
erledigt wird, sondern es wird etwas mehr gebraucht ...
SQL> exec ctx_ddl.sync_index('FT_DOKTEST_LISTPART'); BEGIN ctx_ddl.sync_index('FT_DOKTEST_LISTPART'); END; * FEHLER in Zeile 1: ORA-20000: Oracle Text-Fehler: DRG-13102: Name von Index-Partition muss angegeben werden ORA-06512: in "CTXSYS.DRUE", Zeile 160 ORA-06512: in "CTXSYS.CTX_DDL", Zeile 848 ORA-06512: in Zeile 1
Der Name der Indexpartition entspricht normalerweise dem der Tabellenpartition. Im Zweifelsfalle
kann man sie aber aus dem Oracle TEXT Data Dictionary auslesen:
SQL> select IXP_INDEX_PARTITION_NAME from CTX_USER_INDEX_PARTITIONS where ixp_index_name='FT_DOKTEST_LISTPART'; IXP_INDEX_PARTITION_NAME ------------------------------ P_ABC P_A1A2A3 P_DEF
Braucht man nun doch ein Kommando, welches den ganzen Index synchronisiert, kann man sich
also mit dieser Abfrage helfen ...
create or replace procedure sync_full_index(p_idx_name in varchar2) as begin for ip in ( select IXP_INDEX_PARTITION_NAME from CTX_USER_INDEX_PARTITIONS where ixp_index_name = p_idx_name ) loop ctx_ddl.sync_index( idx_name => p_idx_name, part_name => ip.IXP_INDEX_PARTITION_NAME ); end loop; end; /
Wobei die Möglichkeit, eine Synchronisierung bzw. eine Optimierung nur partitionsweise durchführen zu
können, in vielen Fällen gerade ein Vorteil sein dürfte. Denn es kann ja sein, dass unterschiedliche
Partitionen unterschiedliche Anforderungen haben. Und mit der Partitionierung kann man den Aufwand
auf das nötige Maß begrenzen. Nicht nur die Synchronisierung wird partitionsweise durchgeführt, auch
einige andere Operationen arbeiten so ...
- Index-Synchronisierung (CTX_DDL.SYNC_INDEX)
- Asynchroner Index-Aufbau (CTX_DDL.POPULATE_PENDING)
- Indexoptimierung (CTX_DDL.OPTIMIZE_INDEX)
- Indexoptimierung (CTX_DDL.OPTIMIZE_INDEX)
- Indexstatistiken werden ebenfalls partitionsweise ermittelt (CTX_REPORT.INDEX_STATS, CTX_REPORT.INDEX_SIZE und CTX_REPORT.TOKEN_INFO)
- Online Index Rebuild (CTX_DDL.CREATE_SHADOW_INDEX und CTX_DDL.REBUILD_INDEX_ONLINE) arbeiten ebenfalls partitionsweise
Eine Operation wie ALTER TABLE DROP PARTITION funktioniert mit Oracle TEXT transparent; die
relevante Partition des Oracle TEXT Index wird mitsamt der Tabellenpartition gelöscht. Gerade bei
Oracle TEXT ergibt sich jedoch ein entscheidender Vorteil: Durch ein "klassisches" SQL DELETE würden die
Einträge in die Negativliste wandern ($N-Tabelle) und es würde eine Indexoptimierung nötig. Beim
partitinierten Textindex wird die Tabellenpartition einfach mitsamt der Indexpartition in einem Zug
gelöscht. Es ist weder ein Index-Sync noch ein Index Optimize nötig.
Ein
partitionierter Oracle TEXT Index kann bei großen Datenmengen also eine Menge Vorteile bieten - und
das betrifft nicht nur die Abfrageperformance, sondern auch, und besonders die Wartungs- und "Housekeeping"-Arbeiten
an Tabelle und Index.
- Partition Pruning bei Volltextabfragen - Abfragen werden auf die relevante Indexpartition begrenzt - was zu besserer Performance führt. Davon profitieren übrigens nicht die die Abfragen, sondern auch etwaige Sortierungen.
- Mit einem ALTER TABLE DROP PARTITION können veraltete Daten recht einfach und ohne viel Aufwand gelöscht werden.
- Neue Datenbestände können in eine eigene Tabelle geladen, indiziert und dann per ALTER TABLE EXCHANGE PARTITION in die Zieltabelle eingehängt werden. Auf gleichem Wege kann so ein Online-Rebuild einer Indexpartition erfolgen. Die Daten werden in eine eigene Tabelle kopiert; darauf wird der Index neu erstellt und ein ALTER TABLE EXCHANGE PARTITION nimmt Tabelle und Index auf einmal auf.
- Auch Oracle-TEXT spezifische Operationen können partitionsweise durchgeführt und damit besser gesteuert werden.
Abonnieren
Posts (Atom)
Beliebte Postings
-
Oracle TEXT ist eine in die Datenbank integrierte Volltextrecherche, die in allen Datenbankeditionen enthalten ist (kostet also nichts extra...
-
Wie kann man überprüfen, dass die Oracle Text Umgebung korrekt installiert ist? Wie kann man Oracle Text nachinstallieren? Das sind häufig ...
-
Beim Erstellen eines Oracle TEXT-Index kann man eine ganze Menge Einstellungen vornehmen. Im letzten Posting ging es um das Thema St...
-
Wie in vorangegangen Blogs gezeigt wurde, kann die sogenannte Mixed Query Problematik - eine Kombination aus relationalem und Volltextrech...
-
MDATA Sections sind in 10 g eingeführt worden, um gemischte Abfragen (auch mixed queries genannt) - also Abfragen mit Text- und relational...