Freitag, 9. September 2011

Mehrere Sprachen in einer Tabelle unterstützen: MULTI_LEXER

Die Lexer Einstellungen beeinflussen die Art der Speicherung von Texten und den Zugriff auf den Textindex. Stand Datenbank Version 11g gibt es unterschiedliche Lexer-Typen wie zum Beispiel BASIC_LEXER, MULTI_LEXER und WORLD_LEXER. Die Definitionen zu den unterschiedlichen Lexer-Typen sind in Kurzfassung in der Tabelle zu finden.
Wir wollen uns im folgenden Blogeintrag mit dem MULTI_LEXER beschäftigen. Der MULTI_LEXER ist eine Art "Container" für verschiedene Sub-Lexer. Die Nutzung kann in multi-lingualen Umgebungen sinnvoll sein. Im Gegensatz zum WORLD_LEXER erfordert der MULTI_LEXER allerdings eine zusätzliche Language Spalte. Dies bedeutet die Tabellen müssen als zusätzliche Metadateninformation eine Spalte mit der Sprachzugehörigkeit beinhalten.

Nehmen wir als Beispiel eine Tabelle, die nicht nur deutsche sondern auch englische Dokumente abspeichert. Das unterschiedliche Vorkommen wird mit einem Eintrag in einer Language Spalte SPRACHE dokumentiert. Es sollen dabei unterschiedliche Spracheigenschaften wie zum Beispiel Groß-Kleinschreibung und unterschiedliche Stopplisten berücksichtigt werden.
Dazu definieren wir folgende Präferenzen für einen deutschen und einen englischen Lexer.
execute ctx_ddl.drop_preference('global_lexer'); 
execute ctx_ddl.drop_preference('english_lexer');
execute ctx_ddl.drop_preference('german_lexer');

begin
ctx_ddl.create_preference('english_lexer', 'basic_lexer'); 
ctx_ddl.set_attribute('english_lexer','SKIPJOINS','_');
ctx_ddl.set_attribute('english_lexer','mixed_case','no'); 

ctx_ddl.create_preference('german_lexer', 'basic_lexer');
ctx_ddl.set_attribute('german_lexer','mixed_case','yes'); 
ctx_ddl.set_attribute('german_lexer','composite','german'); 
ctx_ddl.set_attribute('german_lexer','alternate_spelling','german');
end;
/
Zusätzlich werden nun auch sprach-spezifische Stopplisten erzeugt. Beim Anlegen des Index, wird je nach Dokumentsprache, das entsprechende Stoppwort ausgesondert.
execute ctx_ddl.drop_stoplist('mymulti_stoplist');

begin
ctx_ddl.create_stoplist('mymulti_stoplist', 'MULTI_STOPLIST');
ctx_ddl.add_stopword('mymulti_stoplist', 'den','german');
ctx_ddl.add_stopword('mymulti_stoplist', 'this','english');
end;
/
Nun definieren wir die MULTI_LEXER Präferenz.
execute ctx_ddl.create_preference('global_lexer','multi_lexer');
Im nächsten Schritt müssen die sprach-spezifischen Lexer mit einem ADD_SUB_LEXER Aufruf hinzugefügt werden. Wichtig ist eine Default Sprache zu definieren - in unserem Fall Deutsch.
begin
ctx_ddl.add_sub_lexer('global_lexer', 'default', 'german_lexer');  
ctx_ddl.add_sub_lexer('global_lexer', 'english','english_lexer', 'eng'); 
end;
/
Nun erzeugen wir die Tabelle mit der zusätzlichen Language Spalte SPRACHE und fügen einige Einträge hinzu.
DROP TABLE globaldoc PURGE;

CREATE TABLE globaldoc ( 
   doc_id       NUMBER, 
   sprache      VARCHAR2(30), 
   text         CLOB); 

INSERT INTO globaldoc values (10,'english','this is not America');
INSERT INTO globaldoc values (12,'english','this is user_110');
INSERT INTO globaldoc values (13,'english','this or that');
INSERT INTO globaldoc values (1,'deutsch','Wer den Pfennig nicht ehrt');
INSERT INTO globaldoc values (2,'deutsch','das ist user_111');
INSERT INTO globaldoc values (5,'deutsch','München');
INSERT INTO globaldoc values (6,'deutsch','Muenchen');
COMMIT;
Nun wird der Index erzeugt.
DROP INDEX globalx FORCE;

CREATE INDEX globalx ON globaldoc (text) 
  indextype is ctxsys.context 
  parameters ('lexer global_lexer 
               language column sprache 
               stoplist mymulti_stoplist'); 
Um mehr über den Index zu erfahren, listen wir die Tokenliste auf. Der Eintrag USER110 ist zu finden, allerdings nicht USER111. Das ist ein Beispiel für die sprach-spezifische Präferenz SKIPJOINS.
SQL> SELECT token_text FROM dr$globalx$i;
TOKEN_TEXT
----------------------------------------------------------------
111
AMERICA
IS
Muenchen
München
NOT
OR
Pfennig
THAT
USER110
Wer
das
den
ehrt
ist
nicht
user
Zum Abfragezeitpunkt untersucht der MULTI_LEXER die Sessionsprache und nutzt die entsprechende Sub-Lexer Präferenz und die aktive stoplist zum Parsen der Abfrage. Falls die Sprache nicht gesetzt ist, wird der Default Lexer verwendet. Um die Abfrage auf eine bestimmte Sprache einzuschränken, kann eine zusätzliche Abfrage-Erweiterung auf die Language Spalte SPRACHE sinnvoll sein.
Folgende Abfragen dokumentieren das Verhalten.
ALTER SESSION SET NLS_LANGUAGE='GERMAN';

-- wegen MIXED_CASE
-- SElECT * FROM globaldoc WHERE contains(text, 'Pfennig')>0;
-- oder
SELECT * FROM globaldoc WHERE contains(text, 'Pfennig')>0 AND sprache='deutsch';
    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
         1 deutsch         Wer den Pfennig nicht ehrt

SELECT * FROM globaldoc WHERE contains(text, 'PFENNIG')>0;
no rows selected

SELECT * FROM globaldoc WHERE contains(text, 'pfennig')>0;
no rows selected

-- wegen alternate_spelling
SELECT * FROM globaldoc WHERE contains(text, 'Muenchen')>0;

    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
         5 deutsch         München
         6 deutsch         Muenchen

-- nun englische Dokumente
ALTER SESSION SET NLS_LANGUAGE=ENGLISH;

-- wegen MIXED_CASE
SELECT * FROM globaldoc WHERE contains(text, 'AMERICA')>0;
    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
        10 english         this is not America

SELECT * FROM globaldoc WHERE contains(text, 'america')>0;
    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
        10 english         this is not America

SELECT * FROM globaldoc WHERE contains(text, 'America')>0;
    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
        10 english         this is not America

-- wegen SKIPJOINS und stoplist mit 'this'
SELECT * FROM globaldoc WHERE contains(text, 'user110 or this')>0 AND sprache='English';

    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
        12 english         this is user_110


SELECT * FROM globaldoc WHERE contains(text, 'user110 or that')>0 AND sprache='English';

    DOC_ID SPRACHE         TEXT
---------- --------------- -----------------------------------
        12 english         this is user_110
        13 english         this or that

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

Beliebte Postings