Montag, 20. Februar 2012

Statistiken im Oracle Text Umfeld

Spätestens seit Oracle 10g besitzt das Sammeln von Statistiken bzw. die Relevanz von korrekten Statistiken einen hohen Stellenwert und stellt einen Garant für gute Performance dar. Um das Management für das Sammeln von Statistiken zu vereinfachen, führte Oracle daher mit 10g die sogenannten Maintenance Jobs ein, die meist in einem nächtlichen Job diese Aufgabe übernehmen.

Was bedeutet dies nun für den Oracle Text Index?
Zieht man das Handbuch Oracle Text Application Developer's Guide 11g Release 2 (11.2) zu rate, findet man im Abschnitt "Tuning Oracle Text" auf die Frage, ob man Statistiken sammeln sollte oder nicht, folgende nicht eindeutige Antwort dazu:
"Yes. Collecting statistics on your tables enables Oracle Text to do cost-based analysis. This helps Oracle Text choose the most efficient execution plan for your queries. If your queries are always pure text queries (no structured predicate and no joins), you should delete statistics on your Oracle Text index."

Noch einmal zur Erinnerung, wenn wir einen Text Index (z. B. TEXT_IDX) erzeugen, werden automatisch mindestens folgende Objekte erzeugt:
  • DR$TEXT_IDX$I: Token Tabelle
  • DR$TEXT_IDX$X: Index
  • DR$TEXT_IDX$K: DOCID (nach ROWID) Mapping Tabelle
  • DR$TEXT_IDX$N: Tabelle für Negativliste für alle gelöschten DOCIDs
  • DR$TEXT_IDX$R: ROWID (nach DOCID) Mapping Tabelle
Was bedeutet dies für die einzelnen Objekte des Textindex?
  1. Sollten Statistiken auf den einzelnen DR$ Objekten existieren?
    Die Antwort darauf lautet nein! Alle internen rekursiven Abfragen haben Hints (wie zum Beispiel /*+DYNAMIC_SAMPLING(0) INDEX(T "DR$TEST_IDX$X")*/) um einen festgelegten Ausführungsplan zu verwenden. Dieser Plan hat sich als bester Plan erwiesen. Statistiken auf den Objekten könnten zu einem veränderten und somit schlechteren Plan führen.
  2. Werden Statistiken im Maintenance Job für Oracle Text mitgepflegt?
    Auch hier lautet die Antwort nein. DBMS_STATS.GATHER_SCHEMA_STATS oder DBMS_STATS.GATHER_DATABASE_STATS analysiert keine internen Texttabellen und Indizes.

Um zu verifizieren, ob Ihre Umgebung die Regel 1) erfüllt, führen Sie einfach folgenden Test durch:
SQL> select table_name,last_analyzed 
     from user_tables where table_name like '%TEST_IDX%';

TABLE_NAME                     LAST_ANAL
------------------------------ ---------
DR$TEST_IDX$I
DR$TEST_IDX$R
DR$TEST_IDX$K
DR$TEST_IDX$N
Für die Indizes ergibt sich folgende Abfrage:
SQL> select index_name, user_stats, global_stats, last_analyzed
     from user_indexes where table_name like '%TEST_IDX%';

INDEX_NAME                     USE GLO LAST_ANAL
------------------------------ --- --- ---------
SYS_IOT_TOP_223146             NO  NO
DR$TEST_IDX$X                  NO  NO  
SYS_IL0000223143C00002$$       NO  NO
SYS_IOT_TOP_223141             NO  NO
SYS_IL0000223138C00006$$       NO  NO
Haben die Einträge in der Spalte LAST_ANALYZED den Wert "null", müssen Sie nichts unternehmen. Finden Sie Statistiken auf einem dieser Objekte, löschen Sie diese wie folgendes Beispiel zeigt.
SQL> select index_name, user_stats, global_stats, last_analyzed
     from user_indexes where table_name like '%TEST_IDX%';

INDEX_NAME                     USE GLO LAST_ANAL
------------------------------ --- --- ---------
SYS_IOT_TOP_223146             NO  NO
DR$TEST_IDX$X                  NO  NO  20-FEB-12
SYS_IL0000223143C00002$$       NO  NO
SYS_IOT_TOP_223141             NO  NO
SYS_IL0000223138C00006$$       NO  NO

SQL> execute dbms_stats.delete_index_stats('US','DR$TEST_IDX$X');

--- zur Kontrolle

SQL> select index_name, user_stats, global_stats, last_analyzed
     from user_indexes where table_name like '%TEST_IDX%';

INDEX_NAME                     USE GLO LAST_ANAL
------------------------------ --- --- ---------
SYS_IOT_TOP_223146             NO  NO
DR$TEST_IDX$X                  NO  NO
SYS_IL0000223143C00002$$       NO  NO
SYS_IOT_TOP_223141             NO  NO
SYS_IL0000223138C00006$$       NO  NO
Weitere Informationen zu dem Thema finden Sie auch in der öffentlichen My Oracle Support Note DOC ID 139979.1.

Dienstag, 24. Januar 2012

Neue Oracle Text Features

Häufig werden wir nach den neuen Features im Bereich Oracle Text gefragt. Besonders wichtig wird dies, wenn eine Migration ansteht. Wichtig zu wissen ist, dass in der Regel keine Veränderungen an den Applikationen vorgenommen werden müssen, sondern die Anwendungen ohne weitere Bearbeitung übernommen werden können. Einzige Ausnahme bildet vielleicht der Wechsel in der Filternutzung. Ab 11g Release 1 ist der INSO_FILTER Typ durch den AUTO_FILTER Typ ersetzt worden. Dies wird für die meisten Text Nutzer keinen großen Unterschied darstellen. Wer sicher gehen möchte, welches die genauen Unterschiede zwischen den beiden Filtern sind, kann im Oracle Text Application Developer's Guide 11g Release 2 (11.2) Handbuch unter New Filter (INSO_FILTER versus AUTO_FILTER) nachsehen. Ausserdem kann aus Kompatibilitätsgrunden in 11g immer noch den INSO_FILTER Typ verwendet werden.
Bei der Migration möchte man allerdings nicht nur die Funktionalität und Performance der Anwendungen aufrecht erhalten, sondern auch von Vereinfachungen bzw. Erweiterungen im neuen Release profitieren. Wo findet man diese Informationen? Die beste Quelle ist das Oracle Text Reference 11g Release 2 (11.2) Handbuch. Hier findet man folgende Link-Einträge zum Thema 11gR1 und 11gR2: Im folgenden Abschnitt wollen wir die wichtigsten Features noch einmal auflisten und die entsprechenden Blog-Einträge - falls vorhanden - dazu angeben.
In 11g Release 1 sind folgende Features eingeführt worden:
  1. Zero Downtime für Textanwendungen mit neuem inkrementellen Index-Feature und Online Index Creation bzw. Re-creation
  2. NOPOPULATE Option für ALTER INDEX Kommando zur Unterstützung der inkrementellen Indizierung
  3. Neue Oracle Text Manager Features im Oracle Enterprise Manager
  4. Support für Composite Domain Indextyp um die mixed-query Performance zu erhöhen
  5. SDATA Section Typ und SDATA Operator für die Bereichssuche von Metadata
  6. User definiertes Scoring Feature mit DEFINESCORE und DEFINEMERGE Operatoren
  7. Detailliertes Database Usage Tracking Feature für Oracle Text
In 11g Release 2 finden sich folgende Erweiterungen:
  1. Name Searching: 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. 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. Hier könnte das Name earching Feature weiterhelfen. Die Blogeinträge zu dem Thema finden sich unter
  2. Result set interface: 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.

Montag, 21. November 2011

Tablespace und mehr: Storage-Klausel für den Oracle-TEXT Index

Nachdem uns jüngst die Frage erreichte, wie man den Tablespace für einen Oracle TEXT-Index festlegen
kann ... machen wir dazu gleich ein Posting: Im Gegensatz zu einem normalen Textindex kann man nicht
einfach eine TABLESPACE-Klausel an das CREATE INDEX-Kommando anhängen. Das folgende funktioniert also
nicht.
create index ft_dokument on dokumente_tab(dokument)
indextype is mdsys.context
tablespace TS_IDXDATA
/
Bei Oracle TEXT ist hier etwas mehr zu tun. Oracle TEXT erlaubt unglaublich viel Flexibilität, was
auf der anderen Seite natürlich immer Konfigurationsaufwand bedeutet. Aber zum Thema: Ein Oracle TEXT Index
besteht intern stets aus mindestens vier Tabellen: $I, $R, $K und $N. Je nachdem, welche zusätzlichen
Features aktiviert wurden, kommen noch Tabellen dazu. Und jede dieser Tabellen kann ihre eigene
Storage-Klausel bekommen. Wie immer bei Oracle TEXT wird auch hier mit den Preference-Objekten
gearbeitet - diese physikalischen Details werden in eine Storage Preference gespeichert (nicht zu
verwechseln mit der Datastore Preference, die beschreibt, wo die zu indizierenden Daten herkommen). Wir
wollen also unseren TEXT Index in den Tablespace TS_IDXDATA legen. Also brauchen alle Tabellen, die
Oracle TEXT unter der Haube erzeugt, eine entsprechende Storage-Klausel.
begin
  ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');

  ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE', 'tablespace ts_idxdata LOB(DATA) STORE AS (CACHE)');
  ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE', 'tablespace ts_idxdata compress 2');
  ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE', 'tablespace ts_idxdata ');
end;
/
Wie man am Skript sehr schön sehen kann, werden hier die einzelnen Storage Klauseln für alle
an einem Textindex beteiligten Tabellen hinterlegt. Hier kann alles eingetragen werden, was auch
in die Storage-Klausel einer "normalen" Tabelle hineinkann. Wichtig sind allerdings die Einstellungen
LOB (DATA) STORE AS (CACHE) bei der R_TABLE_CLAUSE und COMPRESS 2 bei der I_INDEX_CLAUSE. Diese
Defaulteinstellungen sind sehr wichtig und es ist für die Performance ganz entscheidend, diese
bei eigenen Storage-Klauseln ebenfalls zu übernehmen. Hieran sollte man immer denken!
Die Nutzung der Storage Preference ist nun wieder einfach:
create index ft_dokument on dokumente_tab(dokument)
indextype is mdsys.context
parameters ('storage mystore')
/
Ansonsten gilt, wie gesagt: Es werden Standard-Storage-Klauseln für Tabellen hinterlegt. Neben dem
Tablespace selbst können also alle möglichen Details eingetragen werden. Am wichtigsten sind jedoch
wahrscheinlich in allen Fällen die Einstellungen zum Tablespace, zur Extent-Verwaltung oder zum Cache.
Weitere Details lassen sich in der Dokumentation nachlesen.

Montag, 10. Oktober 2011

Einzelne Zeilen vom Index ausnehmen - Parameter "FORMAT COLUMN"

Heute geht es um einen eher unscheinbaren Parameter beim Erstellen eines Oracle TEXT Index:
Mit FORMAT COLUMN kann zum einen der Filter für Binärdokumente gesteuert werden - darüber hinaus
kann man damit aber auch festlegen, ob das Dokument überhaupt indiziert werden soll. Und das geht so - wir fangen mit einer einfachen Tabelle an:
create table test(
  id     number(10) not null,
  text   varchar2(4000) not null,
  format varchar2(10) not null,
  constraint pk_test primary key (id),
  constraint ck_format check (format in ('TEXT','BINARY','IGNORE'))
)
/

insert into test values (1, 'Dieses Dokument wird indiziert.', 'TEXT')
/
insert into test values (2, 'Hierfür stehen keine Einträge im Textindex', 'IGNORE')
/
Die Indexerstellung funktioniert wie immer - nur, dass der Parameter FORMAT_COLUMN mitgegeben wird.
create index ft_test on test (text)
indextype is ctxsys.context
parameters ('format column format')
/
Danach schauen wir in die $I-Tabelle - dort stehen in der Tat nur Tokens für das erste
Dokument - die Spalte FORMAT enthält hier "TEXT".
SQL> select token_text from dr$ft_test$i;

TOKEN_TEXT
------------------------------------------
DIESES
DOKUMENT
INDIZIERT
WIRD

4 Zeilen ausgewählt.
Aber was passiert, wenn man die Spalte FORMAT ändert - angenommen, wir ändern den Inhalt
für das zweite Dokument von IGNORE in TEXT ...
SQL> update test set format = 'TEXT' where id = 2
/

1 Zeile aktualisiert.
Wenn man nun in der Dictionary View CTX_USER_PENDING nachsieht, ändert sich diese
(obwohl mit der Spalte FORMAT eine "index-relevante" Spalte geändert wurde, nicht. Ein CTX_DDL.SYNC_INDEX
bewirkt also ebenfalls nichts. Man muss
also entweder im Update-Kommando selbst oder mit einem Trigger sicherstellen, dass bei einer
Änderung der Spalte FORMAT auch die Indexspalte selbst "angefasst" wird - das könnte so
aussehen ...
create or replace trigger tr_upd_format
before update of format on test
for each row
begin
  if :old.format != :new.format then 
    :new.text := :old.text;
  end if;
end;
/
Von nun an wirkt eine Änderung an der Spalte FORMAT wie eine Änderung am Dokument - nach der
Index-Synchronisierung ist das Dokument indiziert bzw. aus dem Index entfernt (je nachdem, wie
die Spalte FORMAT gesetzt wurde).
SQL> update test set format = 'IGNORE' where id = 1; 

1 Zeile wurde aktualisiert.

SQL> update test set format = 'TEXT' where id = 2; 

1 Zeile wurde aktualisiert.

SQL> select pnd_rowid from ctx_user_pending; 

PND_ROWID
------------------------------ 
AAAmVGAAFAAAMWTAAA
AAAmVGAAFAAAMWTAAB

2 Zeilen ausgewählt.

SQL> exec ctx_ddl.sync_index('FT_TEST'); 

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> exec ctx_ddl.optimize_index('FT_TEST', ctx_ddl.optlevel_full); 

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select token_text from dr$ft_test$i; 

TOKEN_TEXT
----------------------------------------------------------------
EINTRÄGE
HIERFÜR
IM
KEINE
STEHEN
TEXTINDEX

6 Zeilen ausgewählt.

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.

Beliebte Postings