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.

Donnerstag, 19. Juli 2012

Filter Preferences - Grundsätzliches

Wir haben in unseren Textblogs schon mehrfach über einige Texteinstellungen - die sogenannten Preferences - gesprochen.  Nun ist es an der Zeit, Grundlagen zu den Filter Preferencen zu legen. Filter Preferences sind, wie der Name schon verrät, dazu da den Text zum Indizieren zu filtern. Formatierte Dokumente werden zwar in nativer Format gespeichert; der Filter sorgt allerdings dafür, dass eine vorläufige Version in reinem (plain) Text oder HTML Version des Dokuments zur Verfügung steht, um dann die Wörter zum Indizieren zu erhalten.

Filter-Preferences werden mit dem CREATE INDEX oder ALTER INDEX Statement erzeugt. Man sollte sich dabei nicht auf System Preferencen verlassen, sondern den Filter immer beim Anlegen des Index mitangeben. Folgende Filter Typen existieren:

  • CHARSET_FILTER: Character Set Konvertierung
  • AUTO_FILTER: Für formatierte Dokumente
  • NULL_FILTER: Kein Filter ist erforderlich, nützlich für Plain Text, HTML und XML
  • MAIL_FILTER: Transformiert RFC-822 und RFC-2045 Nachrichten in indizierbaren Text
  • USER_FILTER: Externer Filter für benutzerdefinertes Filtern
  • PROCEDURE_FILTER: Benutzerdefiniertes Filtern definiert über eine Prozedur

Hier im Blog gebe ich Beispiele für NULL_FILTER, AUTO_FILTER und den USER_FILTER. Weitere Beispiele dazu finden sich im Handbuch.
Zuerst wird eine Tabelle erzeugt, die Dokumente im Format Powerpoint, PDF, HTML und ASCII enthält.

drop table filter_test; 
create table filter_test(id number primary key, docs blob);

Zum Laden verwende ich den SQL *Loader. Die CTL Datei sieht folgendermassen aus:
LOAD DATA
INFILE 'filter_load.dat'
INTO TABLE null_filter
FIELDS TERMINATED BY ','
(id SEQUENCE (MAX,1) ,
ext_fname FILLER CHAR(50),
docs LOBFILE(ext_fname) TERMINATED BY EOF)

Die filter_load.dat Datei hat folgende Inhalte.
snaps.sql
powerp.pptx
plain.txt
replay.html
one_page.pdf

Im ersten Beispiel wird ein Index mit Filter Preference NULL_FILTER erzeugt. Um zu überprüfen, ob im Index Create Prozeß keine Fehler passiert sind, schalte ich Logging ein und überprüfe den Index nach dem Anlegen mit CTX_USER_INDEX_ERRORS.
drop index filter_test_idx force;
execute CTXSYS.CTX_ADM.SET_PARAMETER ('LOG_DIRECTORY','/tmp'); 
exec CTX_OUTPUT.START_QUERY_LOG('filterlog');

create index filter_test_idx on filter_test ( docs )
indextype is ctxsys.context
parameters ('FILTER ctxsys.NULL_FILTER');

execute CTX_OUTPUT.END_QUERY_LOG;
SQL> select * from ctx_user_index_errors;
no rows selected

Nun wird das Ergebnis überprüft. Um einen Einruck von dem Inhalt der Ergebnismenge zu bekommen, verwende ich CTX_DOC.SNIPPED. Die REGEXP_REPLACE Funktion macht das Ergebnis lesbarer und eliminiert die überflüssigen Leerzeilen.

SQL> execute ctx_doc.set_key_type('ROWID');
SQL> select id, regexp_replace(ctx_doc.snippet 
('FILTER_TEST_IDX', rowid, 'plain or OLTP or replay or snapshot or SPA or
 REPLAY',starttag=>'###',endtag =>'###'),'\s+',' ') snippet
 from filter_test
 where contains ( docs, 'plain or OLTP or replay or snapshot or SPA or REPLAY') > 0; 
ID
----------
SNIPPET
--------------------------------------------------------------------------------
1
hh24:mi') from dba_hist_###snapshot### order by BEGIN_INTERVAL_TIME
3
dies ist ein ###plain### text
4
performance of a workload ###replay### against the performance of...The f
irst ###replay### would try to mimic the captured

Wie zu erwarten, enthält das Ergebnis die Dokumente snaps.sql (mit 1), plain.txt (mit 2) und replay.html (mit 3).
Nun verwenden wir den AUTO_FILTER. Das Vorgehen ist ähnlich wie oben, nur die FILTER Preference lautet nun AUTO_FILTER.
drop index filter_test_idx force; 
execute CTXSYS.CTX_ADM.SET_PARAMETER ('LOG_DIRECTORY','/tmp'); 
execute CTX_OUTPUT.START_QUERY_LOG('filterlog');

create index filter_test_idx on filter_test (docs)
indextype is ctxsys.context
parameters ('FILTER ctxsys.AUTO_FILTER');

execute CTX_OUTPUT.END_QUERY_LOG;
SQL> select * from ctx_user_index_errors;
no rows selected
Nun wird wieder das Ergebnis selektiert.
SQL> execute ctx_doc.set_key_type('ROWID');

SQL>select id, regexp_replace(ctx_doc.snippet
('FILTER_TEST_IDX', rowid, 'plain or OLTP or replay or snapshot or SPA or
 REPLAY',starttag=>'###',endtag =>'###'),'\s+',' ') snippet
 from filter_test
 where contains ( docs, 'plain or OLTP or replay or snapshot or SPA or REPLAY') > 0; 
ID
----------
SNIPPET
--------------------------------------------------------------------------------
1
hh24:mi') from dba_hist_###snapshot### order by BEGIN_INTERVAL_TIME
2
Sesssions und Calls ###OLTP###-Datenbanken mit ca.12.000...Siehe Enhancem
ent Request ?7523016: ###REPLAY### REPORTS ORA-933 ON SET ROLE
3
dies ist ein ###plain### text
4
performance of a workload ###replay### against the performance of...The f
irst ###replay### would try to mimic the captured
5
2) Before using the ###SPA### or Database Replay, understand...3) Perform
STS Capture for ###SPA### & workload capture for Database

Es werden nun alle Dokumente gefiltert, indiziert und gefunden. Möchte man einen Überblick über alle unterstützten Formate erhalten, kann man dies im Handbuch nachschlagen.

Nun kommen wir zum letzten Beispiel - einem USER_FILTER. Hierzu ist ein Programm notwendig, das beim Filtern für jede Zeile ausgeführt wird. Explizit wird nun eine Preference MY_FILTER mit einem speziellen Attribut erzeugt, das den Programmnamen festlegt - hier upper.pl
execute ctx_ddl.drop_preference('my_filter');
BEGIN
ctx_ddl.create_preference ('my_filter', 'user_filter');
ctx_ddl.set_attribute ('my_filter', 'command', 'upper.pl');
END;
/
PL/SQL procedure successfully completed.
Das Programm ist ein Perl-Programm, das den Text in Großbuchstaben konvertiert. Es muß unbedingt im Verzeichnis $ORACLE_HOME/ctx/bin zu finden sein.

#!/usr/bin/perl
open(IN, $ARGV[0]);
open(OUT, ">".$ARGV[1]);
while ()
{
tr/a-z/A-Z/;
print OUT;
}
close (IN);
close (OUT);

Nun wird der Index erzeugt.

drop index filter_test_idx force;

create index filter_test_idx on filter_test (docs) 
indextype is ctxsys.context
parameters ('filter my_filter');

SQL> select * from ctx_user_index_errors;
no rows selected

Das Ergebnis sieht dann folgendermassen aus.

SQL> select id, regexp_replace(ctx_doc.snippet
('FILTER_TEST_IDX', rowid, 'plain or OLTP or replay or snapshot or SPA or
REPLAY',starttag=>'###',endtag =>'###'),'\s+',' ') snippet
from filter_test
where contains ( docs, 'plain or OLTP or replay or snapshot or SPA or REPLAY') > 0;
ID
----------
SNIPPET
--------------------------------------------------------------------------------
4
CAPTURE VS. ###REPLAY###
...PERFORMANCE OF A WORKLOAD ###REPLAY### AGAINST THE PERFORMANCE
OF...CAPTURED SYSTEM, WHILE "###REPLAY###" REFERS TO THE
REPLAYED WORKLOAD.

Mittwoch, 6. Juni 2012

Name Matching (NDATA) mit einem Thesaurus "erweitern"

Zum Thema Oracle TEXT Name Matching hatten wir ja bereits einige Blog Postings. Heute möchte ich das Thema nochmal aufgreifen und besonders die Erweiterbarkeit mit einem Thesaurus vorstellen. Zunächst zur Ausgangssituation: Wir legen eine Tabelle an und füllen diese mit ein paar Namen:
create table tab_namen (
  id           number(10),
  vorname      varchar2(100),
  nachname     varchar2(100)
)
/

insert into tab_namen values (1, 'Carsten','Czarski');
insert into tab_namen values (2, 'Ulrike', 'Schwinn');
insert into tab_namen values (3, 'Max',    'Meier');
insert into tab_namen values (4, 'Moritz', 'Meyer');
insert into tab_namen values (5, 'Franz',  'Mayer');
insert into tab_namen values (6, 'Fritz',  'Maier');
Damit man einen Oracle TEXT Index anlegen kann, der auch alle Namen enthält, muss man nun mit einem Multicolumn-Datastore oder einem User-Datastore arbeiten. Da ich die Namen aber gerne per NDATA (Name Matching) und per "klassischer" Fuzzy-Suche (FUZZY-Operator) finden möchte, brauche ich einen User-Datastore (Blog-Posting). Also zuerst die PL/SQL-Prozedur anlegen ...
 create or replace procedure namen_uds_proc(
  rid  in            rowid,
  tlob in out nocopy varchar2
) is
  l_vorname  tab_namen.vorname%type;
  l_nachname tab_namen.nachname%type;
begin
  select vorname, nachname into l_vorname, l_nachname
  from tab_namen where rowid = rid;
  tlob := 
    '<VORNAME>'   || l_vorname                       || '</VORNAME>'  ||  
    '<NACHNAME>'  || l_nachname                      || '</NACHNAME>' ||  
    '<NAME>'      || l_vorname || ' ' || l_nachname  || '</NAME>';
end;
/
sho err
Die Prozedur als User Datastore registrieren ...

begin
  ctx_ddl.create_preference('names_ds', 'user_datastore');
  ctx_ddl.set_attribute('names_ds', 'procedure', 'namen_uds_proc');
end;
/
sho err
Mit dem Section Group-Objekt wird festgelegt, wie die einzelnen Sections (XML-Tags) von Oracle TEXT behandelt werden sollen ... VORNAME und NACHNAME werden normal behandelt; der zusammengesetzte "NAME" wird als NDATA-Section deklariert.
begin
  ctx_ddl.create_section_group('names_sg', 'xml_section_group');
  ctx_ddl.add_field_section('names_sg', 'VORNAME', 'VORNAME', false);
  ctx_ddl.add_field_section('names_sg', 'NACHNAME', 'NACHNAME', false);
  ctx_ddl.add_ndata_section('names_sg', 'NAME', 'NAME');
end;
/
Schließlich den Index anlegen ...
create index ft_names on tab_namen (nachname)
indextype is ctxsys.context
parameters('
  datastore     names_ds
  section group names_sg
  memory        100m
')
/
Und dann kann man abfragen - zuerst mit "Meier" ...
select * from tab_namen where contains(nachname, 'ndata(name, Meier)')>0
/

        ID VORNAME         NACHNAME
---------- --------------- ---------------
         3 Max             Meier
         4 Moritz          Meyer
         6 Fritz           Maier
Ups ... da fehlt doch einer ... der "Mayer" ist für den NDATA-Algorithmus wohl "zu weit" weg ... Probieren wir noch ein wenig mit dem "Czarski".
select * from tab_namen where contains(nachname, 'ndata(name, Czarsky)')>0
/

        ID VORNAME         NACHNAME
---------- --------------- -------------
         1 Carsten         Czarski

select * from tab_namen where contains(nachname, 'ndata(name, Tsarski)')>0
/

        ID VORNAME         NACHNAME
---------- --------------- -------------
         1 Carsten         Czarski

select * from tab_namen where contains(nachname, 'ndata(name, Tsarsky)')>0
/

Es wurden keine Zeilen ausgewählt
Funktioniert ganz gut, lässt aber doch noch Wünsche offen. Eine Ähnlichkeitssuche wird aber immer Wünsche offenlassen, gerade wenn sie nicht für eine bestimmte Sprache optimiert ist und global funktionieren soll. Die gute Nachricht ist aber, dass NDATA durch einen Thesaurus erweitert werden kann. In diesem Thesaurus können unterschiedliche Schreibweisen von Namen als Synonymbeziehungen hinterlegt und die Treffermenge von NDATA so erweitert werden. Und das Gute ist, dass NDATA dann auch zu den Synonymen ähnliche (!) Namen finden wird. Die Treffermenge wird also nicht nur um die Synonyme selbst, sondern auch um den Synonymen (nach NDATA) ähnliche Namen erweitert.
Wie man einen Thesaurus anlegt und pflegt, ist in diesem Blog-Posting beschrieben. Also erzeugen wir einen Thesaurus wie folgt ...
begin
  ctx_thes.create_thesaurus('namesthes');
  ctx_thes.create_relation('namesthes', 'meyer', 'SYN', 'maier');
  ctx_thes.create_relation('namesthes', 'meyer', 'SYN', 'meier');
  ctx_thes.create_relation('namesthes', 'meyer', 'SYN', 'mayer');
  ctx_thes.create_relation('namesthes', 'czarski', 'SYN', 'czarsky');
end;
/
Als nächstes müssen wir Oracle TEXT sagen, dass es den Thesaurus für NDATA nutzen soll - das geschieht mit einer Wordlist Preference.
begin
  ctx_ddl.create_preference('names_wl', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute('names_wl', 'NDATA_ALTERNATE_SPELLING', 'FALSE');
  ctx_ddl.set_attribute('names_wl', 'NDATA_BASE_LETTER',        'TRUE');
  ctx_ddl.set_attribute('names_wl', 'NDATA_THESAURUS',          'namesthes');
end;
/
Und dann muss der Index gelöscht und neu angelegt werden ...
drop index ft_names
/

create index ft_names on tab_namen (nachname)
indextype is ctxsys.context
parameters('
  datastore     names_ds
  section group names_sg
  wordlist      names_wl
  memory        100m
')
/
Und dann probieren wir die Abfragen erneut ...
select * from tab_namen where contains(nachname, 'ndata(name, Mayer)')>0
/

        ID VORNAME         NACHNAME
---------- --------------- ---------------
         3 Max             Meier
         4 Moritz          Meyer
         5 Franz           Mayer
         6 Fritz           Maier

select * from tab_namen where contains(nachname, 'ndata(name, Tsarsky)')>0
/

        ID VORNAME         NACHNAME
---------- --------------- ---------------
         1 Carsten         Czarski
Voilá - man sieht, dass man die NDATA-Funktionalität sehr gut erweitern kann. Wenn man das von vorneherein in seinen Index einbaut, so kann man den Index aufgrund von Nutzerfeedback "lernen" lassen. Mit einem Namens-Thesaurus lässt sich die NDATA-Funktinalität auf jeden Fall sehr gut abrunden.

Freitag, 25. Mai 2012

Oracle TEXT Workshop im Mai in Düsseldorf und Stuttgart

Im Mai 2012 führten wir in Düsseldorf und Stuttgart einen Workshop zum Thema Oracle TEXT durch. Dort wurde Oracle TEXT und die Möglichkeiten im Detail vorgestellt.

Die Agenda
10:00 Beginn der Veranstaltung
Oracle TEXT: Ein Überblick
Konzepte und generelle Funktionsweise von Oracle TEXT, Einsatzszenarien
Umgang mit Oracle TEXT: Tipps & Tricks aus der Praxis
Einblick in die Wartung eines Textindex, wie formuliert man Oracle TEXT Abfragen richtig?
Welche grundsätzlichen Möglichkeiten habe ich?
Mittagspause
Überwachung und Wartung eines Oracle Text Index im Detail
Synchronisierungs- und Optimierungsintervalle, Strategien, Feststellung der Fragmentierung,
Hochverfügbarkeit mit Schattenindex-Technologie und mehr ...
"Textindex nach Art des Hauses": Konfiguration eines TEXT-Index
Lexer-Einstellungen, Stopwortlisten, Umgang mit Umlauten, Thesaurus und mehr ...
Oracle TEXT für alle Daten: User Datastore
Wie erzeugt man einen Oracle TEXT-Index auf mehrere Tabellen ...?
17:00: Ende der Veranstaltung

Die Folien des Workshops stehen ab sofort hier zum Download bereit.

http://apex.oracle.com/folien

Schlüsselwort: oracle-text-workshop

Dienstag, 13. März 2012

Nochmal USER_DATASTORE: Ein umfassendes Beispiel - Teil I

Da wir immer wieder gefragt werden, wie man einen User Datastore mit Oracle TEXT erstellt und auch sehr oft bemerken, dass ein User Datastore DIE Lösung für alle möglichen Suchszenarien ist, möchten wir dieses Blog-Posting nochmals dem User Datastore widmen. Das Beispiel heute ist etwas anspruchsvoller: Wir möchten eine Mitarbeitersuche im Oracle-Beispielschema HR umsetzen. Die zu indizierenden, suchrelevanten Daten sind über mehrere Tabellen verteilt (genau 7) - dennoch soll genau ein Index entstehen, über den ein Mitarbeiter anhand aller möglicher Suchkriterien gefunden werden kann.
  • Anhand des Namens, auch Ähnlichkeitssuche soll möglich sein
  • Anhand der Abteilung
  • Anhand des Ortes, des Landes, der Region
  • Anhand des Jobs
  • Anhand des Managers
Man kann sich schon vorstellen, dass die Lösung dieser Aufgabe mit "klassischem" SQL nicht besonders einfach ist. Aber Oracle TEXT passt hier genau. Zur Verdeutlichung nochmals eine Übersicht über die vorhandenen, zu indizierenden Tabellen.
Damit man die Physik der eigentlichen Datentabellen beliebig ändern kann, werden wir den Volltextindex komplett davon lösen. Es wird also eine eigene "Suchtabelle" angelegt, auf die später der Index erzeugt wird. Damit wir beim Anzeigen der Trefferliste nicht zu den eigentlichen Datentabellen joinen müssen, nehmen wir alle Informationen, die in der Trefferliste angezeigt werden sollen, hier mit auf. Wobei das "nur" die Daten sind, die potenziell in der Trefferliste dargestellt werden - in den Index werden noch mehr Daten aufgenommen.
create table employees_search(
  employee_id      number(6) primary key,
  FIRST_NAME       varchar2(20),
  LAST_NAME        varchar2(25),
  EMAIL            varchar2(25),
  PHONE_NUMBER     varchar2(20),
  JOB_TITLE        varchar2(35),
  DEPARTMENT_NAME  varchar2(30),
  POSTAL_CODE      varchar2(12),
  CITY             varchar2(30),
  COUNTRY_NAME     varchar2(40)
)
/

insert into employees_search(
  select 
    e.EMPLOYEE_ID,
    e.FIRST_NAME,
    e.LAST_NAME,
    e.EMAIL,
    e.PHONE_NUMBER,
    j.JOB_TITLE,
    d.department_name,
    l.postal_code,
    l.city,
    c.country_name
  from 
    employees e 
      join jobs j on (j.job_id = e.job_id)
      join departments d on (d.department_id = e.department_id)
      join locations l on (d.location_id = l.location_id)
      join countries c on (c.country_id = l.country_id)
)
/
Als nächstes wird die Prozedur erstellt, welche die zu indizierenden Daten aufbereitet. Oracle TEXT wird diese Prozedur später für jede Zeile der zu indizierenden Tabelle (EMPLOYEES_SEARCH) aufrufen und das, was die Prozedur zurückgibt, indizieren. Der PL/SQL-Code sollte dann die zu indizierenden Daten aus allen Tabellen "zusammensammeln" und dabei möglichst effizient arbeiten. Wir bedienen uns eines expliziten Cursors in einem Helper-Package. Da wir den Index auf die Tabelle EMPLOYEES_SEARCH erzeugen wollen, müssen wir mit dem Cursor, anhand einer ROWID aus dieser Tabelle, alle Daten zusammenstellen.
create or replace package emp_suche_uds_helper is
 cursor emp_suche_cur (emp_rid rowid) is
  select 
    e.EMPLOYEE_ID,
    e.FIRST_NAME || ' ' || e.LAST_NAME as full_name,
    e.EMAIL,
    e.PHONE_NUMBER,
    to_char(e.hire_date, 'YYYY-MM-DD') hire_date,
    m.first_name || ' ' ||m.last_name as mgr_full_name,
    j.JOB_TITLE,
    d.department_name,
    l.street_address,
    l.postal_code,
    l.city,
    l.state_province,
    c.country_name,
    r.region_name
  from 
    employees_search es 
      join employees e on (e.employee_id = es.employee_id) 
      join jobs j on (j.job_id = e.job_id)
      join departments d on (d.department_id = e.department_id)
      join locations l on (d.location_id = l.location_id)
      join countries c on (c.country_id = l.country_id)
      join regions r on (r.region_id = c.region_id)
      left outer join employees m on (m.employee_id = e.manager_id)
  where es.rowid = emp_rid;
end emp_suche_uds_helper;
/ 
sho err
Danach kommt die eigentliche Prozedur für den User Datastore. Beachtet bitte immer deren Signatur - die ist von Oracle TEXT vorgegeben:
  • Als erstes wird eine ROWID als IN-Parameter erwartet.
  • Der zweite Parameter muss IN OUT und vom Datentyp VARCHAR2, CLOB oder BLOB sein.
Der Code sieht dann wie folgt aus.
create or replace procedure emp_suche_uds_proc(
  rid         in rowid,
  tlob        in out nocopy varchar2
) is
  l_row emp_suche_uds_helper.emp_suche_cur%ROWTYPE;
begin
  if emp_suche_uds_helper.emp_suche_cur%ISOPEN then 
    close emp_suche_uds_helper.emp_suche_cur;
  end if;
 
  open emp_suche_uds_helper.emp_suche_cur(rid);
  fetch emp_suche_uds_helper.emp_suche_cur into l_row;
  tlob := 
    '<EMPLOYEE_ID>'      || l_row.employee_id     || '</EMPLOYEE_ID>'      ||
    '<FULL_NAME>'        || l_row.full_name       || '</FULL_NAME>'        ||
    '<ND_FULL_NAME>'     || l_row.full_name       || '</ND_FULL_NAME>'     ||
    '<EMAIL>'            || l_row.email           || '</EMAIL>'            ||
    '<PHONE_NUMBER>'     || l_row.phone_number    || '</PHONE_NUMBER>'     ||
    '<HIRE_DATE>'        || l_row.hire_date       || '</HIRE_DATE>'        ||
    '<MGR_FULL_NAME>'    || l_row.mgr_full_name   || '</MGR_FULL_NAME>'    ||
    '<ND_MGR_FULL_NAME>' || l_row.mgr_full_name   || '</ND_MGR_FULL_NAME>' ||
    '<JOB_TITLE>'        || l_row.job_title       || '</JOB_TITLE>'        ||
    '<DEPARTMENT_NAME>'  || l_row.department_name || '</DEPARTMENT_NAME>'  ||
    '<STREET_ADDRESS>'   || l_row.street_address  || '</STREET_ADDRESS>'   ||
    '<POSTAL_CODE>'      || l_row.postal_code     || '</POSTAL_CODE>'      ||
    '<CITY>'             || l_row.city            || '</CITY>'             ||
    '<STATE_PROVINCE>'   || l_row.state_province  || '</STATE_PROVINCE>'   ||
    '<COUNTRY_NAME>'     || l_row.country_name    || '</COUNTRY_NAME>'     ||
    '<REGION_NAME>'      || l_row.region_name     || '</REGION_NAME>';
  close emp_suche_uds_helper.emp_suche_cur;
end emp_suche_uds_proc;
/
sho err
Als nächstes sollte man die Prozedur mal testen - eine ROWID der Tabelle EMPLOYEES_SEARCH reingeben und es sollte ein XML-Dokument zurückkommen. Mit SQL*Plus sieht der Test dann so aus ...
SQL> var XML varchar2(4000);
SQL> exec emp_suche_uds_proc('AAArJdAAEAAAJx7AAA', :XML);
SQL> print

XML
--------------------------------------------------------------------------------
<EMPLOYEE_ID>198</EMPLOYEE_ID><FULL_NAME>Donald OConnell</FULL_NAME><ND_FULL_NAM
E>Donald OConnell</ND_FULL_NAME><EMAIL>DOCONNEL</EMAIL><PHONE_NUMBER>650.507.983
3</PHONE_NUMBER><HIRE_DATE>2007-06-21</HIRE_DATE><MGR_FULL_NAME>Kevin Mourgos</M
GR_FULL_NAME><ND_MGR_FULL_NAME>Kevin Mourgos</ND_MGR_FULL_NAME><JOB_TITLE>Shippi
ng Clerk</JOB_TITLE><DEPARTMENT_NAME>Shipping</DEPARTMENT_NAME><STREET_ADDRESS>2
011 Interiors Blvd</STREET_ADDRESS><POSTAL_CODE>99236</POSTAL_CODE><CITY>South S
an Francisco</CITY><STATE_PROVINCE>California</STATE_PROVINCE><COUNTRY_NAME>Unit
ed States of America</COUNTRY_NAME><REGION_NAME>Americas</REGION_NAME>

Puristen mögen einwenden, dass dies gar kein richtiges XML-Dokument ist - denn es fehlt das Root-Tag. Aber das ist Oracle TEXT egal: Ein Root-Tag braucht es nicht unbedingt, also verzichten wir darauf. Wenn die PL/SQL-Prozedur soweit funktioniert, können wir damit beginnen, sie im Oracle TEXT Dictionary zu registrieren. Dazu werden Preference-Objekte erzeugt. Wir beginnen mit der Datastore-Preference:
begin
  ctx_ddl.drop_preference(
    preference_name => 'employee_ds'
  );
end;
/
sho err

begin
  ctx_ddl.create_preference(
    preference_name => 'employee_ds',
    object_name     => 'user_datastore'
  );
  ctx_ddl.set_attribute(
    preference_name => 'employee_ds',
    attribute_name  => 'procedure',
    attribute_value => 'emp_suche_uds_proc'
  );
end;
/
sho err
Jetzt könnte man den Index schon erzeugen und über alle Attribute suchen. Allerdings wollen wir noch zwei Dinge zusätzlich:
  • Wir wollen auch gezielt nach bestimmten Attributen suchen, also nach Manager, Abteilung oder Adresse
  • Für den Employee- und den Managernamen gezielt das Feature "Name Search" einsetzen. Aus diesem Grund sind beide Namen im generieren XML auch zweimal enthalten (FULL_NAME und ND_FULL_NAME, MGR_FULL_NAME und ND_MGR_FULL_NAME).
  • Wir wollen für das HIREDATE die in Version 11 neu eingeführten SDATA-Sections nutzen. Damit wird es möglich, eine Datumssuche (<, >) über den Volltextindex zu machen.
begin
  ctx_ddl.drop_section_group(
    group_name    => 'employee_sg'
  );
end;
/

begin
  ctx_ddl.create_section_group(
     group_name      => 'employee_sg',
     group_type      => 'XML_SECTION_GROUP'
  );
  /*
   * Einfache "Field Sections" für die Suchelemente. Der letzte Parameter legt fest,
   * ob der Section-Name bei Suchen angegeben werden muss ("false") oder ob es auch
   * ohne geht ("true"). 
   */
  ctx_ddl.add_field_section('employee_sg', 'EMPLOYEE_ID',      'EMPLOYEE_ID',      false);
  ctx_ddl.add_field_section('employee_sg', 'FULL_NAME',        'FULL_NAME',        false);
  ctx_ddl.add_field_section('employee_sg', 'EMAIL',            'EMAIL',            false);
  ctx_ddl.add_field_section('employee_sg', 'PHONE_NUMBER',     'PHONE_NUMBER',     false);
  ctx_ddl.add_field_section('employee_sg', 'MGR_FULL_NAME',    'MGR_FULL_NAME',    false);
  ctx_ddl.add_field_section('employee_sg', 'JOB_TITLE',        'JOB_TITLE',        false);
  ctx_ddl.add_field_section('employee_sg', 'DEPARTMENT_NAME',  'DEPARTMENT_NAME',  false);
  ctx_ddl.add_field_section('employee_sg', 'STREET_ADDRESS',   'STREET_ADDRESS',   false);
  ctx_ddl.add_field_section('employee_sg', 'POSTAL_CODE',      'POSTAL_CODE',      false);
  ctx_ddl.add_field_section('employee_sg', 'CITY',             'CITY',             false);
  ctx_ddl.add_field_section('employee_sg', 'STATE_PROVINCE',   'STATE_PROVINCE',   false);
  ctx_ddl.add_field_section('employee_sg', 'COUNTRY_NAME',     'COUNTRY_NAME',     false);
  ctx_ddl.add_field_section('employee_sg', 'REGION_NAME',      'REGION_NAME',      false);
  /*
   * Auf das Hiredate soll die Suche auch mit ">" und "<" möglich sein, daher SDATA-Section 
   */
  ctx_ddl.add_sdata_section('employee_sg', 'HIRE_DATE',        'HIRE_DATE',        'DATE');
  /*
   * Zusätzliche NDATA-Sections für Namenssuche 
   */
  ctx_ddl.add_ndata_section('employee_sg', 'ND_MGR_FULL_NAME', 'ND_MGR_FULL_NAME');
  ctx_ddl.add_ndata_section('employee_sg', 'ND_FULL_NAME',     'ND_FULL_NAME'    );
end;
/
sho err
Es werden drei unterschiedliche Section-Typen innerhalb der Section Group employee_sg erzeugt. Eine Field Section ist der einfachste Typ: In einer solchen Section kann einfacher Text stehen, der normal indiziert wird. Untertags sind jedoch nicht erlaubt; geschachtelte Strukturen müssen als Zone-Sections deklariert werden - die brauchen aber etwas mehr Platz im Index. Wichtig beim Aufruf von ADD_FIELD_SECTION ist der letzte Parameter VISIBLE. Wird er auf "false" gesetzt, so muss die Section bei der Suche stets angebenen werden - es muss also immer "MILLER within (FULL_NAME)" gesucht werden. Steht er auf "true", kann man auch einfach nur nach "MILLER" suchen - letzteres macht den Index aber auch größer - man muss einfach anhand der Anforderungen entscheiden. Mehr zu Zone- und Field-Sections findet Ihr hier.
Für die Namenssuche haben wir, wie schon gesagt, zusätzliche XML-Tags erzeugt - für diese Tags werden eigene NDATA-Sections erzeugt. Diese machen die in 11.2 neu eingeführte Name Search möglich. Man kann also für eine Namenssuche entweder mit der "normalen" Fuzzy-Suche arbeiten, oder, wenn diese "nicht genug" findet, die spezielle Namenssuche anwerfen.
Als nächstes legen wir eine Wordlist-Preference an. Damit werden wir einige Einstellungen für Name Search vornehmen und allgemein Dinge wie die Reduktion von diakritischen Zeichen auf ihre Grundformen aktivieren. Ein Herr "Müller" wird also als "MULLER" in den Index geschrieben - man kann ihn dann entweder als Müller oder als Muller finden - letzteres ist wichtig, wenn international gearbeitet werden soll - schließlich ist nicht jede Tastatur mit deutschen Umlauten gesegnet. Im folgenden werden die dazu nötigen Wordlist und Lexer Preferences eingestellt.
begin
 ctx_ddl.drop_preference('employee_wl');
end;
/
sho err

begin
  ctx_ddl.create_preference('employee_wl', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute('employee_wl', 'NDATA_ALTERNATE_SPELLING', 'TRUE');
  ctx_ddl.set_attribute('employee_wl', 'NDATA_BASE_LETTER',        'TRUE');
end;
/
sho err


begin
 ctx_ddl.drop_preference('employee_lx');
end;
/
sho err

begin
  ctx_ddl.create_preference('employee_lx', 'BASIC_LEXER');
  ctx_ddl.set_attribute('employee_lx', 'MIXED_CASE',       'NO');
  ctx_ddl.set_attribute('employee_lx', 'BASE_LETTER',      'YES');
  ctx_ddl.set_attribute('employee_lx', 'BASE_LETTER_TYPE', 'GENERIC');
end;
/
sho err
Mehr Informationen zu den verfügbaren Einstellungen findet sich in der Dokumentation:
Nun ist es geschafft. Wir können den Index (endlich) anlegen.
create index ft_employee_suche on employees_search(last_name)
indextype is ctxsys.context
parameters('
  datastore      employee_ds
  section group  employee_sg
  wordlist       employee_wl
  lexer          employee_lx
  stoplist       ctxsys.empty_stoplist
  memory 500M
')
/
Wenn der Index fertig ist, kann man suchen ... die SQL-Abfrage sieht immer etwa so aus ..
select employee_id, first_name, last_name from employees_search 
where contains(last_name, '{contains-query}') > 0
  • Suche nach einem "Accountant" namens "Higins" oder so ähnlich:
    ?Higins within (FULL_NAME) and ?Accountant within (JOB_TITLE)
  • Suche nach dem Team von einem Manager, dessen Name irgendwie auf "assuriz" endet (klassisch mit "Fuzzy" findet nichts):
    ?assuriz within (MGR_FULL_NAME)
  • Suche nach dem Team von einem Manager, dessen Name irgendwie auf "assuriz" endet (Name Search ist erfolgreich):
    NDATA(ND_MGR_FULL_NAME, assuriz)
  • Mit Name Search kann man Vor- und Nachnamen auch verdrehen:
    NDATA(FULL_NAME, Baer Hermann)
  • Alle Angestellten in Oxford, die nach dem 01.01.2008 eingestellt wurden:
    Oxford within (CITY) and SDATA(HIRE_DATE >= '2008-01-01')
Es sind beliebige Abfragen denkbar. Innerhalb von CONTAINS kann man ja mit AND, OR, NOT arbeiten und sich damit beliebig komplexe Abfrageausdrücke überlegen. Und das alles wird aus ein- und demselben Index bedient und zusätzlich hat man noch linguistische Features wie die Base-Letter Konvertierung (Ä -> A), Ähnlichkeits- und Namenssuche. Für Suchapplikationen (bspw. im Callcenter) kann Oracle TEXT so eine sehr mächtige Angelegenheit sein und durchaus auch mit spezieller Software mithalten. Und bei allem immer im Auge behalten: Oracle TEXT ist in der Datenbank "drin" und kostet nix extra!.
Es bleibt nun die Frage, was bei Datenänderungen (DML) an den zugrundeliegenden Tabellen passiert. Soviel vorab: Der bis hierher angelegte Index bekommt von etwaigen Änderungen überhaupt nichts mit. Um ihn zu aktualisieren, müsste man ihn neu bauen - und für manche Anwendungen würde das vielleicht auch in einem nächtlichen Wartungsfenster reichen. Andere Anwendungen brauchen eher einen ständig aktuellen Index - und wie man das macht, erfahrt Ihr im nächsten Blog-Posting.

Beliebte Postings