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