Dienstag, 26. Juli 2011

Webseminar: Oracle TEXT und APEX

Das Thema Suchen ist in Geschäftsanwendungen allgegenwärtig. Seien es Kunden, Produkte, Lieferanten oder Geschäftsstellen - immer wieder muss man "etwas finden". Jeder Leser dieses Blogs weiss, dass Oracle TEXT hierfür genau richtig ist.
Doch wie bringt man Oracle TEXT möglichst schnell und einfach zum Anwender? Schließlich arbeitet kein Endanwender direkt mit SQL*Plus. Hier bietet sich Application Express an - damit lassen sich schnell und einfach Webanwendungen bauen.
Wie man Oracle TEXT und APEX gemeinsam nutzt, dazu gibt es ein Webseminar am 11. August 2011 um 11:00 Uhr an. Alles, was man braucht, ist ein Browser, ein Telefon für die Audiokonferenz (Gebührenfrei) und eine Stunde Zeit. Die Teilnahme ist kostenlos - am besten gleich anmelden.

Dienstag, 31. Mai 2011

Unscharfe Namenssuche (Name Search) mit NDATA

Vor einiger Zeit als das Feature Name Search mit 11.2.0.2 zur Verfügung gestellt wurde, haben wir schon eine Einführung in das Thema gegeben. Der Blogeintrag dazu ist hier zu finden.
Da mittlerweile einige Migrationen nach 11.2 durchgeführt wurden, wollen wir das Thema noch einmal aufgreifen und einige zusätzliche Informationen dazu geben. Generell ist das Name Searching Feature mit dem neuen Operator NDATA zu verwenden und hilft eine unscharfe Suche durchzuführen. Ich verwende dazu in folgendem Beispiel eine Tabelle mit ca 200 000 Sätzen und ca 8200 verschiedenen Einträgen in einer Spalte ORT. Um Name Searching zu verwenden, benötigen wir einen XML Tag, den ich mit einem MULTICOLUMN DATASTORE wie folgt zur Verfügung stelle.
exec ctx_ddl.drop_preference('name_ds')
begin
  ctx_ddl.create_preference('name_ds', 'MULTI_COLUMN_DATASTORE');
  ctx_ddl.set_attribute('name_ds', 'COLUMNS', 'ort');
end;
/
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', 'ort', 'ort');
end;
/
Zusätzlich kann auch ALTERNATE SPELLING und BASE LETTER mit NDATA verwendet werden. Hierzu erzeugen wir folgende BASIC_WORDLIST.
exec ctx_ddl.drop_preference('name_wl');
begin
ctx_ddl.create_preference('name_wl', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('name_wl', 'NDATA_ALTERNATE_SPELLING', 'TRUE');
ctx_ddl.set_attribute('name_wl', 'NDATA_BASE_LETTER', 'TRUE');
end;
/
Nun kann der Index erzeugt werden.
CREATE INDEX ns_ort ON basic_lob (ort)
INDEXTYPE IS ctxsys.context
PARAMETERS ('sync (on commit) datastore name_ds 
section group name_sg wordlist name_wl');
SELECT * FROM ctx_index_errors;
Im ersten Versuch suchen wir den Ort "Vilshofen" in Niederbayern - die Schreibweise ist dabei etwas ungewöhnlich. Der Ort wird allerdings schon bei der ersten Abfrage gefunden.
 
SQL> SELECT distinct ort, score(1) FROM basic_lob
     WHERE contains(ort, 'NDATA(ort,Filschthofen)',1) > 0 
     AND score(1)>40;
ORT                                        SCORE(1)
---------------------------------------- ----------
Vilshofen                                        68
Osthofen                                         45
Nun suchen wir einen Ort in Kalifornien, der mit "Santa" beginnt - auch hier mit etwas unüblicher Schreibweise. Zusätzlich können weitere Argumente mitgegeben werden. Das Argument "Proximity" zum Beispiel gibt an, ob die Ähnlichkeit des Suchbegriffs zum tatsächlichen Namen den Score beeinflussen soll.
  
SQL> SELECT distinct ort, score(1) FROM basic_lob
     WHERE contains(ort, 'NDATA(ort,santa kallifornia,, proximity)',1) > 0 
     AND score(1)>40 ORDER BY 2;
ORT                                        SCORE(1)
---------------------------------------- ----------
Santa Clara/Kalifornien                          52
Staat Kalifornien                                52
Santa Maria                                      57
Santa Monica                                     57
santa clara1 california                          61
Santa California                                 83
Santa Kalifornia                                 96

SQL> SELECT distinct ort, score(1) FROM basic_lob 
     WHERE contains(ort, 'NDATA(ort,santa kallifornia)',1) > 0 
     AND score(1)>40 ORDER BY 2;
ORT                                        SCORE(1)
---------------------------------------- ----------
Santa Clara/Kalifornien                          52
Staat Kalifornien                                52
Santa Maria                                      57
Santa Monica                                     57
Santa California                                 83
santa clara1 california                          83
Santa Kalifornia                                 96
Was bedeutet das nun für unseren Index? Schauen wir uns noch kurz die Statistiken an. Klar wird dabei, dass diese umfangreiche Suche auch mit entsprechenden Ressourcen verbunden ist. Die Anzahl der Tokens ist beispielsweise höher als beim Index ohne NDATA. Der nächste Ausschnitt gibt Aufschluss über die Statistik der Tokens. Informationen über die Vorgehensweise findet sich auch in folgendem Blogeintrag.
DROP TABLE ausgabe;
CREATE TABLE ausgabe (resultat CLOB);
declare
    ergebnis clob := null;
  begin
    ctx_report.index_stats(index_name=>'NS_ORT',report=>ergebnis,stat_type=>null);
    insert into ausgabe values (ergebnis);
    commit;
    dbms_lob.freetemporary(ergebnis);
end;
/
set long 32000
set head off
set pagesize 10000
SELECT * FROM ausgabe;
Folgende Ausgabe liefert diese Abfrage:
                       STATISTICS FOR "US"."NS_ORT"
===========================================================================
indexed documents:                                                200,003
allocated docids:                                                 200,003
$I rows:                                                          170,909
---------------------------------------------------------------------------
                             TOKEN STATISTICS
---------------------------------------------------------------------------
unique tokens:                                                     66,955
average $I rows per token:                                           2.55
...
token statistics by type:
  token type:                                             200:NDATA "ORT"
    unique tokens:                                                 66,955
    total rows:                                                   170,909
    average rows:                                                    2.55
    total size:                                     26,653,305 (25.42 MB)
    average size:                                                     398
    average frequency:                                              61.15
Vergleicht man dies mit einer Context Index ohne die Verwendung von NDATA kommt man in unserem Beispiel nur auf ca 6100 eindeutige Tokens und eine Größe von 1,4 MB. Bevor man Name Search verwendet, kann man auch alternativ den FUZZY Operator ausprobieren. Die vollständige Syntax in folgendem Blogeintrag zeigt, wie umfangreich auch mit FUZZY gesucht werden kann.

Freitag, 25. März 2011

Mehr Performance durch Index-Preloading

Wie kann man die Performance von Zugriffen auf den Textindex erhöhen? Eine Möglichkeit besteht darin, Tabellen und Indizes im Cache zu halten, damit möglichst wenig I/O durchgeführt wird. Roger Ford, Development Manager für Oracle Text, hat zu diesem Thema schon vor längerer Zeit einen Artikel auf OTN verfasst. Da das Thema nicht an Relevanz verloren hat, wollen wir auch hier in unserem Blog das Thema besprechen.
Die Technik, die man zum optimierten Speichern von Segmenten im Cache verwendet, ist die Nutzung von unterschiedlichen Pools im Datenbank Cache. Es ist zum Beispiel sinnvoll, Objekte, auf die sehr häufig zugegriffen wird (z.B. Lookup-Tabellen), dem sogenannten KEEP Pool Cache zuzuordnen. Dabei ist der KEEP Cache ein eigener Bereich im Datenbank Cache, der mit dem Parameter DB_KEEP_CACHE_SIZE konfiguriert wird. Standardmässig ist der Wert auf 0 gesetzt.
Drei Schritte sind zur Nutzung des KEEP Pools nötig:
  • die Bestimmung der Größe des KEEP Pools und Setzen von DB_KEEP_CACHE_SIZE
  • das Setzen der KEEP Storage Option im Index- und Tabellen-Segment
  • das Laden der Objekte in den Cache mit den entsprechenden SQL-Kommandos

Um ein Gefühl für die Größeneinstellung des Pools zu erhalten, kann man sich zuerst die Größe des Index ansehen. Dazu eignet sich die Funktion CTX_REPORT, wie folgt:
SET long 10000
SELECT ctx_report.index_size('TXT_IDX') FROM dual;

TOTALS FOR INDEX US.TXT_IDX
--------------------------------------------------------------------------
CTX_REPORT.INDEX_SIZE('TXT_IDX')
--------------------------------------------------------------------------
TOTAL BLOCKS ALLOCATED:                                              51496
TOTAL BLOCKS USED:                                                   51167
TOTAL BYTES ALLOCATED:                             421,855,232 (402.31 MB)
TOTAL BYTES USED:                                  419,160,064 (399.74 MB)
In unserem Beispiel ist der Index insgesamt 400 MB groß. Nach einigen typischen Abfragen können wir überprüfen, welche Objekte mit wievielen Blöcken sich im Cache befinden. Eine Abfrage auf die V$BH und DBA_OBJECTS Tabelle listet die Objekte auf, die sich momentan im Cache befinden. Die Abfrage sieht dann folgendermassen aus:
COLUMN OWNER FORMAT A10
COLUMN OBJECT_NAME FORMAT A25
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999

SELECT o.object_name, o.owner, o.object_type, COUNT(*) NUMBER_OF_BLOCKS
FROM dba_objects o, v$bh bh
WHERE o.data_object_id = bh.objd AND o.owner ='US'
GROUP BY o.object_name, o.owner, o.object_type
ORDER BY COUNT(*);

OBJECT_NAME               OWNER      OBJECT_TYPE         NUMBER_OF_BLOCKS
------------------------- ---------- ------------------- ----------------
DR$TXT_IDX$R              US         TABLE                              7
SYS_LOB0000143350C00002$$ US         LOB                              345
DR$TXT_IDX$X              US         INDEX                            440
DR$TXT_IDX$I              US         TABLE                            495
BASIC_LOB                 US         TABLE                          5,634
Die Basistabelle BASIC_LOB oder Teile davon befinden sich offensichtlich im Cache. Der Textindex besteht aus mehreren Komponenten, die ebenfalls in der Abfrage aufgelistet werden: die Token Tabelle $I, der Index $X der $I Tabelle, die ROWID Tabelle $R und ein zusätzliches Lobsegment SYS_LOB0000143350C00002$$ der $R-Tabelle. Folgende Abfrage zeigt die Tabellen und die zugehörigen LOB-Segmente:
SELECT table_name, segment_name, in_row, cache FROM user_lobs;

TABLE_NAME                     SEGMENT_NAME                   IN_ CACHE
------------------------------ ------------------------------ --- ----------
BASIC_LOB                      SYS_LOB0000094513C00010$$      YES NO
DR$TXT_IDX$I                   SYS_LOB0000143761C00006$$      YES NO
DR$TXT_IDX$R                   SYS_LOB0000143766C00002$$      YES YES
...
Die Texte (hier: Spalte TEXT) der Basistabelle liegen in einem LOB-Segment. Ob LOB-Segmente generell den Buffer Cache verwenden oder nicht, hängt von dem Storage-Parameter CACHE ab. Standardmässig werden LOB-Segmente nicht in den Buffer geladen. Um dies einzustellen, ist folgendes Kommando notwendig.
ALTER TABLE BASIC_LOB MODIFY LOB(text) CACHE;
Nun stellt sich die Frage, wie man die Textkomponenten wie $I usw. und die Tabelle in den Cache laden kann. Folgende Abfragen können dabei hilfreich sein:
SELECT /*+ FULL(ITAB) */ SUM(token_count), SUM(LENGTH(token_info))
                                             FROM dr$txt_idx$i ITAB;

SELECT /*+ INDEX(ITAB) */ SUM(LENGTH(token_text))
                                             FROM dr$txt_idx$i ITAB;
SELECT SUM(row_no) FROM dr$txt_idx$r;
SELECT /*+ FULL(BTAB) */ SUM(dok_id) FROM basic_lob BTAB;
Bei den Full Table Scans (FTS) muss allerdings berücksichtigt werden, dass Oracle ein optimiertes Verfahren verwendet, um Objekte in den Cache zu laden. Falls die Größe der Tabelle 2% des Buffer Cache übersteigt - was häufig der Fall sein kann - wird über direct load gelesen und nicht über den Buffer Cache. Möchte man das Laden über den Buffer Cache erzwingen, ist der Einsatz folgendes Parameters notwendig:
ALTER SESSION SET "_small_table_threshold"=zahl;
-- wobei Zahl die Blockgröße angibt, die grösser als das Segment ist.
-- danach kann der FTS ausgeführt werden
SELECT /*+ FULL(BTAB) */ SUM(dok_id) FROM basic_lob BTAB;
Nachlesen kann man diese Information auch in der Oracle Support Note Doc ID 787373.1.
Damit die Segmente auch im KEEP Pool Cache gespeichert werden, müssen nun noch die Buffer Pool Einstellungen der Objekte verändert werden. Folgende Kommandos passen die Storage-Klausel an:
ALTER TABLE dr$txt_idx$i STORAGE (buffer_pool keep);

ALTER INDEX dr$txt_idx$x STORAGE (buffer_pool keep);

ALTER TABLE dr$txt_idx$r STORAGE (buffer_pool keep);

ALTER TABLE basic_lob STORAGE (buffer_pool keep);
Allerdings ist ein weiterer Schritt notwendig, um die Lob-Segmente der $R Tabelle im KEEP Pool zu speichern. Die LOB-Segmente der $I und $R Tabellen werden standardmässig "in-row" gespeichert. Falls die Werte kleiner als 4K sind, wird der Inhalt in der Tabelle selbst gespeichert, wächst er darüber hinaus, wird ein separates Segment angelegt. Da die LOB-Länge der Token Tabellen $I auf 4K limitiert ist, werden die TOKEN-Informationen immer im Segment selbst abgespeichert.
Ganz im Gegensatz dazu kann die Liste der ROWIDs, die in der $R Tabelle gespeichert sind, größer als 4K sein. Dann kommt es zu einer Speicherung in einem separaten Segment. Um sicherzustellen, dass die Informationen der LOBs auch im KEEP Pool gespeichert werden, sollte folgende Einstellung durchgeführt werden.
ALTER TABLE dr$txt_idx$r MODIFY LOB (data) (STORAGE (buffer_pool keep));
Mit den oben aufgeführten Abfragen lassen sich allerdings die LOB-Segmente im separat gespeicherten Segment NICHT in den Cache laden. Folgende Prozedur erledigt diese Aufgabe. Die einzelnen ROWID-Segmente, die grösser als 4K sind, werden dabei ausgelesen.
create or replace procedure loadAllDollarR (idx_name varchar2) is
  v_idx_name varchar2(30) := upper(idx_name);
  type c_type is ref cursor;
  c2 c_type;
  s varchar2(2000);
  b blob;
  buff varchar2(100);
  siz number;
  off number;
  cntr number;
begin
-- wenn Index partitioniert, dann mehr als eine Tabelle
  for c1 in (select table_name t from user_tables
             where table_name like 'DR_'v_idx_name'%$R') loop
    dbms_output.put_line('loading from table 'c1.t);
    s := 'select data from 'c1.t;
    open c2 for s;
    loop
       fetch c2 into b;
       exit when c2%notfound;
       siz := 10;
       off := 1;
       cntr := 0;
-- falls ROWIDs gespeichert sind, dann ROWID (Laenge 10) lesen und OFFSET plus4096
   if dbms_lob.getlength(b) > 4096 then
         begin
           loop
             dbms_lob.read(b, siz, off, buff);
             cntr := cntr + 1;
             off := off + 4096;
           end loop;
         exception when no_data_found then
           if cntr > 0 then
             dbms_output.put_line('4K chunks fetched: 'cntr);
           end if;
         end;
       end if;
    end loop;
  end loop;
end;
/
--Ausführung
exec LoadAllDollarR('TXT_IDX')
Zum Laden der Daten sind insgesamt folgende Abfragen notwendig:
ALTER SESSION SET "_small_table_threshold"= wert;

SELECT /*+ FULL(ITAB) */ SUM(token_count), SUM(LENGTH(token_info))
                                           FROM dr$index_name$i ITAB
SELECT /*+ INDEX(ITAB) */ SUM(LENGTH(token_text))
                                           FROM dr$index_name$i ITAB
SELECT SUM(row_no) FROM dr$index_name$r;
SELECT /*+ FULL(BTAB) */ SUM(spalte_name) FROM table_name BTAB;
exec LoadAllDollarR('index_name')
Summiert man die Anzahl der Blöcke der V$BH Tabelle auf, erhält man den Wert für die Größe von DB_KEEP_CACHE_SIZE.
Folgende Kommandos zeigt die Einstellung dazu:
-- DB_CACHE_SIZE anpassen
ALTER SYSTEM SET DB_CACHE_SIZE=wert;
-- DB_KEEP_CACHE_SIZE setzen
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=wert;
Viel Spass beim Ausprobieren...

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!

Beliebte Postings