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