Dienstag, 20. Juli 2010

Ähnliche "Tokens" mit der BROWSE_WORDS-Funktion finden

Die Funktion CTX_QUERY.BROWSE_WORDS ist sehr hilfreich, wenn es darum geht, "sich im Textindex" umzusehen. So kann man damit bspw. die aus dem Internet bekannte "Meinten Sie ...?"-Funktion umsetzen.
BROWSE_WORDS ist hier dokumentiert. Ausgehend von einem Start-Token sucht die Funktion die alphabetisch "danach" oder "davor" kommenden Tokens aus dem Index heraus. Liefert eine Textabfrage also nur wenige Ergebnisse zurück, so kann man mit dieser Funktion Suchvorschläge machen; könnte ja sein, dass der Anwender sich einfach nur vertippt hat. Das "Start-Token" kann, muss aber nicht im Index enthalten sein.
Hier ein Beispiel: Rund um das "Start-Token" XDBADNIN (Tippfehler) werden nun 10 Tokens herausgesucht ...
set serveroutput on;

declare
  v_resarr ctx_query.browse_tab;
begin
  ctx_query.browse_words(
    index_name => 'ftx_scripts',
    seed       => 'XDBADNIN',
    resarr     => v_resarr,
    numwords   => 10,
    direction  => CTX_QUERY.BROWSE_AROUND
  );
  for i in 1..v_resarr.count loop
    dbms_output.put_line(
      rpad(v_resarr(i).word, 45) || ':' || 
      lpad(v_resarr(i).doc_count, 3)
    );
  end loop;
end;
/
Die Ausgabe ist dann wie folgt ...
XDB1M102                                     :  2
XDB3G65V2HGYFNY6SPJQOPJZUK6YG                :  1
XDB7A24C                                     :  1
XDBACL                                       :  1
XDBADMIN                                     : 18
XDBAPP                                       :  1
XDBBRNNM6W5FHBEMZV5PCLLGABJNO7Z6BJL22DCN     :  1
XDBCONFIG                                    : 23
XDBCORE                                      :  4
XDBDBMIG                                     :  8
Gut zu erkennen ist, dass zusätzlich auch noch die Anzahl der Dokumente, in denen das jeweilige Token vorkommt, mitgeliefert wird. Man könnte in dieser Liste also das Token mit dem höchsten Document Count heraussuchen und dem Endanwender vorschlagen ...
Wichtig bei meinen Tests mit der Funktion war die Einstellung von NLS_SORT - für diese Funktion sollte es auf BINARY stehen (sonst werden die Tokens "vorher" nicht richtig herausgesucht). Wenn eure Anwendung hier mit LINGUISTIC arbeitet, sollte es für diese Funktion umgestellt werden; NLS_SORT kann mit einem ALTER SESSION geändert werden. Das folgende Beispiel zeigt, wie man das erreichen kann.
set serveroutput on;

declare
  v_resarr  ctx_query.browse_tab;
  v_nlssort varchar2(4000);
begin
  v_nlssort := sys_context('userenv', 'nls_sort');
  dbms_session.set_nls('NLS_SORT', 'BINARY');

  ctx_query.browse_words(
    index_name => 'ftx_scripts',
    seed       => 'XDBADNIN',
    resarr     => v_resarr,
    numwords   => 10,
    direction  => CTX_QUERY.BROWSE_AROUND
  );
  for i in 1..v_resarr.count loop
    dbms_output.put_line(
      rpad(v_resarr(i).word, 45) || ':' || 
      lpad(v_resarr(i).doc_count, 3)
    );
  end loop;

  dbms_session.set_nls('NLS_SORT', v_nlssort);
end;
/

Dienstag, 15. Juni 2010

Oracle Text Komponente überprüfen und installieren

Wie kann man überprüfen, dass die Oracle Text Umgebung korrekt installiert ist? Wie kann man Oracle Text nachinstallieren? Das sind häufig Fragen, die im Zusammenhang mit der Oracle Text Komponente auftauchen. Normalerweise ist Oracle Text in allen Editionen - Oracle Database Standard Edition One, Oracle Database Standard Edition (SE), Oracle Database Enterprise Edition (EE), Oracle Database Express Edition (XE) und Oracle Database Personal Edition - ohne zusätzliche Kosten verfügbar und wird auch automatisch mitinstalliert. Um zu überprüfen, ob die Installation fehlerfrei ist, eignen sich folgende Schritte, die als User SYS durchgeführt werden.

Mit der View DBA_REGISTRY können alle Komponenten und ihr zugehöriger Status angezeigt werden, die in das Verzeichnis der Datenbank geladen sind. Hier wird auf die COMP_ID CONTEXT abgefragt, um den Status zu Oracle Text zu evaluieren.

SQL> SELECT comp_name, status, substr(version,1,10) as version
     FROM dba_registry WHERE comp_id = 'CONTEXT';

COMP_NAME                                STATUS     VERSION
---------------------------------------- ---------- ----------
Oracle Text                              VALID      11.2.0.1.0

Danach wird die Version des CTXSYS Data Dictionaries und die Code Versionsnummer ausgegeben.

SQL> SELECT * FROM ctxsys.ctx_version;

VER_DICT   VER_CODE
---------- ----------
11.2.0.1.0 11.2.0.1.0

SQL> SELECT ctxsys.dri_version VER_CODE FROM dual;

VER_CODE
--------------------------------------------------------------------------------
11.2.0.1.0

Im nächsten Schritt wird überprüft, ob alle Objekte im CTXSYS Schema angelegt sind, keine invaliden Objekte vorhanden sind und keine CTXSYS-Objekte im SYS Schema enthalten sind. Das Ergebnis sollte dann folgendermassen aussehen.

SQL> SELECT count(*) FROM dba_objects WHERE owner='CTXSYS';
  
COUNT(*)
----------
       366

SQL> SELECT object_type, count(*) FROM dba_objects 
     WHERE owner='CTXSYS' GROUP BY object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
SEQUENCE                     3
PROCEDURE                    2
OPERATOR                     6
LOB                          2
LIBRARY                      1
PACKAGE                     73
PACKAGE BODY                62
TYPE BODY                    6
INDEX                       56
TABLE                       47
VIEW                        71
FUNCTION                     2
INDEXTYPE                    4
TYPE                        31

14 rows selected.

SQL> SELECT object_name, object_type, status 
     FROM dba_objects WHERE owner='CTXSYS' AND status != 'VALID' 
     ORDER BY object_name;

no rows selected


SQL> SELECT owner, object_name, object_type, status FROM dba_objects
     WHERE owner = 'SYS' AND (object_name like 'CTX_%' or object_name like 'DRI%')
     ORDER BY 2,3; 

no rows selected

Soweit ist alles in unserer Installation in Ordnung... Möchte man diese und weitere Überprüfungen zu den existierenden Textindizes automatisch durchführen, eignet sich das kleine Health Check Skript aus Note 823649.1.

Nun wollen wir in einer anderen Umgebung Oracle Text Release 11gR2 nachinstallieren. Dazu werden zwei Skripte benötigt. Das Skript catctx.sql im Verzeichnis $ORACLE_HOME/ctx/admin erzeugt das Oracle Text Dictionary und das Schema CTXSYS. Als SYS eingeloggt werden dabei zu Beginn die Einstellungen zu User CXTSYS - wie Passwort, Default und Temporary Tablespace und LOCK oder UNLOCK Status - abgefragt. Danach werden die entsprechenden Dictionary Objekte angelegt.

SQL> connect / as sysdba 
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql
Enter value for 1: oracle
Enter value for 2: SYSAUX
Enter value for 3: TEMP
Enter value for 4: NOLOCK
...creating user CTXSYS
old   2: identified by &pass default tablespace &tbs temporary tablespace &ttbs
new   2: identified by oracle default tablespace SYSAUX temporary tablespace TEMP

User created.


Grant succeeded.


Grant succeeded.
...

Nach wenigen Minuten ist das Dictionary angelegt. Nun werden im zweiten Schritt die Sprach-spezifischen Default Präferenzen erzeugt. Es existieren Skripte für die unterschiedlichen Sprachen im $ORACLE_HOME/ctx/admin/defaults Verzeichnis. Die Skripte heissen drdef(sprachkuerzel).sql. Das Sprachkürzel D steht beispielsweise für deutsche, US für englische, F für französische Spracheinstellungen. Im folgenden legen wir eine deutsche Umgebung an, dazu loggen wir uns als CTXSYS ein und starten das Skript dr0defin.sql mit Parameter GERMAN, das automatisch das korrekte Spracheinstellungs-Skript ausführt.

SQL> connect ctxsys/oracle
SQL> @dr0defin.sql "GERMAN"
old   1: SELECT DECODE('&nls_language',
new   1: SELECT DECODE('GERMAN',

L
-
d

Creating lexer preference...

PL/SQL procedure successfully completed.

Creating wordlist preference...

PL/SQL procedure successfully completed.

Creating stoplist...

PL/SQL procedure successfully completed.

Creating default policy...

PL/SQL procedure successfully completed.

SQL>

Nach ein paar Sekunden ist dies auch erledigt. Zum Abschluss können wir die zu Beginn beschriebenen Skripte starten, um nun eine Validierung dieser Installation durchzuführen.

Montag, 17. Mai 2010

"Sicheres" Suchen: Oracle TEXT und die "Virtual Private Database"

Eine Volltextsuche geht bekanntlich immer über die ganze Tabelle. Manchmal bestehen allerdings Anforderungen an "sicheres Suchen", es soll also nicht jeder alles sehen können. Hierzu bietet sich im "normalen" Datenbankumfeld die Virtual Private Database an. Diese bewirkt, kurzgefasst, dass unterschiedliche Nutzer ein SELECT * FROM TABELLE absetzen, jeder sieht jedoch was anderes (das Feature gehört zur Enterprise Edition der Datenbank). Das wollen wir nun im Zusammenspiel mit Oracle TEXT einsetzen. Zuerst erzeugen wir die Tabelle und fügen ein paar Spalten ein:
create table dokumente(
  id                 number(10),
  sicherheitsmerkmal varchar2(50),
  dokument           clob
)
/

insert into dokumente values (1, 'LOW', 'Oracle TEXT dient zur Volltextrecherche');
insert into dokumente values (2, 'LOW', 'Oracle TEXT beherrscht linguistische Suche');
insert into dokumente values (3, 'MEDIUM', 'Der Gewinn wird im nächsten Jahr steigen');
insert into dokumente values (4, 'HIGH', 'Die Spionageabteilung sitzt in Berlin');
Das Besondere ist die Spalte SICHERHEITSMERKMAL - sie kann die Werte MEDIUM, HIGH oder LOW annehmen. Es fehlt nun nur noch eine Tabelle, mit welcher wir die Datenbanknutzer auf die Sicherheitsklassen abbilden ...
create table dokumente_nutzer(
  userid             varchar2(30),
  sicherheitsmerkmal varchar2(100)
)
/

insert into dokumente_nutzer values ('SCOTT', 'HIGH');
insert into dokumente_nutzer values ('SCOTT', 'MEDIUM');
insert into dokumente_nutzer values ('SCOTT', 'LOW');
insert into dokumente_nutzer values ('REVISION', 'MEDIUM');
insert into dokumente_nutzer values ('REVISION', 'LOW');
insert into dokumente_nutzer values ('ALLE', 'LOW');
Dann werden (als SYS) die Nutzer angelegt und, da die Privilegiensteuerung komplett über die Virtual Private Database gesteuert wird, SELECT-Privilegien an der Tabelle an PUBLIC vergeben.
create user revision identified by revision
/

create user alle identified by alle
/

grant create session to revision
/

grant create session to alle
/

grant select on scott.dokumente to public
/

Nun geht es ans Einrichten der Virtual Private Database. Zunächst wird die sog. Policy Function benötigt. Die Policy Function generiert eine WHERE-Bedingung, die dann von der Datenbank automatisch in das SELECT-Kommando integriert wird. Durch diese Vorgehensweise (die Sicherheitsregel "hängt" direkt an der Tabelle) kann die Sicherheitsregel nicht umgangen werden! Unsere PL/SQL-Funktion ist recht einfach ...
create or replace function my_policy(
 p_schema varchar2, p_object varchar2
) return varchar2 is
  v_where varchar2(32767) := 'SICHERHEITSMERKMAL IN (';
  v_users boolean := false;
begin
  for i in (
    select sicherheitsmerkmal from dokumente_nutzer
    where userid = user
  ) loop
    v_users := true;
    v_where := v_where || '''' || i.sicherheitsmerkmal || ''', ';
  end loop;
  if v_users then 
    v_where := substr(v_where, 1, length(v_where) - 2) || ')'; 
  else
    v_where := '1=0';
  end if;
  return v_where;
end;  
Als letztes machen wir die Virtual Private Database "scharf" ... Um dieses Kommando laufen zu lassen, wird das EXECUTE-Privileg auf DBMS_RLS benötigt - oder der DBA macht es gleich selbst ...
begin
 DBMS_RLS.ADD_POLICY(
    object_schema => 'SCOTT',
    object_name => 'DOKUMENTE',
    policy_name => 'SCOTT_DOKUMENT_POLICY',
    function_schema => 'SCOTT',
    policy_function => 'MY_POLICY'
  );
end;
/
Und nun kommt der erste Test (noch ohne Volltextindex). Probiert nun mal, die Tabelle mit verschiedenen Nutzerkonten zu selektieren ... Zuerst als SCOTT:
SQL> select * from scott.dokumente;

        ID SICHERHEITSMERKMAL                                 DOKUMENT
---------- -------------------------------------------------- --------------------------------------------------
         1 LOW                                                Oracle TEXT dient zur Volltextrecherche
         2 LOW                                                Oracle TEXT beherrscht linguistische Suche
         3 MEDIUM                                             Der Gewinn wird im nächsten Jahr steigen
         4 HIGH                                               Die Spionageabteilung sitzt in Berlin

4 Zeilen ausgewählt.
... dann als User ALLE:
SQL> select * from scott.dokumente;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         1 LOW             Oracle TEXT dient zur Volltextrecherche
         2 LOW             Oracle TEXT beherrscht linguistische Suche

2 Zeilen ausgewählt.
... schließlich als irgendein User, der in der Tabelle DOKUMENTE_NUTZER nicht vorkommt ...
SQL> select * from scott.dokumente;

Es wurden keine Zeilen ausgewählt
Man sieht, dass zwar alle die Tabelle selektieren können, jeder sieht allerdings was anderes. Und das kann man natürlich auch mit dem Volltextindex kombinieren. Den müssen wir natürlich zuerst mal anlegen. Und hier wollen wir auch gleich das in 11g neue Feature des Composite Domain Index nutzen (siehe das eigene Blog Posting dazu. Nötig ist das, da in diesem Fall jede Abfrage eine Mixed Query sein wird - die Virtual Private Database hängt ja eine relationale Abfrage an jede Textabfrage an ...
create index vt_dokumente on dokumente(dokument)
indextype is ctxsys.context
filter by sicherheitsmerkmal
parameters ('transactional')
/
Nun können wir testen: Wir arbeiten als User ALLE und probieren mal einige Vollextabfragen ...
SQL> select * from scott.dokumente where contains (dokument,'Oracle') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         1 LOW             Oracle TEXT dient zur Volltextrecherche
         2 LOW             Oracle TEXT beherrscht linguistische Suche

2 Zeilen ausgewählt.

SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

Es wurden keine Zeilen ausgewählt
Die Abfrage mit dem Gewinn probieren wir danach mal als User REVISION - der darf mehr sehen, aber nicht alles ...
SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         3 MEDIUM          Der Gewinn wird im nächsten Jahr steigen

1 Zeile ausgewählt.

SQL> select * from scott.dokumente where contains (dokument,'Spionageabteilung') > 0;

Es wurden keine Zeilen ausgewählt.
Die Spionageabteilung darf aber nur der SCOTT sehen ...
SQL> select * from scott.dokumente where contains (dokument,'Spionageabteilung') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         4 HIGH            Die Spionageabteilung sitzt in Berlin

1 Zeile ausgewählt.
Und das Schöne ist, dass diese Sicherheitsregeln unabhängig vom Textindex arbeiten; sie ziehen sowohl bei "normalen" als auch bei Volltextrecherchen. Zum Abschluß werfen wir einen Blick auf die Performance - normalerweise sind solche Mixed Queries ja problematisch ... insbesondere, wenn das Volltextkriterium nur gemeinsam mit dem relationalen Kriterium selektiv ist. Auch dazu bauen wir einen kleinen Testcase und fügen einige Sätze in die Tabelle DOKUMENTE ein.
drop index vt_dokumente
/

begin
  for i in 1..100000 loop
    insert into dokumente values (i+10, 'MEDIUM', 'Der Gewinn ist dieses Jahr stabil');
    insert into dokumente values (i+200010, 'LOW', 'Oracle TEXT ist Teil der Datenbank');
   end loop;
end;
/

commit
/

insert into dokumente values (500000, 'LOW', 'Gewinn ist: Mehr Einnahmen als Ausgaben');
    

create index vt_dokumente on dokumente(dokument)
indextype is ctxsys.context
filter by sicherheitsmerkmal
parameters ('transactional')
/
Diese Situation bringt das Mixed Query-Problem auf den Punkt. Die Volltextrecherche nach Gewinn allein ist nun extrem unselektiv (100.000 mal in MEDIUM, 1 mal in LOW). Die Sicherheitsklasse LOW ist aber auch unselektiv (kommt auch 100.000 mal vor). Die Kombination als Volltextsuche und relationalen Kriterium allein ist sehr selektiv - denn Gewinn kommt in der Sicherheitsklasse LOW nur einmal vor. Schauen wir uns die Abfrage nun an - wir arbeiten mit dem User ALLE (dieser hat nur Zugriff auf LOW), es darf also nur die Zeile der Klasse LOW zurückkommen ...
SQL>select * from scott.dokumente where contains (dokument,'Gewinn') > 0

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
    300000 LOW             Gewinn ist: Mehr Einnahmen als Ausgaben

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.17
Sieht auf den ersten Blick ganz gut aus - und der Ausführungsplan ...?
SQL> set autotrace traceonly
SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

1 Zeile wurde ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3982102634

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 42738 |  6135K|  9161   (4)| 00:01:50 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DOKUMENTE    | 42738 |  6135K|  9161   (4)| 00:01:50 |
|*  2 |   DOMAIN INDEX              | VT_DOKUMENTE |       |       |  7710   (1)| 00:01:33 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("DOKUMENT",'Gewinn')>0)
       filter("SICHERHEITSMERKMAL"='LOW')


Statistiken
----------------------------------------------------------
         35  recursive calls
          0  db block gets
        551  consistent gets
          0  physical reads
          0  redo size
        928  bytes sent via SQL*Net to client
        648  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
Im Ausführungsplan erkennt man, dass die komplette Abfrage aus dem Volltextindex heraus bedient wurde. Genau für solche Fälle wurde dieses Feature in 11g auch geschaffen ... Zum Abschluß noch ein paar Links zu weiterführender Dokumentation:

Freitag, 9. April 2010

Die Wordlist und das wildcard_maxterms Attribut

In folgendem Blog wird das WILDCARD_MAXTERMS Attribut, das eine Eigenschaft der BASIC_WORDLIST ist, kurz beschrieben und an einem Beispiel erklärt. Die Einstellung von WILDCARD_MAXTERMS gibt die maximale Anzahl der Ergebnisbegriffe einer Wildcard Suche an. Was bedeutet dies nun?

Wir nutzen im Beispiel die Tabelle TEXTTABELLE aus dem Blog und definieren zusätzlich die Präferenz für Mixed Case.

execute ctx_ddl.drop_preference('MY_LEXER');
begin
  ctx_ddl.create_preference(
    preference_name => 'MY_LEXER',
    object_name     => 'BASIC_LEXER');
  -- Mixed Case abschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO');
end;
/

Danach setzen wir explizit die BASIC_WORDLIST auf den Wert 1, um einen Fehler auszulösen.

execute ctx_ddl.drop_preference('my_wordlist');
BEGIN
  ctx_ddl.create_preference('my_wordlist', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute('my_wordlist', 'WILDCARD_MAXTERMS', 1);
END;
/

Nun wird der Index angelegt ...

CREATE INDEX idx_text ON texttabelle (dokument)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('WORDLIST my_wordlist LEXER MY_LEXER')
/

Bei der folgenden Abfrage erhalten wir nun einen Fehler, da eigentlich mehr als ein Ergebnis zu erwarten ist.

SQL> SELECT * FROM texttabelle WHERE contains (dokument,'%gewinn%')>0;
SELECT * FROM texttabelle WHERE contains (dokument,'%gewinn%')>0
*
FEHLER in Zeile 1:
ORA-29902: Fehler bei der Ausführung von Routine ODCIIndexStart()
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms

Verifizieren wir das Ganze nun mit der Token-Tabelle, um die Ergebnisbegriffe einzusehen.

SQL> SELECT token_text FROM dr$idx_text$i WHERE upper(token_text) like '%GEWINN%';

TOKEN_TEXT
----------------------------------------------------------------
GEWINNT
GEWINNZUWACHS

Ändern wir also in der Wordlist das WILDCARD_MAXTERMS Attribut auf den Wert 20000. Dies ist übrigens in 11g der Default-Wert.
 
execute ctd_ddl.drop_preference('my_wordlist');
BEGIN  
  ctx_ddl.create_preference ('my_wordlist', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute ('my_wordlist', 'WILDCARD_MAXTERMS', 20000);
END;
/

Passen wir im nächsten Schritt die Wordlist des Index an. Dies könnte Online mit Unterstützung des Shadow Index durchgeführt (siehe Blog) werden.

execute ctx_ddl.recreate_index_online('IDX_TEXT','REPLACE WORDLIST my_wordlist');

Nun erhalten wir das erwartete Ergebnis.

SQL> SELECT * FROM texttabelle WHERE contains (dokument,'%gewinn%')>0

        ID DOKUMENT
---------- --------------------------------------------------
         2 A-Partei gewinnt Wahl in Hansestadt
         4 Wirtschaft: Erneuter Gewinnzuwachs in diesem Jahr

Je nach Release der Datenbank gibt es unterschiedliche Default-Werte für das WILDCARD_MAXTERMS Attribut. Prüfen lässt sich dies mit folgender Abfrage:

SQL> SELECT oat_default, oat_min, oat_max  
FROM ctx_object_attributes 
WHERE oat_attribute LIKE '%MAXTERM%'

OAT_DEFAULT             OAT_MIN OAT_MAX
-------------------- ---------- ----------------------------------------
20000                         0 50000

Der aktuelle Wert lässt sich über folgende Abfrage ermitteln:

SQL> SELECT * FROM ctx_user_preference_values;

PRV_PREFERENCE                 PRV_ATTRIBUTE                  PRV_VALUE
------------------------------ ------------------------------ ----------
MY_LEXER                       MIXED_CASE                     NO
MY_WORDLIST                    WILDCARD_MAXTERMS              50000

Eine Anmerkung zum Schluss: Eine Erhöhung von WILDCARD_MAXTERMS muss nicht die optimale Lösung sein. Manchmal hilft eine Erweiterung der Stoppliste oder eine Anpassung der Auswahlliste innerhalb einer Webapplikation um den Anforderungen zu genügen.

Dienstag, 16. März 2010

Arbeiten mit einem Thesaurus in Oracle TEXT

Oracle TEXT bietet Thesaurus-Unterstützung out-of-the-box an. Das bedeutet, dass man nicht nur nach einem Wort oder einer Phrase suchen kann, sondern auch nach "verwandten" Wörtern. Die Beziehungen zwischen verwandten Wörtern müssen allerdings in der Datenbank hinterlegt sein. Dieses Posting stellt kurz vor, wie man einen einfachen Thesaurus anlegt und nutzt. Oracle selbst stellt Thesauri aber nur für die englische Sprache zur Verfügung; im deutschsprachigen Bereich muss man sich diesen selbst erstellen.
Thesauri werden angelegt und verwaltet mit dem PL/SQL-Paket CTX_THES. Der folgende Aufruf legt einen neuen (noch leeren) Thesaurus an. Wie für andere administrative Aufgaben benötigt Ihr auch zum Verwalten von Thesauri die Rolle CTXAPP:
begin
  ctx_thes.create_thesaurus(
    name     => 'MEIN_THESAURUS',
    casesens => false
  );
end;
/
Nun geht es daran, Wortbeziehungen in den Thesaurus einzutragen. Dabei werden folgende Beziehungen unterstützt:
  • Narrower Term (NT): Ein Begriff wird enger gefasst. Eine solche Beziehung könnte sein Sport NT Fußball.
  • Broader Term (BT): Ein Begriff wird weiter gefasst. Das ist das Gegenteil zum Narrower Term. Eine solche Beziehung könnte sein AktieBT Wertpapier.
  • Broader Term (BT): Ein Begriff wird weiter gefasst. Das ist das Gegenteil zum Narrower Term. Eine solche Beziehung könnte sein AktieBT Wertpapier.
  • Synonym (SYN): Eine Synonymbeziehung meint, dass zwei Begriffe das gleiche bedeuten. Geld SYN Moneten ist ein Beispiel für eine solche Beziehung.
  • Übersetzungen (TR): Wie der Name schon sagt; im Thesaurus lassen sich auch Übersetzungen eines Begriffs in andere Sprachen verwalten. Ein Beispiel wäre Geld ENGLISH: Money.
Das folgende Beispiel generiert einige Beziehungen in den soeben erstellten Thesaurus:
begin
  ctx_thes.create_relation('MEIN_THESAURUS', 'Elfmeter', 'SYN', 'Strafstoß');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Schiedsrichter', 'SYN', 'Unparteiischer');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Trainer', 'SYN', 'Coach');

  ctx_thes.create_relation('MEIN_THESAURUS', 'Ballspiel', 'NT', 'Fußball');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Liga', 'NT', 'Bundesliga');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Liga', 'NT', 'Regionalliga');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Liga', 'NT', 'Champions League');

  ctx_thes.create_relation('MEIN_THESAURUS', 'Handspiel', 'BT', 'Foul');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Stürmer', 'BT', 'Spieler');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Libero', 'BT', 'Spieler');
end;
/
Nun können die Beziehungen schon getestet werden. Zunächst wollen wir prüfen, ob die Synonyme für "Trainer" funktionieren.
select ctx_thes.syn('Trainer', 'MEIN_THESAURUS') from dual;

CTX_THES.SYN('TRAINER','MEIN_THESAURUS')
---------------------------------------------
{TRAINER}|{COACH}

select ctx_thes.syn('Coach', 'MEIN_THESAURUS') from dual;

CTX_THES.SYN('COACH','MEIN_THESAURUS')
----------------------------------------------------
{COACH}|{TRAINER}
Nun geht es an einen richtigen Test. Der Thesaurus soll in einer Volltextabfrage mit CONTAINS genutzt werden. Die folgenden Anweisungen erzeugen eine Tabelle, einige Dokumente und schließlich einen Volltextindex. Thesaurus und Index sind aber voneinander unabhängig! Ein vorhandener Volltextindex muss nach Erstellen oder Ändern eines Thesaurus nicht neu gebaut werden.
create table test_thes_tab(text varchar2(4000))
/

insert into test_thes_tab values ('Der Trainer wurde entlassen');
insert into test_thes_tab values ('Der Verein war zurück in der Bundesliga');
insert into test_thes_tab values ('Der Stürmer lieferte eine Glanzleistung ab.');
insert into test_thes_tab values ('In der 15. Minute gab es einen Elfmeter');
insert into test_thes_tab values ('Der Schiedsrichter blieb hart.');

create index idx_thestest_volltext on test_thes_tab (text) 
indextype is ctxsys.context
/
Nun einige Abfragen ...
  • Die einfache Suche nach Coach bringt keine Ergebnisse:
    select text from test_thes_tab where contains(text, 'Coach') > 0;
    
    No rows selected.
    
  • Die Synonymbeziehung bringt den Erfolg:
    select text from test_thes_tab where contains(text, 'SYN(Coach, MEIN_THESAURUS)') > 0;
    
    TEXT
    --------------------------------------------------------------------------------
    Der Trainer wurde entlassen
    
  • Die Suche nach Spieler bringt keine Treffer:
    select text from test_thes_tab where contains(text, 'Spieler') > 0;
    
    Es wurden keine Zeilen ausgewählt.
    
  • Sucht man nach den Narrower Terms (hier: bis zu 10 Stufen), so findet man auch die Stürmer.
    select text from test_thes_tab where contains(text, 'NT(Spieler,10,MEIN_THESAURUS)') > 0;
    
    TEXT
    --------------------------------------------------------------------------------
    Der Stürmer lieferte eine Glanzleistung ab.
    
In diesen Beispielen wurde der Thesaurus mit MEIN_THESAURUS überall mit angegeben. Es ist jedoch auch möglich, einen Thesaurus namens DEFAULT zu erzeugen oder einen bestehenden in DEFAULT umzubenennen. Dieser wird dann immer verwendet, wenn kein Thesaurus-Name angegeben wird. Das folgende Kommando benennt den Thesaurus MEIN_THESAURUS in DEFAULT um.
begin
  ctx_thes.alter_thesaurus(
    tname   => 'MEIN_THESAURUS',
    op      => ctx_thes.op_rename, 
    operand => 'DEFAULT'
  );
end;
/
Nun muss der Thesaurus-Name nicht mehr explizit angegeben werden.
select text from test_thes_tab where contains(text, 'NT(Spieler,10)') > 0;

TEXT
--------------------------------------------------------------------------------
Der Stürmer lieferte eine Glanzleistung ab.
Wie für alles gibt es auch für Thesauri in der Datenbank Dictionary Views:
  • CTX_USER_THESAURI enthält die definierten Thesauri.
  • CTX_USER_THES_PHRASES gibt die in den Thesauri enthaltenen Begriff zurück. Die Beziehungen selbst sind darin aber nicht enthalten; diese können mit dem Paket CTX_THES und den darin enthaltenen Funktionen wie NT oder BT oder SYN ermittelt werden.
Schließlich können auch Übersetzungen im Thesaurus gespeichert werden. Hier ein Beispiel:
begin
  ctx_thes.create_relation('DEFAULT', 'Referee', 'GERMAN:', 'Schiedsrichter');
end;  
Nun kann man auch nach den Übersetzungen von Referee suchen ...
select text from test_thes_tab where contains(text, 'TR(Referee)') > 0;

TEXT
-------------------------------------------------------------------------------
Der Schiedsrichter blieb hart.
Weitere Informationen zum Thema findet Ihr hier:

Dienstag, 16. Februar 2010

Der Textindex und der Datentyp SECUREFILE

Der neue Datentyp SECUREFILE für unstrukturierter Daten und das Datenbank-Filesystem gehören zu wichtigen Neuigkeiten in Oracle 11g bzw. in Oracle 11g Release 2 sind. Der Datentyp SECUREFILE kann dabei mit allen zusätzlichen Eigenschaften wie Deduplikation, Verschlüsselung und Komprimierung ohne weitere Anpassung verwendet werden. Der folgende Beitrag zeigt einige Beispiel dazu.

Nehmen wir folgende SECURE_LOB Tabelle, die alle verfügbaren Eigenschaften eines SECUREFILE Datentyps besitzt.

SQL> select column_name, compression, in_row, securefile, deduplication, encrypt 
     from user_lobs where table_name='SECURE_LOB';

COLUMN_NAME          COMPRE IN_ SEC DEDUPLICATION   ENCR
-------------------- ------ --- --- --------------- ----
TEXT                 HIGH   YES YES LOB             YES


Hinweis: Damit fallen zusätzliche Lizenzgebühren für die zusätzliche Option Advanced Security Option (ASO) bzw. die Advanced Compression Option an. Der Erwerb einer Lizenz für ASO erlaubt dabei im Rahmen des Feature Transparent Data Encryption (TDE) neben der Verschlüsselung von Benutzerdaten bspw. auch die Verschlüsselung des kompletten Datenverkehrs von und zur Datenbank. Mehr dazu im Tipp der DBADMIN Community unter Daten verschlüsseln mit Transparent Data Encryption (TDE)). Die Advanced Compression Option stellt bei unstrukturierten Daten sicher, dass keine Duplizierung der Daten erfolgt und die Daten komprimiert abgespeichert vorliegen können. Generell zum Umgang mit SecureFiles und Compression gibt der Tipp LOB-Management in 11g: Einstieg in die Nutzung von SECUREFILEs) eine Einführung.
Nach dem Anlegen eines Textindex auf die Spalte TEXT der Tabelle SECURE_LOB, fragen wir auf Text-Snippets der Tabelle SECURE_LOB ab:

SQL> set pagesize 100
SQL> set define '^'
SQL> prompt

SQL> accept SUCHE       prompt '>> Suchwort:     '
>> Suchwort:     Polizei AND Helfer
SQL> execute ctx_doc.set_key_type('ROWID');

PL/SQL procedure successfully completed.

SQL> select regexp_replace(ctx_doc.snippet(index_name=>'IDX_VOLLTEXT', textkey=>rowid, text_query=> '^SUCHE',starttag=>'###',endtag =>'###'),'\s+',' ') from SECURE_LOB where contains (text,'^SUCHE')>0 and rownum=1;

old   1: select regexp_replace(ctx_doc.snippet(index_name=>'IDX_VOLLTEXT', textkey=>rowid, text_query=> '^SUCHE',starttag=>'###',endtag =>'###'),'\s+',' ') from SECURE_LOB where contains (text,'^SUCHE')>0 and rownum=1
new   1: select regexp_replace(ctx_doc.snippet(index_name=>'IDX_VOLLTEXT', textkey=>rowid, text_query=> 'Polizei AND Helfer',starttag=>'###',endtag =>'###'),'\s+',' ') from SECURE_LOB where contains (text,'Polizei AND Helfer')>0 and rownum=1

REGEXP_REPLACE(CTX_DOC.SNIPPET(INDEX_NAME=>'IDX_VOLLTEXT',TEXTKEY=>ROWID,TEXT_QU
--------------------------------------------------------------------------------
Auch die ###Helfer### brauchen Hilfe Utl: Dramatische Einsatze...mit seel
ischer Not - ###Polizei### setzt auf Kirche Von

Sie finden mehr zur Verwendung von CTX_DOC.SNIPPET im Keyword in Kontext-Blog. Die Funktion für reguläre Ausdrücke REGEXP_REPLACE wurde dabei verwendet, um in SQL*Plus das Ausgabeformat zu optimieren und die überflüssigen Leerzeichen zu entfernen.

Das Gleiche lässt sich auch mit dem neuen Datenbank-Filesystem realisieren. Das Standard-Datenbank-Filesystem stellt eine vorstrukturierte Tabelle T_... mit Blob-Spalte FILEDATA zur Verfügung gestellt. Diese kann mit Text indiziert werden und wie oben abgfragt werden. Das folgende Beispiel zeigt eine solche Tabelle mit Namen T_DBFS1 und die möglichen Eigenschaften der BLOB Spalte.

SQL> select column_name, compression, in_row, securefile, deduplication, encrypt
     from user_lobs where table_name like 'T_%';

COLUMN_NAME          COMPRE IN_ SEC DEDUPLICATION   ENCR
-------------------- ------ --- --- --------------- ----
FILEDATA             NO     YES YES NO              NO

Hinweis: Um ein Standard-Datenbank-Filesystem zur Verfügung zu stellen, sind nur wenige Arbeitsschritte nötig. Literatur dazu findet sich in Oracle® Database SecureFiles and Large Objects Developer's Guide 11g Release 2 (11.2) im Kapitel 6.

Montag, 18. Januar 2010

Abfragen vordefinieren: Stored Query Expressions (SQE)

Eine wenig bis gar nicht bekannte Eigenschaft von Oracle TEXT sind die Stored Query Expressions (SQE). Damit können bestimmte TEXT-Abfragen quasi im Vorfeld unter einem Begriff gespeichert und anschließend von allen Nutzern verwendet werden ... Ein einfaches Beispiel anhand des Oracle TEXT Handbuchs ...
begin
  ctx_query.store_sqe('textdebug_cczarski', 'trace or log or logging or ctx_log');
end;
Von nun an kann man diesen Ausdruck wie ein normales Wort in CONTAINS-Abfragen verwenden ...
SQL> select id from dokument_tab where contains(content, 'sqe(textdebug_cczarski)') > 0;

        ID
----------
         1
Wendet man das in einem früheren Blog-Posting vorgestellte CTX_QUERY.EXPLAIN an, so kann man sich die Vorgehensweise von Oracle TEXT näher ansehen ...
ID         OPERATION       OPTIO OBJECT_NAME       POSITION
---------- --------------- ----- --------------- ----------
    1      OR                                             1
      2    WORD                  trace                    1
      3    WORD                  log                      2
      4    WORD                  logging                  3
      5    WORD                  ctx_log                  4
Man sieht von der SQE eigentlich gar nichts mehr - Oracle TEXT löst diese einfach transparent auf. SQE's sind insbesondere hilfreich, wenn es darum geht, schwierige CONTAINS-Abfragen (deren Ausarbeitung viel Arbeit war) für andere einfach nutztbar zu machen.

Beliebte Postings