Montag, 7. Februar 2011

"Section"-Suche in Oracle TEXT

Wie die meisten wissen, ist Oracle TEXT in der Lage, XML-Dokumente (oder besser: "getaggte Dokumente") zu durchsuchen. Und wie Oracle TEXT mit solchen Dokumenten umgeht, kann sehr stark beeinflusst werden. Heute geht es also um das Section Searching und einige Grundlagen, die man dazu wissen sollte ... Angenommen, wir haben eine Tabelle mit folgenden Inhalten.
create table xml_text(
  id  number,
  doc xmltype
)
/

insert into xml_text values (1, '<KUNDE><NAME>Czarski</NAME><VORNAME>Carsten</VORNAME></KUNDE>')
/
insert into xml_text values (2, '<KUNDE><NAME>Schwinn</NAME><VORNAME>Ulrike</VORNAME></KUNDE>')
/
insert into xml_text values (3, '<KUNDE><NAME>Mustermann</NAME><VORNAME>Max</VORNAME></KUNDE>')
/
... dann legt man den Textindex darauf bspw. so an (das ist am einfachsten).
create index ft_xmltext on xml_text (doc)
indextype is ctxsys.context
parameters ('section group ctxsys.auto_section_group');
Danach kann man suchen ...
SQL> select id from xml_text where contains(doc, 'Carsten within (VORNAME)') > 0;

        ID
----------
         1

1 Zeile wurde ausgewählt.

SQL> select id from xml_text where contains(doc, 'Carsten within (NAME)') > 0;

Es wurden keine Zeilen ausgewählt
So weit so gut - schauen wir mal in den Index hinein (genauer gesagt: in die Token-Tabelle):
SQL> select token_text, token_type from dr$ft_xmltext$i

TOKEN_TEXT           TOKEN_TYPE
-------------------- ----------
CARSTEN                       0
CZARSKI                       0
KUNDE                         2
MAX                           0
MUSTERMANN                    0
NAME                          2
SCHWINN                       0
ULRIKE                        0
VORNAME                       2
Man sieht, dass die XML-Tags mit im Index stehen - sie haben den Token Type 2. Das bedeutet aber, dass der Index bei großen Tabellen eine ganze Menge Tags mitindiziert. Und vor allem indiziert er alle Tags - manche braucht man eigentlich gar nicht: in unserem Fall hier ist das Tag KUNDE völlig überflüssig.
Grundsätzlich bietet Oracle Text verschiedene Varianten (Section Group Types) für die Abschnittssuche an. Die Dokumentation enthält eine Übersicht. Für XML-Dokumente kommt es nun darauf an, wie man suchen möchte.
  • Möchte man eine Pfadsuche machen, also in XML-Manier (/KUNDE/VORNAME) in den Dokumenten suchen, so muss man die PATH_SECTION_GROUP verwenden. In CONTAINS kann dann mit den Abfrageoperatoren INPATH und HASPATH gearbeitet werden. Dies ist von der Indizierung her die aufwändigste Variante - der Index wird am größten. Auf der anderen Seite hat man mit INPATH und HASPATH die mächtigsten Abfragemöglichkeiten. Man sollte diese Variante aber auch nur dann wählen, wenn diese Möglichkeiten tatsächlich gebraucht werden.
  • XML_SECTION_GROUP und AUTO_SECTION_GROUP erlauben die einfache Section-Suche mit WITHIN. Während die AUTO_SECTION_GROUP bis auf explizit ausgeschlossene XML-Tags alle indiziert, müssen zu indizierende Tags bei der XML_SECTION_GROUP manuell angegeben werden. Das bedeutet aber auch mehr Kontrolle.
Also könnte man mit der XML_SECTION_GROUP arbeiten und nur die Tags NAME und VORNAME indizieren - damit würde das Tag KUNDE aus dem Index rausfallen - brauchen wir ohnehin nicht.
begin
  ctx_ddl.create_section_group('kunde_section_group', 'XML_SECTION_GROUP');
  ctx_ddl.add_zone_section('kunde_section_group', 'NAME', 'NAME');
  ctx_ddl.add_zone_section('kunde_section_group', 'VORNAME', 'VORNAME');
end;
/

create index ft_xmltext on xml_text (doc)
indextype is ctxsys.context
parameters ('section group kunde_section_group');
Die Suche funktioniert genauso wie vorhin - die Token-Tabelle ist leicht verändert.
SQL> select token_text, token_type from dr$ft_xmltext$i;

TOKEN_TEXT           TOKEN_TYPE
-------------------- ----------
CARSTEN                       0
CZARSKI                       0
MAX                           0
MUSTERMANN                    0
NAME                          2
SCHWINN                       0
ULRIKE                        0
VORNAME                       2
Das Token KUNDE ist weg. Jetzt haben wir allerdings die Tags NAME und VORNAME als Zone Sections indiziert. Zone Sections sind hier erklärt. Wie XML-Tags können Sie mehrfach im Dokument vorkommen und verschachtelt sein. Bei XML-Tags wird das ja durchaus gebraucht.
In unserem Falle aber nicht! Beide kommen nur einmal vor und enthalten nur noch Text.
Wenn man weiss, dass die Tags nur noch Text enthalten (also nicht mehr verschachtelt sind) und nur einmal im Dokument vorkommen, so kann man auch Field Sections (Dokumentation) verwenden.
begin
  ctx_ddl.create_section_group('kunde_section_group', 'XML_SECTION_GROUP');
  ctx_ddl.add_field_section('kunde_section_group', 'NAME', 'NAME', false);
  ctx_ddl.add_field_section('kunde_section_group', 'VORNAME', 'VORNAME', false);
end;
/
Der Index ist nun noch kompakter ...
SQL> select token_text, token_type from dr$ft_xmltext$i;

TOKEN_TEXT           TOKEN_TYPE
-------------------- ----------
CARSTEN                      17
CZARSKI                      16
MAX                          17
MUSTERMANN                   16
SCHWINN                      16
ULRIKE                       17
Die Suche funktioniert wieder wie vorhin ... mit einer Ausnahme ...
SQL> select id from xml_text where contains(doc, 'Carsten') > 0;

Es wurden keine Zeilen ausgewählt
Eine Field Section ist im Rest des Dokumentes nicht sichtbar. Für die Vornamen und Namen kann man in diesem Falle nur noch Section-Suche machen. Wenn man möchte, dass die globale Dokumentsuche trotzdem funktioniert, muss man den letzten Parameter beim Aufruf von ADD_FIELD_SECTION auf true setzen.
begin
  ctx_ddl.create_section_group('kunde_section_group', 'XML_SECTION_GROUP');
  ctx_ddl.add_field_section('kunde_section_group', 'NAME', 'NAME', true);
  ctx_ddl.add_field_section('kunde_section_group', 'VORNAME', 'VORNAME', true);
end;
/
Aber Achtung: Die Tokens werden dann doppelt indiziert ...
SQL> select token_text, token_type from dr$ft_xmltext$i;

TOKEN_TEXT           TOKEN_TYPE
-------------------- ----------
CARSTEN                       0
CARSTEN                      17
CZARSKI                       0
CZARSKI                      16
MAX                           0
MAX                          17
MUSTERMANN                    0
MUSTERMANN                   16
SCHWINN                       0
:                             :
Man sieht, dass man bei der Section-Suche eine ganze Menge Möglichkeiten hat, das Verhalten des Index zu beeinflussen. Und gerade bei großen Dokumentbeständen und Indizes kommt es auf die Indexgröße an - ein kleinerer Textindex kann noch in den Hauptspeicher passen und für performantere Abfragen sorgen ... Bei Bedarf empfiehlt es sich, auf diese Aspekte besonders Acht zu geben ...
Übrigens ist das Geschriebene auch bei Verwendung eines USER_DATASTORE oder eines MULTICOLUMN_DATASTORE relevant - in beiden Fällen werden die Informationen als XML-Dokumente aufbereitet und an den Index übergeben.

Mittwoch, 8. Dezember 2010

Mächtige Suchabfragen: PL/SQL-Funktionen innerhalb CONTAINS()

Wusstet Ihr schon, dass Ihr in einer CONTAINS-Abfrage auch SQL- und PL/SQL-Funktionen aufrufen könnt ...?
Das kann man nutzen, um Suchbegriffe durch eine Funktion aufzubereiten. Einfache Synonymbeziehungen lassen sich zwar auch mit einem Thesaurus abbilden, wenn die Beziehungen aber komplexerer Natur sind oder zwingend prozeduralen Code erfordern, ist die Nutzung einer PL/SQL-Funktion eine gute Alternative. Dazu ein Beispiel:
Zuerst Tabelle erstellen und einige "Dokumente" einfügen.
create table doc (
  id     number,
  doc    varchar2(4000)
);

insert into doc values (1, 'Oracle 11g');
insert into doc values (2, 'Ein Test');
insert into doc values (3, 'Oracle 9iR2');
insert into doc values (4, 'Oracle 11.2.0.1');
insert into doc values (5, 'Oracle 11gR2');
Dann indizieren ...
create index ft_doc on doc (doc)
indextype is ctxsys.context
/
Man sieht, dass die Datenbankversionen völlig unterschiedlich in der Tabelle auftauchen. Eine Variante wäre mit Sicherheit ein Thesaurus, aber in diesem Beispiel möchten wir das mit einer PL/SQL-Funktion erschlagen. Und die sähe wie folgt aus.
create or replace function format_release(
  p_release in varchar2
) return varchar2 is 
  v_tokens varchar2(4000);
begin
  if p_release like '11.2%' then
    v_tokens := '(11.2%) or {11gR2} or {11g} or {11g Release 2}';
  elsif p_release like '11.1%' then
    v_tokens := '(11.1%) or {11gR1} or {11g} or {11g Release 1}';
  elsif p_release like '10.2%' then
    v_tokens := '(10.2%) or {10gR1} or {10g} or {10g Release 2}';
  elsif p_release like '9.2%' then
    v_tokens := '(9.2%) or {9iR2} or {9i} or {9i Release 2}';
  else 
    v_tokens := p_release;
  end if;
  return v_tokens;
end;
/
Die Anwendung sieht dann so aus ...
SQL> select * from doc where contains(doc, format_release('11.2%')) > 0

        ID DOC
---------- ------------------------------
         1 Oracle 11g
         4 Oracle 11.2.0.1
         5 Oracle 11gR2

3 Zeilen ausgewählt.
In der Funktion lässt sich natürlich kodieren, was man möchte. So kann man auch Informationen aus einer Tabelle holen - damit könnte man ein Synonym wie "bester_kunde" definieren; diese Funktion holt den Namen des umsatzstärksten Kunden aus einer Tabelle und liefert ihn zurück. Man könnte damit also (lediglich anhand des Stichworts bester_kunde) nach allen Dokumenten suchen, in denen der Name des aktuell umsatzstärksten Kunden vorkommt. Eine andere Variante wäre die Kombination mit räumlichen Features der Datenbank. Dann könnte die Funktion in etwa so aussehen (Pseudocode) ...
create or replace function kunden_nahe(
  p_stadt in varchar2
) return varchar2 is 
  v_tokens varchar2(4000) := '';
begin
  -- Räumliche Abfrage: Hole alle Kundennamen, die sich
  -- innerhalb eines 10km-Radius um die gegebene Stadt 
  -- befinden
  for kd in (
    select k.name 
    from kunden k, staedte s
    where sdo_within_distance(k.position, s.position, 10, 'unit=km') = 'TRUE'
    and s.name = p_stadt
  ) loop
    v_tokens := v_tokens ||'(' || kd.name || ') or ';
  end loop;
  v_tokens := substr(v_tokens, 1, length(v_tokens) - 4);
  return v_tokens;
end;
/
Man sieht, dass diese "kleine Randnotiz" (man kann PL/SQL-Funktionen in CONTAINS verwenden), zu sehr mächtigen Suchanfragen führen kann. Die Praxis kennt die besten Beispiele ...

Dienstag, 26. Oktober 2010

Neues Oracle Text Feature XML QUERY RESULT SET

Im letzten Blog wurde schon das neue Text Feature NAME SEARCH, das mit Veröffentlichung des Patchsets 11.2.0.2 zur Verfügung steht, erläutert. In folgendem Tipp soll ein weiteres neues Feature - das sogenannte RESULT SET INTERFACE - an einem Beispiel illustriert werden.
Die Idee ist, vielfältige komplexe Abfragen für Ergebnismengen im Textumfeld, auf einen einzigen SQL Call zu reduzieren und damit Ressourcen zu sparen und die Performance zu erhöhen.
Als Beispieltabelle soll folgende Defintion verwendert werden:

 
DROP TABLE dokumente PURGE;
CREATE TABLE dokumente (id number, autor varchar2(30), datum date, titel varchar2(200), dokument clob);

INSERT INTO dokumente VALUES (1,'Doderer',sysdate,'Buch1','Doderer: Buch1: Oracle Buch');
INSERT INTO dokumente VALUES (2, 'Swobodnik' ,sysdate+1,'Buch2', 'Swobodnik: Buch2');
INSERT INTO dokumente VALUES (3, 'Tucholsky' ,sysdate+2,'Buch3', 'Tucholsky: Buch3');
INSERT INTO dokumente VALUES (4, 'Varga' ,sysdate+3,'Buch4', 'Varga: Buch4');
INSERT INTO dokumente VALUES (5, 'Maurer' ,sysdate+4,'Buch5', 'Maurer: Buch5');
INSERT INTO dokumente VALUES (6, 'Steinfest' ,sysdate+5,'Buch6', 'Steinfest: Buch6: Oracle Kategorie');
INSERT INTO dokumente VALUES (7, 'Rowling' ,sysdate+6,'Buch7', 'Rowling: Buch7');
INSERT INTO dokumente VALUES (8, 'Poe' ,sysdate+7,'Buch8', 'Poe: Buch8');
INSERT INTO dokumente VALUES (9, 'Beauvoir' ,sysdate+8,'Buch9', 'Beauvoir: Buch9');
INSERT INTO dokumente VALUES (10, 'Schwarzer' ,sysdate+9,'Buch10', 'Schwarzer: Buch10');
INSERT INTO dokumente VALUES (11, 'Schmidt' ,sysdate+10,'Buch11', 'Schmidt: Buch11');
INSERT INTO dokumente VALUES (12, 'Sartre' ,sysdate+11,'Buch12', 'Sartre: Buch12');
INSERT INTO dokumente VALUES (13, 'Maurer' ,sysdate+12,'Buch13', 'Maurer: Buch13');
INSERT INTO dokumente VALUES (14, 'Maurer' ,sysdate+13,'Buch14', 'Maurer: Buch14: Oracle Buch');
INSERT INTO dokumente VALUES (15, 'Maurer' ,sysdate+10,'Buch15', 'Maurer: Buch15: Oracle Buch');
INSERT INTO dokumente VALUES (16, 'Maurer' ,sysdate+10,'Buch16', 'Maurer: Buch16: Oracle ');
INSERT INTO dokumente VALUES (17, 'Maurer' ,sysdate+10,'Buch17', 'Maurer: Buch17');
INSERT INTO dokumente VALUES (18, 'Maurer' ,sysdate+10,'Buch18', 'Maurer: Buch18');
INSERT INTO dokumente VALUES (19, 'Maurer' ,sysdate+10,'Buch19', 'Maurer: Buch19');
INSERT INTO dokumente VALUES (20, 'Maurer' ,sysdate+10,'Buch20', 'Maurer: Buch20');
COMMIT;
Um effiziente Abfragen auf die Spalten AUTOR, DATUM und TITEL durchzuführen, wird die folgende Indexdefinition mit Angabe der FILTER BY und ORDER BY Klausel ausgeführt. Dieser Index - auch Composite Domain Index genannt - ist neu in Oracle 11g und bietet besonders gute Perfromance bei Mixed Query- Abfragen (siehe auch den Blogeintrag zum Thema).
 
DROP INDEX dokumente_IDX; 
CREATE INDEX dokumente_idx ON dokumente(dokument) INDEXTYPE IS ctxsys.contextFILTER BY autor, datum, titel;
SELECT err_text FROM ctx_user_index_errors WHERE err_index_name = 'DOKUMENTE_IDX';

Um die verschiedensten Informationen über die gespeicherten Dokumente zu erhalten, könnten sich nun folgende Fragestellungen ergeben: 1)Wie groß ist die Anzahl der Bücher, die das Wort "Oracle" enthalten?

SELECT count(*) FROM dokumente WHERE contains(dokument, 'oracle',1)>0; 
COUNT(*)
----------         
5
Eine effiziente Alternative bietet dabei die Funktion COUNT_HITS (siehe auch den Blogeintrag zum Thema) wie in folgendem Beispiel zu sehen ist:
 
set serveroutput on
declare  v_number number;
begin  v_number := ctx_query.count_hits (
      index_name => 'DOKUMENTE_IDX',
      text_query => 'oracle',   
           exact => true  );  
dbms_output.put_line('Anzahl Treffer: '||v_number);
end;
/
2) Wie groß ist diese Anzahl der Bücher gruppiert nach Datum?

SELECT datum, count(*) FROM dokumente 
where contains(dokument, 'oracle',1)>0 GROUP BY datum;
DATUM       COUNT(*)
--------- ----------
26-OCT-10          1
31-OCT-10          1
08-NOV-10          1
05-NOV-10          2
3) Wie groß ist diese Anzahl der Bücher gruppiert nach Autor?

SELECT autor, count(*) FROM dokumente 
WHERE contains(dokument, 'oracle',1)>0 GROUP BY autor;
AUTOR                            COUNT(*)
------------------------------ ----------
Doderer                                 1
Maurer                                  3
Steinfest                               1
4) Welche Bücher aus der Topliste enthalten das Wort "Oracle"?
 
SELECT * FROM  
(SELECT /*+ first_rows */ rowid, titel, autor, datum   
 FROM dokumente WHERE contains(dokument, 'oracle',1)>0   
 ORDER BY datum desc, score(1) desc)
WHERE rownum <= 3;
ROWID              TITEL      AUTOR      DATUM
------------------ ---------- ---------- ---------
AAAXJXAAFAAAfAUAAN Buch14     Maurer     08-NOV-10
AAAXJXAAFAAAfAUAAO Buch15     Maurer     05-NOV-10
AAAXJXAAFAAAfAUAAP Buch16     Maurer     05-NOV-10
Das Ganze lässt sich in 11.2.0.2 alternativ mit dem neuen Feature XML QUERY RESULT SET Interface lösen. Die Idee dabei ist, die mehrmalige Ausführung von SQL Statements wie in unserem Beispiel durchgeführt zu vermeiden und die Ergebnisse mit einem einzigen SQL Aufruf zu erhalten. Somit könnte eine schnellere und effizientere Ausgaben erfolgen. Wie der Name des Features schon andeutet, ist zur Beschreibung der Abfrage eine bestimmte XML-Eingabe - der XML SET Descriptor - notwendig. Auch das Ergebnis, die Hitliste, ist im XML Format. Verwendet wird zusätzlich die SDATA Sektionen, die automatisch mit Erzeugung des Composite Domain Index zur Verfügung stehen. So wird die Information über Gruppierungen in der Attributliste GROUP SDATA angegeben und END_HIT_NUMBER gibt die Größe der Topliste aus. Eine umfangreiche und vollständige Beschreibung findet sich im Handbuch. In unserem Beispiel sieht die Abfrage dann folgendermassen aus:
  
declare   
   ergebnis_clob clob;
begin   
   dbms_lob.createtemporary(ergebnis_clob, true, dbms_lob.session);
   ctx_query.result_set('dokumente_idx', 'Oracle', '
       <ctx_result_set_descriptor> <count/>
         <hitlist start_hit_num="1" end_hit_num="3" order="datum desc, score desc">
           <score/>
           <rowid/>
           <sdata name="titel"/>
           <sdata name="autor"/>
           <sdata name="datum"/>
         </hitlist>
        <group sdata="datum">
          <count/>
        </group>
        <group sdata="autor">
          <count/>
        </group>
     </ctx_result_set_descriptor>
                                                    ',ergebnis_clob); 
dbms_output.put_line('Ergebnis: '|| ergebnis_clob);
dbms_lob.freetemporary(ergebnis_clob);
 exception   
  when others 
  then    
    dbms_lob.freetemporary(ergebnis_clob);    
    raise;
end;
/
Auch das Ergebnis wird im XML Format dargestellt. In unserem Fall erhalten wir folgende Ausgabe. (Zur besseren Lesbarkeit wurden Leerzeichen eingefügt.)

Ergebnis:

<ctx_result_set>
 <hitlist>
  <hit><score>5</score><rowid>AAAXJXAAFAAAfAUAAN</rowid>
    <sdata name="TITEL">Buch14</sdata><sdata name="AUTOR">Maurer</sdata>
    <sdata name="DATUM">2010-11-08 11:56:06</sdata>
  </hit>
  <hit><score></score><rowid>AAAXJXAAFAAAfAUAAO</rowid>
   <sdata name="TITEL">Buch15</sdata><sdata name="AUTOR">Maurer</sdata>
   <sdata name="DATUM">2010-11-05 11:56:06</sdata>
  </hit>
  <hit><score>5</score><rowid>AAAXJXAAFAAAfAUAAP</rowid>
   <sdata name="TITEL">Buch16</sdata><sdata name="AUTOR">Maurer</sdata>
   <sdata name="DATUM">2010-11-05 11:56:06</sdata>
 </hit>
 </hitlist>
<count>5</count>
<groups sdata="DATUM">
  <group value="2010-10-26 11:56:06"><count>1</count></group>
  <group value="2010-10-31 11:56:06"><count>1</count></group>
  <group value="2010-11-05 11:56:06"><count>2</count></group>
  <group value="2010-11-08 11:56:06"><count>1</count></group>
</groups>
<groups sdata="AUTOR">
  <group value="Doderer"><count>1</count></group>
  <group value="Maurer"><count>3</count></group>
  <group value="Steinfest"><count>1</count></group>
</groups>
</ctx_result_set>

PL/SQL procedure successfully completed.
Um eine besser lesbare Ausgabeform zu erhalten, bietet sich beispielsweise die Nutzung der XML-Funktionen an. Dazu mehr in einem unserer nächsten Ausgaben...

Dienstag, 21. September 2010

Neues Oracle TEXT-Feature NAME SEARCH

Seit kurzem ist das erste Patchset für 11g Release 2 (11.2.0.2) erschienen. Das ist in der Tat besonders für die Nutzer von Oracle TEXT wichtig, denn das Basisrelease 11.2.0.1 enthielt keine neuen Funktionen für Oracle TEXT - die wurden nun mit dem Patchset eingeführt. Im einzelnen sind das ...
  • Name search - dieser werden wir uns heute widmen
  • Entity extraction and identification
  • Result Set Interface
Interessant ist daher auch die Dokumentation zu Oracle TEXT - diese wurde mit dem Erscheinen des Patchset übrigens ebenfalls ausgetauscht.
Doch heute mehr zum Thema Name Search. Diese neue Funktion in Oracle TEXT ist speziell für die Suche nach Namen vorgesehen. Namen werden ja, insbesondere wenn Sie aus einem anderen Sprachraum kommen, recht häufig falsch geschrieben oder falsch verstanden. Man wendet die Rechtschreibregeln, die man kennt (hierzulande Deutsch) auf den fremden Namen an. Und das resultiert dann in einer völlig anderen Schreibweise. Eine exakte Suche hilft oft nicht weiter. Mit der FUZZY-Suche kann man zwar schon nach ähnlich geschriebenen Begriffen suchen, für eine umfassende Suche nach Namen reicht das jedoch vielfach nicht aus. Ich kenne das selbst recht gut - und werde auch meinen Namen "Czarski" hernehmen, um die neue Funktionalität vorzustellen.
Damit Namen mit der neuen Name Search-Funktion durchsucht werden können, müssen Sie beim Indizieren besonders behandelt werden - wie wir noch sehen werden, erfolgt die Zerlegung in Tokens etwas anders als bei normalen Texten. Aus diesem Grunde müssen Sie durch eine SECTION_GROUP mit Hilfe von XML-Tags vom Rest des Dokumentes abgegrenzt werden. Das kann in beispielsweise so aussehen ...
drop table names
/

create table names (
  id number(10),
  name varchar2(200)
)
/

insert into names values (1, '<name>Max Mustermann</name>');
insert into names values (2, '<name>Larry Ellison</name>');
insert into names values (3, '<name>Ulrike Schwinn</name>');
insert into names values (4, '<name>Carsten Czarski</name>');
insert into names values (5, '<name>Günther Stürner</name>');
Natürlich kann man auch Namen aus normalen, relationalen Tabellenspalten durchsuchbar machen. Diese indiziert man dann mit dem Multicolumn Datastore oder dem User Datastore. Wichtig ist, dass der Name in einem XML-Tag steht - das Tag selbst kann frei gewählt werden. Die Dokumentation zeigt im Abschnitt "Name Search" weiterführende Beispiele zur Namens-Indizierung von Tabellenspalten. Die SECTION_GROUP für die Namen muss dann als NDATA-Section Group deklariert werden.
begin
  ctx_ddl.drop_section_group('name_sg');
end;
/

begin
  ctx_ddl.create_section_group('name_sg', 'BASIC_SECTION_GROUP');
  ctx_ddl.add_ndata_section('name_sg', 'name', 'name');
end;
/
Alles innerhalb des XML-Tags <name> gehört nun zur NDATA-Section und wird speziell für die Namenssuche indiziert. Der nächste Schritt ist folgerichtig das Erstellen des Volltextindex.
create index ft_names on names (name)
indextype is ctxsys.context
parameters ('section group name_sg')
/
Anschließend der erste spannende Moment - wir werfen mal einen Blick in die Tokentabelle ...
select token_type, token_text from dr$ft_names$i
/

TOKEN_TYPE TOKEN_TEXT
---------- ----------------------------------------------------------------
       200 ^arr
       200 ^ars
       200 ^ax$
       200 ^azi
       200 ^bck
       200 ^bec
       200 ^bek
       200 ^car
       200 ^cas
       200 ^chw
         : :
Das sieht ja schonmal etwas anders aus ... nun probieren wir das mal aus. Zunächst kann man die Namen in beliebiger Reihenfolge suchen (schon mal nicht schlecht). Obwohl in der Tabelle Carsten Czarski steht, kann ich nach Czarski, Carsten suchen ...
SQL> select * from names where contains(name, 'NDATA(name,Czarski\, Carsten)') > 0

        ID NAME
---------- ----------------------------------------
         4 Carsten Czarski

1 Zeile wurde ausgewählt.
Beachtet die Syntax: Eine Namenssuche wird durch den NDATA-Operator innerhalb der CONTAINS-Abfrage eingeleitet. Der erste Parameter des NDATA-Operators ist wiederum die NDATA-Section-Group, also das XML-Tag, in dem sich der Name befindet (hier: name). Es könnte ja sein, dass es in XML-Dokumenten mehrere Namensabschnitte gibt. Danach kommt die Phrase, nach der gesucht wird und danach kommen noch zwei Parameter: ORDER / NOORDER legt fest, ob die Reihenfolge der Namensbestandteile berücksichtigt werden soll (NOORDER ist der Default) und PROXIMITY / NOPROXIMITY bestimmt, ob die Ähnlichkeit des Suchbegriffs zum tatsächlichen Namen den Score beeinflussen soll.
Als nächstes habe ich ein paar Tests gemacht - mit dem Namen Czarski ist man ja einiges gewöhnt. Erstmal einfach ...
SQL> select * from names where contains(name, 'NDATA(name,zarsky)') > 0

        ID NAME
---------- ----------------------------------------
         4 Carsten Czarski

1 Zeile wurde ausgewählt.
Gut ... das hätte man mit dem FUZZY-Operator auch noch geschafft. Also noch etwas nachlegen ...
SQL> select * from names where contains(name, 'NDATA(name,Tsarski)') > 0

        ID NAME
---------- ----------------------------------------
         4 Carsten Czarski
Noch ein wenig ...
SQL> select * from names where contains(name, 'NDATA(name,Tscharski)') > 0

        ID NAME
---------- ----------------------------------------
         4 Carsten Czarski
Und ein letztes, weil's so schön ist ...
SQL> select * from names where contains(name, 'NDATA(name,Saarski Karsden)') > 0

        ID NAME
---------- ----------------------------------------
         4 Carsten Czarski
Das ist schonmal nicht schlecht - ein FUZZY-Operator hätte hier schon seine Grenzen gehabt ... Probieren wir mal was anderes ... nicht ganz so exotisch und in Deutschland durchaus häufig anzutreffen.
SQL> select * from names where contains(name, 'NDATA(name,Schtürner)') > 0;

        ID NAME
---------- ----------------------------------------
         5 Günther Stürner

1 Zeile wurde ausgewählt.
Auch das konnte ich mit FUZZY nicht hinbekommen. Die ersten Tests sind also recht ermutigend; ich könnte mir vorstellen, dass dieses neue Feature nicht nur für die Volltextrecherche, sondern für viele "ganz normale" Applikationen interessant ist, denn nach Namen sucht man häufig und vielfach hat man einen Namen "nur gehört" und ist sich nicht sicher, wie er geschrieben ist. Es kann also durchaus sein, dass wir in Zukunft öfter mal einen Oracle TEXT-Index auf einer ganz normalen Tabelle (mit Namensspalten) sehen werden.
Viel Spaß beim Ausprobieren!

Freitag, 20. August 2010

Anwendungsbeispiel für MULTI_COLUMN_DATASTORE, MDATA und Operatoren ACCUM und WEIGHT

Ist es möglich Dokumenten unterschiedlichen Formats (Dateityps) eine unterschiedliche Gewichtung zu geben? Beispielsweise ist die Vorgabe, dass Dokumente im Format PDF eine höhere Gewichtung erhalten sollen als Dokumente im Format HTML. Mit MULTI_COLUMN_DATASTORE, MDATA und den unterschiedlichen Operatoren wie ACCUM und WEIGHT kann dies möglich gemacht werden. Mehr Informationen zur MDATA-Nutzung und Multicolumn-Datastore finden Sie übrigens im Metadatensuche mit MDATA und im MData Section und MULTI_COLUMN_DATASTORE Blog.
Nehmen wir im folgenden Beispiel die Tabelle DOCUMENTS mit Spalten INHALT und DATEI_TYP.

CREATE TABLE documents
(id NUMBER, datei_typ VARCHAR2(10), INHALT blob);

Danach füllen wir die Tabelle mit unterschiedlichen Inhalten. Wir verwenden eine LOBLOAD-Prozedur, die die Spalteninhalte als Parameter übergibt. Der Dateiname gibt dabei Auskunft über den Inhalt und das Format.

execute lobload(1,'html','inhalt1.html')
execute lobload(2, 'html','inhalt2.html');
execute lobload(3,'PDF','inhalt1.PDF');
execute lobload(4,'PDF','inhalt2.PDF');

Danach erzeugen wir einen Multicolumn-Datastore über die beiden Spalten DATEI_TYP und INHALT, um eine einzige CONTAINS- Abfrage über die beiden Spalten durchzuführen. Um den Multicolumn-Datastore nutzen zu können, muss zunächst eine Preference erzeugt werden; hier werden die Spalten, welche gemeinsam indiziert werden sollen, konfiguriert. Da nur die Spalte INHALT und nicht die Spalte DATEI_TYP gefiltert werden soll, setzen wir die FILTER-Attribute auf 'N,Y'.

begin
ctx_ddl.create_preference('mds', 'multi_column_datastore');
ctx_ddl.set_attribute('mds', 'columns', 'datei_typ, inhalt');
ctx_ddl.set_attribute('mds', 'filter', 'N,Y');
end;
/

Die Spalten sind nun im Multicolumn-Datastore MDS zusammengeführt. Da die Spalte DATEI_TYP als Metadatenspalte aufgefasst werden kann und keine Aufarbeitung in Tokens benötigt, definieren wir die entsprechende MDATA-Section für DATEI_TYP.

begin 
ctx_ddl.create_section_group('bsg', 'basic_section_group');
ctx_ddl.add_mdata_section('bsg', 'datei_typ', 'datei_typ');
end;
/

Im letzten Schritt erzeugen wir noch den Index IDX auf die Spalte INHALT.

SQL> CREATE INDEX idx ON documents(inhalt) INDEXTYPE IS ctxsys.context 
     PARAMETERS ('section group bsg datastore mds filter ctxsys.auto_filter');
Index created.
SQL> SELECT err_index_name, err_timestamp, err_textkey, err_text
     FROM ctx_user_index_errors ORDER BY err_index_name, err_timestamp;
no rows selected

Überprüfen wir nun die Dokumente auf einen Inhalt zum Beispiel auf "external". Die Dokumente mit ID 1 und 3 enthalten offensichtlich das gesuchte Token.

SQL> SELECT score(1), id, datei_typ 
     FROM documents WHERE contains(inhalt, 'external', 1)>0;

   SCORE(1)         ID DATEI_TYP
---------- ---------- ----------
        12          1 html
        12          3 PDF


Im nächsten Schritt sollen PDF-Dokumente eine höhere Priorität als HTML-Dokumente erhalten. Dazu nutzen wir den Operator MDATA. Zu beachten ist dabei, dass MDATA Sektionen immer case-sensitiv sind. Das bedeutet wir müssen auf "PDF" und "html" abfragen. Zusätzlich liefert das Ergebnis des MDATA-Operators immer den Wert 0 für "keine Treffer" und 100 für "Treffer". Überprüfen wir zuerst das Scoring auf PDF-Formate . Dokument 3 und 4 sind PDF Dokumente und erhalten den Score 100.

SQL> SELECT score(1), id, datei_typ FROM documents 
     WHERE contains(inhalt, 'mdata(datei_typ, PDF)', 1)>0;
  SCORE(1)         ID DATEI_TYP
---------- ---------- ----------
       100          3 PDF
       100          4 PDF

Benutzen wir zusätzlich die Suchabfrage "external" und verbinden wir das Ganze mit dem ACCUM- Operator (hier mit Komma ','). Wir erhalten nun alle PDF-Dokumente und das HTML-Dokument mit dem entsprechenden Suchwort. Dann sieht das Ergebnis der Abfrage folgendermassen aus.

SQL> SELECT score(1), id, datei_typ FROM documents 
     WHERE contains(inhalt, 'mdata(datei_typ, PDF), external', 1)>0
     ORDER BY 1;
 SCORE(1)         ID DATEI_TYP
---------- ---------- ----------
         6          1 html
        50          4 PDF
        78          3 PDF

Wie arbeitet dabei der Operator ACCUM? Der Operator ACCUM sucht im ersten Schritt nach erfolgreichen Bedingungen und errechnet danach den Score aus der Anzahl der Treffer-Häufigkeit. Bei zwei Bedingungen und Treffer in den beiden Bedingungen (hier PDF und Suchwort) liegt dabei der Scorewert zwischen 51 und 100, ansonsten zwischen 1 und 50.
Um weitere Möglichkeiten aufzuzeigen, kombinieren wir die Abfrage mit einer zusätzlichen Gewichtung (in unserem Fall mit "*2") und nehmen zusätzlich das Format "html" hinzu. Nun können wir folgende Resultate erhalten.

SQL> SELECT score(1), id, datei_typ FROM documents 
     WHERE contains(inhalt, 'mdata(datei_typ, PDF)*2, mdata(datei_typ, html),    
     external', 1)>0 ORDER BY 1;
 SCORE(1)         ID DATEI_TYP
---------- ---------- ----------
        25          2 html
        39          1 html
        50          4 PDF
        68          3 PDF

SQL> SELECT score(1), id, datei_typ FROM documents 
     WHERE contains(inhalt, 'mdata(datei_typ, PDF)*2, mdata(datei_typ, html), 
     external*2', 1)>0 ORDER BY 1;

  SCORE(1)         ID DATEI_TYP
---------- ---------- ----------
        20          2 html
        40          4 PDF
        48          1 html
        71          3 PDF

Wie man im letzten Beispiel sehen kann, gibt es eine Vielfalt von möglichen Kombinationen. Probieren Sie es einfach aus...

Dienstag, 20. Juli 2010

Ähnliche "Tokens" mit der BROWSE_WORDS-Funktion finden

Die Funktion CTX_QUERY.BROWSE_WORDS ist sehr hilfreich, wenn es darum geht, "sich im Textindex" umzusehen. So kann man damit bspw. die aus dem Internet bekannte "Meinten Sie ...?"-Funktion umsetzen.
BROWSE_WORDS ist hier dokumentiert. Ausgehend von einem Start-Token sucht die Funktion die alphabetisch "danach" oder "davor" kommenden Tokens aus dem Index heraus. Liefert eine Textabfrage also nur wenige Ergebnisse zurück, so kann man mit dieser Funktion Suchvorschläge machen; könnte ja sein, dass der Anwender sich einfach nur vertippt hat. Das "Start-Token" kann, muss aber nicht im Index enthalten sein.
Hier ein Beispiel: Rund um das "Start-Token" XDBADNIN (Tippfehler) werden nun 10 Tokens herausgesucht ...
set serveroutput on;

declare
  v_resarr ctx_query.browse_tab;
begin
  ctx_query.browse_words(
    index_name => 'ftx_scripts',
    seed       => 'XDBADNIN',
    resarr     => v_resarr,
    numwords   => 10,
    direction  => CTX_QUERY.BROWSE_AROUND
  );
  for i in 1..v_resarr.count loop
    dbms_output.put_line(
      rpad(v_resarr(i).word, 45) || ':' || 
      lpad(v_resarr(i).doc_count, 3)
    );
  end loop;
end;
/
Die Ausgabe ist dann wie folgt ...
XDB1M102                                     :  2
XDB3G65V2HGYFNY6SPJQOPJZUK6YG                :  1
XDB7A24C                                     :  1
XDBACL                                       :  1
XDBADMIN                                     : 18
XDBAPP                                       :  1
XDBBRNNM6W5FHBEMZV5PCLLGABJNO7Z6BJL22DCN     :  1
XDBCONFIG                                    : 23
XDBCORE                                      :  4
XDBDBMIG                                     :  8
Gut zu erkennen ist, dass zusätzlich auch noch die Anzahl der Dokumente, in denen das jeweilige Token vorkommt, mitgeliefert wird. Man könnte in dieser Liste also das Token mit dem höchsten Document Count heraussuchen und dem Endanwender vorschlagen ...
Wichtig bei meinen Tests mit der Funktion war die Einstellung von NLS_SORT - für diese Funktion sollte es auf BINARY stehen (sonst werden die Tokens "vorher" nicht richtig herausgesucht). Wenn eure Anwendung hier mit LINGUISTIC arbeitet, sollte es für diese Funktion umgestellt werden; NLS_SORT kann mit einem ALTER SESSION geändert werden. Das folgende Beispiel zeigt, wie man das erreichen kann.
set serveroutput on;

declare
  v_resarr  ctx_query.browse_tab;
  v_nlssort varchar2(4000);
begin
  v_nlssort := sys_context('userenv', 'nls_sort');
  dbms_session.set_nls('NLS_SORT', 'BINARY');

  ctx_query.browse_words(
    index_name => 'ftx_scripts',
    seed       => 'XDBADNIN',
    resarr     => v_resarr,
    numwords   => 10,
    direction  => CTX_QUERY.BROWSE_AROUND
  );
  for i in 1..v_resarr.count loop
    dbms_output.put_line(
      rpad(v_resarr(i).word, 45) || ':' || 
      lpad(v_resarr(i).doc_count, 3)
    );
  end loop;

  dbms_session.set_nls('NLS_SORT', v_nlssort);
end;
/

Dienstag, 15. Juni 2010

Oracle Text Komponente überprüfen und installieren

Wie kann man überprüfen, dass die Oracle Text Umgebung korrekt installiert ist? Wie kann man Oracle Text nachinstallieren? Das sind häufig Fragen, die im Zusammenhang mit der Oracle Text Komponente auftauchen. Normalerweise ist Oracle Text in allen Editionen - Oracle Database Standard Edition One, Oracle Database Standard Edition (SE), Oracle Database Enterprise Edition (EE), Oracle Database Express Edition (XE) und Oracle Database Personal Edition - ohne zusätzliche Kosten verfügbar und wird auch automatisch mitinstalliert. Um zu überprüfen, ob die Installation fehlerfrei ist, eignen sich folgende Schritte, die als User SYS durchgeführt werden.

Mit der View DBA_REGISTRY können alle Komponenten und ihr zugehöriger Status angezeigt werden, die in das Verzeichnis der Datenbank geladen sind. Hier wird auf die COMP_ID CONTEXT abgefragt, um den Status zu Oracle Text zu evaluieren.

SQL> SELECT comp_name, status, substr(version,1,10) as version
     FROM dba_registry WHERE comp_id = 'CONTEXT';

COMP_NAME                                STATUS     VERSION
---------------------------------------- ---------- ----------
Oracle Text                              VALID      11.2.0.1.0

Danach wird die Version des CTXSYS Data Dictionaries und die Code Versionsnummer ausgegeben.

SQL> SELECT * FROM ctxsys.ctx_version;

VER_DICT   VER_CODE
---------- ----------
11.2.0.1.0 11.2.0.1.0

SQL> SELECT ctxsys.dri_version VER_CODE FROM dual;

VER_CODE
--------------------------------------------------------------------------------
11.2.0.1.0

Im nächsten Schritt wird überprüft, ob alle Objekte im CTXSYS Schema angelegt sind, keine invaliden Objekte vorhanden sind und keine CTXSYS-Objekte im SYS Schema enthalten sind. Das Ergebnis sollte dann folgendermassen aussehen.

SQL> SELECT count(*) FROM dba_objects WHERE owner='CTXSYS';
  
COUNT(*)
----------
       366

SQL> SELECT object_type, count(*) FROM dba_objects 
     WHERE owner='CTXSYS' GROUP BY object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
SEQUENCE                     3
PROCEDURE                    2
OPERATOR                     6
LOB                          2
LIBRARY                      1
PACKAGE                     73
PACKAGE BODY                62
TYPE BODY                    6
INDEX                       56
TABLE                       47
VIEW                        71
FUNCTION                     2
INDEXTYPE                    4
TYPE                        31

14 rows selected.

SQL> SELECT object_name, object_type, status 
     FROM dba_objects WHERE owner='CTXSYS' AND status != 'VALID' 
     ORDER BY object_name;

no rows selected


SQL> SELECT owner, object_name, object_type, status FROM dba_objects
     WHERE owner = 'SYS' AND (object_name like 'CTX_%' or object_name like 'DRI%')
     ORDER BY 2,3; 

no rows selected

Soweit ist alles in unserer Installation in Ordnung... Möchte man diese und weitere Überprüfungen zu den existierenden Textindizes automatisch durchführen, eignet sich das kleine Health Check Skript aus Note 823649.1.

Nun wollen wir in einer anderen Umgebung Oracle Text Release 11gR2 nachinstallieren. Dazu werden zwei Skripte benötigt. Das Skript catctx.sql im Verzeichnis $ORACLE_HOME/ctx/admin erzeugt das Oracle Text Dictionary und das Schema CTXSYS. Als SYS eingeloggt werden dabei zu Beginn die Einstellungen zu User CXTSYS - wie Passwort, Default und Temporary Tablespace und LOCK oder UNLOCK Status - abgefragt. Danach werden die entsprechenden Dictionary Objekte angelegt.

SQL> connect / as sysdba 
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql
Enter value for 1: oracle
Enter value for 2: SYSAUX
Enter value for 3: TEMP
Enter value for 4: NOLOCK
...creating user CTXSYS
old   2: identified by &pass default tablespace &tbs temporary tablespace &ttbs
new   2: identified by oracle default tablespace SYSAUX temporary tablespace TEMP

User created.


Grant succeeded.


Grant succeeded.
...

Nach wenigen Minuten ist das Dictionary angelegt. Nun werden im zweiten Schritt die Sprach-spezifischen Default Präferenzen erzeugt. Es existieren Skripte für die unterschiedlichen Sprachen im $ORACLE_HOME/ctx/admin/defaults Verzeichnis. Die Skripte heissen drdef(sprachkuerzel).sql. Das Sprachkürzel D steht beispielsweise für deutsche, US für englische, F für französische Spracheinstellungen. Im folgenden legen wir eine deutsche Umgebung an, dazu loggen wir uns als CTXSYS ein und starten das Skript dr0defin.sql mit Parameter GERMAN, das automatisch das korrekte Spracheinstellungs-Skript ausführt.

SQL> connect ctxsys/oracle
SQL> @dr0defin.sql "GERMAN"
old   1: SELECT DECODE('&nls_language',
new   1: SELECT DECODE('GERMAN',

L
-
d

Creating lexer preference...

PL/SQL procedure successfully completed.

Creating wordlist preference...

PL/SQL procedure successfully completed.

Creating stoplist...

PL/SQL procedure successfully completed.

Creating default policy...

PL/SQL procedure successfully completed.

SQL>

Nach ein paar Sekunden ist dies auch erledigt. Zum Abschluss können wir die zu Beginn beschriebenen Skripte starten, um nun eine Validierung dieser Installation durchzuführen.

Montag, 17. Mai 2010

"Sicheres" Suchen: Oracle TEXT und die "Virtual Private Database"

Eine Volltextsuche geht bekanntlich immer über die ganze Tabelle. Manchmal bestehen allerdings Anforderungen an "sicheres Suchen", es soll also nicht jeder alles sehen können. Hierzu bietet sich im "normalen" Datenbankumfeld die Virtual Private Database an. Diese bewirkt, kurzgefasst, dass unterschiedliche Nutzer ein SELECT * FROM TABELLE absetzen, jeder sieht jedoch was anderes (das Feature gehört zur Enterprise Edition der Datenbank). Das wollen wir nun im Zusammenspiel mit Oracle TEXT einsetzen. Zuerst erzeugen wir die Tabelle und fügen ein paar Spalten ein:
create table dokumente(
  id                 number(10),
  sicherheitsmerkmal varchar2(50),
  dokument           clob
)
/

insert into dokumente values (1, 'LOW', 'Oracle TEXT dient zur Volltextrecherche');
insert into dokumente values (2, 'LOW', 'Oracle TEXT beherrscht linguistische Suche');
insert into dokumente values (3, 'MEDIUM', 'Der Gewinn wird im nächsten Jahr steigen');
insert into dokumente values (4, 'HIGH', 'Die Spionageabteilung sitzt in Berlin');
Das Besondere ist die Spalte SICHERHEITSMERKMAL - sie kann die Werte MEDIUM, HIGH oder LOW annehmen. Es fehlt nun nur noch eine Tabelle, mit welcher wir die Datenbanknutzer auf die Sicherheitsklassen abbilden ...
create table dokumente_nutzer(
  userid             varchar2(30),
  sicherheitsmerkmal varchar2(100)
)
/

insert into dokumente_nutzer values ('SCOTT', 'HIGH');
insert into dokumente_nutzer values ('SCOTT', 'MEDIUM');
insert into dokumente_nutzer values ('SCOTT', 'LOW');
insert into dokumente_nutzer values ('REVISION', 'MEDIUM');
insert into dokumente_nutzer values ('REVISION', 'LOW');
insert into dokumente_nutzer values ('ALLE', 'LOW');
Dann werden (als SYS) die Nutzer angelegt und, da die Privilegiensteuerung komplett über die Virtual Private Database gesteuert wird, SELECT-Privilegien an der Tabelle an PUBLIC vergeben.
create user revision identified by revision
/

create user alle identified by alle
/

grant create session to revision
/

grant create session to alle
/

grant select on scott.dokumente to public
/

Nun geht es ans Einrichten der Virtual Private Database. Zunächst wird die sog. Policy Function benötigt. Die Policy Function generiert eine WHERE-Bedingung, die dann von der Datenbank automatisch in das SELECT-Kommando integriert wird. Durch diese Vorgehensweise (die Sicherheitsregel "hängt" direkt an der Tabelle) kann die Sicherheitsregel nicht umgangen werden! Unsere PL/SQL-Funktion ist recht einfach ...
create or replace function my_policy(
 p_schema varchar2, p_object varchar2
) return varchar2 is
  v_where varchar2(32767) := 'SICHERHEITSMERKMAL IN (';
  v_users boolean := false;
begin
  for i in (
    select sicherheitsmerkmal from dokumente_nutzer
    where userid = user
  ) loop
    v_users := true;
    v_where := v_where || '''' || i.sicherheitsmerkmal || ''', ';
  end loop;
  if v_users then 
    v_where := substr(v_where, 1, length(v_where) - 2) || ')'; 
  else
    v_where := '1=0';
  end if;
  return v_where;
end;  
Als letztes machen wir die Virtual Private Database "scharf" ... Um dieses Kommando laufen zu lassen, wird das EXECUTE-Privileg auf DBMS_RLS benötigt - oder der DBA macht es gleich selbst ...
begin
 DBMS_RLS.ADD_POLICY(
    object_schema => 'SCOTT',
    object_name => 'DOKUMENTE',
    policy_name => 'SCOTT_DOKUMENT_POLICY',
    function_schema => 'SCOTT',
    policy_function => 'MY_POLICY'
  );
end;
/
Und nun kommt der erste Test (noch ohne Volltextindex). Probiert nun mal, die Tabelle mit verschiedenen Nutzerkonten zu selektieren ... Zuerst als SCOTT:
SQL> select * from scott.dokumente;

        ID SICHERHEITSMERKMAL                                 DOKUMENT
---------- -------------------------------------------------- --------------------------------------------------
         1 LOW                                                Oracle TEXT dient zur Volltextrecherche
         2 LOW                                                Oracle TEXT beherrscht linguistische Suche
         3 MEDIUM                                             Der Gewinn wird im nächsten Jahr steigen
         4 HIGH                                               Die Spionageabteilung sitzt in Berlin

4 Zeilen ausgewählt.
... dann als User ALLE:
SQL> select * from scott.dokumente;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         1 LOW             Oracle TEXT dient zur Volltextrecherche
         2 LOW             Oracle TEXT beherrscht linguistische Suche

2 Zeilen ausgewählt.
... schließlich als irgendein User, der in der Tabelle DOKUMENTE_NUTZER nicht vorkommt ...
SQL> select * from scott.dokumente;

Es wurden keine Zeilen ausgewählt
Man sieht, dass zwar alle die Tabelle selektieren können, jeder sieht allerdings was anderes. Und das kann man natürlich auch mit dem Volltextindex kombinieren. Den müssen wir natürlich zuerst mal anlegen. Und hier wollen wir auch gleich das in 11g neue Feature des Composite Domain Index nutzen (siehe das eigene Blog Posting dazu. Nötig ist das, da in diesem Fall jede Abfrage eine Mixed Query sein wird - die Virtual Private Database hängt ja eine relationale Abfrage an jede Textabfrage an ...
create index vt_dokumente on dokumente(dokument)
indextype is ctxsys.context
filter by sicherheitsmerkmal
parameters ('transactional')
/
Nun können wir testen: Wir arbeiten als User ALLE und probieren mal einige Vollextabfragen ...
SQL> select * from scott.dokumente where contains (dokument,'Oracle') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         1 LOW             Oracle TEXT dient zur Volltextrecherche
         2 LOW             Oracle TEXT beherrscht linguistische Suche

2 Zeilen ausgewählt.

SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

Es wurden keine Zeilen ausgewählt
Die Abfrage mit dem Gewinn probieren wir danach mal als User REVISION - der darf mehr sehen, aber nicht alles ...
SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         3 MEDIUM          Der Gewinn wird im nächsten Jahr steigen

1 Zeile ausgewählt.

SQL> select * from scott.dokumente where contains (dokument,'Spionageabteilung') > 0;

Es wurden keine Zeilen ausgewählt.
Die Spionageabteilung darf aber nur der SCOTT sehen ...
SQL> select * from scott.dokumente where contains (dokument,'Spionageabteilung') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         4 HIGH            Die Spionageabteilung sitzt in Berlin

1 Zeile ausgewählt.
Und das Schöne ist, dass diese Sicherheitsregeln unabhängig vom Textindex arbeiten; sie ziehen sowohl bei "normalen" als auch bei Volltextrecherchen. Zum Abschluß werfen wir einen Blick auf die Performance - normalerweise sind solche Mixed Queries ja problematisch ... insbesondere, wenn das Volltextkriterium nur gemeinsam mit dem relationalen Kriterium selektiv ist. Auch dazu bauen wir einen kleinen Testcase und fügen einige Sätze in die Tabelle DOKUMENTE ein.
drop index vt_dokumente
/

begin
  for i in 1..100000 loop
    insert into dokumente values (i+10, 'MEDIUM', 'Der Gewinn ist dieses Jahr stabil');
    insert into dokumente values (i+200010, 'LOW', 'Oracle TEXT ist Teil der Datenbank');
   end loop;
end;
/

commit
/

insert into dokumente values (500000, 'LOW', 'Gewinn ist: Mehr Einnahmen als Ausgaben');
    

create index vt_dokumente on dokumente(dokument)
indextype is ctxsys.context
filter by sicherheitsmerkmal
parameters ('transactional')
/
Diese Situation bringt das Mixed Query-Problem auf den Punkt. Die Volltextrecherche nach Gewinn allein ist nun extrem unselektiv (100.000 mal in MEDIUM, 1 mal in LOW). Die Sicherheitsklasse LOW ist aber auch unselektiv (kommt auch 100.000 mal vor). Die Kombination als Volltextsuche und relationalen Kriterium allein ist sehr selektiv - denn Gewinn kommt in der Sicherheitsklasse LOW nur einmal vor. Schauen wir uns die Abfrage nun an - wir arbeiten mit dem User ALLE (dieser hat nur Zugriff auf LOW), es darf also nur die Zeile der Klasse LOW zurückkommen ...
SQL>select * from scott.dokumente where contains (dokument,'Gewinn') > 0

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
    300000 LOW             Gewinn ist: Mehr Einnahmen als Ausgaben

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.17
Sieht auf den ersten Blick ganz gut aus - und der Ausführungsplan ...?
SQL> set autotrace traceonly
SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

1 Zeile wurde ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3982102634

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 42738 |  6135K|  9161   (4)| 00:01:50 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DOKUMENTE    | 42738 |  6135K|  9161   (4)| 00:01:50 |
|*  2 |   DOMAIN INDEX              | VT_DOKUMENTE |       |       |  7710   (1)| 00:01:33 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("DOKUMENT",'Gewinn')>0)
       filter("SICHERHEITSMERKMAL"='LOW')


Statistiken
----------------------------------------------------------
         35  recursive calls
          0  db block gets
        551  consistent gets
          0  physical reads
          0  redo size
        928  bytes sent via SQL*Net to client
        648  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
Im Ausführungsplan erkennt man, dass die komplette Abfrage aus dem Volltextindex heraus bedient wurde. Genau für solche Fälle wurde dieses Feature in 11g auch geschaffen ... Zum Abschluß noch ein paar Links zu weiterführender Dokumentation:

Freitag, 9. April 2010

Die Wordlist und das wildcard_maxterms Attribut

In folgendem Blog wird das WILDCARD_MAXTERMS Attribut, das eine Eigenschaft der BASIC_WORDLIST ist, kurz beschrieben und an einem Beispiel erklärt. Die Einstellung von WILDCARD_MAXTERMS gibt die maximale Anzahl der Ergebnisbegriffe einer Wildcard Suche an. Was bedeutet dies nun?

Wir nutzen im Beispiel die Tabelle TEXTTABELLE aus dem Blog und definieren zusätzlich die Präferenz für Mixed Case.

execute ctx_ddl.drop_preference('MY_LEXER');
begin
  ctx_ddl.create_preference(
    preference_name => 'MY_LEXER',
    object_name     => 'BASIC_LEXER');
  -- Mixed Case abschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO');
end;
/

Danach setzen wir explizit die BASIC_WORDLIST auf den Wert 1, um einen Fehler auszulösen.

execute ctx_ddl.drop_preference('my_wordlist');
BEGIN
  ctx_ddl.create_preference('my_wordlist', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute('my_wordlist', 'WILDCARD_MAXTERMS', 1);
END;
/

Nun wird der Index angelegt ...

CREATE INDEX idx_text ON texttabelle (dokument)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('WORDLIST my_wordlist LEXER MY_LEXER')
/

Bei der folgenden Abfrage erhalten wir nun einen Fehler, da eigentlich mehr als ein Ergebnis zu erwarten ist.

SQL> SELECT * FROM texttabelle WHERE contains (dokument,'%gewinn%')>0;
SELECT * FROM texttabelle WHERE contains (dokument,'%gewinn%')>0
*
FEHLER in Zeile 1:
ORA-29902: Fehler bei der Ausführung von Routine ODCIIndexStart()
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms

Verifizieren wir das Ganze nun mit der Token-Tabelle, um die Ergebnisbegriffe einzusehen.

SQL> SELECT token_text FROM dr$idx_text$i WHERE upper(token_text) like '%GEWINN%';

TOKEN_TEXT
----------------------------------------------------------------
GEWINNT
GEWINNZUWACHS

Ändern wir also in der Wordlist das WILDCARD_MAXTERMS Attribut auf den Wert 20000. Dies ist übrigens in 11g der Default-Wert.
 
execute ctd_ddl.drop_preference('my_wordlist');
BEGIN  
  ctx_ddl.create_preference ('my_wordlist', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute ('my_wordlist', 'WILDCARD_MAXTERMS', 20000);
END;
/

Passen wir im nächsten Schritt die Wordlist des Index an. Dies könnte Online mit Unterstützung des Shadow Index durchgeführt (siehe Blog) werden.

execute ctx_ddl.recreate_index_online('IDX_TEXT','REPLACE WORDLIST my_wordlist');

Nun erhalten wir das erwartete Ergebnis.

SQL> SELECT * FROM texttabelle WHERE contains (dokument,'%gewinn%')>0

        ID DOKUMENT
---------- --------------------------------------------------
         2 A-Partei gewinnt Wahl in Hansestadt
         4 Wirtschaft: Erneuter Gewinnzuwachs in diesem Jahr

Je nach Release der Datenbank gibt es unterschiedliche Default-Werte für das WILDCARD_MAXTERMS Attribut. Prüfen lässt sich dies mit folgender Abfrage:

SQL> SELECT oat_default, oat_min, oat_max  
FROM ctx_object_attributes 
WHERE oat_attribute LIKE '%MAXTERM%'

OAT_DEFAULT             OAT_MIN OAT_MAX
-------------------- ---------- ----------------------------------------
20000                         0 50000

Der aktuelle Wert lässt sich über folgende Abfrage ermitteln:

SQL> SELECT * FROM ctx_user_preference_values;

PRV_PREFERENCE                 PRV_ATTRIBUTE                  PRV_VALUE
------------------------------ ------------------------------ ----------
MY_LEXER                       MIXED_CASE                     NO
MY_WORDLIST                    WILDCARD_MAXTERMS              50000

Eine Anmerkung zum Schluss: Eine Erhöhung von WILDCARD_MAXTERMS muss nicht die optimale Lösung sein. Manchmal hilft eine Erweiterung der Stoppliste oder eine Anpassung der Auswahlliste innerhalb einer Webapplikation um den Anforderungen zu genügen.

Dienstag, 16. März 2010

Arbeiten mit einem Thesaurus in Oracle TEXT

Oracle TEXT bietet Thesaurus-Unterstützung out-of-the-box an. Das bedeutet, dass man nicht nur nach einem Wort oder einer Phrase suchen kann, sondern auch nach "verwandten" Wörtern. Die Beziehungen zwischen verwandten Wörtern müssen allerdings in der Datenbank hinterlegt sein. Dieses Posting stellt kurz vor, wie man einen einfachen Thesaurus anlegt und nutzt. Oracle selbst stellt Thesauri aber nur für die englische Sprache zur Verfügung; im deutschsprachigen Bereich muss man sich diesen selbst erstellen.
Thesauri werden angelegt und verwaltet mit dem PL/SQL-Paket CTX_THES. Der folgende Aufruf legt einen neuen (noch leeren) Thesaurus an. Wie für andere administrative Aufgaben benötigt Ihr auch zum Verwalten von Thesauri die Rolle CTXAPP:
begin
  ctx_thes.create_thesaurus(
    name     => 'MEIN_THESAURUS',
    casesens => false
  );
end;
/
Nun geht es daran, Wortbeziehungen in den Thesaurus einzutragen. Dabei werden folgende Beziehungen unterstützt:
  • Narrower Term (NT): Ein Begriff wird enger gefasst. Eine solche Beziehung könnte sein Sport NT Fußball.
  • Broader Term (BT): Ein Begriff wird weiter gefasst. Das ist das Gegenteil zum Narrower Term. Eine solche Beziehung könnte sein AktieBT Wertpapier.
  • Broader Term (BT): Ein Begriff wird weiter gefasst. Das ist das Gegenteil zum Narrower Term. Eine solche Beziehung könnte sein AktieBT Wertpapier.
  • Synonym (SYN): Eine Synonymbeziehung meint, dass zwei Begriffe das gleiche bedeuten. Geld SYN Moneten ist ein Beispiel für eine solche Beziehung.
  • Übersetzungen (TR): Wie der Name schon sagt; im Thesaurus lassen sich auch Übersetzungen eines Begriffs in andere Sprachen verwalten. Ein Beispiel wäre Geld ENGLISH: Money.
Das folgende Beispiel generiert einige Beziehungen in den soeben erstellten Thesaurus:
begin
  ctx_thes.create_relation('MEIN_THESAURUS', 'Elfmeter', 'SYN', 'Strafstoß');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Schiedsrichter', 'SYN', 'Unparteiischer');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Trainer', 'SYN', 'Coach');

  ctx_thes.create_relation('MEIN_THESAURUS', 'Ballspiel', 'NT', 'Fußball');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Liga', 'NT', 'Bundesliga');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Liga', 'NT', 'Regionalliga');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Liga', 'NT', 'Champions League');

  ctx_thes.create_relation('MEIN_THESAURUS', 'Handspiel', 'BT', 'Foul');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Stürmer', 'BT', 'Spieler');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Libero', 'BT', 'Spieler');
end;
/
Nun können die Beziehungen schon getestet werden. Zunächst wollen wir prüfen, ob die Synonyme für "Trainer" funktionieren.
select ctx_thes.syn('Trainer', 'MEIN_THESAURUS') from dual;

CTX_THES.SYN('TRAINER','MEIN_THESAURUS')
---------------------------------------------
{TRAINER}|{COACH}

select ctx_thes.syn('Coach', 'MEIN_THESAURUS') from dual;

CTX_THES.SYN('COACH','MEIN_THESAURUS')
----------------------------------------------------
{COACH}|{TRAINER}
Nun geht es an einen richtigen Test. Der Thesaurus soll in einer Volltextabfrage mit CONTAINS genutzt werden. Die folgenden Anweisungen erzeugen eine Tabelle, einige Dokumente und schließlich einen Volltextindex. Thesaurus und Index sind aber voneinander unabhängig! Ein vorhandener Volltextindex muss nach Erstellen oder Ändern eines Thesaurus nicht neu gebaut werden.
create table test_thes_tab(text varchar2(4000))
/

insert into test_thes_tab values ('Der Trainer wurde entlassen');
insert into test_thes_tab values ('Der Verein war zurück in der Bundesliga');
insert into test_thes_tab values ('Der Stürmer lieferte eine Glanzleistung ab.');
insert into test_thes_tab values ('In der 15. Minute gab es einen Elfmeter');
insert into test_thes_tab values ('Der Schiedsrichter blieb hart.');

create index idx_thestest_volltext on test_thes_tab (text) 
indextype is ctxsys.context
/
Nun einige Abfragen ...
  • Die einfache Suche nach Coach bringt keine Ergebnisse:
    select text from test_thes_tab where contains(text, 'Coach') > 0;
    
    No rows selected.
    
  • Die Synonymbeziehung bringt den Erfolg:
    select text from test_thes_tab where contains(text, 'SYN(Coach, MEIN_THESAURUS)') > 0;
    
    TEXT
    --------------------------------------------------------------------------------
    Der Trainer wurde entlassen
    
  • Die Suche nach Spieler bringt keine Treffer:
    select text from test_thes_tab where contains(text, 'Spieler') > 0;
    
    Es wurden keine Zeilen ausgewählt.
    
  • Sucht man nach den Narrower Terms (hier: bis zu 10 Stufen), so findet man auch die Stürmer.
    select text from test_thes_tab where contains(text, 'NT(Spieler,10,MEIN_THESAURUS)') > 0;
    
    TEXT
    --------------------------------------------------------------------------------
    Der Stürmer lieferte eine Glanzleistung ab.
    
In diesen Beispielen wurde der Thesaurus mit MEIN_THESAURUS überall mit angegeben. Es ist jedoch auch möglich, einen Thesaurus namens DEFAULT zu erzeugen oder einen bestehenden in DEFAULT umzubenennen. Dieser wird dann immer verwendet, wenn kein Thesaurus-Name angegeben wird. Das folgende Kommando benennt den Thesaurus MEIN_THESAURUS in DEFAULT um.
begin
  ctx_thes.alter_thesaurus(
    tname   => 'MEIN_THESAURUS',
    op      => ctx_thes.op_rename, 
    operand => 'DEFAULT'
  );
end;
/
Nun muss der Thesaurus-Name nicht mehr explizit angegeben werden.
select text from test_thes_tab where contains(text, 'NT(Spieler,10)') > 0;

TEXT
--------------------------------------------------------------------------------
Der Stürmer lieferte eine Glanzleistung ab.
Wie für alles gibt es auch für Thesauri in der Datenbank Dictionary Views:
  • CTX_USER_THESAURI enthält die definierten Thesauri.
  • CTX_USER_THES_PHRASES gibt die in den Thesauri enthaltenen Begriff zurück. Die Beziehungen selbst sind darin aber nicht enthalten; diese können mit dem Paket CTX_THES und den darin enthaltenen Funktionen wie NT oder BT oder SYN ermittelt werden.
Schließlich können auch Übersetzungen im Thesaurus gespeichert werden. Hier ein Beispiel:
begin
  ctx_thes.create_relation('DEFAULT', 'Referee', 'GERMAN:', 'Schiedsrichter');
end;  
Nun kann man auch nach den Übersetzungen von Referee suchen ...
select text from test_thes_tab where contains(text, 'TR(Referee)') > 0;

TEXT
-------------------------------------------------------------------------------
Der Schiedsrichter blieb hart.
Weitere Informationen zum Thema findet Ihr hier:

Dienstag, 16. Februar 2010

Der Textindex und der Datentyp SECUREFILE

Der neue Datentyp SECUREFILE für unstrukturierter Daten und das Datenbank-Filesystem gehören zu wichtigen Neuigkeiten in Oracle 11g bzw. in Oracle 11g Release 2 sind. Der Datentyp SECUREFILE kann dabei mit allen zusätzlichen Eigenschaften wie Deduplikation, Verschlüsselung und Komprimierung ohne weitere Anpassung verwendet werden. Der folgende Beitrag zeigt einige Beispiel dazu.

Nehmen wir folgende SECURE_LOB Tabelle, die alle verfügbaren Eigenschaften eines SECUREFILE Datentyps besitzt.

SQL> select column_name, compression, in_row, securefile, deduplication, encrypt 
     from user_lobs where table_name='SECURE_LOB';

COLUMN_NAME          COMPRE IN_ SEC DEDUPLICATION   ENCR
-------------------- ------ --- --- --------------- ----
TEXT                 HIGH   YES YES LOB             YES


Hinweis: Damit fallen zusätzliche Lizenzgebühren für die zusätzliche Option Advanced Security Option (ASO) bzw. die Advanced Compression Option an. Der Erwerb einer Lizenz für ASO erlaubt dabei im Rahmen des Feature Transparent Data Encryption (TDE) neben der Verschlüsselung von Benutzerdaten bspw. auch die Verschlüsselung des kompletten Datenverkehrs von und zur Datenbank. Mehr dazu im Tipp der DBADMIN Community unter Daten verschlüsseln mit Transparent Data Encryption (TDE)). Die Advanced Compression Option stellt bei unstrukturierten Daten sicher, dass keine Duplizierung der Daten erfolgt und die Daten komprimiert abgespeichert vorliegen können. Generell zum Umgang mit SecureFiles und Compression gibt der Tipp LOB-Management in 11g: Einstieg in die Nutzung von SECUREFILEs) eine Einführung.
Nach dem Anlegen eines Textindex auf die Spalte TEXT der Tabelle SECURE_LOB, fragen wir auf Text-Snippets der Tabelle SECURE_LOB ab:

SQL> set pagesize 100
SQL> set define '^'
SQL> prompt

SQL> accept SUCHE       prompt '>> Suchwort:     '
>> Suchwort:     Polizei AND Helfer
SQL> execute ctx_doc.set_key_type('ROWID');

PL/SQL procedure successfully completed.

SQL> select regexp_replace(ctx_doc.snippet(index_name=>'IDX_VOLLTEXT', textkey=>rowid, text_query=> '^SUCHE',starttag=>'###',endtag =>'###'),'\s+',' ') from SECURE_LOB where contains (text,'^SUCHE')>0 and rownum=1;

old   1: select regexp_replace(ctx_doc.snippet(index_name=>'IDX_VOLLTEXT', textkey=>rowid, text_query=> '^SUCHE',starttag=>'###',endtag =>'###'),'\s+',' ') from SECURE_LOB where contains (text,'^SUCHE')>0 and rownum=1
new   1: select regexp_replace(ctx_doc.snippet(index_name=>'IDX_VOLLTEXT', textkey=>rowid, text_query=> 'Polizei AND Helfer',starttag=>'###',endtag =>'###'),'\s+',' ') from SECURE_LOB where contains (text,'Polizei AND Helfer')>0 and rownum=1

REGEXP_REPLACE(CTX_DOC.SNIPPET(INDEX_NAME=>'IDX_VOLLTEXT',TEXTKEY=>ROWID,TEXT_QU
--------------------------------------------------------------------------------
Auch die ###Helfer### brauchen Hilfe Utl: Dramatische Einsatze...mit seel
ischer Not - ###Polizei### setzt auf Kirche Von

Sie finden mehr zur Verwendung von CTX_DOC.SNIPPET im Keyword in Kontext-Blog. Die Funktion für reguläre Ausdrücke REGEXP_REPLACE wurde dabei verwendet, um in SQL*Plus das Ausgabeformat zu optimieren und die überflüssigen Leerzeichen zu entfernen.

Das Gleiche lässt sich auch mit dem neuen Datenbank-Filesystem realisieren. Das Standard-Datenbank-Filesystem stellt eine vorstrukturierte Tabelle T_... mit Blob-Spalte FILEDATA zur Verfügung gestellt. Diese kann mit Text indiziert werden und wie oben abgfragt werden. Das folgende Beispiel zeigt eine solche Tabelle mit Namen T_DBFS1 und die möglichen Eigenschaften der BLOB Spalte.

SQL> select column_name, compression, in_row, securefile, deduplication, encrypt
     from user_lobs where table_name like 'T_%';

COLUMN_NAME          COMPRE IN_ SEC DEDUPLICATION   ENCR
-------------------- ------ --- --- --------------- ----
FILEDATA             NO     YES YES NO              NO

Hinweis: Um ein Standard-Datenbank-Filesystem zur Verfügung zu stellen, sind nur wenige Arbeitsschritte nötig. Literatur dazu findet sich in Oracle® Database SecureFiles and Large Objects Developer's Guide 11g Release 2 (11.2) im Kapitel 6.

Montag, 18. Januar 2010

Abfragen vordefinieren: Stored Query Expressions (SQE)

Eine wenig bis gar nicht bekannte Eigenschaft von Oracle TEXT sind die Stored Query Expressions (SQE). Damit können bestimmte TEXT-Abfragen quasi im Vorfeld unter einem Begriff gespeichert und anschließend von allen Nutzern verwendet werden ... Ein einfaches Beispiel anhand des Oracle TEXT Handbuchs ...
begin
  ctx_query.store_sqe('textdebug_cczarski', 'trace or log or logging or ctx_log');
end;
Von nun an kann man diesen Ausdruck wie ein normales Wort in CONTAINS-Abfragen verwenden ...
SQL> select id from dokument_tab where contains(content, 'sqe(textdebug_cczarski)') > 0;

        ID
----------
         1
Wendet man das in einem früheren Blog-Posting vorgestellte CTX_QUERY.EXPLAIN an, so kann man sich die Vorgehensweise von Oracle TEXT näher ansehen ...
ID         OPERATION       OPTIO OBJECT_NAME       POSITION
---------- --------------- ----- --------------- ----------
    1      OR                                             1
      2    WORD                  trace                    1
      3    WORD                  log                      2
      4    WORD                  logging                  3
      5    WORD                  ctx_log                  4
Man sieht von der SQE eigentlich gar nichts mehr - Oracle TEXT löst diese einfach transparent auf. SQE's sind insbesondere hilfreich, wenn es darum geht, schwierige CONTAINS-Abfragen (deren Ausarbeitung viel Arbeit war) für andere einfach nutztbar zu machen.

Mittwoch, 2. Dezember 2009

Der CTXCAT-Index

Neben dem "normalen" Context Index gibt es schon seit geraumer Zeit den kaum bekannten "kleinen Bruder" CTXCAT. Dieses Posting möchte ich daher gerne dem CTXCAT-Index widmen. Möchte man die Unterschiede in einem Satz herausarbeiten, so bietet der CTXCAT-Index zunächst mal weniger Features, ist für kleinere Dokumente gedacht und arbeitet synchron, ist also einfacher zu verwalten. Im einzelnen ...
  • Der CTXCAT Index ist für kleinere Textfragmente gedacht - in der Dokumentation ist von "wenigen Zeilen" die Rede; um das auf den Punkt zu bringen; das Maximum sollten so 50 Worte sein ...
  • Der Index arbeitet komplett synchron - per DML gemachte Änderungen sind sofort im Index sichtbar. Der Prozess des Index-Synchronisierens und -Optimierens fällt hier also weg.
  • Es werden weniger "Features" unterstützt - so gibt es keine Data Stores wie beim CONTEXT-Index. Die zu indizierenden Daten müssen also genau so in der Tabellenspalte drinstehen.
  • XML wird nicht unterstützt, da CTXCAT keine Section Groups kennt
  • CTXCAT unterstützt allerdings sog. Sub-Indexes, mit denen man Mixed Queries unterstützen kann. Eine Mixed Query ist eine Abfrage, die sowohl relationale (strukturierte) als auch Volltextkriterien enthält.
Der CTXCAT-Index wird genauso angelegt wie ein CONTEXT-Index, nur mit einem anderen Indextypen ... Als Beispiel nehmen wir eine Art Umfragetabelle, in der die Ergebnisse einer Umfrage zur "Servicequalität" gespeichert werden ...
create table umfrageergebnis(
  id        number(10),
  altersang number(3),
  note      number(1),
  kommentar varchar2(1000)
);
Der Index kann sofort erzeugt werden ...
create index suche_idx on UMFRAGEERGEBNIS(KOMMENTAR)
indextype is ctxsys.ctxcat;
Wie der CONTEXT-Index unterstützt auch der CTXCAT-Index linguistische Features. Das folgende Beispiel legt den Index explizit als Case-Sensitiv fest und definiert den Bindestrich als sog. Printjoin.
begin
  ctx_ddl.create_preference('CAT_LEXER','BASIC_LEXER');
  ctx_ddl.set_attribute('CAT_LEXER','MIXED_CASE','YES');
  ctx_ddl.set_attribute('CAT_LEXER','PRINTJOINS', '-');
end;
/

create index suche_idx on UMFRAGEERGEBNIS(KOMMENTAR)
indextype is ctxsys.ctxcat
parameters ('LEXER CAT_LEXER');
Stopwortlisten werden natürlich analog unterstützt. Nun kann man ein paar Dokumente einpflegen ...
insert into umfrageergebnis values (1, 35, 1, 'service war ausgezeichnet');
insert into umfrageergebnis values (2, 55, 3, 'ganz gut');
insert into umfrageergebnis values (3, 45, 5, 'ich wurde unfreundlich bedient');
insert into umfrageergebnis values (4, 42, 2, 'etwas unfreundlich, aber fehlerfrei und korrekt');
Und man kann sofort abfragen ...
select * from umfrageergebnis where catsearch(kommentar, 'unfreundlich', null) > 0;

        ID  ALTERSANG       NOTE KOMMENTAR
---------- ---------- ---------- ------------------------------------------------
         3         45          5 ich wurde unfreundlich bedient
         4         42          2 etwas unfreundlich, aber fehlerfrei und korrekt
Interessant wären nun die Mixed Queries. Angenommen, wir interessieren uns für die Ergebnisse, die aufgrund von "Unfreundlichkeit" schlecht waren. Also brauchen wir noch ein relationales Kriterium auf der Schulnote. Zunächst also den Index wieder droppen ...
drop index suche_idx;
Und nun erzeugen wir ein Index Set; dieses enthält die strukturierten "Unter-Indizes". Dem Index-Set wird dann ein Index auf die Spalten "Note", "Alter" und auf die Kombination "Note und Alter" hinzugefügt.
begin
  ctx_ddl.create_index_set('umfrage_iset');
  ctx_ddl.add_index('umfrage_iset','note'); 
  ctx_ddl.add_index('umfrage_iset','altersang'); 
  ctx_ddl.add_index('umfrage_iset','note, altersang');
end;
Danach den Index neu anlegen ...
create index suche_idx on UMFRAGEERGEBNIS(KOMMENTAR)
indextype is ctxsys.ctxcat
parameters ('LEXER CAT_LEXER INDEX SET UMFRAGE_ISET');
Nun wieder abfragen ... mit dem CATSEARCH-Operator ...
select * from umfrageergebnis where catsearch(kommentar, 'unfreundlich', 'note >= 4') > 0;

        ID  ALTERSANG       NOTE KOMMENTAR
---------- ---------- ---------- ------------------------------------------------
         3         45          5 ich wurde unfreundlich bedient
Schauen wir uns mal den Index genauer an. Wie der CONTEXT-Index besteht auch der CTXCAT-Index aus Tabellen, die ins Schema gelegt werden, und die mit dem Präfix DR$ beginnen. Nur ist es beim CTXCAT-Index genau eine-Tabelle ...
SQL> desc DR$SUCHE_IDX$I
 Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------

 DR$TOKEN                                  NOT NULL VARCHAR2(64)
 DR$TOKEN_TYPE                             NOT NULL NUMBER(3)
 DR$ROWID                                  NOT NULL ROWID
 DR$TOKEN_INFO                             NOT NULL RAW(2000)
 ALTERSANG                                 NOT NULL NUMBER(3)
 NOTE                                      NOT NULL NUMBER(1)
Und hier seht Ihr, dass der CTXCAT-Index wesentlich simpler aufgebaut ist als der CONTEXT-Index. So werden ROWIDs direkt verwendet (der CONTEXT-Index generiert die kompakteren DOCIDs). Die soeben erzeugten "Sub-Indexes" führen dazu, dass zusätzliche Spalten angelegt werden. Das alles führt dazu, dass CTXCAT-Indizes recht viel Platz verbrauchen - die ROWIDs belegen 10 Byte und auch die Werte der zusätzlichen Spalten werden für jedes Wort (Token) dupliziert. Es ist einleuchtend, warum dieser Index nur für kleine Textfragmente geeignet ist - bei großen Dokumenten würde er schlicht explodieren ...
Hierzu ein kleiner Test. Wir haben eine Tabelle mit ca. 300.000 kleineren Texten (ein CTXCAT-Index kommt also in Frage). Die Tabelle ist (festgestellt mit diesem Skript) ca. 30MB groß.
select * from table(get_space_info('TEST_CTXCAT'));

SEGMENT_NAME                     COLUMN_NAME                    PARTITION_NAME                   SEGMENT_TYPE                     ALLOC_BYTES FREE_BYTES
-------------------------------- ------------------------------ -------------------------------- -------------------------------- ----------- ----------
TEST_CTXCAT                                                                                      TABLE                               31457280      65536

1 Zeile wurde ausgewählt.
Auf diese Tabelle legen wir nun einen CTXCAT-Index und einen CONTEXT-Index und stellen auch hierfür die jeweilige Größe fest. Zunächst der CTXCAT-Index (er besteht nur aus einer einzigen "DR$"-Tabelle.
select
  segment_name, 
  column_name, 
  segment_type, 
  alloc_bytes / 1048576 alloc_mb, 
  free_bytes / 1048576 free_mb 
from table(get_space_info('DR$IDX_TEST_CTXCAT$I'))

SEGMENT_NAME                     COLUMN_NAME                              SEGMENT_TYPE                     ALLOC_MB  FREE_MB
-------------------------------- ---------------------------------------- -------------------------------- -------- --------
DR$IDX_TEST_CTXCAT$I                                                      TABLE                               80,00     0,00
DR$IDX_TEST_CTXCAT$R             DR$ROWID                                 INDEX                               62,00     0,56
DR$IDX_TEST_CTXCAT$X             DR$TOKEN,DR$TOKEN_TYPE,DR$ROWID          INDEX                               59,00     0,14
Das wären also ca. 200MB für den CTXCAT-Index. Schauen wir uns den CONTEXT-Index an. Hierfür muss die GET_SPACE_INFO-Funktion für alle zum CONTEXT-Index gehörenden DR$-Tabellen ausgeführt werden - das wären die $I, die $N, die $R und die $K-Tabelle.
TABLE SEGMENT_NAME                     COLUMN_NAME                              SEGMENT_TYPE                     ALLOC_MB  FREE_MB
----- -------------------------------- ---------------------------------------- -------------------------------- -------- --------
$I    DR$IDX_TEST_CONTEXT$I                                                     TABLE                               11,00     0,95  
$I    SYS_LOB0000225699C00006$$        TOKEN_INFO                               LOBSEGMENT                           0,06     0,00
$I    DR$IDX_TEST_CONTEXT$X            TOKEN_TEXT,TOKEN_TYPE,TOKEN_FIRST, ...   INDEX                                0,19     0,03
$I    SYS_IL0000225699C00006$$                                                  LOBINDEX                             0,06     0,03

$N    SYS_IOT_TOP_225707               NLT_DOCID                                INDEX                                0,06     0,03

$R    DR$IDX_TEST_CONTEXT$R                                                     TABLE                                0,06     0,00
$R    SYS_LOB0000225704C00002$$        DATA                                     LOBSEGMENT                           6,00     0,13
$R    SYS_IL0000225704C00002$$                                                  LOBINDEX                             0,06     0,00

$K    SYS_IOT_TOP_225702               TEXTKEY                                  INDEX                                9,00     0,59
Zusammengezählt in etwa 25MB. Alle linguistischen Einstellungen Stopwortlisten, Lexer sind identisch. Man sieht hier sehr deutlich den Unterschied.
Wann ist also ein CTXCAT-Index sinnvoll ... ? Naja, vor allem dann, wenn Ihr kleine Datenmengen in der Tabellenspalte habt, auf einen ständig synchronen Volltextindex Wert legt und Mixed Queries benötigt. Letzteres ist ab Oracle11g allerdings auch mit dem CONTEXT Index möglich (Composite Domain Index). Wenn der Text aber in mehreren Spalten ist oder die Textfragmente größer werden, dann werden CTXCAT-Indizes schnell zu groß. Ein CONTEXT-Index mit Multi-Column Datastore wesentlich kompakter und effizienter. Der CTXCAT-Index ist halt, wie eingangs gesagt, der "kleine Bruder" des CONTEXT Index und eignet sich nur für bestimmte Fälle ...

Beliebte Postings