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.

Beliebte Postings