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...

Ä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;
/

CTX_QUERY.EXPLAIN: Erklärungen für eine TEXT-Abfrage

Vor einiger Zeit hatten wir ein Blog Posting zum Thema "Fuzzy-Suche". Darin könnt Ihr nachlesen, wie eine Ähnlichkeitssuche mit Oracle TEXT funktioniert und wie Ihr sie parametrisieren könnt. Wie beschrieben, führt Oracle TEXT eine sog. Termexpansion durch. Die Suchabfrage wird also zunächst um Tokens aus dem Textindex, die dem gesuchten Begriff ähnlich sind, erweitert und anschließend wird mit dieser Token-Liste eine OR-Suche durchgeführt. Die Parameter des FUZZY-Operators steuern diese Termexpansion.

Nun wäre es schön, wenn man sich ansehen könnte, was er da tut - welche Tokens also in die Liste mit aufgenommen werden. Und genau das ist mit der Prozedur CTX_QUERY.EXPLAIN möglich (übrigens nicht nur für die Fuzzy-Suche, sondern für alle TEXT-Abfragen). Die EXPLAIN-Funktion generiert eine Art "Ausführungsplan" für den Oracle TEXT Index.

Zunächst benötigen wir eine EXPLAIN TABLE - dort schreibt die Prozedur die Erklärungen zu einer Textquery hinein. In der Oracle-Dokumentation "Text Reference" findet Ihr Erlärungen zu Aufbau und Inhalt. Erzeugt wird sie mit diesem CREATE TABLE-Kommando:

create table meine_explain_tabelle(
  explain_id  varchar2(30),
  id          number,
  parent_id   number,
  operation   varchar2(30),
  options     varchar2(30),
  object_name varchar2(64),
  position    number,
  cardinality number
);

Wenn Ihr die Tabelle erstellt habt, könnt Ihr euch eine Textquery beschreiben lassen.

begin
  ctx_query.explain(
    index_name    => 'IDX_DOKUMENTE',
    text_query    => '?sptial or geodaten',
    explain_table => 'MEINE_EXPLAIN_TABELLE'
  );
end;
/

Anschließend stehen die Erklärungen in der erzeugten EXPLAIN-Tabelle. Wichtig ist der Parameter SHARELEVEL, der in diesem Aufruf nicht angegeben wurde. Der Default ist "0" (Null), was bedeutet, dass die Tabelle vorher leergeräumt wurd (TRUNCATE). Wenn Ihr EXPLAIN-Ergebnisse aufheben möchtet, setzt den Parameter auf "1" - zusätzlich benötigt Ihr dann eine EXPLAIN_ID, damit Ihr die Erklärungen später wiederfinden könnt.

Die Einträge in der Tabelle sind hierarchisch organisiert - am besten fragt Ihr sie daher mit einem START WITH - CONNECT BY wie folgt ab.

select
  lpad(' ',level * 2)|| to_char(id, '99') id, 
  operation,
  options,
  object_name,
  position
from meine_explain_tabelle
start with parent_id =0 
connect by parent_id = prior id
/

Das Ergebnis sieht dann etwa so aus

ID         OPERATION       OPTIONS    OBJECT_NAME     POSITION 
---------- --------------- ---------- --------------- --------
    1      OR                                                1
      2    EQUIVALENCE     (?)        sptial                 1
        3  WORD                       Spatial                1
        4  WORD                       special                2
        5  WORD                       Special                3
        6  WORD                       Spezial                4
        7  WORD                       Spiel                  5
        8  WORD                       spielt                 6
        9  WORD                       spielte                7
       10  WORD                       Spitze                 8
       11  WORD                       sptial                 9
     12    WORD                       geodaten               2

Man erkennt sehr schön die ähnlichen Worte, die Oracle TEXT in die Abfrage eingebunden hat; man könnte nun, wie im Blog Posting zur "Fuzzy-Suche" beschrieben, das Schlüsselwort FUZZY nutzen und parametrisieren: Wie versuchen also die Abfrage FUZZY(sptial,70,5) or geodaten. Die in Frage kommenden Wörter müssen sich also ähnlicher sein (Wert "70" im Gegensatz zum Default von "60") und es werden maximal fünf Wörter in die Termexpansion einbezogen. Das Ergebnis ...

ID         OPERATION       OPTIONS    OBJECT_NAME     POSITION 
---------- --------------- ---------- --------------- -------- 
    1      OR                                                1
      2    EQUIVALENCE     (?)        sptial                 1
        3  WORD                       Spatial                1
        4  WORD                       sptial                 2
      5    WORD                       geodaten               2

Und wenn man nach "Spatial" im Zusammenhang mit "Geodaten" sucht, erkennt man sofort, dass diese Suche viel zielgenauer ist. Gerade wenn es um das Spielen mit den Parametern für eine FUZZY-Suche geht, ist die EXPLAIN-Funktion eine wertvolle Hilfe.

Viel Spaß beim Ausprobieren!