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.

Mittwoch, 2. Dezember 2009

Der CTXCAT-Index

Neben dem "normalen" Context Index gibt es schon seit geraumer Zeit den kaum bekannten "kleinen Bruder" CTXCAT. Dieses Posting möchte ich daher gerne dem CTXCAT-Index widmen. Möchte man die Unterschiede in einem Satz herausarbeiten, so bietet der CTXCAT-Index zunächst mal weniger Features, ist für kleinere Dokumente gedacht und arbeitet synchron, ist also einfacher zu verwalten. Im einzelnen ...
  • Der CTXCAT Index ist für kleinere Textfragmente gedacht - in der Dokumentation ist von "wenigen Zeilen" die Rede; um das auf den Punkt zu bringen; das Maximum sollten so 50 Worte sein ...
  • Der Index arbeitet komplett synchron - per DML gemachte Änderungen sind sofort im Index sichtbar. Der Prozess des Index-Synchronisierens und -Optimierens fällt hier also weg.
  • Es werden weniger "Features" unterstützt - so gibt es keine Data Stores wie beim CONTEXT-Index. Die zu indizierenden Daten müssen also genau so in der Tabellenspalte drinstehen.
  • XML wird nicht unterstützt, da CTXCAT keine Section Groups kennt
  • CTXCAT unterstützt allerdings sog. Sub-Indexes, mit denen man Mixed Queries unterstützen kann. Eine Mixed Query ist eine Abfrage, die sowohl relationale (strukturierte) als auch Volltextkriterien enthält.
Der CTXCAT-Index wird genauso angelegt wie ein CONTEXT-Index, nur mit einem anderen Indextypen ... Als Beispiel nehmen wir eine Art Umfragetabelle, in der die Ergebnisse einer Umfrage zur "Servicequalität" gespeichert werden ...
create table umfrageergebnis(
  id        number(10),
  altersang number(3),
  note      number(1),
  kommentar varchar2(1000)
);
Der Index kann sofort erzeugt werden ...
create index suche_idx on UMFRAGEERGEBNIS(KOMMENTAR)
indextype is ctxsys.ctxcat;
Wie der CONTEXT-Index unterstützt auch der CTXCAT-Index linguistische Features. Das folgende Beispiel legt den Index explizit als Case-Sensitiv fest und definiert den Bindestrich als sog. Printjoin.
begin
  ctx_ddl.create_preference('CAT_LEXER','BASIC_LEXER');
  ctx_ddl.set_attribute('CAT_LEXER','MIXED_CASE','YES');
  ctx_ddl.set_attribute('CAT_LEXER','PRINTJOINS', '-');
end;
/

create index suche_idx on UMFRAGEERGEBNIS(KOMMENTAR)
indextype is ctxsys.ctxcat
parameters ('LEXER CAT_LEXER');
Stopwortlisten werden natürlich analog unterstützt. Nun kann man ein paar Dokumente einpflegen ...
insert into umfrageergebnis values (1, 35, 1, 'service war ausgezeichnet');
insert into umfrageergebnis values (2, 55, 3, 'ganz gut');
insert into umfrageergebnis values (3, 45, 5, 'ich wurde unfreundlich bedient');
insert into umfrageergebnis values (4, 42, 2, 'etwas unfreundlich, aber fehlerfrei und korrekt');
Und man kann sofort abfragen ...
select * from umfrageergebnis where catsearch(kommentar, 'unfreundlich', null) > 0;

        ID  ALTERSANG       NOTE KOMMENTAR
---------- ---------- ---------- ------------------------------------------------
         3         45          5 ich wurde unfreundlich bedient
         4         42          2 etwas unfreundlich, aber fehlerfrei und korrekt
Interessant wären nun die Mixed Queries. Angenommen, wir interessieren uns für die Ergebnisse, die aufgrund von "Unfreundlichkeit" schlecht waren. Also brauchen wir noch ein relationales Kriterium auf der Schulnote. Zunächst also den Index wieder droppen ...
drop index suche_idx;
Und nun erzeugen wir ein Index Set; dieses enthält die strukturierten "Unter-Indizes". Dem Index-Set wird dann ein Index auf die Spalten "Note", "Alter" und auf die Kombination "Note und Alter" hinzugefügt.
begin
  ctx_ddl.create_index_set('umfrage_iset');
  ctx_ddl.add_index('umfrage_iset','note'); 
  ctx_ddl.add_index('umfrage_iset','altersang'); 
  ctx_ddl.add_index('umfrage_iset','note, altersang');
end;
Danach den Index neu anlegen ...
create index suche_idx on UMFRAGEERGEBNIS(KOMMENTAR)
indextype is ctxsys.ctxcat
parameters ('LEXER CAT_LEXER INDEX SET UMFRAGE_ISET');
Nun wieder abfragen ... mit dem CATSEARCH-Operator ...
select * from umfrageergebnis where catsearch(kommentar, 'unfreundlich', 'note >= 4') > 0;

        ID  ALTERSANG       NOTE KOMMENTAR
---------- ---------- ---------- ------------------------------------------------
         3         45          5 ich wurde unfreundlich bedient
Schauen wir uns mal den Index genauer an. Wie der CONTEXT-Index besteht auch der CTXCAT-Index aus Tabellen, die ins Schema gelegt werden, und die mit dem Präfix DR$ beginnen. Nur ist es beim CTXCAT-Index genau eine-Tabelle ...
SQL> desc DR$SUCHE_IDX$I
 Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------

 DR$TOKEN                                  NOT NULL VARCHAR2(64)
 DR$TOKEN_TYPE                             NOT NULL NUMBER(3)
 DR$ROWID                                  NOT NULL ROWID
 DR$TOKEN_INFO                             NOT NULL RAW(2000)
 ALTERSANG                                 NOT NULL NUMBER(3)
 NOTE                                      NOT NULL NUMBER(1)
Und hier seht Ihr, dass der CTXCAT-Index wesentlich simpler aufgebaut ist als der CONTEXT-Index. So werden ROWIDs direkt verwendet (der CONTEXT-Index generiert die kompakteren DOCIDs). Die soeben erzeugten "Sub-Indexes" führen dazu, dass zusätzliche Spalten angelegt werden. Das alles führt dazu, dass CTXCAT-Indizes recht viel Platz verbrauchen - die ROWIDs belegen 10 Byte und auch die Werte der zusätzlichen Spalten werden für jedes Wort (Token) dupliziert. Es ist einleuchtend, warum dieser Index nur für kleine Textfragmente geeignet ist - bei großen Dokumenten würde er schlicht explodieren ...
Hierzu ein kleiner Test. Wir haben eine Tabelle mit ca. 300.000 kleineren Texten (ein CTXCAT-Index kommt also in Frage). Die Tabelle ist (festgestellt mit diesem Skript) ca. 30MB groß.
select * from table(get_space_info('TEST_CTXCAT'));

SEGMENT_NAME                     COLUMN_NAME                    PARTITION_NAME                   SEGMENT_TYPE                     ALLOC_BYTES FREE_BYTES
-------------------------------- ------------------------------ -------------------------------- -------------------------------- ----------- ----------
TEST_CTXCAT                                                                                      TABLE                               31457280      65536

1 Zeile wurde ausgewählt.
Auf diese Tabelle legen wir nun einen CTXCAT-Index und einen CONTEXT-Index und stellen auch hierfür die jeweilige Größe fest. Zunächst der CTXCAT-Index (er besteht nur aus einer einzigen "DR$"-Tabelle.
select
  segment_name, 
  column_name, 
  segment_type, 
  alloc_bytes / 1048576 alloc_mb, 
  free_bytes / 1048576 free_mb 
from table(get_space_info('DR$IDX_TEST_CTXCAT$I'))

SEGMENT_NAME                     COLUMN_NAME                              SEGMENT_TYPE                     ALLOC_MB  FREE_MB
-------------------------------- ---------------------------------------- -------------------------------- -------- --------
DR$IDX_TEST_CTXCAT$I                                                      TABLE                               80,00     0,00
DR$IDX_TEST_CTXCAT$R             DR$ROWID                                 INDEX                               62,00     0,56
DR$IDX_TEST_CTXCAT$X             DR$TOKEN,DR$TOKEN_TYPE,DR$ROWID          INDEX                               59,00     0,14
Das wären also ca. 200MB für den CTXCAT-Index. Schauen wir uns den CONTEXT-Index an. Hierfür muss die GET_SPACE_INFO-Funktion für alle zum CONTEXT-Index gehörenden DR$-Tabellen ausgeführt werden - das wären die $I, die $N, die $R und die $K-Tabelle.
TABLE SEGMENT_NAME                     COLUMN_NAME                              SEGMENT_TYPE                     ALLOC_MB  FREE_MB
----- -------------------------------- ---------------------------------------- -------------------------------- -------- --------
$I    DR$IDX_TEST_CONTEXT$I                                                     TABLE                               11,00     0,95  
$I    SYS_LOB0000225699C00006$$        TOKEN_INFO                               LOBSEGMENT                           0,06     0,00
$I    DR$IDX_TEST_CONTEXT$X            TOKEN_TEXT,TOKEN_TYPE,TOKEN_FIRST, ...   INDEX                                0,19     0,03
$I    SYS_IL0000225699C00006$$                                                  LOBINDEX                             0,06     0,03

$N    SYS_IOT_TOP_225707               NLT_DOCID                                INDEX                                0,06     0,03

$R    DR$IDX_TEST_CONTEXT$R                                                     TABLE                                0,06     0,00
$R    SYS_LOB0000225704C00002$$        DATA                                     LOBSEGMENT                           6,00     0,13
$R    SYS_IL0000225704C00002$$                                                  LOBINDEX                             0,06     0,00

$K    SYS_IOT_TOP_225702               TEXTKEY                                  INDEX                                9,00     0,59
Zusammengezählt in etwa 25MB. Alle linguistischen Einstellungen Stopwortlisten, Lexer sind identisch. Man sieht hier sehr deutlich den Unterschied.
Wann ist also ein CTXCAT-Index sinnvoll ... ? Naja, vor allem dann, wenn Ihr kleine Datenmengen in der Tabellenspalte habt, auf einen ständig synchronen Volltextindex Wert legt und Mixed Queries benötigt. Letzteres ist ab Oracle11g allerdings auch mit dem CONTEXT Index möglich (Composite Domain Index). Wenn der Text aber in mehreren Spalten ist oder die Textfragmente größer werden, dann werden CTXCAT-Indizes schnell zu groß. Ein CONTEXT-Index mit Multi-Column Datastore wesentlich kompakter und effizienter. Der CTXCAT-Index ist halt, wie eingangs gesagt, der "kleine Bruder" des CONTEXT Index und eignet sich nur für bestimmte Fälle ...

Montag, 2. November 2009

Hochverfügbarer Textindex mit Schattenindex-Technologie

Heutzutage müssen auch Anwendungen mit Oracle Textindizes hochverfügbar sein. Allerdings kann schon ein REBUILD des Index dazu führen, dass der Index nicht mehr online zur Verfügung steht. Ein Neuaufbau des Index könnte z.B. aus mehreren Gründen nötig werden:
  • Spezielle Index-Preferences bzw. -Optionen wie Lexereinstellungen sollen geändert werden oder Stoppwörter hinzugefügt werden.
  • Aus Maintenance- und Performance-Gründen soll der Index neu aufgebaut werden - um z.B. eine stark vergrößerte $I Tabelle zu optimieren.
Ein REBUILD des Index ist bis einschliesslich Oracle Database 10g nicht immer vollständig online möglich. Eine mögliche Lösung wäre einen zusätzlichen zweiten Schatten-Index manuell zu erzeugen und diesen mitzupflegen. Die Tabelle könnte dabei folgendermassen aussehen:

CREATE TABLE mytable (text CLOB, dummy1 CHAR, dummy2 CHAR);

Da nur ein einziger Textindex auf einer Spalte möglich ist, wird die Indizierung über den Einsatz von USER_DATA_STORE bzw. MULTI_COLUMN_DATATORE gelöst. Diese erlauben es, einen Index auf der Spalte DUMMY1 zu erzeugen, aber die aktuellen zu indizierenden Daten werden von einer anderen Spalte z.B. TEXT geliefert. Die Abfragen sehen also folgendermassen aus:

... WHERE CONTAINS (dummy1, '') > 0

Im Falle eines Neuaufbaus steht die Spalte DUMMY2 zur Verfügung, die ihre Daten ebenfalls aus der Spalte TEXT erhält. Falls der Aufbau beendet ist, müssen die Queries folgendermassen geändert werden:

... WHERE CONTAINS (dummy2, '') > 0

In Oracle Database 11g ist es nun möglich, ein vollständiges REBUILD online durchzuführen - entweder in einem Ein-Schritt-Verfahren oder um mehr Kontrolle zu gewährleisten bzw. falls Partitionen verwendet werden als mehrstufiger Prozess. Die Idee dabei ist der Einsatz eines Schatten-Index (auch Shadow Index), der parallel zum urspünglichen Indexaufbau mitgeführt wird. Um die Nutzung an einem Beispiel zu demonstrieren, nehmen wir folgenden Index, der auf eine Tabelle SEC_TABLE und der Spalte TEXT erzeugt wurde als Grundlage.

CREATE INDEX sec_ind ON sec_table(text) INDEXTYPE IS ctxsys.context PARALLEL 4;
SELECT * FROM ctx_user_index_errors;
no rows selected

Nun wollen wir eine zusätzliche Spalte für Sprachen ergänzen und den folgenden Multi-Lexer nutzen.

execute ctx_ddl.create_preference('en_lx','basic_lexer');
execute ctx_ddl.set_attribute('en_lx','index_themes','yes');
execute ctx_ddl.create_preference('f_lx','basic_lexer');
execute ctx_ddl.set_attribute('f_lx','base_letter','yes');
execute ctx_ddl.create_preference('d_lx','basic_lexer');
execute ctx_ddl.set_attribute('d_lx','composite','german');
execute ctx_ddl.set_attribute('d_lx','mixed_case','yes');
execute ctx_ddl.set_attribute('d_lx','alternate_spelling','german');
begin
ctx_ddl.create_preference('multi_lx','multi_lexer');
ctx_ddl.add_sub_lexer('multi_lx','german','d_lx');
ctx_ddl.add_sub_lexer('multi_lx','french','f_lx');
ctx_ddl.add_sub_lexer('multi_lx','default','en_lx');
end;
PL/SQL procedure successfully completed.

ALTER TABLE sec_table ADD (lang varchar2(10) default 'de');

Nun erzeugen wir den Schattenindex in einem Einschrittverfahren. Laut Syntax ist zwar die Angabe einer DOP (Degree Of Parallelism) möglich, leider aber noch nicht im aktuellen Release verwendbar. Die Dokumentation gibt dazu folgenden Hinweis: "Reserved for future use. Specify the degree of parallelism. Parallel operation is not supported in the current release."

SQL> execute CTX_DDL.RECREATE_INDEX_ONLINE('SEC_IND','REPLACE LEXER multi_lx language column lang');
PL/SQL procedure successfully completed.

Schaut man zwischendurch in CTX_USER_INDEXES kann man den Aufbau des temporären Index (siehe Präfix RIO) monitoren.

SQL> SELECT idx_name, idx_table, idx_status FROM ctx_user_indexes;
IDX_NAME                       IDX_TABLE                      IDX_STATUS
------------------------------ ------------------------------ ------------
BASIC_IND1                     BASIC_TABLE1                   INDEXED
COMPRESS_IND                   COMPRESS_TABLE                 INDEXED
RIO$1880                       SEC_TABLE                      POPULATE
SEC_IND                        SEC_TABLE                      INDEXED

Nach Aufbau der Schattenindexstruktur erfolgt automatisch ein Austausch (auch Exchange). Textabfragen sind zu jedem Zeitpunkt möglich und dabei ist kein Eingriff in die Applikationen notwendig. Überprüft man zum Schluss die Struktur, kann man die Veränderungen beispielsweise mit CTX_REPORT.CREATE_INDEX_SCRIPT feststellen.

SQL> set long 10000
SQL> set pagesize 1000
SQL> SELECT ctx_report.create_index_script('SEC_IND') FROM dual;

CTX_REPORT.CREATE_INDEX_SCRIPT('SEC_IND')
--------------------------------------------------------------------------------
begin
  ctx_ddl.create_preference('"SEC_IND_DST"','DIRECT_DATASTORE');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_FIL"','NULL_FILTER');
end;
/
begin
  ctx_ddl.create_section_group('"SEC_IND_SGP"','NULL_SECTION_GROUP');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_LEX"','MULTI_LEXER');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_L00"','BASIC_LEXER');
  ctx_ddl.set_attribute('"SEC_IND_L00"','INDEX_THEMES','YES');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_LF"','BASIC_LEXER');
  ctx_ddl.set_attribute('"SEC_IND_LF"','BASE_LETTER','YES');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_LD"','BASIC_LEXER');
  ctx_ddl.set_attribute('"SEC_IND_LD"','COMPOSITE','GERMAN');
  ctx_ddl.set_attribute('"SEC_IND_LD"','MIXED_CASE','YES');
  ctx_ddl.set_attribute('"SEC_IND_LD"','ALTERNATE_SPELLING','GERMAN');
end;
/
begin
  ctx_ddl.add_sub_lexer('"SEC_IND_LEX"','DEFAULT','"SEC_IND_L00"');
  ctx_ddl.add_sub_lexer('"SEC_IND_LEX"','FRENCH','"SEC_IND_LF"');
  ctx_ddl.add_sub_lexer('"SEC_IND_LEX"','GERMAN','"SEC_IND_LD"');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_WDL"','BASIC_WORDLIST');
  ctx_ddl.set_attribute('"SEC_IND_WDL"','STEMMER','ENGLISH');
  ctx_ddl.set_attribute('"SEC_IND_WDL"','FUZZY_MATCH','GENERIC');
end;
/
begin
  ctx_ddl.create_stoplist('"SEC_IND_SPL"','BASIC_STOPLIST');
...

Möchte man das Ganze kontrolliert in einem 2 Schrittverfahren durchführen, kann man die folgenden beiden Prozeduren verwenden. Auch hier ist noch (im aktuellen Release) keine Parallelisierung möglich.

execute CTX_DDL.CREATE_SHADOW_INDEX (idx_name=>'SEC_IND',parameter_string=>'REPLACE LEXER multi_lx language column lang');
execute CTX_DDL.EXCHANGE_SHADOW_INDEX(idx_name=>'SEC_IND');

Um den Schattenindex zu löschen, kann man die spezielle Prozedur CTX_DDL.DROP_SHADOW_INDEX verwenden. Mehr Tipps und Tricks in einem der nächsten Blogs.....

Beliebte Postings