Donnerstag, 29. August 2013

DBMS_PCLXUTIL zur Erzeugung von lokalen Indizes

Um die Performance beim Aufbau eines Index zu erhöhen, können Oracle Text Indizes mit dem Schlüsselwort PARALLEL aufgebaut werden. Geht es um den parallelen Aufbau eines lokalen partitionierten Index, kann allerdings auch das altbewährte Package DBMS_PCLXUTIL eine Alternative darstellen. Wer eine Beschreibung des Package sucht, wird im PL/SQL Packages Guide fündig; die Verwendung im Oracle Text Umfeld ist etwas versteckt im Oracle Text Reference Guide nachzuschlagen. Da es in letzter Zeit vermehrt Anfragen dazu gab, haben wir uns entschlossen, die Verwendung an einem Beispiel zu demonstrieren.
Zuerst zur Definition des Package: Das Package DBMS_PCLXUTIL sieht folgendermassen aus:
desc dbms_pclxutil
PROCEDURE BUILD_PART_INDEX
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOBS_PER_BATCH                 NUMBER                  IN     DEFAULT
 PROCS_PER_JOB                  NUMBER                  IN     DEFAULT
 TAB_NAME                       VARCHAR2                IN     DEFAULT
 IDX_NAME                       VARCHAR2                IN     DEFAULT
 FORCE_OPT                      BOOLEAN                 IN     DEFAULT
Die Verwendung von TAB_NAME und IDX_NAME muß sicherlich nicht erklärt werden. Anzumerken ist höchstens, dass man nur als Eigentürmer der Tabellen und des Index das Package verwenden kann; eine Benennung über eine zusätzliche Schemabezeichnung ist nicht möglich. Die beiden Parameter JOBS_PER_BATCH und PROCS_PER_JOB bestimmen dabei im Unterschied zu anderen Methoden die Parallelisierung auf zwei (!) Ebenen. JOBS_PER_BATCH ist für die sogenannte Inter Parallelität zuständig, die mit DBMS_JOB Prozessen realisiert wird, und PROCS_PER_JOB für die sogenannte Intra Parallelität, die mit parallelen Prozessen ausgeführt wird.

Genauer bedeutet dies:
JOBS_PER_BATCH steht für die Anzahl der Job Prozesse, die gleichzeitig arbeiten. Hier sollte gelten: Das Minimum ist 1, das Maximum stellt die Anzahl der Partitionen dar.

PROCS_PER_JOB bestimmt die Anzahl der parallelen Query Prozesse pro Job. Auch hier stellt die Zahl 1 das Minimum dar.

FORCE_OPT kann den Wert TRUE oder FALSE haben. FALSE führt nur ein REBUILD für UNUSABLE Indizes aus, TRUE hingegen für alle Partitionen.

Bevor wir starten, sollte zuerst die Einstellung der Job Prozesse geprüft werden. Der Initialisierungsparameter JOB_QUEUE_PROCESSES gibt die maximale Anzahl der Job Prozesse an.
show parameter job

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000
Wichtig zu wissen ist, dass die Prozedur BUILD_PART_INDEX davon ausgeht, dass die Data Dictionary Informationen zum Index schon existieren. Falls nicht erhält man folgende Fehlermeldung:
*
ERROR at line 1:
ORA-20001: Specified local index name 'PROD_DESC_IDX' does not exist
ORA-06512: at "SYS.DBMS_PCLXUTIL", line 301
ORA-06512: at line 1
Vorab ist also folgendes Kommando sinnvoll:
CREATE INDEX prod_desc_idx ON product_part (prod_desc) INDEXTYPE IS ctxsys.context LOCAL UNUSABLE;
Ab 11g Release 2 wird für UNUSABLE Index Partitionen übrigens kein Speicherplatz mehr verbraucht, so dass die Information nicht in USER_SEGMENTS verzeichnet ist. Die Data Dictionary Informationen sind aber wie immer in USER_INDEXES gespeichert.
SELECT * FROM user_segments WHERE segment_name='PROD_DESC_IDX';

no rows selected

SELECT index_name, index_type FROM user_indexes WHERE index_name LIKE 'PROD_DESC%'

INDEX_NAME           INDEX_TYPE
-------------------- ---------------------------
PROD_DESC_IDX        DOMAIN
Um eine Anwendung zu demonstrieren, erzeugen wir einen lokalen Text Index auf die Spalte PROD_DESC der partitionierten Tabelle PRODUCTS_PART mit den 4 Partitionen P_10, P_100, P_1000 und PARTMAXVALUE.
EXECUTE dbms_pclxutil.build_part_index
(JOBS_PER_BATCH => 4,
 PROCS_PER_JOB  => 1,
 TAB_NAME       => 'PRODUCT_PART',
 IDX_NAME       => 'PROD_DESC_IDX',
 FORCE_OPT      => TRUE); 
Parallel dazu lassen sich die Job Prozesse überwachen - es werden 4 Jobs gestartet jeweils mit Parallelität 1.
SELECT job, this_date, next_date, failures, what FROM dba_jobs;

       JOB THIS_DATE        NEXT_DATE        FAILURES
---------- ---------------- ---------------- ----------
WHAT
--------------------------------------------------------------------------------
        42                  27.08.2013 17:41
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_100" parallel (degree 1)');

        43                  27.08.2013 17:41
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_1000" parallel (degree 1)');

        44                  27.08.2013 17:41
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "PARTMAXVALUE" parallel (degree 1)');

        41                  27.08.2013 17:41
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_10" parallel (degree 1)');
Am Schluss sollte natürlich auch die erfolgreiche Indexerstellung überprüft werden.
SELECT err_timestamp, err_text FROM ctx_user_index_errors
ORDER BY err_timestamp DESC;

no rows selected
Möglich wäre allerdings auch folgendes Kommando mit insgesamt 2 Jobs - dabei jeweils 2 parallele Prozessen pro Job.
EXECUTE dbms_pclxutil.build_part_index
(JOBS_PER_BATCH => 2,
 PROCS_PER_JOB  => 2,
 TAB_NAME       => 'PRODUCT_PART',
 IDX_NAME       => 'PROD_DESC_IDX',
 FORCE_OPT      => TRUE);
Prüft man die Jobs, stellt man folgende Aufrufe fest.
SELECT job, this_date, next_date, failures, what FROM dba_jobs;

       JOB THIS_DATE NEXT_DATE FAILURES
---------- --------- --------- ----------
WHAT
--------------------------------------------------------------------------------
       221 28-AUG-13 28-AUG-13
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "PARTMAXVALUE" parallel (degree 2)');

       222 28-AUG-13 28-AUG-13
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_10" parallel (degree 2)');
Dann nach einer gewissen Zeit ...
SELECT job, this_date, next_date, failures, what FROM dba_jobs;

       JOB THIS_DATE NEXT_DATE FAILURES
---------- --------- --------- ----------
WHAT
--------------------------------------------------------------------------------
       241 28-AUG-13 28-AUG-13
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_100" parallel (degree 2)');

       242 28-AUG-13 28-AUG-13
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_1000" parallel (degree 2)');
Er laufen also immer zwei Jobs zu einer Zeit. Prüft man gleichzeitig die Parallelität über v$px_session und V$session, erkennt man, dass wirklich 2 Prozesse pro Job arbeiten.
Username     QC/Slave   Slave Set  SID    QC SID Requested DOP Actual DOP
------------ ---------- ---------- ------ ------ ------------- ----------
SH           QC                    34     34
 - p001      (Slave)    1          43     34                 2          2
 - p000      (Slave)    1          87     34                 2          2
SH           QC                    94     94
 - p003      (Slave)    1          95     94                 2          2
 - p002      (Slave)    1          98     94                 2          2
Wo liegt nun der Unterschied zwischen den beiden Läufen? Beim zweiten Lauf arbeiten mehrere -hier 2 - Prozesse pro Job gleichzeitig. Unter Umständen sind dadurch die Index Partitionen schneller erstellt, was bei großen Indizes mit langen Laufzeiten sicherlich wünschenswert ist. Allerdings ist folgendes zu beachten: Da wir mit mehreren Prozessen pro Job arbeiten, kann es zu einer höheren Fragmentierung der einzelnen Index Partitionen kommen. Hier wäre dann ein anschliessendes CTX_DDL.OPTIMIZE_INDEX nötig.

Zum Schluss vielleicht noch ein wichtiger Hinweis: Vergessen Sie bei all diesen Operationen die Memory Einstellung nicht. Die Überprüfung kann zum Beispiel über CTX_PARAMETERS erfolgen.
SELECT * FROM ctx_parameters WHERE par_name LIKE '%MEM%';

PAR_NAME                       PAR_VALUE
------------------------------ -----------------------------------
DEFAULT_INDEX_MEMORY           67108864
MAX_INDEX_MEMORY               1073741824
Da DBMS_PCLXUTIL ein Package zur allgemeinen Verwendung ist, sind keine Text spezifischen Einstellungen über den Package Aufruf möglich - also auch keine Memory Einstellungen. Möchte man eine spezielle Memory Einstellung verwenden, kann beispielsweise die Prozedur CTXSYS.CTX_ADM.SET_PARAMETER verwendet werden.

Montag, 26. August 2013

Alle 12c Oracle Text Features auf einen Blick!

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, 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 ...
Performance, Monitoring ...
Spezielle Suchabfragen unterstützen
Mixed Queries, Datastores ...

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.

Beliebte Postings